CHAPTER 16

Oracle Security

Exam Objectives

• 062.6.1    Create and Manage Database User Accounts

• 062.6.2    Grant and Revoke Privileges

• 062.6.3    Create and Manage Roles

• 062.6.4    Create and Manage Profiles

• 062.9.1    Explain DBA Responsibilities for Security and Auditing

• 062.9.2    Enable Standard Database Auditing and Unified Auditing

Database security has several aspects. First, there is authentication: Who can connect? How does one identify them? Second, there is authorization: What are users allowed to do? How does one restrict their actions? Finally, there is auditing: Given that users can connect and perform certain actions, how do you track what they are doing? These aspects are covered in this chapter.

When a user logs on to the database, following some means of identification, they connect to a user account, which defines their initial access permissions and the attributes of the session. Associated with a user account is a schema. The terms user, user account, and schema can often be used interchangeably in the Oracle environment, but they are not always the same thing. A user is a person who connects to a user account by establishing a session against the instance and logging on with the user account name. A schema is a set of objects owned by a user account.

A user account must be granted privileges before a session (or sessions) connected to the account can do anything. Many different privileges can be granted for many different objects and actions, and managing privileges individually is not practical for any but the simplest systems. Privileges are usually grouped into roles, which make privilege administration much easier.

You can use profiles to manage passwords and (to a limited extent) control the resources a user is allowed to consume within the instance and the database.

In many environments, users will have permission to do certain things, but that doesn’t mean they should be able to do them without a record being kept. For example, a database administrator (DBA) will (usually) be able to read and write any row in any table in the database. This is part of their job. For example, if a business data corruption occurs (perhaps through user error or through application software issues), the DBA will need to use SQL*Plus or some other tool that can bypass all the application security and rules and edit the data to fix it. But that does not mean the DBA should be diving into tables of sensitive data without good reason. Actions such as this cannot be prevented, but they can, and must, be tracked. This is the purpose of auditing: to record actions that are permitted but potentially harmful.

Create and Manage Database User Accounts

To establish a session against an instance and a database, a user must connect to a user account. The account must be specified by name and authenticated by some means. The way the account was created will set up a range of attributes for the session, some of which can be changed later while the session is in progress.

User Account Attributes

A user account has a number of attributes defined at account creation time. These attributes will be applied to sessions that connect to the account, although some can be modified by the session or the DBA while the session is running. These attributes are as follows:

•  Username

•  Authentication method

•  Default tablespace

•  Tablespace quotas

•  Temporary tablespace

•  User profile

•  Account status

All of these should be specified when creating the user, although only username and authentication methods are mandatory; the others have defaults.

Username

The username must be unique in the database and must conform to certain rules. A username must begin with a letter, must be no more than 30 characters, and can consist of only letters, digits, and the dollar sign ($) and underscore (_) characters. A username cannot be a reserved word. The letters are case sensitive but will be automatically converted to uppercase. All these rules (with the exception of the length) can be broken if the username is specified within double quotes, as shown in Figure 16-1.

Figure 16-1    How to create users with nonstandard names

In the first example in the figure, the username JOHN is created. This was entered in lowercase but will have been converted to uppercase, as shown in the first query. The second example uses double quotes to create the user with a name in lowercase. The third and fourth examples use double quotes to bypass the rules on characters and reserved words; both of these would fail without the double quotes. If a username includes lowercase letters or illegal characters or is a reserved word, then double quotes must always be used to connect to the account subsequently.

Default Tablespace and Quotas

Every user account has a default tablespace, which is the tablespace where any schema objects (such as tables or indexes) created by a user will reside. It is possible for users to own objects in any tablespace on which they have been given a quota, but unless another tablespace is specified when creating the object, it will go into a user’s default tablespace.

A database-wide default tablespace will be applied to all user accounts if a default tablespace is not specified when creating the user. The default can be set when creating the database and then changed later with the following:

If a default tablespace is not specified when the database is created, it will be set to the SYSTEM tablespace.

A quota is the amount of space in a tablespace that a user is allowed to occupy. The user can create objects and allocate extents to them until the quota is reached. If the user has no quota on a tablespace, the user cannot create any objects at all. Quotas can be changed at any time. If a user’s quota is reduced to less than the size of the objects the user already owns (or even reduced to zero), the objects will survive and will still be usable, but they will not be permitted to get any bigger.

Figure 16-2 shows how to investigate and set quotas.

Figure 16-2    Managing user quotas

The first query in the figure is against DBA_USERS and determines the default and temporary tablespaces for the user JOHN, created in Figure 16-1. DBA_USERS has one row for every user account in the database. User JOHN has picked up the database defaults for the default and temporary tablespaces, which are shown in the last query against DATABASE_PROPERTIES.

The two ALTER USER commands in Figure 16-2 give JOHN the capability to take up to 10MB of space in the USERS tablespace and to have an unlimited amount of space in the EXAMPLE tablespace. The query against DBA_TS_QUOTAS confirms this; the number −1 is how “unlimited” is represented. At the time the query was run, JOHN had not created any objects, so the figures for BYTES are zeros, indicating that he is not currently using any space in either tablespace.

Temporary Tablespace

Permanent objects (such as tables) are stored in permanent tablespaces; temporary objects are stored in temporary tablespaces. A session will need space in a temporary tablespace if it needs space for certain operations that exceed the space available in the session’s Program Global Area (PGA). Every user account is assigned a temporary tablespace, and all user sessions connecting to the account will share this temporary tablespace.

The query against DBA_USERS in Figure 16-2 shows user JOHN’s temporary tablespace, which in this case is the database default temporary tablespace because an alternative temporary tablespace was not specified when the user was created. This is shown by the last query in Figure 16-2, against DATABASE_PROPERTIES.

A user does not need to be granted a quota on their temporary tablespace. This is because the objects in it are not actually owned by the user; they are owned by the SYS user, who has an unlimited quota on all tablespaces.

To change a user’s temporary tablespace (which will affect all future sessions that connect to the account), use an ALTER USER command.

Account Status

Every user account has a certain status, as listed in the ACCOUNT_STATUS column of DBA_USERS. There are five possibilities:

•  OPEN    The account is available for use.

•  LOCKED    This indicates that the DBA deliberately locked the account. No user can connect to a locked account.

•  EXPIRED    This indicates that the lifetime has expired. Passwords can have a limited lifetime. No user can connect to an EXPIRED account until the password is reset.

