오라클 권한(Privilege)과 Role(역할) 조회/생성/부여/회수

개요

구성환경

Oracle 12c

 

Role 이란

Role은 하나이상의 권한으로 이루어진 집합체

관련 뷰

- DBA_ROLE_PRIVS, USER_ROLE_PRIVS :  데이타베이스내에 모든사용자와 Role에 부여된 Role 기술

- DBA_SYS_PRIVS, USER_SYS_PRIVS :  사용자와 Role에 부여된 시스템 권한 기술

 

오라클 시스템 권한 종류

 1. 시스템 권한

 - create table, create view, select any table, create user, ....

 2. Role

 - resource, connect, dba ....

1. 오라클 시스템 권한 및 Role 부여 및 회수

Syntax) grant privilege to [user | role | PUBLIC] [WITH ADMIN OPTION]

- WITH ADMIN OPTION 이란 시스템 권한을 부여받은 자가 또 다른 USER(또는 ROLE)에게 부여 받은 시스템 권한을 부여할수 있는 권한, 즉 A가 SELECT ANY 시스템 권한을 부여받았으면 다른 B유저에게 SELECT ANY 권한을 부여 할수 있음, WITH ADMIN OPTION으로 권한 부여후 A사용자에게서 권한을 회수하면 B유저에게 부여된 권한도 회수됨

  1.1 권한 부여

SQL> grant resource,connect to hoya ; => Role 권한 부여 권한이 부여되었습니다.  SQL> grant select any table to hoya ; => 시스템 권한 부여 권한이 부여되었습니다. SQL>

  1.2 권한 회수

Syntax) REVOKE privilege from  [user | role | PUBLIC]

SQL> revoke resource,connect from hoya ;  권한이 취소되었습니다.  SQL>

2. 시스템 권한 조회

 2.1 사용자에게 부여된 시스템 권한 조회

  ** 사용자에게 부여되는 전체 권한은 시스템 권한과 ROLE 두개의 조합으로 만들어짐

SQL> select * from dba_sys_privs where grantee='HOYA' ; GRANTEE              PRIVILEGE            ADM COM INH -------------------- -------------------- --- --- --- HOYA                 UNLIMITED TABLESPACE NO  NO  NO HOYA                 SELECT ANY TABLE     NO  NO  NO SQL>

 2.2 현재 세션 사용자의 시스템 권한 조회

SQL> column username format a10  SQL> column privilege format a30  SQL>  select * from user_sys_privs ; USERNAME   PRIVILEGE            ADM COM INH ---------- -------------------- --- --- --- HOYA       SELECT ANY TABLE     NO  NO  NO HOYA       UNLIMITED TABLESPACE NO  NO  NO SQL>

 2.3 전체 시스템 권한 조회

SQL> select * from system_privilege_map ;   PRIVILEGE NAME                                       PROPERTY  ---------- ---------------------------------------- ----------          -3 ALTER SYSTEM                                      0          -4 AUDIT SYSTEM                                      0          -5 CREATE SESSION                                    0          -6 ALTER SESSION                                     0          -7 RESTRICTED SESSION                                0         -10 CREATE TABLESPACE                                 0         -11 ALTER TABLESPACE                                  0         -12 MANAGE TABLESPACE                                 0         -13 DROP TABLESPACE                                   0      =========== 중략 ============================       -406 DROP ANY HIERARCHY                                0        -407 CREATE ANALYTIC VIEW                              0   PRIVILEGE NAME                                       PROPERTY  ---------- ---------------------------------------- ----------        -408 CREATE ANY ANALYTIC VIEW                          0        -409 ALTER ANY ANALYTIC VIEW                           0        -410 DROP ANY ANALYTIC VIEW                            0  256 행이 선택되었습니다.  SQL>

 

3. Role 조회

SQL> grant resource,connect to hoya ;  권한이 부여되었습니다. SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'HOYA' ; => hoya사용자에게 부여된 Role  GRANTEE    GRANTED_ROLE                   ADM DEL DEF COM INH ---------- ------------------------------ --- --- --- --- --- HOYA       CONNECT                        NO  NO  YES NO  NO HOYA       RESOURCE                       NO  NO  YES NO  NO SQL>

 

4. Role에 부여된 시스템 권한 조회

