Restricting access in Oracle 10g

Learning objective

After completing this topic, you should be able to recognize how to restrict access to an Oracle database.

1. The principle of least privilege

A secure system ensures the confidentiality of the data it contains. The Oracle Database 10g administrator should follow best practices and continually monitor database activity.

Oracle Database 10g must be protected and properly configured to maximize the security features offered.

Basic security practices and properly used security features protect against threats and attacks and provide a more secure operating environment.

There are several aspects of database security:

• restricting access to data and services
• authenticating users
• monitoring for suspicious activity
  

restricting access to data and services

You should restrict access to data stored in your database. Credit card information and other sensitive data must be protected from unauthorized access.

authenticating users

To enforce access controls on sensitive data, the system must first know who is trying to access the data. The most basic form of user authentication is asking the user to provide a password. Ensuring that passwords follow simple rules can greatly increase the security of your system. Oracle supports advanced authentication techniques through the Advanced Security Option.

monitoring for suspicious activity

Even authenticated users can sometimes compromise your system. Oracle provides tools to track user activity and identify suspicious trends, such as an employee querying large amounts of credit card details or other sensitive information.

The principle of least privilege means that a user should only be given the privileges required to complete a task efficiently.

This reduces the risk of users either accidentally or maliciously viewing or modifying data that they should not have the privilege to view or modify.

Applying the principle of least privilege entails

• protecting the data dictionary
• revoking unnecessary privileges from PUBLIC
• restricting the directories accessible by users
• limiting the number of users with administrative privileges
• restricting remote database authentication

Question

Identify the statement that best describes the principle of least privilege.

Options:

1. Users should be given all available privileges
2. Users should not be given privileges
3. Users should only be given the privileges required for efficient completion of their tasks

Answer

The principle of least privilege means that a user should only be given the privileges required to carry out a task efficiently.

Option 1 is incorrect. Giving users too many privileges could result in their modifying or viewing data that they should not have the privilege to modify or view.

Option 2 is incorrect. Users need the privileges that allow them to carry out necessary tasks.

Option 3 is correct. Applying the principle of least privilege reduces the risk that users, either accidentally or maliciously, can modify or view data that they should not have the privilege to modify or view.

2. Protecting data and controlling privileges

The data dictionary contains information about your database structure.

Nonadministrators do not need access to the data dictionary, but may gain access if you grant a system privilege such as SELECT ANY TABLE or UPDATE ANY TABLE.

If O7_DICTIONARY_ACCESSIBILITY is not set to FALSE, any user with the DROP ANY TABLE system privilege can maliciously or accidentally drop parts of the data dictionary.

To exempt data dictionary tables from the * ANY TABLE privileges, you need to set the O7_DICTIONARY_ACCESSIBILITY initialization parameter to FALSE. You can do this in Enterprise Manager.

You first click All Initialization Parameters to open the Initialization Parameters page.

On the Initialization Parameters page, you click SPFile to open the SPFile tabbed page.

On the SPFile tabbed page, you can use the Filter field to locate the O7_DICTIONARY_ACCESSIBILITY initialization parameter.

To search for the parameter, you type a keyword - DICTIONARY, in this case - into the Filter field and click Go.

The O7_DICTIONARY_ACCESSIBILITY initialization parameter is the only result returned by the search, so it is selected by default.

Now you need to set it to FALSE.

You click the down-pointing arrow and select FALSE. Then, you click Apply.

An update message appears, confirming that you have changed the parameter successfully.

Question

What parameter do you use to protect the data dictionary?

Options:

1. O7_DICTIONARY
2. O7_DICTIONARY_ACCESSIBILITY
3. DICTIONARY_ACCESSIBILITY

Answer

The parameter you use to protect the data dictionary is O7_DICTIONARY_ACCESSIBILITY.

Option 1 is incorrect. In fact, you need to add "_ACCESSIBILITY" to the end of this to target the correct parameter.

Option 2 is correct. You can protect the data dictionary by setting the O7_DICTIONARY_ACCESSIBILITY initialization parameter to FALSE in Enterprise Manager.

Option 3 is incorrect. In fact, you need to prefix this with "O7_" to target the correct parameter.

Privileges granted to the PUBLIC user group can be exercised by any database user.

