Oracle 9iR2 10gR2 11gR2 connect resource角色拥有的权限的不同

超、凢脫俗 2023-10-09 12:35 30阅读 0赞

SQL\*Plus: Release 9.2.0.8.0 - Production on Wed Sep 25 19:47:58 2019

SQL> conn / as sysdba  
Connected.  
SQL> select grantee, privilege from dba\_sys\_privs where grantee = 'CONNECT' order by privilege;

GRANTEE PRIVILEGE  
\------------------------------ ----------------------------------------  
CONNECT ALTER SESSION  
CONNECT CREATE CLUSTER  
CONNECT CREATE DATABASE LINK  
CONNECT CREATE SEQUENCE  
CONNECT CREATE SESSION  
CONNECT CREATE SYNONYM  
CONNECT CREATE TABLE  
CONNECT CREATE VIEW

8 rows selected.

SQL> select grantee, privilege from dba\_sys\_privs where grantee = 'RESOURCE' order by privilege;

GRANTEE PRIVILEGE  
\------------------------------ ----------------------------------------  
RESOURCE CREATE CLUSTER  
RESOURCE CREATE INDEXTYPE  
RESOURCE CREATE OPERATOR  
RESOURCE CREATE PROCEDURE  
RESOURCE CREATE SEQUENCE  
RESOURCE CREATE TABLE  
RESOURCE CREATE TRIGGER  
RESOURCE CREATE TYPE

8 rows selected.

SQL\*Plus: Release 10.2.0.5.0 - Production on Wed Sep 25 19:49:50 2019

SQL> conn / as sysdba  
Connected.  
SQL> select grantee, privilege from dba\_sys\_privs where grantee = 'CONNECT' order by privilege;

GRANTEE PRIVILEGE  
\------------------------------ ----------------------------------------  
CONNECT CREATE SESSION

SQL> select grantee, privilege from dba\_sys\_privs where grantee = 'RESOURCE' order by privilege;

8 rows selected.

SQL\*Plus: Release 11.2.0.4.0 Production on Wed Sep 25 19:51:00 2019

SQL> conn / as sysdba  
Connected.  
SQL> select grantee, privilege from dba\_sys\_privs where grantee = 'CONNECT' order by privilege;

GRANTEE PRIVILEGE  
\------------------------------ ----------------------------------------  
CONNECT CREATE SESSION

SQL> select grantee, privilege from dba\_sys\_privs where grantee = 'RESOURCE' order by privilege;

8 rows selected.

SQL>  
可以看到10gR2 11gR2 connect角色拥有的权限比9iR2少了如下7个权限；

CONNECT ALTER SESSION  
CONNECT CREATE CLUSTER  
CONNECT CREATE DATABASE LINK  
CONNECT CREATE SEQUENCE  
CONNECT CREATE SYNONYM  
CONNECT CREATE TABLE  
CONNECT CREATE VIEW

10gR2 11gR2 resource角色拥有的权限和9iR2一样。

所以在10gR2 11gR2，一般应用使用9iR2中connect+ resource角色的权限分配方法已经不够用了，需要单独分配

ALTER SESSION  
CREATE DATABASE LINK  
CREATE SYNONYM  
CREATE VIEW

这4个权限才能达到原9iR2中connect+ resource角色相同权限