[오라클] user, role, privilege 개념 (실습 스크립트 포함)

1. 권한은 사용자에게 부여될수도, 롤에 부여될수도 있습니다.

  - 예시: SYS(GRANTEE)유저는 DBA(GRANTED_ROLE)롤을 부여받습니다. DBA(ROLE)은 DBA_SYS_PRIVS 딕셔너리 뷰의 GRANTEE 컬럼에도 포함 --> 시스템 권한은 사용자에게 부여될수도, 롤에 부여될수도 있습니다.

  - DBA_SYS_PRIVS: 시스템 권한(PRIVILEGE 컬럼, 예: CREATE SESSION, SELECT ANY TABLE...)을 부여받은 유저(또는 롤, GRANTEE 컬럼) 정보 표시하는 딕셔너리 뷰.  

  - 참고: dba_sys_privs와 아래에 설명하는 dba_role_privs 모두 admin_option 컬럼 존재. 해당 옵션 yes일 경우 grantee가 다른 유저(롤)에 권한(롤) 부여할 수 있습니다. 

SELECT DISTINCT GRANTEE FROM DBA_SYS_PRIVS;
SELECT * FROM DBA_ROLE_PRIVS;
SELECT DISTINCT GRANTEE FROM DBA_ROLE_PRIVS;

  - DBA_ROLE_PRIVS: 롤(GRANTED_ROLE 컬럼, 예: CONNECT, DBA, DATAPUMP_EXP_FULL_DATABASE, EXP_FULL_DATABASE)을 부여받은 유저(또는 롤, GRANTEE 컬럼)의 정보를 표시하는 딕셔너리 뷰. 

 

2. 당연하게도 유저(또는 롤)는 롤을 부여받지 않고 시스템 권한만 부여받을 수 있습니다. 

  - 참고: CREATE SESSION 시스템 권한은 유저가 데이터베이스에 접속할 수 있게 해주는 권한입니다. 해당 권한을 받지 않은 유저를 다음과 같이 쿼리할 수 있습니다. 비슷하게 WHERE PRIVILEGE 절에 값을 찾고자하는 권한으로 바꿔 적용할 수 있습니다. 

-- create session 권한 받지 않은 유저 확인
SELECT GRANTEE FROM (SELECT DISTINCT GRANTEE FROM DBA_SYS_PRIVS dsp2 
MINUS
SELECT GRANTEE FROM DBA_SYS_PRIVS dsp WHERE PRIVILEGE = 'CREATE SESSION')
WHERE GRANTEE IN (SELECT USERNAME FROM ALL_USERS);

 

3. 하나의 유저가 2개 이상의 롤을 가질 수 있습니다. 

이것을 확인하기 위해 새로운 테스트 롤과 유저를 생성하고 직접 실습을 통해 확인합니다:

-- test - test_role_user1,test_role_user2생성 -> test_role1,test_role2 생성..  
-- 		  test_role1,test_role2에 권한 부여 -> 유저에 test_role1,,test_role2롤 부여 
GRANT CREATE SESSION TO test_role_user1 IDENTIFIED BY 1111;
GRANT CREATE SESSION TO test_role_user2 IDENTIFIED BY 1111;
CREATE ROLE test_role1;
CREATE ROLE test_role2;
GRANT CREATE TABLE, CREATE SESSION TO test_role1;
GRANT CREATE USER, SELECT ANY TABLE TO test_role2;
SELECT * FROM DBA_SYS_PRIVS dsp WHERE GRANTEE = 'TEST_ROLE1';  -- role에 부여된 권한 확인
GRANT test_role1 TO test_role_user1;
SELECT * FROM DBA_SYS_PRIVS dsp WHERE GRANTEE = 'TEST_ROLE_USER1';  -- 유저에게 (롤 통하지 않고) 부여된 시스템 권한 확인
SELECT * FROM DBA_ROLE_PRIVS drp WHERE GRANTEE = 'TEST_ROLE_USER1';  -- 유저에게 부여된 롤 확인 
GRANT test_role2 TO test_role_user1;  -- 동일 유저에게 다른 롤 부여 
SELECT * FROM DBA_SYS_PRIVS dsp WHERE GRANTEE = 'TEST_ROLE_USER1';  -- 유저에게 (롤 통하지 않고) 부여된 시스템 권한 확인
SELECT * FROM DBA_ROLE_PRIVS drp WHERE GRANTEE = 'TEST_ROLE_USER1';  -- 유저에게 부여된 롤 확인.. TEST_ROLE2 부여 확인 
DROP USER test_role_user1;

 

4. 위의 2번 항목에서 특정 시스템 권한에 대해 권한을 부여받은 유저를 확인해봤는데, 이와 유사하게 어떤 유저가 특정 롤을 부여받았는지와 해당 롤에 부여된 권한까지 조회할 수 있습니다:

