[DB] ORACLE USER 조회, ROLE, PROFILE

회사에서 새로오신 분들이 있거나 발령나서 오신분들이 계시다면 db 접속 할 수 있는 권한을 부여해야한다.

DBA가 된다면 사람들이 하는 말이 있다.

엔터를 누르기 전에 다시 한번 생각해보고 눌러라

그러므로 DBA_USERS , ALL_USERS를 통해서 이미 user가 생성되어 있는지, 권한은 어떻게 주어야하는지를 미리 확인을 해보고 생성하면 좋다.

 select * from DBA_USERS;

User 계정 생성 및 권한 부여

create user U1234 identified by "U1234" profile 프로파일_이름;
grant connect to U1234;

User 삭제
: cascade를 하면 유저 뿐 아니라 관련된 프로파일 및 role 도 삭제가 된다!

drop user U1234 cascade;

ROLL 조회

# User 권한 조회 
select * from dba_role_privs where grantee = '유저명';

계정을 생성할 때 끝에 profile 프로파일_이름 을 추가하는 것을 확인할 수 있다.

Profile

Profile 이란, oracle DB를 사용함에 따르는 암호, pw 와 리소스에 대한 제한을 주기 위해 생성하는 하나의 오브젝트로서 일단 생성해 놓은 후 오라클 사용자에게 DB 서버자원, 암호에 대한 설정을 그룹으로 만들어 놓고 사용자에게 할당한다.

# 프로파일 종류 조회
select distince profile from DBA_PROFILES;

# 각 프로파일에 정의된 설정값 확인
select * from dba_profiles order by resource_type;

# 각 user에게 할당된 profile 조회
Select username,profile from dba_users;

# 어떠한 프로파일의 자원, 암호, 설정값 확인
select * from dba_profiles where profile = '프로파일_이름'; 

가끔 유저 계정 점검을 해야할 때도 있다. 우리 회사는 3개월마다 비밀번호를 초기화하여 바꾸고 5번 이상 입력 실패하면 초기화 된다.

SELECT 'ORACLE' as "DB_TYPE",
       (SELECT HOST_NAME FROM V$INSTANCE) as "HOST",
       (SELECT INSTANCE_NAME FROM V$INSTANCE) as "SID",
       AB.USERNAME as "USERNAME",
       AB.PROFILE as "PROFILE",
       MAX(LIFE) as "패스워드변경주기",
       MAX(ATTEMPTS) as "패스워드샐패횟수"
       AB.EXPIRY_DATE as "expiry_date"
       TO_CHAR(AB.CREATED, 'YYYY-MM-DD HH24:MI:SS') as "created"
FROM (
   A.USERNAME AS "USERNAME",
   A.ACCOUNT_STATUS as "account status",
   A.EXPIRY_DATE as "expiry date",
   A.CREATED as "created",
   CASE WHEN B.RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION' THEN B.LIMIT END AS "PWD",
   CASE WHEN B.RESOURCE_NAME='PASSWORD_LIFE_TIME' THEN B.LIMIT END AS "LIFE",
   CASE WHEN B.RESOURCE_NAME='FAILED_LOGIN_ATTEMPTS' THEN B.LIMIT END AS "LIFE"
FROM DBA_USERS A, DBA_PROFILES B
WHERE A.PROFILE = B.PROFILE
 AND B.RESOURCE_NAME IN ('PASSWORD_VERIFY_FUNCTION','PASSWORD_LIFE_TIME','FAILED_LOGIN_ATTEMPTS')
)AB
GROUP BY AB.USERNAME, AB.ACCOUNT_STATUS,AB.PROFILE, AB.EXPIRY_DATE, AB.CREATED
ORDER BY PROFILE, USERNAME;

user lock

USER 의 lock 상태를 검사하는 쿼리이다.

SELECT USERNAME, USER_ID, ACCOUNT_STATUS, LOCK_DATE
FROM DBA_USERS
ORDER BY USER_ID;

사용하지 않는 user는 lock을 걸을 수 있다.

# LOCK 걸기
ALTER USER [user name] ACCOUNT LOCK;

# Lock 해제
ALTER USER [user name] ACCOUNT UNLOCK;

실무에서는 안쓰는 유저를 바로 삭제하지 않고, 일단 lock을 걸어놓고 일주일 정도 지켜본 후 아무도 접속하지 않으면 그때 지우기도 한다.