Because of this, it is important to revoke any unnecessary privileges and roles from this user group.

Many of the built-in DBMS_* and UTL_* packages are installed with the EXECUTE privilege granted to PUBLIC.

Unless absolutely necessary, you should revoke these permissions from PUBLIC and grant them only to individual users or roles as required.

The more powerful packages with potential for misuse include

• UTL_SMTP
• UTL_TCP
• UTL_HTTP
• UTL_FILE
• DBMS_OBFUSCATION_TOOLKIT

UTL_SMTP

The UTL_SMTP package permits arbitrary mail messages to be sent using the database as an SMTP mail server. Granting this package to PUBLIC may enable the unauthorized exchange of mail messages.

UTL_TCP

The UTL_TCP package permits outgoing network connections to be established by the database server to any receiving or waiting network service.

UTL_HTTP

The UTL_HTTP package allows the database server to request and retrieve data via HTTP. Granting this package to PUBLIC may permit data to be sent via HTML forms to a malicious web site.

UTL_FILE

If configured improperly, the UTL_FILE package allows text-level access to any file on the host operating system. Even when properly configured, this package does not distinguish between its calling applications, with the result that one application with access to UTL_FILE may write arbitrary data into the same location that is written to by another application.

DBMS_OBFUSCATION_TOOLKIT

The DBMS_OBFUSCATION_TOOLKIT package encrypts data. Most users should not be given permission to encrypt data, since you can't recover encrypted data if the encryption keys are not stored and managed securely.

Suppose, for example, you want to revoke the EXECUTE privilege on the UTL_FILE package from the PUBLIC user group.

You use the following command to do this:

REVOKE execute ON UTL_FILE FROM PUBLIC

Question

Now see if you can identify the command used to revoke privileges to UTL_HTTP from the PUBLIC user group.

Options:

1. REVOKE execute ON UTL_SMTP FROM PUBLIC
2. REVOKE execute ON UTL_HTTP FROM PUBLIC
3. REVOKE execute ON DBMS_OBFUSCATION_TOOLKIT FROM PUBLIC

Answer

You use REVOKE execute ON UTL_HTTP FROM PUBLIC to revoke privileges to UTL_HTTP from the PUBLIC user group.

Option 1 is incorrect. This command prevents the unauthorized exchange of mail messages.

Option 2 is correct. This command prevents data being sent via HTML forms to unauthorized destinations.

Option 3 is incorrect. This command prevents users from encrypting data.

3. Protecting OS directories and limiting users

The UTL_FILE_DIR parameter designates which operating system directories PL/SQL users have access to read from and write to.

By default, no directories can be accessed.

To configure the UTL_FILE_DIR parameter, you first open the Initialization Parameters page of Enterprise Manager and click the SPFile tab.

On the SPFile tabbed page, you type UTL_FILE into the Filter field and click Go to locate the parameter.

The UTL_FILE_DIR parameter is the only result returned from your search.

To specify the directory PL/SQL users can access, you need to enter the directory path in the Value field.

You enter /oracle/stage1 in the Value field and click Apply.

An update message confirms that the changes have been made successfully.

If you want to specify multiple directories to be accessible to PL/SQL users, you type each required directory in single quotes, separated by a comma.

This is not a dynamic parameter, so the instance must be restarted for changes to take effect.

Operating-system privileges still apply. Directories that the user who started the database could not access are still inaccessible, regardless of the UTL_FILE_DIR setting.

Question

What configuration parameter allows you to restrict the directories a user can access on the host operating system?

Options:

1. UTL_FILE
2. UTL_FILE_DIR
3. UTL_HTTP

Answer

UTL_FILE_DIR allows you to restrict the directories a user can access.

Option 1 is incorrect. UTL_FILE allows text-level access to specified files on the host operating system.

Option 2 is correct. To specify multiple directories, you type each required directory in single quotes, separated by a comma.

Option 3 is incorrect. UTL_HTTP allows the database server to request and retrieve data via HTTP.

As well as controlling what directories are accessible to PL/SQL users, you also need to limit the number of users with administrative privileges.

You can do this by restricting

• grants of system and object privileges
• SYS-privileged connections to the database, such as SYSDBA and SYSOPER
• other DBA-type privileges, such as DROP ANY TABLE
• runtime permissions

