mysql create view 权限_Oracle 11g Connect角色去掉了CREATE VIEW权限

今天在做测试的时候，无意中发现我新建的用户无法创建视图：

SQL> create view v_t as select * from tb;

create view v_t as select * from tb

*

ERROR at line 1:

ORA-01031: insufficient privileges

可之前我已经给这个用户授予了connect和resource角色了：

SQL> grant connect,resource to alex1;

Grant succeeded.

resource角色是具有创建表、存储过程、函数这些权限的，为什么光是没有创建视图的权限呢？查了一下，发现原来之前的理解有误，CREATE

VIEW这个系统权限之前也没有定义在resource，而是在connect角色。在10.1及之前的版本里，connect角色包含了一系列的系统权限，其中就包含着CREATE

VIEW(下面是10.1的文档)：

Role

Name

Created By

(Script)

Description

CONNECT

SQL.BSQ

Includes the following system

privileges: ALTER

SESSION, CREATE

CLUSTER, CREATE DATABASE

LINK, CREATE

SEQUENCE, CREATE

SESSION, CREATE

SYNONYM, CREATE

TABLE, CREATE VIEW

RESOURCE

SQL.BSQ

Includes the following system

privileges: CREATE

CLUSTER, CREATE

INDEXTYPE, CREATE

OPERATOR, CREATE

PROCEDURE, CREATE

SEQUENCE, CREATE

TABLE, CREATE

TRIGGER, CREATE TYPE

可是从10.2开始，Connect角色的权限就被删除只剩下create session了：

Role Name

Created By (Script)

Description

CONNECT

SQL.BSQ

Includes only the following system

privilege: CREATE

SESSION

RESOURCE

SQL.BSQ

Includes the following system

privileges: CREATE

CLUSTER, CREATE

INDEXTYPE, CREATE

OPERATOR, CREATE

PROCEDURE, CREATE SEQUENCE,CREATE

TABLE, CREATE

TRIGGER, CREATE TYPE

换句话说，以前connect角色能做的创建视图、数据库链接、同义词，现在统统不能做了，除非显式授权。

Oracle指出，这些预定义的角色只是为了向后兼容，除此之外不应该再使用这些预定义角色，而应该用用户自行定义的角色来代替它们。

另外，可以从role_sys_privs视图查看各个角色具有哪些权限，如：

SQL> select * from role_sys_privs where role in

('CONNECT','RESOURCE') order by role;

ROLE  PRIVILEGE  ADM

------------------------------

---------------------------------------- ---

CONNECT  CREATE SESSION  NO

RESOURCE  CREATE

CLUSTER  NO

RESOURCE  CREATE

INDEXTYPE  NO

RESOURCE  CREATE

OPERATOR  NO

RESOURCE  CREATE

PROCEDURE  NO

RESOURCE  CREATE

SEQUENCE  NO

RESOURCE  CREATE

TABLE  NO

RESOURCE  CREATE

TRIGGER  NO

RESOURCE  CREATE

TYPE  NO

给用户授予CREATE VIEW权限后，视图顺利创建。