Oracle/Database

권한 관리 (grant , revoke)

닥터 후 2010. 8. 4. 16:32

Oracle 은 아래와 같이 기본적으로 권한이 포함된 Role 을 정의하고 있다.

ROLE PRIVILEGE ADMIN_OPTION
CONNECT ALTER SESSION NO
CONNECT CREATE CLUSTER NO
CONNECT CREATE DATABASE LINK NO
CONNECT CREATE SEQUENCE NO
CONNECT CREATE SESSION NO
CONNECT CREATE SYNONYM NO
CONNECT CREATE TABLE NO
CONNECT CREATE VIEW NO
DBA ADMINISTER ANY SQL TUNING SET YES
DBA ADMINISTER DATABASE TRIGGER YES
DBA ADMINISTER RESOURCE MANAGER YES
DBA ADMINISTER SQL TUNING SET YES
DBA ADVISOR YES
DBA ALTER ANY CLUSTER YES
DBA ALTER ANY DIMENSION YES
DBA ALTER ANY EVALUATION CONTEXT YES
DBA ALTER ANY INDEX YES
DBA ALTER ANY INDEXTYPE YES
DBA ALTER ANY LIBRARY YES
DBA ALTER ANY MATERIALIZED VIEW YES
DBA ALTER ANY OUTLINE YES
DBA ALTER ANY PROCEDURE YES
DBA ALTER ANY ROLE YES
DBA ALTER ANY RULE YES
DBA ALTER ANY RULE SET YES
DBA ALTER ANY SEQUENCE YES
DBA ALTER ANY SQL PROFILE YES
DBA ALTER ANY TABLE YES
DBA ALTER ANY TRIGGER YES
DBA ALTER ANY TYPE YES
DBA ALTER DATABASE YES
DBA ALTER PROFILE YES
DBA ALTER RESOURCE COST YES
DBA ALTER ROLLBACK SEGMENT YES
DBA ALTER SESSION YES
DBA ALTER SYSTEM YES
DBA ALTER TABLESPACE YES
DBA ALTER USER YES
DBA ANALYZE ANY YES
DBA ANALYZE ANY DICTIONARY YES
DBA AUDIT ANY YES
DBA AUDIT SYSTEM YES
DBA BACKUP ANY TABLE YES
DBA BECOME USER YES
DBA CHANGE NOTIFICATION YES
DBA COMMENT ANY TABLE YES
DBA CREATE ANY CLUSTER YES
DBA CREATE ANY CONTEXT YES
DBA CREATE ANY DIMENSION YES
DBA CREATE ANY DIRECTORY YES
DBA CREATE ANY EVALUATION CONTEXT YES
DBA CREATE ANY INDEX YES
DBA CREATE ANY INDEXTYPE YES
DBA CREATE ANY JOB YES
DBA CREATE ANY LIBRARY YES
DBA CREATE ANY MATERIALIZED VIEW YES
DBA CREATE ANY OPERATOR YES
DBA CREATE ANY OUTLINE YES
DBA CREATE ANY PROCEDURE YES
DBA CREATE ANY RULE YES
DBA CREATE ANY RULE SET YES
DBA CREATE ANY SEQUENCE YES
DBA CREATE ANY SQL PROFILE YES
DBA CREATE ANY SYNONYM YES
DBA CREATE ANY TABLE YES
DBA CREATE ANY TRIGGER YES
DBA CREATE ANY TYPE YES
DBA CREATE ANY VIEW YES
DBA CREATE CLUSTER YES
DBA CREATE DATABASE LINK YES
DBA CREATE DIMENSION YES
DBA CREATE EVALUATION CONTEXT YES
DBA CREATE EXTERNAL JOB YES
DBA CREATE INDEXTYPE YES
DBA CREATE JOB YES
DBA CREATE LIBRARY YES
DBA CREATE MATERIALIZED VIEW YES
DBA CREATE OPERATOR YES
DBA CREATE PROCEDURE YES
DBA CREATE PROFILE YES
DBA CREATE PUBLIC DATABASE LINK YES
DBA CREATE PUBLIC SYNONYM YES
DBA CREATE ROLE YES
DBA CREATE ROLLBACK SEGMENT YES
DBA CREATE RULE YES
DBA CREATE RULE SET YES
DBA CREATE SEQUENCE YES
DBA CREATE SESSION YES
DBA CREATE SYNONYM YES
DBA CREATE TABLE YES
DBA CREATE TABLESPACE YES
DBA CREATE TRIGGER YES
DBA CREATE TYPE YES
DBA CREATE USER YES
DBA CREATE VIEW YES
DBA DEBUG ANY PROCEDURE YES
DBA DEBUG CONNECT SESSION YES
DBA DELETE ANY TABLE YES
DBA DEQUEUE ANY QUEUE YES
DBA DROP ANY CLUSTER YES
DBA DROP ANY CONTEXT YES
DBA DROP ANY DIMENSION YES
DBA DROP ANY DIRECTORY YES
DBA DROP ANY EVALUATION CONTEXT YES
DBA DROP ANY INDEX YES
DBA DROP ANY INDEXTYPE YES
DBA DROP ANY LIBRARY YES
DBA DROP ANY MATERIALIZED VIEW YES
DBA DROP ANY OPERATOR YES
DBA DROP ANY OUTLINE YES
DBA DROP ANY PROCEDURE YES
DBA DROP ANY ROLE YES
DBA DROP ANY RULE YES
DBA DROP ANY RULE SET YES
DBA DROP ANY SEQUENCE YES
DBA DROP ANY SQL PROFILE YES
DBA DROP ANY SYNONYM YES
DBA DROP ANY TABLE YES
DBA DROP ANY TRIGGER YES
DBA DROP ANY TYPE YES
DBA DROP ANY VIEW YES
DBA DROP PROFILE YES
DBA DROP PUBLIC DATABASE LINK YES
DBA DROP PUBLIC SYNONYM YES
DBA DROP ROLLBACK SEGMENT YES
DBA DROP TABLESPACE YES
DBA DROP USER YES
DBA ENQUEUE ANY QUEUE YES
DBA EXECUTE ANY CLASS YES
DBA EXECUTE ANY EVALUATION CONTEXT YES
DBA EXECUTE ANY INDEXTYPE YES
DBA EXECUTE ANY LIBRARY YES
DBA EXECUTE ANY OPERATOR YES
DBA EXECUTE ANY PROCEDURE YES
DBA EXECUTE ANY PROGRAM YES
DBA EXECUTE ANY RULE YES
DBA EXECUTE ANY RULE SET YES
DBA EXECUTE ANY TYPE YES
DBA EXPORT FULL DATABASE YES
DBA FLASHBACK ANY TABLE YES
DBA FORCE ANY TRANSACTION YES
DBA FORCE TRANSACTION YES
DBA GLOBAL QUERY REWRITE YES
DBA GRANT ANY OBJECT PRIVILEGE YES
DBA GRANT ANY PRIVILEGE YES
DBA GRANT ANY ROLE YES
DBA IMPORT FULL DATABASE YES
DBA INSERT ANY TABLE YES
DBA LOCK ANY TABLE YES
DBA MANAGE ANY FILE GROUP YES
DBA MANAGE ANY QUEUE YES
DBA MANAGE FILE GROUP YES
DBA MANAGE SCHEDULER YES
DBA MANAGE TABLESPACE YES
DBA MERGE ANY VIEW YES
DBA ON COMMIT REFRESH YES
DBA QUERY REWRITE YES
DBA READ ANY FILE GROUP YES
DBA RESTRICTED SESSION YES
DBA RESUMABLE YES
DBA SELECT ANY DICTIONARY YES
DBA SELECT ANY SEQUENCE YES
DBA SELECT ANY TABLE YES
DBA SELECT ANY TRANSACTION YES
DBA UNDER ANY TABLE YES
DBA UNDER ANY TYPE YES
DBA UNDER ANY VIEW YES
DBA UPDATE ANY TABLE YES
RESOURCE CREATE CLUSTER NO
RESOURCE CREATE DATABASE LINK NO
RESOURCE CREATE INDEXTYPE NO
RESOURCE CREATE OPERATOR NO
RESOURCE CREATE PROCEDURE NO
RESOURCE CREATE SEQUENCE NO
RESOURCE CREATE SYNONYM NO
RESOURCE CREATE TABLE NO
RESOURCE CREATE TRIGGER NO
RESOURCE CREATE TYPE NO
RESOURCE CREATE VIEW NO

