프린트 하기

내맘대로긍정이 알려주는

Oracle 23ai 신기능
무료 세미나 발표자료

다운로드 trending_flat

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