视图权限的设置案例

今天在042一个自测试看一个有关权限的问题，做了一个测试，而在测试过程中又犯了个错误，记录此案。

问题如下：

During a single session, you run the following set of statements in your database: 

 

SQL> CREATE USER SCOTT IDENTIFIED BY SCOTT; 

SQL> CREATE ROLE HR_ADMIN; 

SQL> GRANT SELECT ANY TABLE TO HR_ADMIN; 

SQL> GRANT CREATE ANY VIEW TO HR_ADMIN; 

SQL> GRANT HR_ADMIN TO SCOTT; 

 

The database user SCOTT tries to execute a SELECT statement against a table in the HR user's schema, but the following error

is generated: 

 

SQL> SELECT * FROM HR.TRAINING; 

 

ORA-01031: insufficient privileges 

 

Which two could be the possible reasons for this error? (Choose two.)

  The HR_ADMIN role is a secured application role.

  The HR_ADMIN role has not been set as the default role for the user SCOTT.

  A password must be provided to access the privileges granted by the HR_ADMIN role.

  The user HR has not granted select access on the TRAINING table to the user SCOTT.

  The user SCOTT has not enabled the HR_ADMIN role by using the SET ROLE statement.

 

答案：

The HR_ADMIN role has not been set as the default role for the user SCOTT .

The user SCOTT has not enabled the HR_ADMIN role by using the SET ROLE statement.

 

做如下解释：

The two possible reasons for this error are that the HR_ADMIN role has not been set as the default role for the user SCOTT or that the user SCOTT has not enabled the HR_ADMIN role by using the SET ROLE statement. When a role is assigned to a user, the role must be enabled using the SET ROLE statement or the role must be set as the default role for the user so that the user can access the privileges granted to them through the role. The default role is enabled for the user at log on. A non-default role must be enabled using the SET ROLE statement. If the role is not enabled for the user, the user cannot access the privileges. The HR_ADMIN role created in this scenario is granted the SELECT ANY TABLE privilege and the CREATE ANY VIEW privilege. The SELECT ANY TABLE privilege enables you to issue SELECT statements against any tables in any user's schema. The CREATE ANY VIEW privilege enables you to create views based on any table in any user's schema. Apart from having the CREATE ANY VIEW privilege, the user must also have SELECT privileges on the tables on which he is trying to create the view. If the user does not have SELECT privileges on the table on which he is trying to create the view, the user SCOTT will receive an error stating he does not have the required privileges. 

从解释上主要是利用set role设置。

 

现在做了一个测试

创建一个用户

SQL> create user hrk identified by hrk;

 User created

 

创建一个角色

SQL> create role hr_admin;

Role created

 

将两个系统权限赋予给角色hr_admin

SQL> grant select any table to hr_admin;

 Grant succeeded

 

SQL> grant create any view to hr_admin;

 Grant succeeded

 

将角色hr_admin赋给用户

SQL> grant hr_admin to hrk;

 Grant succeeded

 

给用户一个连接权限

SQL> grant connect to hrk;

Grant succeeded

 

用hrk连接进去，建立一个视图。

SQL> conn hrk/hrk

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

Connected as hrk

 

SQL> create view hrk_v as

  2  select * from hrbak.employees where salary>8000;

 

create view hrk_v as

select * from hrbak.employees where salary>8000

 

ORA-01031: insufficient privileges

 

查看权限情况

SQL> select * from user_role_privs;

 

USERNAME   GRANTED_ROLE   ADMIN_OPTION DEFAULT_ROLE OS_GRANTED

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

HRK          CONNECT             NO           YES          NO

HRK          HR_ADMIN            NO           YES          NO

 

SQL> select * from role_sys_privs where role='HR_ADMIN';

 

ROLE                PRIVILEGE                   ADMIN_OPTION

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

HR_ADMIN            SELECT ANY TABLE                  NO

HR_ADMIN            CREATE ANY VIEW                   NO

 

改变用户的默认表空间设定，还是同样的问题

SQL> alter user hrk default tablespace hrbak;

User altered

 

SQL> create view hrk_v as

  2  select * from hrbak.employees where salary>8000;

 

create view hrk_v as

select * from hrbak.employees where salary>8000

 

ORA-01031: insufficient privileges

 

从权限上来看似乎没什么问题，测试不少步骤，最后找到CREATE VIEW的解释。

To create a view in your own schema, you must have the CREATE VIEW system privilege. To create a view in another user's schema, you must have the CREATE ANY VIEW system privilege.

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

To use the basic constructor method of an object type when creating an object view, one of the following must be true:

1.  The object type must belong to the same schema as the view to be created.

2.  You must have the EXECUTE ANY TYPE system privileges.

3.  You must have the EXECUTE object privilege on that object type.

 

应该说是schema的问题，加入一个schema的名称

SQL> create view hrbak.hrk_v as

  2  select * from hrbak.employees where salary>8000;

 

View created

 

总结，create any view可以在任何shema上建立视图，但是注意在视图的前加上所属schema的名称。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9932141/viewspace-660354/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9932141/viewspace-660354/