OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P16: There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credential in the database world. You have made the right decision to pursue certification, because being Oracle Database 11g certified will give you a distinct advantage in this highly competitive market. | Granting and Revoking Privileges 681 If you grant a system privilege WITH ADMIN OPTION and later revoke that privilege the privileges created by the grantee will not be revoked. Unlike object privileges the revocation of system privileges does not cascade. Think of it this way WITH GRANT OPTION includes the keyword GRANT and so implies that a revoke cascades but WITH ADMIN OPTION does not mention GRANT so a revoke has no effect. Here s an example. Mary grants the SELECT ANY TABLE privilege to new DBA Zachary with ADMIN OPTION. Zachary then grants this privilege to Rex. Later Zachary gets promoted and leaves the department so Mary revokes the SELECT ANY TABLE privilege from Zachary. Rex s privilege remains unaffected. You can see this in Figure . FIGURE The revoking of system privileges Mary grants to Zachary. GRANT SELECT ANY TABLE WITH ADMIN OPTION Zachary Zachary grants to Rex. GRANT SELECT ANY TABLE Rex Zachary is dropped and Rex retains privileges. GRANT SELECT ANY TABLE Rex The database records only the privilege granted not who granted it. This behavior differs from object privileges because the database does not record both grantor and grantee for system privileges only the grantee is recorded. The data dictionary view DBA_SYS_PRIVS lists all the system privileges granted in the database. Role Privileges Role privileges confer on the grantee a group of system object and other role privileges. Users who have been granted a role inherit the privileges that have been granted to that role. Roles can be password protected so users may have a role granted to them yet not be able to use that role in all database sessions. I ll cover roles and role privileges including how to grant them in the following section Creating and Managing Roles. 682 Chapter 12 Implementing Security and Auditing Creating and Managing Roles A role is a tool for administering privileges. Privileges can be granted to a role and then that role can be granted to other roles and users.