•  EXPIRED (GRACE)    This indicates that the grace period is in effect. A password need not expire immediately when its lifetime ends; it may be configured with a grace period during which users connecting to the account have the opportunity to change the password.

•  LOCKED (TIMED)    This indicates that the account is locked because of failed login attempts. An account can be configured to lock automatically for a period after an incorrect password is presented a certain number of times.

To lock and unlock an account, use these commands:

To force a user to change their password, use this command:

This will immediately start the grace period, forcing the user to change their password at their next login attempt. There is no such command as ALTER … UNEXPIRE. The only way to make the account fully functional again is to reset the password.

Authentication Methods

A user account must have an authentication method, in other words, some means whereby the database can determine whether the user attempting to create a session connecting to the account is allowed to do so. The simplest technique is by presenting a password that will be matched against a password stored within the database, but there are alternatives. These are the possibilities:

•  Operating system authentication

•  Password file authentication

•  Password authentication

•  External authentication

•  Global authentication

The first two techniques are used only for administrators; the last requires a Lightweight Directory Access Protocol (LDAP) directory server. The LDAP directory server is the Oracle Internet Directory, shipped as part of the Oracle Fusion Middleware Suite.

Operating System and Password File Authentication

To enable operating system and password file authentication (the two go together) for an account, you must grant the user an administration role. There are six of these:

Grant the roles as follows:

Granting any (or all) of these privileges will copy the user’s password from the data dictionary into the external password file, where it can be read by the instance even if the database is not open. Following database creation, the only user with these privileges is SYS. It also allows the instance to authenticate users by checking whether the operating system user attempting the connection is a member of the operating system group mapped to the Oracle group name. On Linux, the operating system groups must be specified when installing the Oracle Home. On Windows, there is no choice. The groups are created implicitly with these names:

To use password file authentication, the user can connect with this syntax with SQL*Plus:

Note that you can use password file authentication for a connection to a remote database over Oracle Net. This is a logical impossibility with operating system authentication because when using a remote database, one never logs on to the remote operating system.

To use operating system authentication, the user can connect with this syntax with SQL*Plus:

The operating system password is not stored by Oracle, and therefore there are no issues with changing passwords.

The equivalent of these syntaxes is also available when connecting with Database Express, by selecting the AS SYSDBA check box in the Database Express login window. To determine to whom the SYSDBA and SYSOPER privileges have been granted, query the view V$PWFILE_USERS. A connection with operating system or password file authentication is always possible, no matter what state the instance and database are in, and is necessary to issue STARTUP and SHUTDOWN commands.

Password Authentication

The syntax for a connection with password authentication using SQL*Plus is as follows:

When the user connects with password authentication, the instance will validate the password given against the password hash stored with the user account in the data dictionary. For this to work, the database must be open; it is logically impossible to issue STARTUP and SHUTDOWN commands when connected with password authentication. The user SYS is not permitted to connect with password authentication; only password file, operating system, and LDAP authentication are possible for SYS.

Usernames are case sensitive but are automatically converted to uppercase unless specified within double quotes. Passwords are case sensitive, and there is no automatic case conversion. It is not necessary to use double quotes; the password will always be read exactly as entered.

Any user can change their user account password at any time, or a highly privileged user (such as SYSTEM) can change any user account password. The syntax (whether you are changing your own password or another one) is as follows:

External Authentication

If a user account is created with external authentication, Oracle will delegate the authentication to an external service; it will not prompt for a password. If the Advanced Security Option has been licensed, then the external service can be a number of third-party services, such as a Kerberos server or a RADIUS server. When a user attempts to connect to the user account, rather than authenticating the user itself, the database instance will accept (or reject) the authentication according to whether the external authentication service has authenticated the user. For example, if Kerberos is being used, the database will check that the user does have a valid Kerberos token.

Without the Advanced Security Option, the only form of external authentication that can be used is operating system authentication. This is a requirement for SYSDBA and SYSOPER accounts (as already discussed) but can also be used for normal users. The technique is to create an Oracle user account with the same name as the operating system user account but prefixed with a string specified by the instance parameter OS_AUTHENT_PREFIX. This parameter defaults to the string OPS$. To check its value, use a query such as this:

On Linux or Unix, external operating system authentication is simple. Assuming that OS_AUTHENT_PREFIX is set to the default and that there is an operating system user called jwatson, if you create an oracle user and grant him the CREATE SESSION privilege, he will be able to log in with no password and will be connected to the database user account ops$jwatson.

Under Windows, when Oracle queries the operating system to find the identity of the user, Windows will usually (depending on the details of Windows security configuration) return the username prefixed with the Windows domain. Assuming that the Windows logon ID is John Watson (including a space), that the Windows domain is JWACER (which happens to be the machine name), and that the OS_AUTHENT_PREFIX is set to the default, the command will be as follows:

Note that the username must be in uppercase, and because of the illegal characters (a backslash and a space), it must be enclosed in double quotes.

Creating Accounts

The CREATE USER command has only two required arguments: a username and a method of authentication. Optionally, it can accept a clause to specify a default tablespace and a temporary tablespace, one or more quota clauses, a named profile, and commands to lock the account and expire the password. This is a typical example (with line numbers added):

Only the first line is required; there are defaults for everything else. Here is what the command does, broken down line by line:

1.  Provide the username and a password for password authentication

2.  Provide the default and temporary tablespaces

3.  Set up quotas on the default and another tablespace

4.  Nominate a profile for password and resource management

5.  Force the user to change the password immediately

6.  Make the account available for use (which would have been the default)

Every attribute of an account can be adjusted later with ALTER USER commands, with the exception of the name. This is how to change the attributes:

Having created a user account, it may be necessary to drop it.

This command will succeed only if the user does not own any objects—if the schema is empty. If you do not want to identify all the objects owned and drop them first, they can be dropped with the user by specifying CASCADE.

Exercise 16-1: Create Users    In this exercise, you will create some users to be used for the remaining exercises in this chapter. It is assumed that there is a permanent tablespace called EXAMPLE and a temporary tablespace called TEMP. If these don’t exist, either create them or use any other suitable tablespaces. Here are the steps to follow:

1.  Connect to your database with SQL*Plus as a highly privileged user, such as SYSTEM or SYS.

2.  Create three users.