* DB 버전에 따라 다를 수 있다.

 ☞  부연 설명
  • $ORACLE_HOME/rdbms/admin/sql.bsq 에 의해 생성
  • RESOURCE role 을 부여받은 사용자는 UNLIMITED TABLESPACE시스템 권한도 명시적으로 부여받습니다. (UNLIMITED TABLESPACE시스템 권한은 RESOURCE롤의 일부가 아닙니다.)
  • DBA role 을 부여받은 사용자는 ADMIN OPTION과 함께 UNLIMITED TABLESPACE시스템 권한도 명시적으로 부여받습니다. (ADMIN OPTION과 함께 사용하는 UNLIMITED TABLESPACE시스템 권한은 DBA role 의 일부가 아닙니다.)
  • catexp.sql 을 실행하면 EXP_FULL_DATABASE 와 IMP_FULL_DATABASE role 도 포함됩니다.


개체 권한과 롤 부여

다음 명령문은 EMP테이블의 모든 열에 대한 SELECT, INSERT, DELETE 개체 권한을 사용자 JFEE와 TSMITH에게 부여합니다.
  
SQL> GRANT SELECT, INSERT, DELETE ON EMP TO JFEE, JSMITH;

사용자 JFEE와 TSMITH에게 EMP테이블의 ENAME과 JOB열에 대해서만 INSERT개체 권한을 부여하려면 다음 명령문을 수행합니다.
  
