[Laskey99] Chapter 6. Security and Monitoring

Stefen 2010-06-17

展开全文

Chapter 6. Security and Monitoring

Up to this point, this book has primarily focused on getting the database installed and operational. This chapter takes you into the day-to-day business of being an Oracle database administrator. We focus on two important areas of administration here: security and monitoring.

6.1. Security

One of your most important responsibilities as a database administrator is the security of the data in your database. You are responsible for ensuring that there is no unauthorized access to the data. Oracle has provided you with many basic features that, when applied properly and in combination, make the process of managing security relatively painless. This section covers the basic security functions of the DBA and the Oracle features that you will use.

6.1.1. Security Policy

Before you can implement specific features, you must develop a security policy. You, your management, the application support team, and your users have to decide on how open the database and its data will be. This decision depends upon the specifics of the application and the requirements of your organization. Security policies vary widely. We have seen read-only databases where everyone in the company has access to query all data, and we have seen databases so sensitive that all access is through restricted terminals.

The following summaries provide lists of questions you will have to answer when establishing your own site's security policy. Later sections contain basic information on how to implement your decisions.

6.1.1.1. User access to the database

Every user must connect to the database using an authorized userid and password. Questions you'll have to address include the following:

Will users share one or more common userids (this is common with many vendor applications) or will each user have a unique userid?
Should passwords expire after a period of time?
Do you need to implement a minimum length for passwords or impose other content restrictions?
Do you want to limit the number of concurrent sessions for a given user?
Will users be able to create their own objects?
Will you be able to restrict access to DBA or other privileged accounts?
Will the database be accessible for remote connections using SQL*Net or Net8?
Will the database be accessible for distributed queries?

6.1.1.2. Read sensitivity of the data

By default, data in a table is only accessible to:

The userid that owns the table
Anyone with the SELECT ANY TABLE system privilege

Questions you'll have to address include the following:

Will the application data be accessible to all users of the database?
Will different levels of access to the data be granted to different users?
Will object grants be given to each user, or will they be grouped using roles?

6.1.1.3. Write sensitivity of the data

By default, tables can only be modified by:

The owner of the table
Anyone with INSERT ANY TABLE, UPDATE ANY TABLE, or DELETE ANY TABLE system privileges for inserts, updates, or deletes

In addition to the questions listed above for select access, questions you'll have to address include the following:

Will some tables contain data that can be updated?
Should some tables be used as logs, with insert-only access granted to users?
Will some data need to be reconstructed so that rows can't ever be deleted, only marked as being logically deleted?
Will some data need to be reconstructed so that rows can't ever be deleted, only marked as being replaced with new rows inserted?

6.1.1.4. Audit policy

Once you determine who should have access to the data, you'll need to decide if you want to know who has accessed the data. Decisions you'll need to make include the following:

Do you need to know who has selected from, inserted into, updated, or deleted from the table?
Do you simply need to know that they did it during their session, or do you need to pinpoint the exact time of every access?
Do you need to know only that someone accessed the table, or do you need to know the specific row that was accessed?

See Chapter 7 , for more information about creating and implementing an audit policy.

6.1.2. Creating Users

Each person accessing the database must have an Oracle userid. While userids are normally created by a DBA, they can be created by anyone who has the CREATE ANY USER system privilege. The usual syntax to create a user is as follows:

CREATE USER
username IDENTIFIED BY
password
    DEFAULT TABLESPACE
default_tablespace_name
    TEMPORARY TABLESPACE
temporary_tablespace_name
    QUOTA 10M ON
default_tablespace_name
    PROFILE
profile_name;

If you do not specify a default tablespace or a temporary tablespace, Oracle will automatically assign the SYSTEM tablespace. However, you must specify a quota for the default tablespace, or the user will still not be able to create any tables or indexes in the default tablespace. You do not need to specify a quota for the temporary tablespace. See Chapter 13 , for the complete CREATE USER syntax.

All options that can be specified in the CREATE USER command can also be changed for existing users with the ALTER USER command.

6.1.3. Profiles

User profiles were introduced in Oracle7 as a way that the DBA could limit the amount of system resources that could be consumed by any particular user. In Oracle8, user profiles have been expanded to support password control. There are two flavors of profiles, resource control and password control, discussed in the following sections.

Profiles are created using the CREATE PROFILE command and can be altered with the ALTER PROFILE command. At database creation time, a single profile, named DEFAULT, is created. You can use the DBA_PROFILES data dictionary view to determine which profiles have been created, and the values of each of the limits contained in the profiles.