3.  Confirm that the users have been created with Database Express. From the database home page, the navigation path is the Security tab | Users link. The users should look something like those shown in Figure 16-3.

Figure 16-3    Users displayed in Database Express

4.  From SQL*Plus, attempt to connect as user ALOIS.

5.  When prompted, select a new password (such as oracle). This won’t get you anywhere because ALOIS does not have the CREATE SESSION privilege.

6.  Refresh the Database Express window and note that the status of the ALOIS account is no longer EXPIRED (indicated with the clock symbol) but rather OPEN (indicated with a tick) because his password has been changed.

Grant and Revoke Privileges

By default, no one can do anything in an Oracle database. A user cannot even connect without being granted a privilege. And once this has been done, they still can’t do anything useful (or dangerous) without being given more privileges. Privileges are assigned to user accounts with a GRANT command and withdrawn with a REVOKE. Additional syntax can give a user the ability to grant any privileges they have to other users. By default, only the DBAs (SYS and SYSTEM) have the right to grant any but the most limited privileges.

Privileges come in two groups: system privileges, which (generally speaking) let users perform actions that affect the data dictionary, and object privileges, which let users perform actions that affect data.

System Privileges

There are more than 200 system privileges. Most apply to actions that affect the data dictionary, such as creating tables or users. Others affect the database or the instance, such as creating tablespaces, adjusting instance parameter values, and establishing a session. These are some of the more commonly used privileges:

•  CREATE SESSION    This privilege lets the user connect. Without it, the user cannot even log on to the database.

•  RESTRICTED SESSION    If the database is started with STARTUP RESTRICT or adjusted with ALTER SYSTEM ENABLE RESTRICTED SESSION, only users with this privilege will be able to connect.

•  ALTER DATABASE    This gives access to many commands necessary for modifying physical structures.

•  ALTER SYSTEM    This gives control over instance parameters and memory structures.

•  CREATE TABLESPACE    This is used along with the ALTER TABLESPACE and DROP TABLESPACE privileges; these privileges will let a user manage tablespaces.

•  CREATE TABLE    This lets the grantee create tables in his own schema; it includes the ability to alter and drop them, to run SELECT and Data Manipulation Language (DML) commands on them, and to create, alter, or drop indexes on them.

•  GRANT ANY OBJECT PRIVILEGE    Lets the grantee grant object permissions on all objects, including those he does not own, to others (but not to himself).

•  CREATE ANY TABLE    The grantee can create tables that belong to other users.

•  DROP ANY TABLE    The grantee can drop tables belonging to any other users.

•  INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE    The grantee can execute these DML commands against tables owned by all other users.

•  SELECT ANY TABLE    The grantee can SELECT from any table in the database, with one provision: Tables owned by SYS, including the data dictionary tables, are not visible.

The syntax for granting system privileges is as follows:

After creating a user account, a command such as the following will grant the system privileges commonly assigned to users who will be involved in developing applications:

These privileges let the user connect and configure a session and then create objects to store data and PL/SQL objects. These objects can exist only in their own schema; they will have no privileges against any other schema. The object creation will also be limited by the quotas they may (or may not) have been assigned on various tablespaces.

A variation in the syntax lets the grantee pass their privilege on to a third party. This is an example:

This gives SCOTT the ability to create tables in his own schema and also to issue the GRANT CREATE TABLE TO command himself.

If a privilege is revoked from a user, any actions the user performed using that privilege (such as creating tables) remain intact. Also, if the user has been granted and has used the ADMIN OPTION, any users to whom the user passed on the privilege will retain it. There is no record kept of the grantor of a system privilege, so it is not possible for a REVOKE to cascade. Figure 16-4 illustrates this.

Figure 16-4    GRANT and REVOKE from SQL*Plus

The ANY privileges give permissions against all relevant objects in the database. Thus, the following will let SCOTT query every table in every schema in the database:

It is often considered bad practice to grant the ANY privileges to any user other than the system administration staff.

Object Privileges

Object privileges give the ability to perform SELECT, INSERT, UPDATE, and DELETE commands against tables and related objects as well as to execute PL/SQL objects. These privileges do not exist for objects in the users’ own schemas; if a user has the system privilege CREATE TABLE, they can perform SELECT and DML operations against the tables they create with no further need for permissions.

The object privileges apply to different types of objects, listed here:

The syntax is as follows:

Here is an example:

Variations include the use of ALL, which will apply all the permissions relevant to the type of object, and the ability to nominate particular columns of views or tables.

This code will let SCOTT query all columns of HR’s EMPLOYEES table but write to only one nominated column, SALARY. Then SCOTT is given all the object privileges (SELECT and DML) on HR’s REGIONS table.

Using WITH GRANT OPTION (or with Database Express, navigate to Security | Users | Privileges and Roles | Edit) lets a user pass their object privilege on to a third party. Oracle retains a record of who granted object privileges to whom; this allows a REVOKE of an object to cascade to all those in the chain. Consider this sequence of commands:

At the conclusion of these commands, neither SCOTT nor JON nor SUE has the SELECT privilege against HR.EMPLOYEES.

Exercise 16-2: Grant Direct Privileges    In this exercise, you will grant some privileges to the users created in Exercise 16-1 and prove that they work. Follow these steps:

1.  Connect to your database as user SYSTEM with SQL*Plus and then grant CREATE SESSION to user ALOIS.

2.  Open another SQL*Plus session and connect as ALOIS. This time, the login will succeed.

3.  As ALOIS, attempt to create a table.

This will fail with the message “ORA-01031: insufficient privileges.”

4.  In the SYSTEM session, grant ALOIS the CREATE TABLE privilege.

5.  In the ALOIS session, try again.

This will fail with the message “ORA-01950: no privileges on tablespace ‘EXAMPLE’.”

6.  In the SYSTEM session, give ALOIS a quota on the EXAMPLE tablespace.

7.  In the ALOIS session, try again. This time, the creation will succeed.

8.  As ALOIS, grant object privileges on the new table.

9.  To retrieve information regarding these grants, as SYSTEM, run these queries:

10.  Revoke the privileges granted to AFRA and ANJA.

11.  Confirm the revocations by rerunning the first query from step 9.

Create and Manage Roles

