Oracle privilege學習與測試

Oracle privilege學習與測試

參考文件

<>P615

<>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許可權的使用者可以授物件許可權)

 

4 System 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