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 revo- cation 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 12.5. FIGURE 12.5 The revoking of system privileges Zachary Mary grants to Zachary. GRANT SELECT ANY TABLE WITH ADMIN OPTION Rex Zachary grants to Rex. GRANT SELECT ANY TABLE Rex Zachary is dropped and Rex retains privileges. GRANT SELECT ANY TABLE 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.” 95127c12.indd 681 2/17/09 2:43:45 PM Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 682 Chapter 12 N 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. 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 an IDENTIFIED 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 Privileges As with object and system privileges, you use the GRANT statement to confer role privileges on either a user or another role. Also, like system privileges, the optional keywords WITH ADMIN OPTION allow the grantee to confer these privileges on other users and roles. For example, to give 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 to allow 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 the session to be able to use the role. In the following sections, you will learn to work with roles in a session. Enabling Roles Roles 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. 95127c12.indd 682 2/17/09 2:43:45 PM Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Granting and Revoking Privileges 683 Use the SET ROLE role_list statement to enable one or more roles. role_list is a comma- delimited list of roles to enable. This list can include the keyword ALL , which enables all the roles granted to the user. You can optionally append a list of roles to exclude from the ALL list by specifying ALL EXCEPT exclusion_list . If a role has a password associated with it, the keywords IDENTIFIED BY password must immediately follow the role name in the role_list . For example, to enable the password-protected role HR_ADMIN , together with the unpro- tected role EMPLOYEE , execute the following: SET ROLE hr_admin IDENTIFIED BY “my!seekrit”, employee; To enable all roles except HR_ADMIN , run this: SET ROLE ALL EXCEPT hr_admin; You can enable as many roles as have been granted to you, up to the MAX_ENABLED_ROLES initialization parameter. Identifying Enabled Roles The roles that are enabled in your session are listed in the data dictionary view SESSION_ ROLES . To identify these enabled roles for your session, run the following: SELECT role FROM session_roles; These roles include the roles that have been granted to you, the roles that have been granted to the special user PUBLIC , and the roles that you have inherited by way of other roles. To identify the roles granted to either user or the special user PUBLIC , run the following: SELECT granted_role FROM user_role_privs WHERE username IN (USER, ‘PUBLIC’); The role DBA includes the role SCHEDULER_ADMIN , which in turn has system privileges (such as CREATE ANY JOB ). A user who has been granted the DBA role inherits the SCHED- ULER_ADMIN role indirectly. To identify the roles that are both enabled in your session and granted directly to you or PUBLIC but not those roles that you inherited, run this: SELECT role FROM session_roles INTERSECT SELECT granted_role FROM user_role_privs WHERE username IN (USER, ‘PUBLIC’); In your sessions, you can disable only these directly granted and public roles. Disabling Roles Roles can be disabled in a database session either en masse or by exception. Use the SET ROLE NONE statement to disable all roles. Use the SET ROLE ALL EXCEPT role_list statement to enable all roles except those in the comma-delimited role_list . There is no way to selectively disable a single role. Also, you cannot disable roles that you inherit by way of another role without disabling the parent role. For example, if you have been granted the DBA , RESOURCE , and CONNECT roles, you inherit several roles through 95127c12.indd 683 2/17/09 2:43:45 PM Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 684 Chapter 12 N Implementing Security and Auditing the DBA role when it is enabled. If you want to disable the SCHEDULER_ADMIN role you inher- ited through the DBA role, you cannot do that. The options you have are that you can disable the DBA role or you can create a new role similar to the DBA role without the SCHEDULER_ ADMIN role and use that role. Setting Default Roles Roles that are enabled by default when you log on are called default roles. You do not need to specify a password for default roles and do not have to execute a SET ROLE statement to enable a default role. Change the default roles for a user account with an ALTER USER DEFAULT ROLE role_list statement. The role_list can include the keywords ALL , NONE , and EXCEPT , in the same manner as with a SET ROLE statement. Including a password-protected role in the role_list defeats the purpose of password protecting the role because it is automatically enabled without the password. When you cre- ate a role, you are implicitly granted that role with the admin option, and it is configured as a default role for your account. For example, to create the role EMPLOYEE , grant it to user scott , and configure all of scott ’s roles except PLUSTRACE as default roles, run the following: CREATE ROLE employee; GRANT employee TO scott; ALTER USER scott DEFAULT ROLE ALL EXCEPT plustrace; Because the creator of a role automatically has that role assigned as a default role, admin- istrative users (such as SYS or SYSTEM ) who create many roles may need to alter their default role list. If you attempt to log on with more default roles than allowed by the MAX_ENABLED_ ROLES initialization parameter, you will raise an exception, and your logon will fail. A Password-Protected Role Lucinda works in HR and needs to be able to modify an employee’s salary after they have a review and their raise is approved. The HR application ensures that the raise is approved and falls within corporate guidelines. Although Lucinda needs to be able to change employee salaries, she should be allowed to do so only from within the HR appli- cation, because it ensures that business rules are followed. You wisely choose to use a password-protected role to satisfy these requirements. Update privilege on the salary table is granted to the password-protected role salary_admin . Lucinda is then granted the salary_admin role, but she is not told the password for it. The HR application has the password encoded within it, so when Lucinda runs the HR appli- cation, unknown to her, a SET ROLE salary_admin IDENTIFY BY password statement is executed, enabling the role and allowing her to change the salary. If Lucinda tries to execute an UPDATE statement on the salary table from SQL*Plus, she will get an insufficient privileges error. 95127c12.indd 684 2/17/09 2:43:46 PM Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Granting and Revoking Privileges 685 Default Database Roles When you create a new Oracle 11g database, Oracle creates several roles in the database based on the options you chose at the database creation. The following are few of the important roles that are created automatically during database creation: CONNECT     This role has only one privilege, CREATE SESSION . RESOURCE    This role has the privileges required to create common objects in the user’s schema. DBA     This is the most powerful role in the database. Only database administrators should be given this role. This role has all the system privileges and several administrative privileges. SELECT_CATALOG_ROLE     This role gives the user access to query the data dictionary views. EXECUTE_CATALOG_ROLE     This role gives the user privileges to execute the packages and procedures in the data dictionary. DELETE_CATALOG_ROLE     This role gives the user the ability to delete records from the system audit table ( SYS.AUD$ ). To list all the roles defined in the database, query the data dictionary view DBA_ROLES . To view the system privileges granted to a role, query the DBA_SYS_PRIVS dictionary view. For example, the following query lists the system privileges granted to the RESOURCE role: SQL> SELECT grantee, privilege, admin_option 2 FROM dba_sys_privs 3 WHERE grantee = ‘RESOURCE’ SQL> / GRANTEE PRIVILEGE ADM -------------------- -------------------- --- RESOURCE CREATE TRIGGER NO RESOURCE CREATE SEQUENCE NO RESOURCE CREATE TYPE NO RESOURCE CREATE PROCEDURE NO RESOURCE CREATE CLUSTER NO RESOURCE CREATE OPERATOR NO RESOURCE CREATE INDEXTYPE NO RESOURCE CREATE TABLE NO 8 rows selected. SQL> 95127c12.indd 685 2/17/09 2:43:46 PM Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 686 Chapter 12 N Implementing Security and Auditing Applying the Principle of Least Privilege The principle of least privilege states that each user should be given only the minimal privi- leges needed to perform their job. This principle is a central tenet to the initially closed philosophy whereby all access is initially closed or unavailable and access is opened on a need-to-know basis. Highly secure environments typically operate under an initially closed philosophy. The contrasting philosophy is an initially open philosophy, whereby all access is by default open to all users and only sensitive areas are closed. Academic or learning environments typically operate under an initially open philosophy. Many IT organizations want the most secure policies for production systems, which calls for the initially closed approach to security. To support the need for administrators and programmers to quickly learn new technology, these shops frequently create “sand- box” systems that follow the initially open philosophy. These sandbox systems afford their limited users the learning benefit of the initially open approach, while not storing or giving gateway access to any sensitive information elsewhere in the enterprise. To implement the principle of least privilege on your production or development systems, you should take several actions, or best practices, while setting up or locking down the database. Let’s take a look at these: Protect the data dictionary Ensure that users with the SELECT ANY TABLE privilege cannot access the tables that underlie the data dictionary by setting O7_DICTIONARY_ACCESSIBILITY = FALSE . This is the default setting. Revoke unnecessary privileges from PUBLIC By default, several packages and roles are granted to the special user PUBLIC . Review these privileges, and revoke the EXECUTE privi- lege from PUBLIC if these packages are not necessary. Some of these packages include the following: UTL_TCP     This permits the grantee to establish a network connection to any waiting TCP/IP network service. Once a connection is established, arbitrary information can be sent and received directly from the database to and from the other TCP services on your network. If your organization is concerned about information exchange over TCP/ IP, revoke the EXECUTE  privilege on this package from PUBLIC . Grant privileges on this package only to those users who need it. UTL_SMTP     This permits the grantee to send arbitrary email. If your organization is con- cerned about information exchange via email, revoke the EXECUTE privilege on this pack- age from PUBLIC . Grant privileges on this package only to those users who need it. UTL_HTTP     This permits the grantee to send and receive arbitrary data via the HTTP protocol. If your organization is concerned about information exchange via HTTP, revoke the EXECUTE privilege on this package from PUBLIC . Grant privileges on this package only to those users who need it. UTL_FILE     This permits the grantee to read and write text data to and from arbitrary operating-system files that are in the designated directories. UTL_FILE does not manage concurrency, so multiple user sessions can step on each other, overwriting changes via UTL_FILE . Consider revoking the EXECUTE privilege on this package from PUBLIC . 95127c12.indd 686 2/17/09 2:43:46 PM Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Granting and Revoking Privileges 687 DBMS_OBFUSCATION_TOOLKIT and DBMS_CRYPTO     These permit the grantee to employ encryption technologies. In a managed environment using encryption, the keys are stored and managed. If encryption keys are lost, the encrypted data is undecipherable. Consider revoking the EXECUTE privilege on these packages from PUBLIC . You can revoke the EXECUTE privileges like this: REVOKE EXECUTE ON utl_tcp FROM PUBLIC; REVOKE EXECUTE ON utl_smtp FROM PUBLIC; REVOKE EXECUTE ON utl_http FROM PUBLIC; REVOKE EXECUTE ON utl_file FROM PUBLIC; REVOKE EXECUTE ON dbms_obfuscation_toolkit FROM PUBLIC; REVOKE EXECUTE ON dbms_crypto FROM PUBLIC; You can query the data dictionary to see what other packages may need to be locked down by revoking the EXECUTE privilege from PUBLIC . Here is a query to list the packages, owned by user SYS , that have the EXECUTE privilege granted to PUBLIC : SELECT table_name FROM dba_tab_privs p ,dba_objects o WHERE p.owner=o.owner AND p.table_name = o.object_name AND p.owner = ‘SYS’ AND p.privilege = ‘EXECUTE’ AND p.grantee = ‘PUBLIC’ AND o.object_type=’PACKAGE’; Limit the users who have administrative privileges Grant administrative privileges to user accounts cautiously. Some powerful administrative privileges and roles to exercise caution with include the following: SYSDBA     This gives the grantee the highest level of privileges with the Oracle Database software. A clever user with the SYSDBA role can circumvent most database security mea- sures. There is usually no good reason to grant this role to any account except SYS , and the SYS password should be both cautiously guarded and changed regularly. Also, guard operating-system accounts carefully. If you are logged on to the database server using a privileged operating-system account, you might be able to connect to the database with SYSDBA authority and no password by entering connect / as sysdba in SQL*Plus. DBA     This permits the grantee to assign privileges and manipulate data throughout the database. A clever user with the DBA role can circumvent most database security mea- sures. Grant this role only to those users who need it. The ANY system privileges SELECT ANY TABLE , GRANT ANY ROLE , DELETE ANY TABLE , and so on, permit the grantee to assign privileges and manipulate data throughout the 95127c12.indd 687 2/17/09 2:43:46 PM Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 688 Chapter 12 N Implementing Security and Auditing database. A malicious user with the one of these roles can wreak havoc in your database. Grant these privileges only to those users who need them. Do not enable REMOTE_OS_AUTHENT The default setting for the initialization parameter REMOTE_OS_AUTHENT is FALSE . There is rarely a reason to enable this feature. When set to TRUE , this parameter tells the database to trust any client to authenticate externally authenticated accounts. For example, if you have an externally identified account named ORACLE that has DBA privileges for use in administrative scripts running on the database server (a common practice), setting this parameter to TRUE will allow someone with a notebook or desktop PC with a locally created ORACLE account to connect to your database with DBA credentials and no password. Controlling Resource Usage by Users An Oracle 11g database lets you limit some resources that your user accounts consume. Disk-space limits are governed by tablespace quotas (discussed in “Assigning Tablespace and Quotas” earlier in the chapter); CPU and memory limits are implemented with profiles. CPU and session-oriented resource limits are managed through profiles. Profiles let you set limits for several resources, including CPU time, memory, and the number of logical reads performed during a user session or database call. A database call is either a parse, an execute, or a fetch. Usually, the database implicitly performs these calls for you. You can explicitly make these database calls from Java, PL/SQL, or Oracle Call Interface (OCI) programs. A logical read is a measure of the amount of work that the database performs while executing SQL statements. Statements that generate more logical reads require the database to perform more work than statements generating fewer logical reads. Technically, a logi- cal read is counted for each row accessed via ROWID (index access) and for each data block accessed via a multiblock read (full-table scan or index fast full scan). To enable resource limit restrictions with profiles, first enable them in the database by setting the initialization parameter resource_limit to TRUE , like this: ALTER SYSTEM SET resource_limit = TRUE SCOPE = BOTH; To assign resource limits to a profile, use the CREATE PROFILE or ALTER PROFILE state- ment with one or more of the kernel resource parameters. The following is an example of the CREATE PROFILE statement, with all the resources that can be controlled. A resource value of DEFAULT indicates that the value is derived from the DEFAULT profile. Initially, the DEFAULT profile has all the system resources set to UNLIMITED . CREATE PROFILE “TEST1” LIMIT CPU_PER_SESSION DEFAULT CPU_PER_CALL DEFAULT CONNECT_TIME DEFAULT IDLE_TIME 10 95127c12.indd 688 2/17/09 2:43:46 PM Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. Controlling Resource Usage by Users 689 SESSIONS_PER_USER DEFAULT LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL 250000 PRIVATE_SGA 25000 COMPOSITE_LIMIT DEFAULT; Each resource is explained here: CONNECT_TIME     This limits any session established by a user having this profile set to the specified number of minutes. Connection time is sometimes called wall clock time to dif- ferentiate it from CPU time. When a session exceeds the specified number of minutes, the database rolls back any uncommitted changes and terminates the session. The next call to the database raises an exception. You can use the special value UNLIMITED to tell the data- base that there is no limit to a session’s duration. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this: CREATE PROFILE agent LIMIT CONNECT_TIME 10; ALTER PROFILE data_analyst LIMIT CONNECT_TIME UNLIMITED; CPU_PER_CALL     This limits the amount of CPU time that can be consumed by any single database call in any session established by a user with this profile. The specified value is in hundredths of a second and applies to a parse, an execute, or a fetch call. These calls are implicitly performed by the database for any SQL statement executed in SQL*Plus and can be explicitly called from OCI, Java, and PL/SQL programs. When this limit is breached, the statement fails and is automatically rolled back, and an exception is raised. The user can then commit or roll back any uncommitted changes in the transaction. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this: CREATE PROFILE agent LIMIT CPU_PER_CALL 3000; ALTER PROFILE data_analyst LIMIT CPU_PER_CALL UNLIMITED; CPU_PER_SESSION     This limits the amount of CPU time that can be consumed in any ses- sion established by a user with this profile. The specified value is in hundredths of a second and applies to a parse, an execute, or a fetch. When this limit is breached, the current state- ment fails, the transaction is automatically rolled back, and an exception is raised. The user can then commit or roll back any uncommitted changes in the transaction before logging off. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this: CREATE PROFILE agent LIMIT CPU_PER_CALL 30000; ALTER PROFILE data_analyst LIMIT CPU_PER_CALL UNLIMITED; IDLE_TIME    This limits the duration of time between database calls to the specified number of minutes. If a user having this profile exceeds this setting, the next statement fails, and the user is allowed to either commit or roll back any uncommitted changes before logging off. Long-running statements are not affected by this setting. Set IDLE_TIME in a CREATE PROFILE or ALTER PROFILE statement like this: CREATE PROFILE agent LIMIT IDLE_TIME 10; ALTER PROFILE daemon LIMIT IDLE_TIME UNLIMITED; 95127c12.indd 689 2/17/09 2:43:46 PM Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. 690 Chapter 12 N Implementing Security and Auditing LOGICAL_READS_PER_CALL    This caps the amount of work that any individual database call performs to the specified number of logical reads. The database call is either a parse, an execute, or a fetch. If the limit is exceeded, the database rolls back the statement, returns an error to the calling program, and allows the user to either commit or roll back any uncom- mitted changes. Logical reads are computed as the sum of consistent gets plus current mode gets. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this: CREATE PROFILE agent LIMIT LOGICAL_READS_PER_CALL 2500; ALTER PROFILE data_analyst LIMIT LOGICAL_READS_PER_CALL 1000000; LOGICAL_READS_PER_SESSION     This limits the amount of database work that a user’s ses- sion can consume to the specified number of logical reads. When the limit is exceeded, the current statement fails and an exception is raised, and the user must either commit or roll back the transaction and end the session. Logical reads are computed as the sum of consis- tent gets plus current mode gets. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this: CREATE PROFILE agent LIMIT LOGICAL_READS_PER_SESSION 250000; ALTER PROFILE data_analyst LIMIT LOGICAL_READS_PER_SESSION 35000000; PRIVATE_SGA     This limits the amount of system global area (SGA) memory in bytes that a user connecting with shared servers (via a multithreaded server [MTS]) can allocate to the persistent area in the program global area (PGA). This area contains bind information among other items. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this: CREATE PROFILE agent LIMIT PRIVATE_SGA 2500; ALTER PROFILE data_analyst LIMIT PRIVATE_SGA UNLIMITED; SESSIONS_PER_USER     This restricts a user with this profile to the specified number of database sessions. This setting can be useful to discourage DBAs from all connecting to a shared administrative account to do their work when corporate policy indicates that they should be connecting to their individual accounts. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this: CREATE PROFILE admin_profile LIMIT SESSIONS_PER_USER 2; ALTER PROFILE data_analyst LIMIT SESSIONS_PER_USER 6; COMPOSITE_LIMIT     This limits the number of service units that can be consumed dur- ing a user session. Service units are calculated as the weighted sum of CPU_PER_SESSION , LOGICAL_READS_PER_SESSION , CONNECT_TIME , and PRIVATE_SGA values. The weightings are established with the ALTER RESOURCE COST statement and can be viewed from the RESOURCE_COST data dictionary view. This COMPOSITE_LIMIT allows you to cap the resource consumption of user groups in more complex ways than a single resource limit. Set this parameter in a CREATE PROFILE or ALTER PROFILE statement like this: CREATE PROFILE admi_profile LIMIT COMPOSITE_LIMIT UNLIMITED; ALTER PROFILE data_analyst LIMIT COMPOSITE_LIMIT 100000; 95127c12.indd 690 2/17/09 2:43:46 PM Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark. [...]... RETURN(TRUE); END; / Oracle 11g provides the PL/SQL code to create a password complexity verify function The script is called utlpwdmg.sql and is in the $ORACLE_ HOME/rdbms/admin directory The name of the function created using this script is called verify_function _11g Auditing Database Activity Auditing involves monitoring and recording specific database activity An Oracle 11g database supports four... system privileges You can create and use roles to make managing these database privileges easier You can enable object, statement, privilege, and fine-grained auditing to help you monitor and record sensitive database activity By default, DBCA enables several key auditing features when you create an Oracle 11g database The Oracle 11g database has several powerful features (user accounts and packages)... stored in either of these locations NN Database NN Operating-system files You tell the Oracle Database where to record audit trail records by setting the initialization parameter audit_trail The default is DB, as in AUDIT_TRAIL=DB, which tells the database to record audit records in the database AUDIT_TRAIL=DB,EXTENDED tells the database to record audit records in the database together with bind variables... -10-Jun-2004 09:48:14 19-Jun-2004 14:50:47 USERHOST -XYZcorp\CHIPNOTEBOOK XYZcorp\HR_PC2 Summary Oracle 11g gives you a well-stocked toolkit for managing users and securing the database You create and manage user accounts with the CREATE, ALTER, and DROP USER statements User passwords in Oracle 11g are case sensitive You can assign tablespace resources to be used for sorting that are different... (shut down and start up) your database instance for the change to take effect When recorded in the database, most audit entries are recorded in the SYS.AUD$ table On Unix systems, operating-system audit records are written into files in the directory specified by the initialization parameter audit_file_dest (which is set to $ORACLE_ BASE/ admin/ $ORACLE_ SID/adump if the database is created using DBCA)... Know how to enable roles. ​ Know when a role needs to be enabled and how to enable it  ​ Be able to secure your database.  ​ Make sure you know how to lock down your database  ​ Know which packages should be secured and how to secure them Know how to implement password security. ​ An Oracle 11g database gives you several  ​ standard password-security settings Know what is available in a profile and what... scope=both; C alter user set failed_login_attempts = 3; D alter system set failed_login_attempts = 3 scope=both; 18 Where can the database write audit_trail records? A In a database table B In a file outside the database C Both in the database and in an operating-system file D Either in the database or in an operating-system file 714  Chapter 12    Implementing Security and Auditing n 19 User JAMES has a table... query user tables in the database To avoid the error, HR_QUERY must be defined as a default role for John, or John should use the SET ROLE statement A password is not needed for SET ROLE because the role is not password protected 15 C.  In Oracle 11g user passwords are case sensitive The username is not case sensitive if you did not enclose it in double quotes 16 B.  In Oracle 11g, the default value... SUCCESS USER_NAME BY ACCESS BY ACCESS NOT SET JUANITA 700  Chapter 12    Implementing Security and Auditing n Oracle Database 11g comes with the following auditing enabled by default: NN ALTER ANY PROCEDURE NN CREATE SESSION NN ALTER ANY TABLE NN CREATE USER NN ALTER DATABASE NN DROP ANY PROCEDURE NN ALTER PROFILE NN DROP ANY TABLE NN ALTER SYSTEM NN DROP PROFILE NN ALTER USER NN DROP USER... records are written to the Event Viewer log file The four levels of auditing are described in the following sections Certain database activities are always recorded in the OS audit files Database connections using administrator privileges such as SYSDBA and SYSOPER are recorded Database startup and shutdown are also recorded in the OS audit files Managing Statement Auditing Statement auditing involves . When you create a new Oracle 11g database, Oracle creates several roles in the database based on the options you chose at the database creation. The following. called verify_function _11g . Auditing Database Activity Auditing involves monitoring and recording specific database activity. An Oracle 11g data- base supports