<< 결론 >>
1. Oracle 에서 칼럼 단위로 권한 부여가 가능한것은 Insert , Update 에 대해서다
2. Delete 의 경우 Row의 구성 Column 전체에 대한 작업이니
칼럼 단위 권한 부여 자체가 불가능한것으로 생각된다.
3. select 의 경우 View 를 통한 대체가 가능하다.
<<추가>>
-. Oracle 10g VPD 기능으로도 구현 가능
http://blog.naver.com/dangtong76/140043148884
-. Oracle Enterprise Edition 에서만 가능
https://docs.oracle.com/cd/E11882_01/license.112/e47877.pdf
<< 테스트 >>
## 테스트 테이블 생성 및 권한 부여
## SELECT 권한 부여, Update(SAL) 권한 부여
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | SYS> select * from v$version ; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production CORE 11.2.0.4.0 Production TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production SYS>create table myemp as select * from scott.emp ; Table created. SYS>select * from myemp ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. SYS>grant select on myemp to scott ; Grant succeeded. SYS>grant update (sal) on myemp to scott ; Grant succeeded. SYS>create public synonym myemp for myemp ; Synonym created. | cs |
## 테스트 결과
## SELECT --> 가능
## DELETE --> 권한 없음
## INSERT --> 권한 없음
## UPDATE --> 권한 없음
## UPDATE ( SAL ) --> 가능
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | SCOTT>select * from myemp ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. SCOTT>delete myemp ; delete myemp * ERROR at line 1: ORA-01031: insufficient privileges SCOTT>desc myemp ; Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) SCOTT>insert into ( EMPNO, ENAME ) values ( 9999, 'TG'); insert into ( EMPNO, ENAME ) values ( 9999, 'TG') * ERROR at line 1: ORA-00928: missing SELECT keyword SCOTT>insert into myemp ( EMPNO, ENAME ) values ( 9999, 'TG'); insert into myemp ( EMPNO, ENAME ) values ( 9999, 'TG') * ERROR at line 1: ORA-01031: insufficient privileges SCOTT>update myemp set ename ='TG' where empno = 7934 ; update myemp set ename ='TG' where empno = 7934 * ERROR at line 1: ORA-01031: insufficient privileges SCOTT>update myemp set sal = 9999 where empno = 7934 ; 1 row updated. SCOTT>rollback ; Rollback complete. | cs |
## INSERT ( EMPNO, ENAME ) 권한 부여
1 2 | SYS>grant insert ( empno, ename ) on myemp to scott ; Grant succeeded. | cs |
1 2 3 4 5 6 7 8 9 10 11 12 13 | ## INSERT ( EMPNO, ENAME, HIREDATE ) --> 권한 없음 SCOTT>insert into myemp ( EMPNO, ENAME,HIREDATE ) values ( 9998,'TG',sysdate); insert into myemp ( EMPNO, ENAME,HIREDATE ) values ( 9998,'TG',sysdate) * ERROR at line 1: ORA-01031: insufficient privileges ## INSERT ( EMPNO, ENAME ) --> 가능 SCOTT>insert into myemp ( EMPNO, ENAME ) values ( 9999, 'TG'); 1 row created. SCOTT> | cs |
<<2018/02/28 칼럼 단위 권한 확인 추가>>
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SYS@TGDB=> set linesize 180 SYS@TGDB=> col owner for a20 SYS@TGDB=> col table_naem for a20 SYS@TGDB=> col column_name for a20 SYS@TGDB=> col grantee for a20 SYS@TGDB=> col privilege for a20 SYS@TGDB=> select owner, table_name, column_name, grantee, privilege from dba_col_privs where owner ='SYS' and table_name ='MYEMP' ; OWNER TABLE_NAME COLUMN_NAME GRANTEE PRIVILEGE -------------------- ------------------------------ -------------------- -------------------- -------------------- SYS MYEMP EMPNO SCOTT INSERT SYS MYEMP ENAME SCOTT INSERT SYS MYEMP SAL SCOTT UPDATE | cs |