[8i] 권한 관리(OLN)

/********************************************************************************************
-- Title : [8i] 권한 관리(OLN)
-- Reference : OLN
-- Key word : privilege grant revoke
********************************************************************************************/

/****************************************************************************************
-- SYSDBA와 SYSOPER 권한
****************************************************************************************/
   SYSOPER : STARTUP
             SHUTDOWN
             ALTER DATABASE OPEN | MOUNT
             ALTER DATABASE BACKUP CONTROLFILE
             ALTER TABLESPACE BEGIN/END BACKUP
             RECOVER DATABASE
             ALTER DATABASE ARCHIVELOG
             RESTRICTED SESSION
            
   SYSDBA  : SYSOPER privileges WITH ADMIN OPTION
             CREATE DATABASE
             RECOVER DATABSE UNTIL

/****************************************************************************************
-- SYSDBA나 SYSOPER 권한을 부여받은 사용자 확인
****************************************************************************************/

SQL> SELECT * FROM v$pwfile_users;

USERNAME  SYSDB SYSOP
--------- ----- -----
INTERNAL  TRUE  TRUE
SYS       TRUE  TRUE

/****************************************************************************************
-- 사용자와 롤에 부여된 시스템 권한 보기
****************************************************************************************/

-- 데이터베이스 레벨 출력
SQL> SELECT * FROM dba_sys_privs
  2  WHERE grantee in ('SCOTT', 'SYSTEM')       
  3  ORDER BY grantee;

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
SCOTT                          UNLIMITED TABLESPACE                     NO
SYSTEM                         UNLIMITED TABLESPACE                     YES

-- 세션 레벨 출력
SQL> CONN scott/tiger
Connected.

SQL> SELECT * FROM session_privs;

PRIVILEGE
---------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

14 rows selected.

/****************************************************************************************
-- 오브젝트 권한
****************************************************************************************/

/****************************************************************************************
-- 사용자와 롤에 부여된 시스템 권한 보기
****************************************************************************************/

       
-- 테이블 레벨 오브젝트 권한 보기
SQL> SELECT *
  2  FROM dba_tab_privs
  3  WHERE grantee = 'MAPBAK';

GRANTEE       OWNER     TABLE_NAME    GRANTOR     PRIVILEGE      GRA
------------- --------- ------------- ----------- -------------- -----
MAPBAK        SYS       DBMS_PIPE     SYS         EXECUTE        NO

-- 컬럼 레벨 오브젝트 권한 보기
SQL> SELECT * FROM dba_col_privs
  2  WHERE owner = 'SCOTT';

GRANTEE     OWNER     TABLE_NAME     COLUMN_NAME     GRANTOR     PRIVILEGE     GRA
----------- --------- -------------- --------------- ----------- ------------- ---
MAPBAK2     SCOTT     EMP            ENAME           SCOTT       UPDATE        YES
MAPBAK2     SCOTT     EMP            SAL             SCOTT       UPDATE        YES

/****************************************************************************************
-- 시스템 권한 부여
****************************************************************************************/
SQL> GRANT CREATE SESSION, CREATE TABLE
  2  TO mapbak;

Grant succeeded.

SQL> create user mapbak2 identified by mp2128#;

User created.

SQL> GRANT CREATE SESSION          
  2  TO mapbak2
  3  WITH ADMIN OPTION;   -- WITH ADMIN OPTION : 권한 받은 사용자가 다른 사용자에게
                          -- 권한이나 롤을 부여/철회 할 수있도록 설정하는 옵션.
Grant succeeded.

/****************************************************************************************
-- 시스템 권한 철회
****************************************************************************************/
SQL> REVOKE CREATE TABLE
  2  FROM mapbak;

Revoke succeeded.

SQL> REVOKE CREATE SESSION           
  2  FROM mapbak2;

Revoke succeeded.

/****************************************************************************************
-- 오브젝트 권한 부여
****************************************************************************************/
SQL> GRANT EXECUTE ON dbms_pipe 
  2  TO mapbak;

Grant succeeded.

SQL> CONN scott/tiger
Connected.

SQL> GRANT UPDATE(ename, sal) ON emp
  2  TO mapbak2 WITH GRANT OPTION;
 
Grant succeeded.

/****************************************************************************************
-- 오브젝트 권한 철회
****************************************************************************************/
SQL> REVOKE execute ON dbms_pipe
  2  FROM mapbak;

Revoke succeeded.