Managing security with directly granted privileges works but has two problems. First, it can be a huge workload; an application with thousands of tables and users could need millions of grants. Second, if a privilege has been granted to a user, that user has it in all circumstances; it is not possible to make a privilege active only in certain circumstances. Both these problems are solved by using roles. A role is a bundle of system and/or object privileges that can be granted and revoked as a unit and, having been granted, can be temporarily activated or deactivated within a session.

Creating and Granting Roles

Roles are not schema objects; they aren’t owned by anyone and therefore cannot be prefixed with a username. However, they do share the same namespace as users. It is not possible to create a role with the same name as an already-existing user or to create a user with the same name as an already-existing role.

Create a role with the CREATE ROLE command.

Then grant privileges to the role with the usual syntax, including WITH ADMIN or WITH GRANT OPTION, as desired.

For example, assume that the HR schema is being used as a repository for data to be used by three groups of staff: managerial staff have full access, senior clerical staff have limited access, and junior clerical staff have restricted access. First, create a role that might be suitable for the junior clerks; all they can do is answer questions by running queries.

Anyone granted this role will be able to log on to the database and run SELECT statements against the HR tables. Next, create a role for the senior clerks, who can also write data to the EMPLOYEES and JOB_HISTORY tables.

This role is first granted the HR_JUNIOR role (there is no problem granting one role to another) with the syntax that will let the senior users assign the junior role to others. Then it is granted DML privileges on just two tables. Now, create the managers’ role, which can update all the other tables.

This third role is given the HR_SENIOR role with the ability to pass it on and then gets full control over the contents of all the tables. But note that the only system privilege this role has is CREATE_SESSION, acquired through HR_SENIOR, which acquired it through HR_JUNIOR. Not even this role can create or drop tables; that must be done by the HR user or an administrator with CREATE ANY TABLE and DROP ANY TABLE system privileges.

Note the syntax WITH ADMIN OPTION, which is the same as that for granting system privileges. As with system privileges, the revocation of a role will not cascade; there is no record kept of who has granted a role to whom.

Finally, grant the roles to the relevant staff. If SCOTT is a manager, SUE is a senior clerk, and JON and ROOP are junior clerks, the flow would be as shown in Figure 16-5.

Figure 16-5    Granting roles with SQL*Plus

Predefined Roles

There are dozens of predefined roles in an Oracle database. Here are some that every DBA should be aware of:

•  CONNECT    This role exists only for backward compatibility. In previous releases, it had the system privileges necessary to create data-storing objects, such as tables; with the current release, it has only CREATE SESSION.

•  RESOURCE    Also for backward compatibility, this role can create both data objects (such as tables) and procedural objects (such as PL/SQL procedures).

•  DBA    This has most of the system privileges as well as several object privileges and roles. Any user granted DBA can manage virtually all aspects of the database, except for startup and shutdown.

•  SELECT_CATALOG_ROLE    This has thousands of object privileges against data dictionary objects but no system privileges or privileges against user data. This is useful for junior administration staff who must monitor and report on the database but not be able to see user data.

•  SCHEDULER_ADMIN    This has the system privileges necessary for managing the Scheduler job-scheduling service.

Also, the predefined role PUBLIC is always granted to every database user account. It follows that if a privilege is granted to PUBLIC, it will be available to all users. Therefore, after the following command, all users will be able to query the HR.REGIONS table:

Enabling Roles

By default, if a user has been granted a role, the role will enabled. This means that the moment a session is established connecting to the user account, all the privileges (and other roles) granted to the role will be active. This behavior can be modified by making the role nondefault. Following the example given in the preceding section, this query shows what roles have been granted to JON:

JON has been granted HR_JUNIOR. He does not have administration on the role (so he cannot pass it on to anyone else), but it is a default role—he will have this role whenever he connects. This situation may well not be what you want. For example, JON has to be able to see the HR tables (it’s his job), but that doesn’t mean you want him to be able to dial in from home, at midnight, and hack into the tables with SQL*Plus. You want to arrange things such that he can see the tables only when he is at a terminal in the personnel office, running the HR application during working hours.

Here is how to change the default behavior:

Now when JON logs on, he will not have any roles enabled. Unfortunately, this means he can’t log on at all—because it is only HR_JUNIOR that gives him the CREATE SESSION system privilege. This is easily fixed.

Now when JON connects, only his CONNECT role is enabled, and the current version of CONNECT is not dangerous at all. Within the application, software commands can be embedded to enable the HR_JUNIOR role. The basic command to enable a role within a session, which can be issued by the user at any time, is as follows:

So, there’s no security yet. But if the role is created with the following syntax, then the role can be enabled only by running the PL/SQL procedure nominated by procedure_name:

This procedure can make any number of checks: that the user is working on a particular TCP/IP subnet, that the user is running a particular user process (probably not SQL*Plus), that the time is in a certain range, and so on. Embedding calls to the enabling procedures at appropriate points in an application can switch roles on and off, as required, while leaving them disabled at all times when a connection is made with an ad hoc SQL tool such as SQL*Plus.

Privilege Analysis

It is sometimes difficult to identify what privileges a user has and what privileges the user actually uses, particularly when roles are involved. For this reason, Oracle provides the Privilege Analysis mechanism. The flow is as follows:

1.  Define the scope of what should be analyzed, such as privilege usage throughout the entire database, privileges used that were accessed through certain roles, and privileges used by particular applications.

2.  Start monitoring activity, allow users to work for a period, and stop monitoring activity.

3.  Generate reports on what privileges were used, and what granted privileges were not used, during the analysis period.

The following are the critical procedures:

•  dbms_privilege_capture.create_capture

•  dbms_privilege_capture.enable_capture

•  dbms_privilege_capture.disable_capture

•  dbms_privilege_capture.generate_result

The following are the critical views:

•  dba_used_privs

•  dba_unused_privs

Exercise 16-3: Create and Grant Roles    In this exercise, you will create some roles, grant them to the users, and demonstrate their effectiveness. Follow these steps:

1.  Connect to your database with SQL*Plus as user SYSTEM.

2.  Create two roles, as follows:

3.  Grant some privileges to the roles and then grant USR_ROLE to MGR_ROLE.

4.  As user SYSTEM, grant the roles to AFRA and ANJA.

5.  Connect to the database as user AFRA.

6.  Grant the USR_ROLE to ANJA and then insert a row into ALOIS.T1.

7.  Confirm that ANJA can connect and query ALOIS.T1 but do nothing else.

8.  As user SYSTEM, adjust ANJA so that by default she can log on but do nothing else.

