[Oracle] 관리자 9장 2 시스템 권한 할당(grant)과 해제(revoke)

GRANT <시스템 권한>,<시스템 권한> .......

TO [<user명 | role명>,<user명 | role명> ...... | PUBLIC];

[WITH ADMIN OPTION];

• User 또는 role에 시스템 권한을 할당한다.
• PUBLIC : DB의 모든 user에게 권한을 할당한다.
• WITH ADMIN OPTION : 할당된 권한을 SYS 관리자와 동일하게 할당하거나 해제할 수 있다.

REVOKE <시스템 권한>,<시스템 권한> .......

FROM [<user명 | role명>,<user명 | role명> ...... | PUBLIC];

• User 또는 role에 시스템 권한을 해제한다.

SELECT grantee, privilege, admin_option FROM dba_sys_privs

WHERE grantee in ('U1','U2','U3')

ORDER BY 1;

GRANT create any table TO u1;

GRANT create sequence TO u1

WITH ADMIN OPTION;

GRANT create table TO u2;

SELECT grantee, privilege, admin_option FROM dba_sys_privs

WHERE grantee in ('U1','U2','U3')

ORDER BY 1;

CONN u1/u1

CREATE TABLE u3.test (no NUMBER);

CONN u3/u3

SELECT * FROM tab;

GRANT create sequence TO u2

WITH ADMIN OPTION;

CONN u2/u2

GRANT create sequence TO u3;

CONN / AS sysdba

SELECT grantee, privilege, admin_option FROM dba_sys_privs

WHERE grantee in ('U1','U2','U3') and privilege = 'CREATE SEQUENCE'

ORDER BY 1;

CONN u2/u2

REVOKE create sequence FROM u1;

CONN / AS sysdba;

SELECT grantee, privilege, admin_option FROM dba_sys_privs

WHERE grantee in ('U1','U2','U3') and privilege = 'CREATE SEQUENCE'

ORDER BY 1;

SELECT grantee, privilege, admin_option FROM dba_sys_privs

WHERE grantee in ('U1','U2','U3')

ORDER BY 1;

REVOKE create any table, create table, create sequence FROM PUBLIC;

REVOKE create any table FROM u1;

REVOKE create table FROM u2;

REVOKE create sequence FROM u2,u3;

SELECT grantee, privilege, admin_option FROM dba_sys_privs

WHERE grantee in ('U1','U2','U3')

ORDER BY 1;