-- 어떤 유저가 test_role1롤 부여받았는지, role에 부여된 시스템권한까지 확인 
SELECT dsp.grantee granted_role, dsp.PRIVILEGE, drp.ADMIN_OPTION, drp.grantee "GRANTEE" FROM DBA_SYS_PRIVS dsp, DBA_ROLE_PRIVS drp
WHERE dsp.GRANTEE = drp.GRANTED_ROLE 
AND dsp.GRANTEE = 'TEST_ROLE1';

-- 롤에 부여된 권한 제외하고, 특정 롤을 부여받은 유저만 조회 (GROUP BY)
SELECT drp.grantee "GRANTEE", dsp.GRANTEE granted_role, count(dsp.PRIVILEGE) FROM DBA_SYS_PRIVS dsp, DBA_ROLE_PRIVS drp
WHERE dsp.GRANTEE = drp.GRANTED_ROLE 
AND dsp.GRANTEE = 'TEST_ROLE1'
GROUP BY drp.grantee, dsp.GRANTEE ;

 

5. 롤도 유저와 마찬가지로 롤을 부여받을 수 있습니다. TEST_ROLE1을 TEST_ROLE2에 부여하는 예입니다:

SELECT * FROM DBA_SYS_PRIVS dsp WHERE GRANTEE = 'TEST_ROLE1';  -- role에 부여된 권한 확인
SELECT * FROM DBA_SYS_PRIVS dsp WHERE GRANTEE = 'TEST_ROLE2';  -- role에 부여된 권한 확인
GRANT test_role1 TO test_role2;  -- GRANT 성공 
SELECT * FROM DBA_SYS_PRIVS dsp WHERE GRANTEE = 'TEST_ROLE2';  -- role에 부여된 권한 확인.. ROLE 자체에는 변화 없음
SELECT dsp.grantee granted_role, dsp.PRIVILEGE, drp.ADMIN_OPTION, drp.grantee "GRANTEE" FROM DBA_SYS_PRIVS dsp, DBA_ROLE_PRIVS drp
WHERE dsp.GRANTEE = drp.GRANTED_ROLE 
AND dsp.GRANTEE = 'TEST_ROLE1';  -- test_role2가 정상적으로 test_role1을 부여받았음을 확인 가능

위에서 보듯 DBA_SYS_PRIVS에 GRANTEE 컬럼으로 WHERE 조건을 걸어서 특정 ROLE에 속한(부여된) 권한을 확인할 수있습니다. 

 

6. 앞에서도 보았듯이 DBA_SYS_PRIVS 딕셔너리 뷰의 GRANTEE 컬럼에는 권한을 부여받은 유저와 롤이 모두 표시됩니다. 원하는 유형만 보기위해서 다음과 같이 쿼리할 수 있습니다. 

  - 참고: a라는 이름을 가진 grantee는 반드시 유저명이거나 롤명 둘 중에 하나입니다. 유저명과 동일한 롤을 생성할 수 없습니다. ( https://stackoverflow.com/questions/40325185/how-to-identify-user-account-in-dba-sys-privs-table-in-oracle-database )

CREATE ROLE test_role_user1;  -- 유저명과 동일한 롤명을 만들수 없다. ora-01921 에러 발생
-- 유저만 조회
SELECT DISTINCT grantee FROM DBA_SYS_PRIVS dsp WHERE grantee IN (SELECT username FROM all_users); -- 30 ROWS
-- 롤만 조회 
SELECT DISTINCT grantee FROM DBA_SYS_PRIVS dsp WHERE grantee NOT IN (SELECT username FROM all_users); -- 31 ROWS, DATAPUMP_EXP_FULL_DATABASE 포함..

---

7. SYSDBA, SYSOPER 등은 DBA_SYS_PRIVS에서 확인할 수 없다. 대신

V$PWFILE 뷰에서 패스워드 파일 내 전체 유저와 해당 유저의 SYSDBA등 권한을 조회할 수 있다. 또한 DBA_SYS_PRIVS에서 PRIVILEGE에 LIKE를 걸어 ALTER SYSTEM등과 관련된 유저, 롤을 확인할 수는 있다. 

SELECT * FROM DBA_SYS_PRIVS WHERE PRIVILEGE LIKE '%SYSTEM' ORDER BY 1;
SELECT *  FROM V$PWFILE_USERS;

그래서 만약 어떤 유저에게 SYSDBA, SYSOPER등과 같은 권한을 주게되면 V$PWFILE_USERS 뷰에서 확인할 수 있다. 아래처럼 GRANT를 줘서 확인해보았다. 

GRANT CREATE SESSION TO TEST_SYSDBA_USER1 IDENTIFIED BY "1111";
SELECT * FROM "V_$PWFILE_USERS" vpu ;
GRANT SYSDBA TO TEST_SYSDBA_USER1;
SELECT * FROM "V_$PWFILE_USERS" vpu ;


SQL> conn test_sysdba_user1/1111 as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> conn test_sysdba_user1/1111
Connected.
SQL> show user
USER is "TEST_SYSDBA_USER1"