OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P16

OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P16: There is high demand for professionals in the information technology (IT) industry, andOracle certifications are the hottest credential in the database world. You have made theright decision to pursue certification, because being Oracle Database 11g certified will giveyou a distinct advantage in this highly competitive market.
Nội dung trích xuất từ tài liệu:
OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P16 Granting and Revoking Privileges 681 If you grant a system privilege WITH ADMIN OPTION and later revoke that privilege, theprivileges created by the grantee will not be revoked. Unlike object privileges, the revo-cation of system privileges does not cascade. Think of it this way: WITH GRANT OPTIONincludes the keyword GRANT and so implies that a revoke cascades, but WITH ADMIN OPTIONdoes not mention GRANT, so a revoke has no effect. Here’s an example. Mary grants theSELECT ANY TABLE privilege to new DBA Zachary with ADMIN OPTION. Zachary then grantsthis privilege to Rex. Later, Zachary gets promoted and leaves the department, so Maryrevokes the SELECT ANY TABLE privilege from Zachary. Rex’s privilege remains unaffected.You can see this in Figure 12.5.F I g u r e 12 . 5 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 bothgrantor 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 PrivilegesRole 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 thatrole. Roles can be password protected, so users may have a role granted to them yet not beable to use that role in all database sessions. I’ll cover roles and role privileges—includinghow to grant them—in the following section, “Creating and Managing Roles.”682 Chapter 12 N Implementing Security and AuditingCreating and Managing RolesA role is a tool for administering privileges. Privileges can be granted to a role, and thenthat role can be granted to other roles and users. Users can thus inherit privileges via roles.Roles serve no other purpose than to administer privileges. To create a role, use the CREATE ROLE statement. You can optionally include anIDENTIFIED BY clause that requires users to authenticate themselves before enabling the role.Roles requiring authentication are typically used inside an application, where a user’s activi-ties are controlled by the application. To create the role APPL_DBA, execute the following:CREATE ROLE appl_dba; To enable a role, execute a SET ROLE statement, like this:SET ROLE appl_dba IDENTIFIED BY seekwrit; The data dictionary view DBA_ROLE_PRIVS lists all the role privileges granted in the database.Granting Role PrivilegesAs with object and system privileges, you use the GRANT statement to confer role privileges oneither a user or another role. Also, like system privileges, the optional keywords WITH ADMINOPTION allow the grantee to confer these privileges on other users and roles. For example, togive the OEM_MONITOR role to user charlie, execute the following:GRANT oem_monitor TO charlie; As with the other privileges, you can grant role privileges to the special user PUBLIC.Granting privileges to PUBLIC allows anyone with a database account to exercise this privi-lege. For example, to give all current and future database users use of the plustrace role,execute the following:GRANT plustrace TO public; To give the INDEX ANY TABLE privilege to the role APPL_DBA together with the permission toallow anyone with the role APPL_DBA to grant this privilege to others, execute the following:GRANT index any table TO appl_dba WITH ADMIN OPTION; When it comes to granting a role WITH ADMIN OPTION, roles behave like system privi-leges, and subsequent revocations do not cascade. If the role granted to a user is not the default role, the user must enable the role in thesession to be able to use the role. In the following sections, you will learn to work withroles in a session.Enabling RolesRoles can be enabled—or disabled, for that matter—selectively in each database session.If you have two concurrent sessions, the roles in effect for each session can be different. ...