9.  Demonstrate the enabling and disabling of roles.

10.  Query the data dictionary to identify their role usage.

Create and Manage Profiles

You can use a profile to enforce a password. Profiles are always used, but the default profile (applied by default to all users, including SYS and SYSTEM) does very little.

Password Profile Limits

These are the limits that can be applied to passwords:

•  FAILED_LOGIN_ATTEMPTS    Specifies the number of consecutive errors on a password before the account is locked. If the correct password is given before this limit is reached, the counter is reset to zero.

•  PASSWORD_LOCK_TIME    Specifies the number of days to lock an account after FAILED_LOGIN_ATTEMPTS is reached.

•  PASSWORD_LIFE_TIME    Specifies the number of days before a password expires. It may still be usable for a while after this time, depending on PASSWORD_GRACE_TIME.

•  PASSWORD_GRACE_TIME    Specifies the number of days following the first successful login after the password has expired during which the password can be changed. The old password is still usable during this time.

•  PASSWORD_REUSE_TIME    Specifies the number of days before a password can be reused.

•  PASSWORD_REUSE_MAX    Specifies the number of password changes before a password can be reused.

•  PASSWORD_VERIFY_FUNCTION    Specifies the name of a function to run whenever a password is changed. The purpose of the function is assumed to be checking the new password for a required degree of complexity, but it can do pretty much anything you want.

To see which profile is currently assigned to each user, run this query:

By default, all users will be assigned the profile called DEFAULT. Then the view that will display the profiles themselves is DBA_PROFILES.

The DEFAULT profile has these password limits:

These restrictions are not too strict; a password can be entered incorrectly ten consecutive times before the account is locked for one day, and a password will expire after about six months with a one-week grace period for changing it after that. There is no check on password complexity.

Creating and Assigning Profiles

The simplest way to enable more sophisticated password management is to run code provided in a supplied script. On Unix or Linux, it is as follows:

On Windows, it is as follows:

On either platform, the script creates a set of functions offering various degrees of password complexity checking.

To create a profile with SQL*Plus, use the CREATE PROFILE command, setting whatever limits are required. Any limits not specified will be picked up from the current version of the DEFAULT profile. For example, it could be that the rules of the organization state that accounts should be locked after five consecutive failed login attempts for one hour, except for administrators, who should be locked after two attempts for a whole day, and that all passwords should be subject to the provided standard password complexity verification algorithm.

Exercise 16-4: Create and Use Profiles    In this exercise, you’ll create, assign, and test a profile that forces some password control. Here are the steps to follow:

1.  Connect to your database with SQL*Plus as user SYS.

2.  Execute the script that will create the supplied verification functions and then apply one to the default profile. Confirm that the function has been created and applied.

3.  Create a profile that will lock accounts after two wrong passwords for 10 minutes.

4.  Assign this new profile to ALOIS.

5.  Deliberately enter the wrong password for ALOIS a few times.

6.  As user SYSTEM, check the status of the ALOIS account and unlock it.

7.  Check that ALOIS can now connect.

8.  Test the verification function by attempting to change the password a few times.

9.  Tidy up by dropping the profile, the roles, and the users. Note the use of CASCADE when dropping the profile to remove it from ALOIS as well as on the DROP USER command to drop his table. Roles can be dropped even if they are assigned to users. The privileges granted on the table will be revoked as the table is dropped.

Explain DBA Responsibilities for Security and Auditing

Auditing is necessary to detect suspicious or even downright illegal activity. Auditing is not an area where the DBA makes all the decisions; they do what they are told. If the business states that all access to certain tables must be tracked, the DBA must arrange this. In some jurisdictions, regulatory requirements make auditing certain actions mandatory. Apart from these business needs, two groups of users require special attention: developers and DBAs. Developers have great power within the database; DBAs have even more. Monitoring activity by these groups to ensure that they are not abusing their powers raises the issue of how to audit users who may be able to modify the audit trail (or “Who will guard the guards themselves?” as Juvenal put it (in Latin) nearly 2,000 years ago).

Reasons for Auditing

Why is an audit considered necessary in virtually all databases? The following are typical reasons:

•  To enable accountability for actions. To hold users responsible for their actions, it is necessary to track what they have done.

•  To deter users from inappropriate activity. Many users will have the ability to perform actions that are damaging or fraudulent. Knowing that they are monitored will dissuade them from doing this.

•  To investigate suspicious activity. Security may be set up correctly, but it is still useful to know whether people are trying to access data or run commands that are beyond their authorization.

•  To notify an auditor of unauthorized activity. In some cases, users may have more capabilities than they need. Auditing can track the use of any privileges considered dangerous and access to sensitive data.

•  Compliance issues. Many applications have quasi-legal requirements for audit. Typically, these are based around access to personal or financial information.

Auditing Techniques

Several auditing techniques are available in an Oracle environment. The most powerful is the Audit Vault. This is a separately installed and licensed product that is far beyond the scope of the Oracle Certified Professional (OCP) syllabus. Most environments will not need this; the internal techniques described in this section will be sufficient for the majority of business needs. Release 12c introduces a new auditing method: unified auditing. It is this technique that is tested.

Standard Database Auditing

Standard database auditing is enabled with one parameter: AUDIT_TRAIL. The default for this parameter is NULL, meaning that standard audit is disabled. However, if the database is created with the Database Configuration Assistant (DBCA), a standard audit will have been enabled by setting this parameter to DB.

Standard auditing is configured with the AUDIT and NOAUDIT commands. Standard auditing can track access to objects, use of privileges, and execution of certain statements. Audit records can be gathered for all users or only some, gathered once per session or once per action in the session, gathered whenever the attempt was made, or filtered by successful or unsuccessful attempts. The audit records are either written to a database table (which is SYS.AUD$) and visible through a set of views or written to the operating system where they are stored as files external to the database.

This technique for auditing is fully supported, but in release 12.1, Oracle Corporation recommends that unified auditing should be used instead.

Fine Grained Auditing

Fine Grained Auditing (FGA) can be configured to focus on precise areas of concern. Rather than auditing access at the object level, FGA can track access to certain rows and columns. Without FGA, an audit tends to produce a large number of “false positives,” that is, audit records that report issues of no concern. The following are examples:

•  Access to a table of employee data is fine in general, but if a SALARY column is accessed, that should be recorded.

