Oracle23ai新特性SCHEMA级授权_oracle schema授权

Oracle23ai新特性SCHEMA级授权

1、需求简介

Oracle23ai之前的版本，想要实现一个用户可以访问另一个用户下的所有表，需要把该用户下所有的表的访问权限依次授权给该用户。这一方式存在的问题是，每当源端用户新创建一个表时，还需要额外地给相关用户做授权。

Oracle 23ai的SCHEMA级授权特性较简单易用的解决了以上问题。通过把SELECT ANY TABLE ON SCHEMA给到另外一个用户，来将原始用户查询的任何权限给到新的用户。

2、用户TESTUSER的表及查询信息

TESTUSER@FREEPDB1> select tname from tab;

TNAME
--------------------------------------------------------------------------------
PERSONNEL
T_MULTIROWS
T_IDENTITY

TESTUSER@FREEPDB1> select * from T_MULTIROWS;

        ID INFONAME
---------- ------------------------------
         1 oracle23c
         2 oracle23ai
         3 mysql8.4

TESTUSER@FREEPDB1> select * from T_IDENTITY;

        ID NAMEINFO                       SYSGUID
---------- ------------------------------ ------------------------------------
         1 generated by                   18EFA5E23DDC0BA1E063E650A8C05A76
         2 DEFAULT                        18EFA5E23DDD0BA1E063E650A8C05A76
         3 AS identity                    18EFA5E23DDE0BA1E063E650A8C05A76
         4 values meger                   18EFA5E23DE10BA1E063E650A8C05A76
         5 䶮中国字                        18EFA5E23DE00BA1E063E650A8C05A76
         7 table                          18EFA5E23DE20BA1E063E650A8C05A76
         8 expressions                    18EFA5E23DE30BA1E063E650A8C05A76

7 rows selected.

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

3、创建devuser1用户

SYS@FREEPDB1> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/FREE/FREEPDB1/system01.dbf
/opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf
/opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf
/opt/oracle/oradata/FREE/FREEPDB1/users01.dbf
/opt/oracle/oradata/FREE/FREEPDB1/testusertbs1.dbf
-- 创建表空间
SYS@FREEPDB1> create tablespace devuser1tbs datafile '/opt/oracle/oradata/FREE/FREEPDB1/devuser1tbs.dbf' size 8M autoextend off;

Tablespace created.
-- 创建devuser1 用户
SYS@FREEPDB1> create user devuser1 identified by oracle default tablespace devuser1tbs ;

User created.
-- 授权create session
SYS@FREEPDB1> grant create session to devuser1;

Grant succeeded.
-- 通过把SELECT ANY TABLE ON SCHEMA给到新建的用户devuser1
SYS@FREEPDB1> grant select any table on schema testuser to devuser1;

Grant succeeded.

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

4、登陆检查角色及权限

SYS@FREEPDB1> conn devuser1/oracle@FREEPDB1
Connected.
DEVUSER1@FREEPDB1> select * from session_privs;

PRIVILEGE
------------------------------
CREATE SESSION

DEVUSER1@FREEPDB1> select * from session_roles;

no rows selected
-- 用户devuser1查看testuser.T_MULTIROWS
DEVUSER1@FREEPDB1> select * from testuser.T_MULTIROWS;

        ID INFONAME
---------- ------------------------------
         1 oracle23c
         2 oracle23ai
         3 mysql8.4

-- 发现可以正常访问
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

5、授权后新增表及视图确认能否正常访问

授权后，用户devuser1除了获得CREAT SESSION的系统权限外，没有被授予任何其他系统权限和系统角色。
SCHEMA级授权是通过对象级的继承权限获得。此时，被授权的用户可以看到原始授权用户下所有的表以及数据。

-- testuser用户创建一个视图v_grant_s_a和一个表t_grant_s_a

-- 视图v_grant_s_a创建
TESTUSER@FREEPDB1> create view v_grant_s_a as  select * from (values(1,'grant'),(2,'select any table'),(3,'on schema')) T (id,nameinfo);

View created.

TESTUSER@FREEPDB1> select * from v_grant_s_a;

        ID NAMEINFO
---------- ------------------------------
         1 grant
         2 select any table
         3 on schema
-- 表t_grant_s_a创建
TESTUSER@FREEPDB1> create table t_grant_s_a as  select * from (values(1,'grant'),(2,'select any table'),(3,'on schema')) T (id,nameinfo);

Table created.

TESTUSER@FREEPDB1> select * from t_grant_s_a;

        ID NAMEINFO
---------- ------------------------------
         1 grant
         2 select any table
         3 on schema


-- devuser1用户查询testuser用户的新建的视图
TESTUSER@FREEPDB1> conn DEVUSER1/oracle@FREEPDB1
Connected.
DEVUSER1@FREEPDB1> select * from testuser.t_grant_s_a;

        ID NAMEINFO
---------- ------------------------------
         1 grant
         2 select any table
         3 on schema

DEVUSER1@FREEPDB1> select * from testuser.v_grant_s_a;

        ID NAMEINFO
---------- ------------------------------
         1 grant
         2 select any table
         3 on schema
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

Oracle 23ai中，Oracle引入了架构级（SCHEMA级别）授权。这允许你以更简单、更直观的方式对整个Schema进行授权。简化之前版本授权这个权限的过程。

此外，Oracle 23ai还提供了其他与Schema级别授权相关的功能和视图，如 ROLE_SCHEMA_PRIVS、USER_SCHEMA_PRIVS 和 SESSION_SCHEMA_PRIVS，这些可以帮助你更好地管理和查看Schema级别的权限。

DEVUSER1@FREEPDB1> select * from USER_SCHEMA_PRIVS;

USERNAME                       PRIVILEGE                      SCHEMA                         ADMIN_OPT COMMON    INHERITED
------------------------------ ------------------------------ ------------------------------ --------- --------- ---------
DEVUSER1                       SELECT ANY TABLE               TESTUSER                       NO        NO        NO

DEVUSER1@FREEPDB1> select * from SESSION_SCHEMA_PRIVS;

PRIVILEGE                      SCHEMA
------------------------------ ------------------------------
SELECT ANY TABLE               TESTUSER
1
2
3
4
5
6
7
8
9
10
11