오라클 23ai 신기능 스키마 레벨 권한 부여

OS 환경 : Oracle Linux 8.4 (64bit)

 

DB 환경 : Oracle Database 23.4.0.24.05 ai Free

 

방법 : 오라클 23ai 신기능 스키마 레벨 권한 부여

오라클 23ai 부터 스키마 레벨로 권한 부여가 가능함
이 기능은 오브젝트 별로 개별 권한을 부여할 필요 없이 스키마 단위로 관리 가능하게하는 기능임
이로 인해 권한 관리가 조금 더 수월해짐
참고로 이 권한을 부여하기 위해선 GRANT ANY SCHEMA, GRANT ANY PRIVILEGE 권한이 필요함

 

 

예를들어 A 유저에 emp1, emp2, emp3 등 여러 개의 테이블이 존재하는 경우 B 유저에게 권한 부여 필요 시 아래와 같이 전체 테이블에 대한 읽기 권한을 B 유저에게 부여하거나,
각각의 테이블들을 모두 명시해서 B 유저에게 부여해줬어야 했음

1 2	SQL> grant select any table to b; SQL> grant select on a.emp1, a.emp2, a.emp3, ... to b;

 

 

하지만 23ai 부터는 아래 명령으로 쉽게 A 유저의 모든 테이블을 B 유저가 읽을수 있게끔 할수 있음

1	SQL> grant select any table on schema a to b;

 

 

테스트
샘플 유저 A, B 생성 및 권한 부여

1 2 3 4

SQL>  create user A identified by A quota unlimited on users; create user B identified by B quota unlimited on users; grant db_developer_role to A, B;

 

 

A 유저 접속 후 테이블 및 오브젝트 생성

1 2 3 4 5 6 7

SQL> conn A/A@localhost:1521/oracle23pdb1 Connected. SQL>  create table t1 (col1 number, col2 number); insert into t1 values (1,1),(2,2),(3,3),(4,4),(5,5); commit; create or replace view v_t1 as select col1 from t1;

 

 

B 유저에서 A 유저 오브젝트 확인

1 2 3 4 5 6 7 8 9 10

SQL>  conn B/B@localhost:1521/oracle23pdb1 set lines 200 pages 1000 col owner for a10 col object_name for a10 col object_type for a10 select owner, object_name, object_type from all_objects where owner = 'A';   no rows selected

현재는 확인이 불가능함

 

 

sysdba 유저로 A 스키마 권한을 B 유저에 부여

1 2 3

SQL> grant select any table on schema a to b;   Grant succeeded.

 

 

B 유저에서 A 유저 오브젝트 확인

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL>  conn B/B@localhost:1521/oracle23pdb1 set lines 200 pages 1000 col owner for a10 col object_name for a10 col object_type for a10 select owner, object_name, object_type from all_objects where owner = 'A';   OWNER      OBJECT_NAM OBJECT_TYP ---------- ---------- ---------- A          T1         TABLE A          V_T1       VIEW

스키마 레벨 권한 부여후 정상적으로 테이블과 뷰가 확인됨

 

 

A 테이블, 뷰 데이터 확인

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

SQL> conn B/B@localhost:1521/oracle23pdb1 Connected. SQL> select * from A.t1;         COL1       COL2 ---------- ----------          1          1          2          2          3          3          4          4          5          5   SQL> select * from A.v_t1;         COL1 ----------          1          2          3          4          5

정상적으로 읽을 수 있음

 

 

B 유저에서 현재 읽을수 있는 스키마 권한 확인

1 2 3 4 5 6 7 8 9

SQL>  set lines 200 pages 1000 col schema for a10 col privilege for a30 select * from session_schema_privs;   PRIVILEGE                      SCHEMA ------------------------------ ---------- SELECT ANY TABLE               A

 

 

sysdba 접속후 부여된 스키마 권한들 확인

1 2 3 4 5 6 7 8 9 10 11 12

SQL> set lines 200 pages 1000 col grantee for a10 col privilege for a30 col schema for a10 select * from dba_schema_privs;   GRANTEE    PRIVILEGE                      SCHEMA     ADM COM INH ---------- ------------------------------ ---------- --- --- --- B          SELECT ANY TABLE               A          NO  NO  NO IMSI       SELECT ANY TABLE               B          NO  NO  NO IMSI       SELECT ANY TABLE               A          NO  NO  NO

모든 스키마 권한들을 확인할 수 있음

 

 

스키마 레벨 권한 부여시 제외되는 권한은 아래와 같음
Table 4-5 System Privileges Excluded from Schema Privileges 파트 참조
https://docs.oracle.com/en/database/oracle/oracle-database/23/dbseg/configuring-privilege-and-role-authorization.html#GUID-58D04BBE-A40D-4699-A2D7-1AB40F532A6D

 

 

참조 : 

https://positivemh.tistory.com/1163
2991177.1
https://apex.oracle.com/pls/apex/features/r/dbfeatures/features?feature_id=1863
https://docs.oracle.com/en/database/oracle/oracle-database/23/dbseg/configuring-privilege-and-role-authorization.html#GUID-1C2A996E-8D01-4B94-BE9F-E5042AA15B15
https://docs.oracle.com/en/database/oracle/oracle-database/23/dbseg/configuring-privilege-and-role-authorization.html#GUID-58D04BBE-A40D-4699-A2D7-1AB40F532A6D
https://blogs.oracle.com/cloudsecurity/post/schemalevel-privilege-grants-with-database-23c
https://oracle-base.com/articles/23/schema-privileges-23
https://github.com/perpetualv/SchemaPrivileges
https://tuna.tistory.com/105
http://www.koreaoug.org/ai23/13329