•  Perhaps a user has full rights to read a table when running an approved application. But if they query the table from SQL*Plus, that should be known.

•  It may not be necessary to record access that occurs through normal means in the working day, but out-of-hours access from a device outside the corporate network should be tracked.

FGA will generate an audit record when certain conditions are met. The condition can be based on a predicate and also on a column list. As well as auditing the event, FGA can execute a user-defined procedure. This is a powerful facility. In effect, it is a trigger on SELECT. It is possible to define different audit policies for different statements. For example, you might want to record changes to data but not reads.

The interface to FGA is a simple-to-use application programming interface (API)—the DBMS_FGA package, with four procedures:

•  DBMS_FGA.ADD_POLICY    Create a policy for a table.

•  DBMS_FGA.DROP_POLICY    Drop a previously created policy.

•  DBMS_FGA.DISABLE_POLICY    Disable the policy. Policies are by default enabled.

•  DBMS_FGA.ENABLE_POLICY    Enable a policy that had been previously disabled.

The API is reasonably self-explanatory. Consider these examples:

The previous code defines three policies on one table, SCOTT.EMP. The first policy, EMP_D10_POL, will generate an audit record whenever any statement is executed that accesses an employee in Department 10. Note that the policy does not include MERGE. A MERGE is captured according to the underlying DML statement that the MERGE implements. Using the default of NULL for AUDIT_COLUMNS means that there is no column restriction; no matter what columns are projected, the statement will be audited.

The second policy will capture all SELECT statements (SELECT is the default for the STATEMENT_TYPE argument) that read the SAL column. The default of NULL for AUDIT_CONDITION means that the audit will be made for any row.

The third policy will generate an audit record and will also run a procedure, SEC.EMPDEL, whenever a row is deleted. The procedure must conform to a defined interface. It must accept three VARCHAR2 arguments, which will be populated with the object schema (in this case, SCOTT), the object name (in this case, EMP), and the policy name (in this case, EMP_DEL_POL).

The audit records are visible in the data dictionary view DBA_FGA_AUDIT_TRAIL. This includes the actual SQL statement, along with details such as who executed it and when. The underlying storage for the view is the table SYS.FGA_LOG$, which by default resides in the SYSTEM tablespace.

Value-Based Auditing

The declarative techniques for auditing (standard auditing, FGA, and unified auditing) do not capture the data themselves. They capture the action: who, what, when, and with which privilege. This means the statement executed, not the values that were actually seen or updated. If it is necessary to see the values, you must resort to value-base auditing, which is implemented with triggers.

A DML trigger designed to fire whenever any DML statement is executed can capture the row values and write them out to a user-defined audit table. Consider this example:

Depending on the nature of the DML, this trigger will capture appropriate values into a logging table. The trigger will fire as part of the DML, and the insert will be committed (or rolled back) with the calling transaction. It is therefore possible that performance may degrade. This should not be the case with standard or FGA auditing, where the capture is accomplished by routines that are internal to the instance, not by user-defined PL/SQL code.

Unified Auditing

Unified auditing is a new technique that replaces standard auditing, although the two can work concurrently. Unified auditing declares policies, which are then enabled for particular (or all) users. Five preconfigured policies are available to be implemented:

•  ORA_SECURECONFIG declares similar audits to the default standard auditing.

•  ORA_DATABASE_PARAMETER captures changes implemented with ALTER DATABASE and ALTER SYSTEM commands.

•  ORA_ACCOUNT_MGMT captures changes made with ALTER, DROP, and CREATE roles and users, as well as GRANT and REVOKE.

•  ORA_RAS_POLICY_MANAGEMENT and ORA_RAS_SESSION_MANAGEMENT have to do with Real Application Security, which is beyond the scope of the OCP examinations.

To confirm whether unified auditing is enabled, query the V$OPTION dynamic performance view. In this example, it is not enabled:

The unified auditing mechanism is efficient. By default, audit records are not written to the audit trail table (in the AUDSYS schema) in real time but via a buffered queue. So, sessions are writing only to a memory structure in the System Global Area (SGA). A background process, the GEN0 generic process, takes the strain of writing the records from the queue to the table asynchronously. This delayed write does raise the possibility of losing audit records in the event of an instance crash. Although enabled by default, this can be changed. To switch from delayed write mode to immediate write mode, use a procedure in the DBMS_AUDIT_MGMT package. This example enables immediate write and then switches back the default queued write:

Unified audit records are written to a table in the AUDSYS schema. The table has a system-generated name that may be different in every database and is read-only to any regular Data Definition Language (DDL) or DML statements. It can be managed only through DBMS_AUDIT_MGMT.

Mandatory Auditing

A small number of operations are always audited, whether configured or not:

•  Execution of the unified auditing commands CREATE/ALTER/DROP AUDIT POLICY

•  Execution of the standard audit commands AUDIT/NOAUDIT

•  Execution of the FGA package DBMS_FGA

•  Execution of the management package DBMS_AUDIT_MGMT

Furthermore, all top-level statements executed by administrative users (sessions connected as SYSDBA, SYSOPER, SYSBACKUP, SYSDG, SYSKM, or SYSASM) while the database is in NOMOUNT or MOUNT mode are audited to the operating system audit trail. On Unix or Linux, the records are written to files in the directory specified by the AUDIT_FILE_DEST parameter; on Windows, they are written to the Windows application log. These statements include connect attempts, startup or shutdown, and any ALTER SYSTEM and ALTER DATABASE commands.

Enable Standard Database Auditing and Unified Auditing

Standard auditing is possible (although may not be enabled or configured) only if the database has not been converted to unified auditing. Unified auditing requires an appropriate dynamic library to be available, which is not the case following installation of the Oracle Home.

Enable Standard Auditing

Figure 16-6 shows a query that displays the current values of the standard auditing parameters as well as two commands to enable auditing.

The parameters are as follows:

Figure 16-6    Standard auditing configuration

The values in Figure 16-6 are typical of many installations. Once the parameters have been set (all of which are static), the AUDIT command enables auditing of the execution of certain statements, the use of certain privileges, or the access to certain objects.

Enable Unified Auditing

If unified auditing is not enabled (which it is not, following a standard installation), it is necessary to enable it within the Oracle executable code; you relink the Oracle executables for Linux or copy in a dynamic link library for Windows.

Here is how to relink the executables:

