Revoke permissions from roles in Oracle

1	Oracle L3 permission system [review]
	1. Oracle's permission system is divided into three levels: Role role, system, and object. The following is a simple description:   Ü Object Privilege is the minimum granularity object of the permission system. The feature is that the permission point is on the object. Objects here, including permissions for eleven objects, including data tables, views, sequences, packages, and stored procedures, in the Oracle system. Each object has its own permissions. For example, you have select, update, and other operation permissions on a data table; Ü system privilege is another layer that describes Oracle user operations. Defines what users can do in the system, such as create table. Note that some system permissions and object permissions may overlap. Use it with caution. Ü role privilege is a combination of object permissions, system permissions, and even role permissions. Object permissions and system permissions are fine-grained permission units. A type of user usually needs the same object permission set and system permission set. If you set the roles separately, there may be omissions. It is easier to set the role based on the responsibilities, and then attach the role to the user;   2. Object permission: Object Privilege The object permission is the basic permission of Oracle, which defines the permissions that a user can use on a specific object. Note: The object permission mentioned here is the permission for an existing object. Currently, Oracle supports nine-sided permissions for eleven objects, not all nine-sided permissions for each type of object. The following table details: As shown in the preceding table, the permissions that can be granted vary with objects. In addition, there is an implicit all permission, indicating all the permissions that an object can grant. View, you can use views such as user_tab_privilege and table_privilege to check the access authorization of objects.   3. system permission system privilege: The system permission is defined in Oracle and has about 160 system permissions in Oracle10g.   4. Role permission role privilege: The Role permission can accept various system permissions and object permissions, or include other role permissions. In the actual development process, there are not many scenarios for custom roles, because there are many restrictions in the development and use of custom role permissions. Note that the predefined roles of different Oracle versions may be different.
2	Role role cannot be repeatedly assigned
	// Customize two empty permission roles; SQL> Create role r_t; Role created   SQL> Create role r_m; Role created   // Try to grant permissions to each other; SQL> grant r_t to r_m; Grant succeeded   SQL> grant r_m to r_t; Grant r_m to r_t   ORA-01934: cyclic Role authorization Detected   The experiment conclusion is obvious: Oracle performs loop detection automatically when granting permissions to the grant role. If a loop is found, the operation is disabled.
3	Removing Effect of role permissions in Stored Procedures
	Roles are indeed a convenient way to organize permission sets and are also widely used in many systems. However, in Oracle, role privilege should be especially careful when using role permissions, especially during database development. Because the structure such as the Oracle stored procedure has a Removing Effect on Role permissions. [Experiment] // Create an experiment role r_cat_role SQL> Create role r_cat_role; Role created   // Grant the system permission select any dictionary to the experiment role. The select any dictionary permission can access most of the metadata views of oracle. SQL> grant select any dictionaryto r_cat_role; Grant succeeded   // Create a user SQL> create user mytest identified by mytest; User Created   SQL> grant create session to mytest; Grant succeeded   SQL> grant r_cat_role to mytest; Grant succeeded   SQL> grant create procedure to mytest; Grant succeeded   SQL> conn mytest/mytest @ ora11g; Connected to Oracle database11genterpriseedition release11.2.0.1.0 Connected as mytest   SQL> select count (*) fromdba_objects;   Count (*) ---------- 72282   The dba_objects view is an integral part of the metadata dictionary. After accepting the role permission r_cat_role, The mytest user has the permission to be directly used in SQL. Below we construct the stored procedure as follows:   SQL> Create or replace procedure p_test_nc 2 is 3 n_res number; 4 begin 5 select count (*) 6 into n_res 7 from dba_objects; 8 9 dbms_output.put_line (to_char (n_res )); 10 end p_test_nc; 11/   Warning: procedure created with compilation errors   SQL> select * From user_errors;   Name type sequence line position text attribute ----------------------------------------------------------------------------------------------------- P_test_nc procedure 1 7 8 PL/SQL: ORA-00942: Table or view no error P_test_nc Procedure 2 5 3 PL/SQL: SQL statement ignored Error Compilation error, which looks incredible. Clearly, mytest users have dba_objects view access permissions and can be directly used in SQL statements. This is because the permission to access dba_objects is granted by the role. The Oracle code structure, such as stored procedures, has a feature that allows you to remove the role permissions in a user's three-tier permissions (role, system, and object.
4	System permission conflicts with object permission
	System permissions include some object-related content. Such as select any table and execute any procedure. These and object permissions overlap. [Experiment] SQL> create user mytest identified by mytest; User Created   SQL> grant connect, resource to mytest; Grant succeeded   SQL> grant select any table to mytest; Grant succeeded   SQL> conn mytest/mytest @ orcl; Connected to Oracle database10genterpriseedition release10.2.0.1.0 Connected as mytest   SQL> select count (*) from Scott. EMP; Count (*) ---------- 14   Try to disable mytest's access to m from the Scott user.     SQL> conn Scott/tiger @ orcl; Connected to Oracle database10genterpriseedition release10.2.0.1.0 Connected as Scott   SQL> revoke select on T from mytest;   Revoke select on T from mytest   ORA-01927: cannot revoke your unauthorized Permissions It can be seen that for Oracle revoke, only the permissions granted by revoke are allowed. Ungranted permissions, or attempts to split system permissions and object permission semantics cannot be achieved.
5	You have the select any table permission, but cannot access data tables under the Sys user. This is an internal protection measure for sys core objects in Oracle. For example, if the Sys user creates a test table and has the select any table permission, the user cannot query the SYS. test table. In addition, users with the select any table permission cannot view the system view in SYS schema. To view the system view, you must set the parameter o7..... to true.