If a profile is not specified for a user in the CREATE USER command, the DEFAULT profile is used. A user's profile can be changed with the ALTER USER command. You can use the DBA_USERS data dictionary view to see which profile has been assigned to a given user.

6.1.3.1. Resource control

Each user profile has eight individual resource limits and one composite limit that can be specified. For each limit, the possible values are an integer or the keywords UNLIMITED or DEFAULT. When DEFAULT is used, the value is replaced by the corresponding value in the DEFAULT profile.

The limits are summarized here:

COMPOSITE_LIMIT: Weighed sum of CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION, and PRIVATE_SGA. The weights applied to each parameter are specified using the ALTER RESOURCE COST command. Parameters that are not specified take a weighted value of 0. Initially, each parameter has a weighted value of 0. If this value is exceeded, the current operation is aborted, an error message is returned, and the user is only able to commit or roll back and then exit.

The data dictionary view RESOURCE_COST shows the assigned values for each parameter.

CPU_PER_SESSION: Total amount of CPU that can be used by the session. Value is in hundredths of a second. If this value is exceeded, the current operation is aborted, an error message is returned, and the user is only able to commit or roll back and then exit.
CPU_PER_CALL: Total amount of CPU that can be used by any one parse, execute, or fetch call. Value is in hundredths of a second. This can be used to terminate runaway queries. If this value is exceeded, the current operation is aborted, an error message is returned, and the user is only able to commit or roll back and then exit.
CONNECT_TIME: Total amount of elapsed time for which a given connection can be maintained. Value is in minutes. This can be used to ensure that a given session does not remain connected to the database indefinitely. If this value is exceeded, the current transaction is rolled back and the user receives an error on the next SQL statement.
IDLE_TIME: Total amount of elapsed time allowed between any two SQL statements. Value is in minutes. This is used to time out inactive sessions, either to free up session slots or to limit exposure when someone leaves an active session on their workstation. If this value is exceeded, the current transaction is rolled back and the user receives an error on the next SQL statement.
LOGICAL_READS_PER_SESSION: Total number of disk blocks (either from memory or disk) that can be read by the session. Value is in blocks. This is used to place a total limit on the amount of I/O that can be done by a session. If this value is exceeded, the current operation is aborted, an error message is returned, and the user is only able to commit or roll back and then exit.
LOGICAL_READS_PER_CALL: Total number of disk blocks (either from memory or disk) that can be read by a parse, execute, or fetch call. Value is in blocks. This is used to place a limit on the amount of I/O that can be performed by a given call in an attempt to identify and stop runaway queries. If this value is exceeded, the current operation is aborted, an error message is returned, and the user is only able to commit or roll back and then exit.
PRIVATE_SGA: Total amount of private space that can be allocated to the session out of the shared pool area for private SQL and PL/SQL areas. This does not apply to the shared SQL or PL/SQL areas. Value is in bytes, but the K and M suffixes are accepted to specify kilobytes or megabytes. This parameter is only used in a Multi-Threaded Server (MTS) system.
SESSIONS_PER_USER: Total number of concurrent sessions the user can have active at a given time. Value is in sessions. This value is normally used to limit a user to a specified maximum number of concurrent sessions. The SESSIONS_PER_USER parameter not only limits the number of concurrent sessions, but also limits the number of Parallel Query sessions that can be used. If the value of SESSIONS_PER_USER is less than the degree of parallelism used by the query, an ORA-2391 error will be generated.

6.1.3.2. Password control

Effective with Oracle8, the DBA can establish a password policy through the use of profiles. The user profile has been extended to include parameters that control a password's lifetime, ability to be reused, and even its length and format. There are also parameters that allow you to lock out an account if unsuccessful attempts are made to connect to it.

The password control parameters include the following:

FAILED_LOGIN_ATTEMPTS

The number of failed login attempts before the account is locked. Value is in failed attempts. This parameter is primarily used to lock an account in the event of an apparent break-in attempt. Once an account is locked, it remains locked for the time specified in PASSWORD_LOCK_TIME.

PASSWORD_GRACE_TIME

The number of days after the password has expired during which you are allowed to continue to connect to the database. During the grace period, an error message is returned upon login that warns you to change your password. The grace period begins the first time a user connects after the password has expired. Value is in days.

PASSWORD_LIFE_TIME

The number of days that a password, once set, is valid. This allows you to establish a password policy that requires passwords to be changed on a regular interval. Value is in days.

PASWORD_LOCK_TIME

The length of time an account is locked after the FAILED_LOGIN_ATTEMPTS maximum is exceeded. Once an account is locked, it can be unlocked either by waiting for the number of days specified in the PASSWORD_LOCK_TIME parameter, or with this SQL command:

