ORACLE 23C新特性之四：Application Developers新角色

郑全

23c前，我们要给一个开发人员创建用户，一般需要授予 CONNECT,RESOURCE角色，从23C开始，ORACLE专门给开发人员一个角色，DB_DEVELOPER_ROLE，这个角色包含了CONNECT,RESOURCE的权限，我们可以看看这三个角色的权限对比：
SQL> SELECT  PRIVILEGE FROM  ROLE_SYS_PRIVS where ROLE='RESOURCE' ORDER BY 1;


PRIVILEGE
------------------------------
CREATE ANALYTIC VIEW
CREATE ATTRIBUTE DIMENSION
CREATE CLUSTER
CREATE HIERARCHY
CREATE INDEXTYPE
CREATE MATERIALIZED VIEW
CREATE OPERATOR
CREATE PROCEDURE
CREATE PROPERTY GRAPH
CREATE SEQUENCE
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW


15 rows selected.


SQL> SELECT  PRIVILEGE FROM  ROLE_SYS_PRIVS where ROLE='CONNECT' order by 1;


PRIVILEGE
------------------------------
CREATE SESSION
SET CONTAINER


SQL> select OWNER,TABLE_NAME,PRIVILEGE from ROLE_tab_PRIVS where ROLE='CONNECT';         


no rows selected


SQL> select OWNER,TABLE_NAME,PRIVILEGE from ROLE_tab_PRIVS where ROLE='RESOURCE';


no rows selected

看看新的开发人员权限：
SQL>  SELECT  PRIVILEGE FROM  ROLE_SYS_PRIVS where ROLE='DB_DEVELOPER_ROLE' ORDER BY 1;


PRIVILEGE
------------------------------
CREATE ANALYTIC VIEW
CREATE ATTRIBUTE DIMENSION
CREATE CUBE
CREATE CUBE BUILD PROCESS
CREATE CUBE DIMENSION
CREATE DIMENSION
CREATE DOMAIN
CREATE HIERARCHY
CREATE JOB
CREATE MATERIALIZED VIEW
CREATE MINING MODEL
CREATE MLE
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
DEBUG CONNECT SESSION
EXECUTE DYNAMIC MLE
FORCE TRANSACTION
ON COMMIT REFRESH


24 rows selected.

  1* select OWNER,TABLE_NAME,PRIVILEGE from ROLE_tab_PRIVS where ROLE='DB_DEVELOPER_ROLE' order by 1,2,3


OWNER                TABLE_NAME                     PRIVILEGE
-------------------- ------------------------------ ------------------------------
SYS                  DBA_PENDING_TRANSACTIONS       SELECT
SYS                  JAVASCRIPT                     EXECUTE
SYS                  V_$PARAMETER                   READ
SYS                  V_$STATNAME                    READ

--两者系统权限差距：
SQL>  SELECT  PRIVILEGE ,ADMIN_OPTION FROM  ROLE_SYS_PRIVS where ROLE='DB_DEVELOPER_ROLE'
  2   minus
  3   SELECT  PRIVILEGE ,ADMIN_OPTION FROM  ROLE_SYS_PRIVS where ROLE='RESOURCE'
  4   ORDER BY 1
  5  ;


PRIVILEGE                                ADM
---------------------------------------- ---
CREATE CUBE                              NO
CREATE CUBE BUILD PROCESS                NO
CREATE CUBE DIMENSION                    NO
CREATE DIMENSION                         NO
CREATE DOMAIN                            NO
CREATE JOB                               NO
CREATE MINING MODEL                      NO
CREATE MLE                               NO
CREATE SESSION                           NO
DEBUG CONNECT SESSION                    NO
EXECUTE DYNAMIC MLE                      NO


PRIVILEGE                                ADM
---------------------------------------- ---
FORCE TRANSACTION                        NO
ON COMMIT REFRESH                        NO


13 rows selected.


对比一下用户登录权限：
SQL> create user sztech_old identified by sztech_4U;

User created.

SQL> grant connect ,resource to sztech_old;

Grant succeeded.

SQL> create user sztech_dev identified by sztech_4U;

User created.

SQL> grant DB_DEVELOPER_ROLE to sztech_dev;

Grant succeeded.

SQL> conn sztech_old/sztech_4U@192.168.133.121/freepdb1
Connected.
SQL> select * from session_privs order by 1;


PRIVILEGE
----------------------------------------
CREATE ANALYTIC VIEW
CREATE ATTRIBUTE DIMENSION
CREATE CLUSTER
CREATE HIERARCHY
CREATE INDEXTYPE
CREATE MATERIALIZED VIEW
CREATE OPERATOR
CREATE PROCEDURE
CREATE PROPERTY GRAPH
CREATE SEQUENCE
CREATE SESSION


PRIVILEGE
----------------------------------------
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
SET CONTAINER


17 rows selected.

SQL> show parameter db_name
ORA-00942: table or view does not exist



SQL> conn sztech_dev/sztech_4U@192.168.133.121/freepdb1
Connected.
SQL> select * from session_privs order by 1;


PRIVILEGE
----------------------------------------
CREATE ANALYTIC VIEW
CREATE ATTRIBUTE DIMENSION
CREATE CUBE
CREATE CUBE BUILD PROCESS
CREATE CUBE DIMENSION
CREATE DIMENSION
CREATE DOMAIN
CREATE HIERARCHY
CREATE JOB
CREATE MATERIALIZED VIEW
CREATE MINING MODEL


PRIVILEGE
----------------------------------------
CREATE MLE
CREATE PROCEDURE
CREATE SEQUENCE
CREATE SESSION
CREATE SYNONYM
CREATE TABLE
CREATE TRIGGER
CREATE TYPE
CREATE VIEW
DEBUG CONNECT SESSION
EXECUTE DYNAMIC MLE


PRIVILEGE
----------------------------------------
FORCE TRANSACTION
ON COMMIT REFRESH


24 rows selected.

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      FREE
SQL>