On Windows, stop the Windows services for the database and the listener and then copy in the appropriate dynamic link library.

Restart the listener and database instance and then confirm in V$OPTION that unified auditing is now enabled.

To configure unified auditing, first grant the necessary roles to a user. These roles are AUDIT_ADMIN to administer unified auditing and AUDIT_VIEWER to view and analyze audit data. Then, as an AUDIT_ADMIN user, enable the supplied policies or create your own. A policy consists of specifying one (or more) of the three categories to audit.

Figure 16-7 shows creating policies of each type, followed by a mixed policy that combines the three types. Finally, it shows an example of an ACTION policy that will apply to just one table.

Figure 16-7    Defining unified auditing policies

Having created the policy, it must be activated, either globally or for individual users. By default, an audit record is generated whenever the audit condition is met, whether the result was successful or not. Figure 16-8 shows activating policies in various ways.

Figure 16-8    Enabling and disabling policies

The first example in the figure enables the privpol policy for all users. Every attempt to use either of the privileges, whether successful or not, will be recorded. The next example enables the actpol policy but will record only successful attempts to execute the statements. The third example will activate the rolepol policy for just two users, recording every time they use a privilege acquired through the nominated role. Then mixedpol is enabled for all users when attempts fail. The syntax to disable a policy is NOAUDIT POLICY <policy name>.

The last example in Figure 16-8 uses the DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL procedure to flush all records currently buffered in the SGA to the audit trail within the database.

Exercise 16-5: Use Unified Auditing    In this exercise, you will enable unified auditing. The method differs between Linux and Windows. Then you will create and configure audit policies as well as query the results. Connect as SYSDBA unless otherwise directed. Here are the steps to follow:

1.  Determine whether unified auditing is enabled by running this query:

Following a standard install, this will return FALSE.

2.  Shut down the Oracle services.

Shut down all database instances and listeners. On Windows, also stop all Oracle-related Windows services.

3.  Enable unified auditing.

In Linux, relink the executables to include the unified auditing libraries.

In Windows, copy in the unified auditing dynamic link library:

4.  Confirm that unified auditing is now enabled.

Start the instance and listener (on Windows, start the relevant services) and rerun the query from step 1. This will return TRUE.

5.  Enable a presupplied audit policy and then create and enable your own.

a.  Confirm what has been configured:

b.  Note for whom the policies have been enabled. Are any policies enabled by default?

6.  Confirm that unified auditing records are generated.

a. Connect as user SYSTEM and perform a few actions. Here is an example:

b. Query the unified audit trail.

7.  Attempt to tamper with the audit trail.

Connected as SYSDBA, identify the name of the audit trail table, and attempt to delete it.

8.  Clean the audit trail.

Execute a procedure to clear out the unified audit trail. This may throw an error message regarding initialization but will function nonetheless.

Two-Minute Drill

Create and Manage Database User Accounts

•  Users connect to a user account, which is connected to a schema.

•  Some form of authentication is always required.

•  A user must have a quota on a tablespace before they can create any objects.

•  A user who owns objects cannot be dropped, unless the CASCADE keyword is used.

Grant and Revoke Privileges

•  Privileges are of two types: object privileges and system privileges.

•  By default, a user can do nothing. They can’t even log on.

•  A revocation of a system privilege does not cascade; a revocation of an object privilege does.

Create and Manage Roles

•  Roles are not schema objects.

•  Roles can contain both system and object privileges as well as other roles.

•  A role can be enabled or disabled for a session.

Create and Manage Profiles

•  Profiles can enforce password policies.

•  Every user always has a profile (by default, the DEFAULT profile).

Explain DBA Responsibilities for Security and Auditing

•  The DBA should configure auditing according to the organization’s requirements.

•  Unified auditing is the recommended approach.

•  Value-based auditing can also be used, based on DML triggers.

Enable Standard Database Auditing and Unified Auditing

•  Standard auditing is still supported and is enabled with instance parameters.

•  Configure standard auditing with the AUDIT and NOAUDIT commands.

•  Unified auditing requires linking appropriate modules into the Oracle executables.

•  Unified auditing is configured by declaring audit policies.

•  It is not possible to edit the unified audit trail with SQL commands.

Self Test

1.  How can you permit users to connect without requiring them to authenticate themselves? (Choose the best answer.)

A.  Grant CREATE SESSION to PUBLIC.

B.  Create a user such as this, without a password:

C.  Create a profile that disables password authentication and assign it to the users.

D.  You cannot do this because all users must be authenticated.

2.  You create a user with this statement:

What more must be done before the user can create a table in the EXAMPLE tablespace? (Choose all correct answers.)

A.  Nothing more is necessary.

B.  Give him a quota on EXAMPLE.

C.  Grant him the CREATE TABLE privilege.

D.  Grant him the CREATE SESSION privilege.

E.  Grant him the MANAGE TABLESPACE privilege.

3.  If a user owns tables in a tablespace, what will be the effect of attempting to reduce the user’s quota on the tablespace to zero? (Choose the best answer.)

A.  The tables will survive, but INSERTS will fail.

B.  The tables will survive but cannot get bigger.

C.  The attempt will fail unless the tables are dropped first.

D.  The tables will be dropped automatically if the CASCADE keyword is used.

4.  If you create a user without specifying a temporary tablespace, what temporary tablespace will be assigned? (Choose the best answer.)

A.  You must specify a temporary tablespace.

B.  SYSTEM.

C.  TEMP.

D.  The database default temporary tablespace.

E.  The user will not have a temporary tablespace.

5.  You issue these commands:

Which grants should be revoked to prevent JON from seeing the contents of HR.REGIONS? (Choose the best answer.)

A.  a, b, c, and d

B.  a, c, and d

C.  a and b

D.  c and d

E.  a, b, and c

6.  Which of these statements about system privileges are correct? (Choose all correct answers.)

A.  Only the SYS and SYSTEM users can grant system privileges.

B.  If a system privilege is revoked from a user, it will also be revoked from all users to whom the user granted it.

C.  If a system privilege is revoked from a user, it will not be revoked from all users to whom the user granted it.

D.  CREATE TABLE is a system privilege.

E.  CREATE ANY TABLE is a system privilege.

7.  Study this script (line numbers have been added):

Which line will cause an error? (Choose the best answer.)

A.  Line 1, because only users, not roles, have passwords.