ALTER USER
username ACCOUNT UNLOCK;

PASSWORD_REUSE_MAX

The number of different passwords that must be used before a user is allowed to reuse the same password. If this parameter is set to an integer value, then PASSWORD_REUSE_TIME must be set to UNLIMITED.

PASSWORD_REUSE_TIME

The number of days that must elapse before a user can reuse the same password. If this parameter is set to a non-zero value, then the PASSWORD_REUSE_MAX parameter must be set to UNLIMITED.

PASSWORD_VERIFY_FUNCTION

Specifies a PL/SQL procedure that is invoked to validate the password. You can use this PL/SQL procedure to enforce any restrictions that can be implemented with PL/SQL. Oracle provides an example PL/SQL procedure in the file $ORACLE_HOME/rdbms/admin/utlpwdmg.sql. The PL/SQL procedure must be owned by SYS.

6.1.3.3. Default profile

At the time the database is created, an initial profile named DEFAULT is established. This is the default profile, and has two functions:

To act as the default profile for a user where no profile is specified
To act as a definition of default values for other profiles

Any of the parameters documented above can have a specified value DEFAULT. Oracle uses the value specified in the DEFAULT profile for that parameter. This process is repeated each time a user connects, so a change to the DEFAULT profile will automatically take effect with the next connection.

6.1.3.4. Implementing profiles

Enforcing profile limits is a three-step process:

Profiles must be defined with the CREATE PROFILE command.
Profiles must be assigned to users with the CREATE USER or ALTER USER command.
Limit checking must be enabled globally at the database level. This can be done either through the INIT.ORA parameter RESOURCE_LIMITS or through the ALTER SYSTEM commands. See Chapter 12, and Chapter 13, for the complete syntax.

6.1.4. System Privileges

System privileges allow a user of the database to perform specific actions within the database. In Oracle8 Release 8.0, there are 90 system privileges that can be assigned to a user or role. In Oracle7 Release 7.3 there are 78 system privileges that can be assigned to a user or role. See Chapter 15 , for a complete list of system privileges that can be assigned.

6.1.5. Object Privileges

Object privileges allow you to access or manipulate objects in the database. Depending upon the type of object, different object privileges apply. Table 6.1 shows the mapping of object privileges to object types.

Table 6.1. Object Privileges Allowed by Object Type
Object Option	Object Type Applicable
ALTER	Table, sequence
DELETE	Table, view, snapshot
EXECUTE	Package, procedure, function, library
INDEX	Table, snapshot
INSERT	Table, view, snapshot
READ (Oracle8 only)	Directory
REFERENCES	Table
SELECT	Table, view, sequence, snapshot
UPDATE	Table, view, snapshot

By default, only the owner of any object automatically has all object privileges for an object. The owner must grant access to the object to other users or roles in order for other users to access the object. The exception to this rule is that someone with the DBA role or with one of the system privileges that allow you to manipulate objects in any schema (they are listed in Table 15.4 in Chapter 15 , which describes privileges in some detail) will be able to access the schema object directly.

6.1.5.1. The WITH GRANT OPTION clause

The owner of an object can grant it to another user by specifying the WITH GRANT OPTION clause in the GRANT statement. In this case, the new grantee can then grant the same level of access to other users or roles. Here are three points to keep in mind about the WITH GRANT OPTION clause:

You cannot grant WITH GRANT OPTION to a role.
If you revoke access to a user who had been granted access to an object WITH GRANT OPTION, and that user had granted access to another user, both sets of grants will be revoked.
The WITH GRANT OPTION does not come automatically with the system privileges listed in Table 15.4 that allow you to manipulate objects in any schema. Thus, although a DBA can create a table in someone else's schema through the CREATE ANY TABLE system privilege, and you can SELECT, INSERT, UPDATE, or DELETE from it through the SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, and DELETE ANY TABLE system privileges, you cannot grant access to the table to any other user or role.

6.1.6. Roles

Roles are the mechanism used by Oracle to facilitate the granting of system and object privileges to users. The granting of roles is a three-step process:

The role is created by someone with the CREATE ROLE system privilege.
System or object privileges are granted to the role by anyone with the proper authorization.
The role is granted to a user by anyone who has the GRANT ANY ROLE system privilege, or by anyone who has been granted the role via the WITH ADMIN OPTION.

6.1.6.1. Default roles

Normally, when a role is granted to a user, it becomes a default role—that is, the role is automatically active at the time the user connects to the database. However, one of the options available with the CREATE USER and ALTER USER commands is the ability to specify a subset of the roles that are granted to the user by default. You can use this approach if you want a user to be granted a role with the condition that explicit actions must be taken to enable it.

