[Db Admin] 19.Managing Privileges

* Privilege는 SYSTEM privilege와 OBJECT privilege 가 있다.

1. SYSTEM privilege
   - 80개정도가 있다.
   - 보통 ANY-keyword가 들어 있는 privilege는 system privilege이다.

   INDEX관련 : CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEX
   TABLE관련 : CREATE TABLE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE,
               SELECT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE
   SESSION관련 : CREATE SESSION, ALTER SESSION, RESTRICTED SESSION
   TABLESPACE관련 : CREATE TABLESPACE, ALTER TABLESAPCE, DROP TABLESPACE,
                    UNLIMITED TABLESPACE
                    
   * SYSTEM_PRIVILEGE_MAP data dictionary로 정보를 볼 수 있다.
   
2. Granting System Privileges
  
   SQL> GRANT CREATE SESSION, CREATE TABLE
     2  TO user1 ;
     
   SQL> GRANT CREATE SESSION TO scott
     2  WITH ADMIN OPTION ;
     
   - PUBLIC : 모든 user 에게 권한 주기
   - WITH ADMIN OPTION : 남에게 자기가 받은 권한을 줄 수 있다.
   
   * session 중에 있는 user에게도 바로 적용된다.
   
3. SYSDBA와 SYSOPER 권한

   SYSOPER : STARTUP
             SHUTDOWN
             ALTER DATABASE OPEN | MOUNT
             ALTER DATABAE BACKUP CONTROLFILE
             ALTER TABLESPACE BEGIN/END BACKUP
             RECOVER DATABASE
             ALTER DATABASE ARCHIVELOG
             RESTRICTED SESSION
             
   SYSDBA  : SYSOPER의 권한을 모두 갖고,
             CREATE DATABASE
             RECOVER DATABASE UNTIL 을 할 수 있다.
             
4. Password file을 이용해서 권한주기

   1) password file을 만든다.
   
      $ orapwd file=$ORACLE_HOME/dbs/orapwSID password=123456 entries=5
      
   2) $ORACLE_HOME/dbs/initSID.ora 파일을 수정한다.
   
      REMOTE_LOGIN_PASSWORD_FILE=EXCLUSIVE
      
   3) sysoper, sysdba 권한을 준다.
   
      SVRMGR> grant sysdba, sysoper to scott ;
      
   4) V$PWFILE_USERS 로 password file의 member들을 확인 할 수 있다.
   
      SVRMGR> select * from v$pwfile_users ;
   
   5) 접속하는 방법.
   
      SVRMGR> connect scott/123456 as sysdba
      
5. System privilege 에 대한 정보를 갖고 있는 view

   DBA_SYS_PRIVS
     - grantee
     - privilege
     - admin_option
     
   SVRMGR> SELECT * FROM dba_sys_privs ;
   
   * session privilege 에 대한 정보를 갖고 있는 view
     ; 현재 접속한 user의 권한을 보여준다.
     
     SESSION_PRIVS
       - privilege
     
     SVRMGR> SELECT * FROM session_privs ;
     
6. System privilege revoke 하기

   REVOKE CREATE TABLE FROM user1 ;
   
   REVOKE CREATE SESSION FROM scott ;
   
   * with admin option 으로 권한을 받은 user1가 다른 user2에게 권한을 나눠 줬을때,
     revoke ... from user1 을 하더라도 user2의 권한은 살아 있다.
     
7. Granting Object Privileges

   SQL> GRANT EXECUTE ON dbms_pipe
     2  TO public ;
     
   SQL> GRANT UPDATE(ename, sal) ON emp
     2  TO user1 WITH GRANT OPTION
     
   - WITH GRANT OPTION : 다른 user에게 자신이 받은 권한을 나눠 줄 수 있다.
   
   * 모든 object를 모든 유저에게  권한 주기
   SQL> GRANT ALL ON emp TO PUBLIC ;

8. Object Privilege에 대한 테이블, 컬럼 정보를 갖고 있는 view

   1) 테이블 정보
      DBA_TAB_PRIVS
       - grantee   <-- 권한 받은 사람
       - owner
       - table_name
       - grantor   <-- 권한 준 사람
       - privilege
       - grantable
       
      SQL> SELECT * FROM dba_tab_privs
        2  WHERE GRANTEE='SCOTT' ;
        
   2) 컬럼 정보
      DBA_COL_PRIVS
       - grantee
       - owner
       - table_name
       - column_name
       - grantor
       - privilege
       - gratable
       
      SQL> SELECT * FROM dba_col_privs ;
      
9. Object privilege revoke 하기

   SQL> REVOKE execute ON dbms_pipe
     2  FROM scott ;

   * with grant option 으로 권한을 받은 user1가 다른 user2에게 권한을 나눠 줬을때,
     revoke ... from user1 을 하면 user2의 권한도 제거된다.

---

[ Lab ]

---

1. with admin option 실습
SQL> grant create user to scott with admin option ;
SQL> connect scott/tiger
SQL> grant create user to bob ;
SQL> connect system/manager
SQL> select * from dba_sys_privs
  2  where grantee in ('SCOT','BOB') ;
SQL> revoke create user from scott ;
SQL> select * from dba_sys_privs where grantee in ('SCOTT','BOB') ;

2. with grant option 실습
SQL> connect scott/tiger
SQL> grant select on test to bob with grant option ;
SQL> connect bob/oracle
SQL> grant select on scott.test to system ;
SQL> connect scott/tiger
SQL> select * from all_tab_privs
  2  where table_name='TEST' ;
SQL> revoke select on test from bob ;
SQL> select * from all_tab_privs
  2  where table_name='TEST' ;