SQL>  SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE' ; => resource role에 부여된 권한 GRANTEE    PRIVILEGE            ADM COM INH ---------- -------------------- --- --- --- RESOURCE   CREATE TABLE         NO  YES YES RESOURCE   CREATE OPERATOR      NO  YES YES RESOURCE   CREATE TYPE          NO  YES YES RESOURCE   CREATE CLUSTER       NO  YES YES RESOURCE   CREATE TRIGGER       NO  YES YES RESOURCE   CREATE INDEXTYPE     NO  YES YES RESOURCE   CREATE PROCEDURE     NO  YES YES RESOURCE   CREATE SEQUENCE      NO  YES YES 8 행이 선택되었습니다. SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='CONNECT' ; connect role에 부여된 권한 GRANTEE              PRIVILEGE            ADM COM INH -------------------- -------------------- --- --- --- CONNECT              SET CONTAINER        NO  YES YES CONNECT              CREATE SESSION       NO  YES YES SQL>

 

5. Role에 시스템 권한 부여 및 회수 

Syntax) GRANT PRIVILEGE  to RoleName ;

SQL> grant create view to resource ;  => view 테이블을 생성할수 있는 시스템 권한을 resource 롤에 부여 권한이 부여되었습니다. SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE' ; GRANTEE    PRIVILEGE            ADM COM INH ---------- -------------------- --- --- --- RESOURCE   CREATE VIEW          NO  NO  NO RESOURCE   CREATE TABLE         NO  YES YES RESOURCE   CREATE OPERATOR      NO  YES YES RESOURCE   CREATE TYPE          NO  YES YES RESOURCE   CREATE CLUSTER       NO  YES YES RESOURCE   CREATE TRIGGER       NO  YES YES RESOURCE   CREATE INDEXTYPE     NO  YES YES RESOURCE   CREATE PROCEDURE     NO  YES YES RESOURCE   CREATE SEQUENCE      NO  YES YES 9 행이 선택되었습니다. SQL> revoke create view from resource ; 권한이 취소되었습니다. SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='RESOURCE' ; GRANTEE    PRIVILEGE            ADM COM INH ---------- -------------------- --- --- --- RESOURCE   CREATE TABLE         NO  YES YES RESOURCE   CREATE OPERATOR      NO  YES YES RESOURCE   CREATE TYPE          NO  YES YES RESOURCE   CREATE CLUSTER       NO  YES YES RESOURCE   CREATE TRIGGER       NO  YES YES RESOURCE   CREATE INDEXTYPE     NO  YES YES RESOURCE   CREATE PROCEDURE     NO  YES YES RESOURCE   CREATE SEQUENCE      NO  YES YES 8 행이 선택되었습니다. SQL>

 

6. Role 생성/부여/조회

Syntax) CREATE ROLE ROLE_NAME

SQL> create role create_synonym_view ;   => create_synonym_view ROLE 생성 롤이 생성되었습니다. SQL> grant create synonym, create view to create_synonym_view ;  => ROLE(create_synonym_view) 에 synonym, view 테이블 생성 권한 부여 권한이 부여되었습니다. SQL> select grantee,granted_role from dba_role_privs where granted_role='CREATE_SYNONYM_VIEW' ; => ROLE 조회 GRANTEE    GRANTED_ROLE ---------- ------------------------------ SYS        CREATE_SYNONYM_VIEW SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='CREATE_SYNONYM_VIEW' ; => Role에 부여된 권한 조회 GRANTEE              PRIVILEGE            ADM COM INH -------------------- -------------------- --- --- --- CREATE_SYNONYM_VIEW  CREATE VIEW          NO  NO  NO CREATE_SYNONYM_VIEW  CREATE SYNONYM       NO  NO  NO SQL> GRANT CREATE_SYNONYM_VIEW to hoya ;  => 생성된 Role을 사용자(hoya) 에게 부여 권한이 부여되었습니다. SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'HOYA' ;  => 사용자에게 부여된 Role 조회 GRANTEE              GRANTED_ROLE                   ADM DEL DEF COM INH -------------------- ------------------------------ --- --- --- --- --- HOYA                 CONNECT                        NO  NO  YES NO  NO HOYA                 RESOURCE                       NO  NO  YES NO  NO HOYA                 CREATE_SYNONYM_VIEW            NO  NO  YES NO  NO ================================================================================ SQL> SELECT * FROM USER_ROLE_PRIVS ;  => 현재 세션 사용자에게 부여된 Role 조회 USERNAME   GRANTED_ROLE         ADM DEL DEF OS_ COM INH ---------- -------------------- --- --- --- --- --- --- HOYA       CONNECT              NO  NO  YES NO  NO  NO HOYA       CREATE_SYNONYM_VIEW  NO  NO  YES NO  NO  NO HOYA       RESOURCE             NO  NO  YES NO  NO  NO SQL>