B.  Line 2, because only users, not roles, can create and own tables.

C.  Line 3, because SELECT TABLE is not a privilege.

D.  Line 4, because a role cannot have a system privilege in addition to table privileges.

8.  Which of these statements is incorrect regarding roles? (Choose the best answer.)

A.  You can grant object privileges and system privileges as well as roles to a role.

B.  A role cannot have the same name as a table.

C.  A role cannot have the same name as a user.

D.  Roles can be enabled or disabled within a session.

9.  If a password profile is dropped, what will be the effect on users to whom it is assigned? (Choose the best answer.)

A.  You cannot drop the profile until it is unassigned from the users.

B.  The profile will be removed if you use the CASCADE keyword.

C.  The users will revert to the default profile.

D.  Users to whom it is assigned will continue to use it, but it can no longer be assigned to anyone else.

10.  Which of these can be controlled by a password profile? (Choose all correct answers.)

A.  Two or more users choosing the same password

B.  Preventing the reuse of a password by the same user

C.  Forcing a user to change the password

D.  Enabling or disabling password file authentication

11.  You want to use unified auditing, and you run this query:

What should you do to enable unified auditing? (Choose the best answer.)

A.  Run ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE; and then restart the database.

B.  Relink the Oracle executables with the unified auditing libraries.

C.  Create and enable one or more unified auditing policies.

D.  Disable standard auditing.

12.  It is necessary to track all executions of SELECT by any users against a particular table. Which auditing tool will do this? (Choose the best answer.)

A.  Standard auditing

B.  Fine Grained Auditing

C.  Unified auditing

D.  All of the above

13.  What types of activity are mandatorily audited? (Choose three answers.)

A.  Creating, altering, or dropping an audit policy

B.  Creating, altering, or dropping a user

C.  Execution of the AUDIT and NOAUDIT commands

D.  All statements executed while connected as SYSDBA

E.  SYSDBA top-level statements in MOUNT or NOMOUNT

F.  DDL against the data dictionary

14.  Is it possible for the unified audit trail to lose audit records? (Choose the best answer.)

A.  The unified audit trail is always protected in all circumstances.

B.  Records cannot be lost, but they can be deleted or modified with DML commands.

C.  The audit trail is protected against DML, but DDL (such as TRUNCATE) can lose records.

D.  It is possible for records to be lost in the event of an instance failure.

Self Test Answers

1.      D. All users must be authenticated.
    A, B, and C are incorrect. A is incorrect because although this will give all users permission to connect, they will still have to authenticate. B is incorrect because a NULL is not acceptable as a password. C is incorrect because a profile can only manage passwords, not disable them.

2.      C and D. All these actions are necessary.
    A, B, and E are incorrect. A is incorrect because without privileges and a quota, JON cannot connect and create a table. B is not necessary because an unlimited quota is implicitly granted on the EXAMPLE tablespace to user JON. E is incorrect because this privilege lets you manage a tablespace, not create objects in it.

3.      B. It will not be possible to allocate further extents to the tables.
    A, C, and D are incorrect. A is incorrect because inserts will succeed as long as there is space in the extents already allocated. C is incorrect because there is no need to drop the tables. D is incorrect because CASCADE cannot be applied to a quota command.

4.      D. There is always a database-wide default, which (by default) is SYSTEM. In many cases, it will have been set to TEMP.
    A, B, C, and E are incorrect. A is incorrect because there is a default. B is incorrect because the default temporary tablespace may have been changed. C is incorrect because although TEMP is frequently used by default, it may not be. E is incorrect because all user accounts must have a temporary tablespace.

5.      B. The grant of the DBA role and the grant to PUBLIC must be removed, as well as the directly granted SELECT privilege.
    A, C, D, and E are incorrect. C, D, and E are incorrect because they all leave one grant in place that must be revoked. A is incorrect because it is not necessary to revoke ALL as well as SELECT; either would be sufficient.

6.      C, D, and E. Answer C is correct because the revocation of a system privilege does not cascade. D and E are correct because any action that updates the data dictionary is a system privilege.
    A and B are incorrect. A is incorrect because system privileges can be granted by any user who has been granted the privilege WITH ADMIN OPTION. B is incorrect because the revocation of a system privilege does not cascade.

7.      C. There is no such privilege as SELECT TABLE; it is granted implicitly with CREATE TABLE.
    A, B, and D are incorrect. A is incorrect because roles can be password protected. B is incorrect because even though tables must be owned by users, permission to create them can be granted to a role. D is incorrect because a role can have any combination of object and system privileges.

8.      B. Roles are not schema objects and therefore can have the same names as tables.
    A, C, and D are incorrect. A is incorrect because roles can have any combination of system, object, and role privileges. C is incorrect because roles cannot have the same names as users. D is incorrect because roles can be enabled and disabled at any time.

9.      C. Dropping a profile implicitly reassigns all relevant users to the default profile.
    A, B, and D are incorrect. A is incorrect because of the implicit reassignment of users. B is incorrect because there is no CASCADE keyword in the DROP PROFILE command; it isn’t necessary. D is incorrect because the effect of dropping a profile is immediate.

10.      B and C. These are both password limits.
    A and D are incorrect. A is incorrect because it is not possible to control this. Oracle has no knowledge of the actual password, only knowledge of the hash of the password. D is incorrect because this is controlled through the REMOTE_LOGIN_PASSWORDFILE instance parameter, not through profiles.

11.      B. It is necessary to link in the unified auditing libraries to the Oracle binaries.
    A, C, and D are incorrect. A is incorrect because the AUDIT_TRAIL parameter enables standard auditing, not unified auditing. C is necessary but not sufficient. D is incorrect because standard and unified auditing can coexist.

12.      D. Any of the auditing methods can do this.
    A, B, and C are incorrect. Each of these methods can track such access, although each has different means for configuration and different destinations for the audit records.

13.      A, C, and E. Commands that configure auditing are themselves audited, as well as all top-level statements executed before the database is opened.
    B, D, and F are incorrect. These classes of commands are not mandatorily audited. Most DBAs will want to audit them, but this must be explicitly configured.

14.      D. By default, records may be buffered in the SGA before being written to the audit trail. Therefore, an instance failure could lose records.
    A, B, and C are incorrect. A is incorrect because the audit trail can in fact lose records—if not configured to write them in real time. B and C are incorrect because the audit trail is protected against both DML and DDL.