檢視許可權的設定案例

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. 

SQL> create user hrk identified by hrk;

 User created

SQL> create role hr_admin;

Role created

SQL> grant select any table to hr_admin;

 Grant succeeded

SQL> grant create any view to hr_admin;

 Grant succeeded

SQL> grant hr_admin to hrk;

 Grant succeeded

SQL> grant connect to hrk;

Grant succeeded

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

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.

SQL> create view hrbak.hrk_v as

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

View created