오라클 객체 권한 조회/추가/회수

 

1.  객체 권한 부여(SELECT 권한)

Syntax) grant object_privilege [column] on object to [user | role | PUBLIC] [WITH  GRANT OPTION]

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

  관련 뷰테이블

   - USER_TAB_PRIVS, USER_TAB_PRIVS_MADE, USER_TAB_PRIVS_RECD : 일반 사용자 조회

   - DBA_TAB_PRIVS : dba 사용자가 조회

 1.1 Alice 사용자가 hoya 사용자의 dept 테이블 조회(select) - 권한 부여 전

SQL> select * from hoya.dept ;  => hoya 사용자의 dept 테이블 조회 select * from hoya.dept                    * 1행에 오류: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다 SQL>

 1.2 hoya 사용자가 alice 사용자에게 dept 테이블에 조회(select) 권한 부여

SQL> grant select on dept to alice ; => alice에게 dept 테이블에 대한 select 권한 부여 권한이 부여되었습니다.  SQL> select grantee,owner,table_name,privilege from user_tab_privs ; => 부여 권한 조회 GRANTEE    OWNER      TABLE_NAME PRIVILEGE ---------- ---------- ---------- ------------------------------ ALICE      HOYA       DEPT       SELECT PUBLIC     SYS        HOYA       INHERIT PRIVILEGES SQL>

 1.3 alice 사용자가 hoya 사용자의 dept 테이블 조회

SQL> select * from hoya.dept ; => hoya 사용자의 dept 테이블 조회     DEPTNO DNAME      LOC  ---------- ---------- ----------           1 a          1F           2 b          2F           3 c          3F           4 d          4F           5 e          5F  SQL> select grantee,owner,table_name,privilege from user_tab_privs ;  => 테이블에 부여된 권한 조회 GRANTEE    OWNER      TABLE_NAME PRIVILEGE ---------- ---------- ---------- ---------------------------------------- ALICE      HOYA       DEPT       SELECT PUBLIC     SYS        ALICE      INHERIT PRIVILEGES SQL>

 1.4  DBA 사용자로 부여된 권한 조회

shell> sqlplus / as sysdba SQL> select grantee,owner,table_name,privilege from dba_tab_privs where grantee='ALICE' ; => alice에게 부여된 권한 GRANTEE    OWNER      TABLE_NAME PRIVILEGE ---------- ---------- ---------- -------------------- ALICE      HOYA       DEPT       SELECT ALICE      HOYA       EMP        SELECT SQL>

 

2. Column 에 대한 권한 부여( 컬럼에 대한 권한 부여는 insert, update, reference 만 가능, select 는 테이블 단위로만 가능)

  관련 뷰테이블 

   - USER_COL_PRIVS, USER_COL_PRIVS_MADE, USER_COL_PRIVS_RECD : 일반 사용자 조회

   - DBA_COL_PRIVS : dba 사용자가 조회

 2.1 Alice 사용자가 hoya 사용자의 dept 테이블 조회(select) - 권한 부여 전

SQL> select * from hoya.emp ; select * from hoya.emp                    * 1행에 오류: ORA-00942: 테이블 또는 뷰가 존재하지 않습니다 SQL>

 2.2 hoya 사용자가 alice 사용자에게 권한 부여

SQL> grant select on emp to alice ;  => select 권한 부여 권한이 부여되었습니다. SQL> grant insert(empno,ename), update(dpname) on emp to alice ; => insert, update 권한 부여 권한이 부여되었습니다. SQL> select * from user_col_privs ; => 컬럼에 부여된 권한 확인 GRANTEE    OWNER      TABLE_NAME COLUMN_NAM GRANTOR    PRIVILEGE  GRA COM INH ---------- ---------- ---------- ---------- ---------- ---------- --- --- --- ALICE      HOYA       EMP        ENAME      HOYA       INSERT     NO  NO  NO ALICE      HOYA       EMP        EMPNO      HOYA       INSERT     NO  NO  NO ALICE      HOYA       EMP        DPNAME     HOYA       UPDATE     NO  NO  NO SQL>SQL> select grantee,owner,table_name,privilege from user_tab_privs ; => 테이블에 부여된 권한 확인 GRANTEE    OWNER      TABLE_NAME PRIVILEGE ---------- ---------- ---------- ---------- ALICE      HOYA       EMP        SELECT ALICE      HOYA       DEPT       SELECT PUBLIC     SYS        HOYA       INHERIT PRIVILEGES SQL>

 2.3  alice 사용자가 부여된 권한으로 테이블 Insert, Update 실행

SQL> select * from hoya.emp ;      EMPNO ENAME                DPNAME ---------- -------------------- --------------------          1 a                    sales          2 b                    sales          3 c                    hr          4 d                    hr          5 e                    finance SQL> insert into hoya.emp(empno,ename) values(6,'f') ; 1 개의 행이 만들어졌습니다. SQL> select * from hoya.emp ;      EMPNO ENAME                DPNAME ---------- -------------------- --------------------          1 a                    sales          2 b                    sales          3 c                    hr          4 d                    hr          5 e                    finance          6 f 6 행이 선택되었습니다. SQL>update hoya.emp set dpname='finance' where empno=6 ; 1 행이 업데이트되었습니다. SQL> select * from hoya.emp ;      EMPNO ENAME                DPNAME ---------- -------------------- --------------------          1 a                    sales          2 b                    sales          3 c                    hr          4 d                    hr          5 e                    finance          6 f                    finance 6 행이 선택되었습니다. SQL> delete from hoya.emp where empno=6 ; => delete 권한이 없음으로 오류 발생 delete from hoya.emp where empno=6                  * 1행에 오류: ORA-01031: 권한이 불충분합니다 SQL>

 2.4 DBA 사용자로 컬럼에 부여된 권한 조회

shell> sqlplus / as sysdba SQL>  select grantee,owner,table_name,column_name,grantor,privilege from dba_col_privs where GRANTOR='HOYA' ; => hoya 사용자가 부여한 권한 조회 GRANTEE    OWNER      TABLE_NAME COLUMN_NAM GRANTOR    PRIVILEGE ---------- ---------- ---------- ---------- ---------- -------------------- ALICE      HOYA       EMP        ENAME      HOYA       INSERT ALICE      HOYA       EMP        EMPNO      HOYA       INSERT ALICE      HOYA       EMP        DPNAME     HOYA       UPDATE SQL>