今天在做测试的时候,无意中发现我新建的用户无法创建视图:
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权限后,视图顺利创建。