Oracle privilege学习与测试

Oracle privilege学习与测试

参考文档

<<Concepts9i>>P615

<<Guides9i>>P749

 

目录

1 Introduction of Privileges

2 System Privileges

3 Schema Object Privileges

4 System Privileges Need to Create or Alter a Procedure

5 when do grants and revokes take effect

6 Cascading effects of Revoking privileges

7 Notes

 

 

1 Introduction of Privileges

 

1) A privilege is a right to execute a particular type of SQL statement or to access another user’s object.

2) A user can receive a privilege in two different ways:

1 Grant privileges to users explicitly;

2 Grant privileges to a role, and then grant the role to one or more users.

(两种授权方式有何细微的区别?本质上是一样的,只不过在有些情况下不能使用由role间接得到的权限.)

 

3)   There are two disticnt categories of privileges: System privileges, Schema object privileges.

 

2 System Privileges

 

There are over 100 distinct system privileges.

Only users who have been granted a specific system privilege with the ADMIN OPTION or users with the system privileges GRANT ANY PRIVILEGE or GRATN ANY OBJECT PRIVILEGE can grant or revoke system privileges to other users.(回答了谁可以授系统权限)

 

3 Schema Object Privileges

 

1) A schema object privilege is a privilege or right to perform. a particular action on a specific schema object: Table, View, Sequence, Procedure, Function, Package.Some schema object, such as clusters, indexes, triggers, and database links, do not have associated object privileges.Their use is controlled with system privileges.(有些对象是没有相关的对象权限的,只有系统权限)

 

2) A schema object and its synonym are equivalent with respect to privileges.(同义词与原对象拥有相同的权限,对同义词的授权等同于对原对象的授权)

 

3) A user can grant any object privilege on any schema object he or she owns to any other user or role. A user with the GRANT ANY OBJECT PRIVILEGE can grant or revoke any specified object privilege to another user.(对象的属主和拥有GRANT ANY OBJECT PRIVILEGE权限的用户可以授对象权限)

 

4System Privileges Need to Create or Alter a Procedure

 

1)     To create a procedure, a user must have the CREATE PROCEDURE or CREATE ANY PROCEDURE system privilege.To alter a procedure,that is, to manually recompile a procedure, a user must own the procedure or have the ALTER ANY PROCEDURE system privilege.(创建与更改存储过程所必须的权限)

2)     The user who owns the procedure also must have privileges for schema objects referenced in the procedure body.To create a procedure, you must have been explicitly granted the necessary privileges(system or object) on all objects referenced by the procedure. You cannot have obtained the required privileges through roles.this includes the EXECUTE privilege for any procedures that are called inside the procedure being created.(创建存储过程,必须对所引用的对象有直接授予的权限,而不能是通过角色间接得到的权限,包括里面调用的存储过程.)

3)     Triggers also require that privileges to referenced objects be granted explicitly to the trigger owner. Anonymouse PL/SQL blocks can use any privilege, whether the privilege is granted explicitly or throuh a role.(触发器同样对所引用的对象同样必须有直接权限,而匿名块则没有这个要求)

 

5 when do grants and revokes take effect

(权限什么时候生效?)

 

1) all grants/revokes of system and object privileges to anything(users, roles and PUBLIC) are immediately observed.(系统对象权限立即生效)

 

2) all grants/revokes of roles to anything(user, other roles, PULBIC) are only observed when a current user session issues a SET ROLE statement to re-enable the the role after the grant/revoke, or when a new user session is created after the grant/revoke.(角色附带的权限只有re-enable or重启会话生效)

 

6 Cascading effects of Revoking privileges

system privileges

1) There are no cascading effects when revoking a system privilege related to DDL operations,regardless of whether the privilege was granted with or without the ADMIN OPTION.(DDL系统权限不会级联回收)

 

2) Cascading effects can be observed when revoking a system privilege related to a DML operation.(DML系统权限会级联回收)

 

3) Object privilege can be revoking cascading.(对象权限会级联回收)
7 Notes