Nonadministrators should not be granted the DBA role. Instead, you should determine the actual privileges they need and grant only those.
To list all users with the DBA role, you enter this SQL query and click Execute.

SELECT grantee FROM dba_role_privs WHERE granted_role = 'DBA';

The system returns a list of users who have been granted DBA privileges.

Question

What privilege types should you restrict in order to limit the number of users with administrative privileges?

Options:

1. Grants of system and object privileges
2. DBA-type privileges in general
3. SYS-privileged connections to the database
4. UTL_FILE_DIR privileges

Answer

To limit the number of users with administrative privileges, you restrict system and object privileges, SYS-privileged connections to the database, and other DBA-type privileges.

Option 1 is correct. You also need to restrict runtime permissions.

Option 2 is correct. You need to restrict privileges such as DROP ANY TABLE.

Option 3 is correct. You need to restrict privileges such as SYSDBA and SYSOPER.

Option 4 is incorrect. You use the UTL_FILE_DIR parameter to control read and write access to directories on the host operating system.

4. OS authentication and locking user accounts

Remote Operating System Authentication allows users on remote workstations to be authenticated externally before logging on to the database.

This feature is disabled by default. If it is enabled, Oracle assumes that the remote operating system has authenticated the user, and does not check authentication details again.

Most remote operating systems should not be trusted to perform authentication properly, so you need to ensure that this feature is disabled.

To ensure that remote authentication is disabled, you first open the Initialization Parameters page of Enterprise Manager and click the SPFile tab.

On the SPFile tabbed page, you type REMOTE_OS into the Filter field and click Go to locate the REMOTE_OS_AUTHENT parameter.

You select remote_os_authent from the results displayed.

Now you need to disable the parameter.

You click the down-pointing arrow in the Value column beside remote_os_authent, select FALSE and click Apply.

An update message appears, confirming that you have set the parameter successfully.

Question

As a database administrator, you have found that users have been authenticating remotely before they log on to your database. This is compromising the security of your database, so you need to disable the parameter that controls remote authentication.

Which sequence of steps would you use to complete this task?

Options:

1. You click the down-pointing arrow in the Value column beside remote_os_authent, select FALSE and click Apply.
2. You select remote_os_authent from the results displayed and click Apply.

Answer

You click the down-pointing arrow in the Value column beside remote_os_authent, select FALSE and click Apply.

When you install Oracle Database 10g, a number of default user accounts are created. These accounts are intended to store data, not to provide database connections.

When the Database Creation Assistant (DBCA) is used to create a database, it automatically locks and expires all default database user accounts except for the following:

• SYS
• SYSTEM
• DBSNMP
• SYSMAN

Databases that have been created manually may not lock the default accounts, so you need to lock and expire any unused accounts.

In this example, CTXSYS is a default account that you need to lock and expire.

You open the Users page and click CTXSYS.

The Edit User page opens. You can now edit the CTXSYS account so that it's locked and expired.

You select Expire Password now. Then, select Locked and click Apply.

An update message appears, confirming that CTXSYS has been expired and locked.

Question

While examining the Users page on Enterprise Manager, you notice that the account status of CTXSYS, a default user, is OPEN. To ensure that your database is secure, you need to expire and lock the account.

Which sequence of steps would you use to complete this task?

Options:

1. You select Expire Password now. Then, you select Locked and click Apply.
2. You select Expire Password now and click Apply.

Answer

To expire and lock the CTXSYS account, you select Expire Password now. Then, you select Locked and click Apply.

Summary

Oracle Database 10g must be protected and properly configured to maximize the security features offered. The principle of least privilege means that a user should only be given the privileges required to complete a task efficiently.

Because the data dictionary contains information about your database structure, you need to protect it by disabling the 07_DICTIONARY_ACCESSIBILITY parameter. It's also important to revoke any unnecessary privileges and roles from the PUBLIC user group.

You can control which operating-system directories PL/SQL users have access to using the UTL_FILE_DIR parameter. You also need to limit the number of users with administrative privileges.

Most remote operating systems can't be trusted to perform remote authentication properly, so you need to disable this feature in Enterprise Manager. You can also use Enterprise Manager to lock and expire unused accounts.