The most common use of non-default roles is to ensure that specific system or object privileges are available only from within an application. In this case, the application would enable the role through the SET ROLE command.

The SET ROLE command specifically enables only those roles listed. Any default roles that are not listed will be disabled. Make sure that any non-default role that is to be enabled at runtime contains all system and object privileges necessary to continue normal processing.

6.1.6.2. Maximum number of enabled roles

The INIT.ORA parameter MAX_ENABLED_ROLES specifies the maximum number of enabled roles a user can have. This number includes the unlisted PUBLIC role. You can determine which roles are currently active by querying the data dictionary view SESSION_ROLES. Oracle counts all roles that have been granted either directly or indirectly through other roles against this limit. Thus, for example, enabling the DBA role actually uses seven of the allocated slots:

DBA

SELECT_CATALOG_ROLE

HS_ADMIN_ROLE

EXECUTE_CATALOG_ROLE

DELETE_CATALOG_ROLE

EXP_FULL_DATABASE

IMP_FULL_DATABASE

A slot is also used for the PUBLIC role.

Since roles are established at connect time or when the SET ROLE command is executed, the most common effect of the MAX_ENABLED_ROLES parameter is for a connection to fail with an ORA-1925 error, which indicates that too many roles have been granted to the user.

6.1.6.3. Password-enabled roles

Roles can have associated passwords, which are established using the IDENTIFIED BY clause in the CREATE ROLE or ALTER ROLE commands. When a role has an associated password, the user must provide the password at the time the role is enabled with the SET ROLE command. The two most common scenarios for this are as follows:

A role is needed to allow an application to have the object privileges necessary to function properly. The role is granted as a non-default role to the user. The application has the password available to it and provides the password independently of the user. This ensures that the user is not able to use ad hoc query and update tools like SQL*Plus to access the objects directly outside the application environment.
The user occasionally requires elevated privileges, but does not normally want to run with the privileges enabled. In this case, the role can be established as a non-default role with a password, requiring the user to explicitly enable the role and provide a password through the SET ROLE command. A user does not have to know the password for any default roles, unless the role needs to be reenabled after a SET ROLE command.

6.1.6.4. The WITH ADMIN OPTION clause

Special attributes of roles can be granted to a user by including the WITH ADMIN OPTION clause in the GRANT statement. In this case, the user is allowed to grant the role to other users or roles. If you are able to grant a role to someone because you have the role WITH ADMIN OPTION, and then the role is revoked from you, the cascaded grants remain. The situation is different in cases where you simply have object privileges.

A user who has been granted a role WITH ADMIN OPTION also has the ability to change the role's password and to drop the role.

Normally, when a role is granted to a user, it is granted as a default role. The exception is when the DEFAULT ROLE clause has previously been used in the CREATE USER or ALTER USER commands. In this case, the role may be granted as a non-default role. In order for the person granting the role to ensure that the role is a default role, he or she must also have been granted the ALTER ANY USER system privilege.

6.1.7. Common Security Holes

This section discusses some of the most common security holes in an Oracle environment.

6.1.7.1. Not changing privileged user passwords

All standard users created by Oracle have associated passwords. The two primary DBA accounts, SYS and SYSTEM, have (unfortunately) well-documented passwords. Most break-ins into Oracle databases are through one of these accounts, where the initial passwords provided with the installed systems have never been changed.

6.1.7.2. Using the same password in all databases

Most users have a strong tendency to keep passwords simple. One of the easiest ways to do this is to have the same password for all of your accounts—either all DBA accounts in the database or all accounts in all databases. The problem with specifying passwords in this way is that if your password is stolen, it opens up multiple accounts for improper usage.

6.1.7.3. The SCOTT/TIGER account

Most DBAs automatically create the SCOTT account with the well-known password TIGER. This account has been used in demos for years by Oracle and by Oracle Education (utilizing the EMP and DEPT tables). However, if this account exists, any grants made to PUBLIC are also available to the SCOTT account. If an unauthorized person accesses the database using the SCOTT account, you may have a security breach.

6.1.7.4. Shared UTL_FILE access to directories

The UTL_FILE built-in package allows a user to read from and write to an operating system file from within PL/SQL. The INIT.ORA parameter UTL_FILE_DIR specifies a list of operating system directories you can use with UTL_FILE. Any PL/SQL procedure executed by any user of the database can read from or write to any file in the directory.

6.1.8. Auditing

After implementing a security policy, you need to audit actions within the database. Chapter 7 , provides specific information on implementing an auditing policy.