SQL> GRANT insert( ename, job ) ON emp TO jfee, tsmith;

사용자 JFEE에게 SALARY뷰에 대해 모든 개체 권한을 부여하려면 다음 예처럼 ALL단축키를 사용합니다.

SQL>

GRANT ALL ON salary TO jfee;


권한 또는 ROLE 조회

dba_role_privs , dba_sys_privs
dba_tab_privs , dba_col_privs

그 외...

 ALL / USER / DBA _COL_PRIVS
 ALL / USER _COL_PRIVS_MADE
 ALL / USER _COL_PRIVS_RECD
 ALL / USER / DBA _TAB_PRIVS
 ALL / USER _TAB_PRIVS_MADE
 ALL / USER _TAB_PRIVS_RECD
 DBA_ROLES
 USER / DBA _ROLE_PRIVS
 USER / DBA _SYS_PRIVS
 COLUMN_PRIVILEGES
 ROLE_ROLE_PRIVS, ROLE_SYS_PRIVS, ROLE_TAB_PRIVS
 SESSION_PRIVS, SESSION_ROLES

10g 이후 resource role 의 권한이 축소 되었다. 필요에 따라 아래와 같이 특정 권한을 resource role 에 부여하여 정의할 수 있다.

SQL> grant  create database link , create synonym , create view to resource ;




특정 상황 권한 부여

상황 1) 모니터링용 권한 부여
SQL> grant SELECT ANY DICTIONARY to USER명;
SQL> SQL> grant SELECT ANY TABLE to USER명; ( 8i 이하 버전 )

상황 2) FUNCTION 실행 권한 부여
SQL> grant EXECUTE ANY PROCEDURE to USER명;

상황 3) 다른 USER 에게 권한을 줄 수 있는 권한 부여
SQL> grant GRANT ANY PRIVILEGE to USER명;
SQL> grant GRANT ANY OBJECT PRIVILEGE to USER명;

상황 4) 특정 USER 에게 특정 테이블스페이스만 사용할 수 있도록 권한 부여
SQL> revoke UNLIMITED TABLESPACE from USER명;
SQL> alter user USER명 quota unlimited on 테이블스페이스명;

상황 5) error message : ora-01720 grant option does not exist for XXX.XXX

cause: User A has a table AA
User B has a table BB
User C has a view CC on AA and BB and want to grant select a User D.
An error ORA-01720 is raised.
The "WITH GRANT OPTION" syntax is missing in the grant expression.
This option allows user C to grant select somebody else on tables where he is not the owner.

fix:
Users A and B must do : GRANT SELECT ON AA/BB TO C WITH GRANT OPTION;
User C must do        : GRANT SELECT ON CC TO D;




이젠 #샤P핑이 대세!!

  쇼팽도 놀랐다!

#샤P핑 하러 가자~~~~

 심심할 땐 #샤P핑의 세계로 - GoGo~

http://www.shapping.co.kr

저작자표시 동일조건 (새창열림)