[Laskey99] Chapter 6. Security and Monitoring

来源：百度文库编辑：神马文学网时间：2024/04/29 19:37:17

Chapter 6. Security and Monitoring

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

6.1. Security

One ofyour most important responsibilities as a database administrator isthe security of the data in your database. You are responsible forensuring that there is no unauthorized access to the data. Oracle hasprovided you with many basic features that, when applied properly andin combination, make the process of managing security relativelypainless. This section covers the basic security functions of the DBAand the Oracle features that you will use.

6.1.1. Security Policy

Beforeyou can implement specific features, you must develop a securitypolicy. You, your management, the application support team, and yourusers have to decide on how open the database and its data will be.This decision depends upon the specifics of the application and therequirements of your organization. Security policies vary widely. Wehave seen read-only databases where everyone in the company hasaccess to query all data, and we have seen databases so sensitivethat all access is through restricted terminals.

The following summaries provide lists of questions you will have toanswer when establishing your own site's security policy. Latersections contain basic information on how to implement yourdecisions.

6.1.1.1. User access to the database

Everyuser must connect to the database using an authorized userid andpassword. Questions you'll have to address include thefollowing:

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

Bydefault, 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

Bydefault, 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 youdetermine who should have access to the data, you'll need todecide if you want to know who has accessed the data. Decisionsyou'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 creatingand implementing an audit policy.

6.1.2. Creating Users

Each person accessing the database must have an Oracle userid. Whileuserids are normally created by a DBA, they can be created by anyonewho has the CREATEANY USER system privilege. The usual syntax to create a user is asfollows:

CREATE USERusername IDENTIFIED BYpassword
    DEFAULT TABLESPACEdefault_tablespace_name
    TEMPORARY TABLESPACEtemporary_tablespace_name
    QUOTA 10M ONdefault_tablespace_name
    PROFILEprofile_name;

If you do not specify a default tablespace or a temporary tablespace,Oracle will automatically assign the SYSTEM tablespace. However, youmust specify a quota for the default tablespace, or the user willstill not be able to create any tables or indexes in the defaulttablespace. You do not need to specify a quota for the temporarytablespace. See Chapter 13, for the complete CREATEUSER 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 inOracle7 as a way that the DBA could limit the amount of systemresources that could be consumed by any particular user. In Oracle8,user profiles have been expanded to support password control. Thereare two flavors of profiles, resource control and password control,discussed in the following sections.

Profiles are created using the CREATE PROFILE command and can bealtered with the ALTER PROFILE command. At databasecreation time, a single profile, named DEFAULT, is created. You canuse the DBA_PROFILES data dictionary view to determine which profileshave been created, and the values of each of the limits contained inthe 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 changedwith the ALTER USER command. You can use the DBA_USERS datadictionary view to see which profile has been assigned to a givenuser.

6.1.3.1. Resource control

Each user profile has eightindividual resource limits and one composite limit that can bespecified. For each limit, the possible values are an integer or thekeywords UNLIMITED or DEFAULT. WhenDEFAULT is used, the value is replaced by the corresponding value inthe 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 toeach 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 isexceeded, the current operation is aborted, an error message isreturned, and the user is only able to commit or roll back and thenexit.

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 inhundredths of a second. If this value is exceeded, the currentoperation is aborted, an error message is returned, and the user isonly 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, orfetch call. Value is in hundredths of a second. This can be used toterminate runaway queries. If this value is exceeded, the currentoperation is aborted, an error message is returned, and the user isonly able to commit or roll back and then exit.
CONNECT_TIME: Total amount of elapsed time for which a given connection can bemaintained. Value is in minutes. This can be used to ensure that agiven session does not remain connected to the database indefinitely.If this value is exceeded, the current transaction is rolled back andthe 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 someoneleaves an active session on their workstation. If this value isexceeded, the current transaction is rolled back and the userreceives an error on the next SQL statement.
LOGICAL_READS_PER_SESSION: Total number of disk blocks (either from memory or disk) that can beread by the session. Value is in blocks. This is used to place atotal limit on the amount of I/O that can be done by a session. Ifthis value is exceeded, the current operation is aborted, an errormessage is returned, and the user is only able to commit or roll backand then exit.
LOGICAL_READS_PER_CALL: Total number of disk blocks (either from memory or disk) that can beread by a parse, execute, or fetch call. Value is in blocks. This isused to place a limit on the amount of I/O that can be performed by agiven call in an attempt to identify and stop runaway queries. Ifthis value is exceeded, the current operation is aborted, an errormessage is returned, and the user is only able to commit or roll backand then exit.
PRIVATE_SGA: Total amount of private space that can be allocated to the sessionout of the shared pool area for private SQL and PL/SQL areas. Thisdoes 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 ormegabytes. 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 agiven time. Value is in sessions. This value is normally used tolimit a user to a specified maximum number of concurrent sessions.The SESSIONS_PER_USER parameter not only limits the number ofconcurrent sessions, but also limits the number of Parallel Querysessions that can be used. If the value of SESSIONS_PER_USER is lessthan the degree of parallelism used by the query, an ORA-2391 errorwill be generated.

6.1.3.2. Password control

Effective with Oracle8, the DBA canestablish a password policy through the use of profiles. The userprofile has been extended to include parameters that control apassword's lifetime, ability to be reused, and even its lengthand format. There are also parameters that allow you to lock out anaccount 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 lockan account in the event of an apparent break-in attempt. Once anaccount is locked, it remains locked for the time specified inPASSWORD_LOCK_TIME.

PASSWORD_GRACE_TIME

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

PASSWORD_LIFE_TIME

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

PASWORD_LOCK_TIME

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

ALTER USERusername ACCOUNT UNLOCK;

PASSWORD_REUSE_MAX

The number of different passwords that must be used before a user isallowed to reuse the same password. If this parameter is set to aninteger 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 samepassword. If this parameter is set to a non-zero value, then thePASSWORD_REUSE_MAX parameter must be set to UNLIMITED.

PASSWORD_VERIFY_FUNCTION

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

6.1.3.3. Default profile

At the time the database is created, aninitial profile named DEFAULT is established. This is the defaultprofile, 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 valueDEFAULT. Oracle uses the value specified in the DEFAULT profile forthat parameter. This process is repeated each time a user connects,so a change to the DEFAULT profile will automatically take effectwith 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

Systemprivileges allow a user of the database to perform specific actionswithin the database. In Oracle8 Release 8.0, there are 90 systemprivileges that can be assigned to a user or role. In Oracle7 Release7.3 there are 78 system privileges that can be assigned to a user orrole. See Chapter 15, for a complete list of systemprivileges that can be assigned.

6.1.5. Object Privileges

Object privileges allow you toaccess or manipulate objects in the database. Depending upon the typeof object, different object privileges apply. Table 6.1 shows the mapping of object privileges toobject 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 objectprivileges for an object. The owner must grant access to the objectto other users or roles in order for other users to access theobject. The exception to this rule is that someone with the DBA roleor with one of the system privileges that allow you to manipulateobjects in any schema (they are listed in Table 15.4 in Chapter 15, whichdescribes privileges in some detail) will be able to access theschema object directly.

6.1.5.1. The WITH GRANT OPTION clause

The owner of an object can grant it to another user by specifying theWITH GRANT OPTION clause in the GRANT statement. In this case, thenew grantee can then grant the same level of access to other users orroles. Here are three points to keep in mind about the WITH GRANTOPTION 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 usedby Oracle to facilitate the granting of system and object privilegesto 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 defaultrole—that is, the role is automatically active at the time theuser connects to the database. However, one of the options availablewith the CREATE USER and ALTER USER commands is the ability tospecify a subset of the roles that are granted to the user bydefault. You can use this approach if you want a user to be granted arole with the condition that explicit actions must be taken to enableit.

The most common use of non-default roles is to ensure that specificsystem or object privileges are available only from within anapplication. In this case, the application would enable the rolethrough 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 themaximum number of enabled roles a user can have. This number includesthe unlisted PUBLIC role. You can determine which roles are currentlyactive by querying the data dictionary viewSESSION_ROLES. Oracle counts all roles thathave been granted either directly or indirectly through other rolesagainst this limit. Thus, for example, enabling the DBA role actuallyuses 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 ROLEcommand is executed, the most common effect of the MAX_ENABLED_ROLESparameter is for a connection to fail with an ORA-1925 error, whichindicates 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 theCREATEROLE or ALTER ROLE commands. When a role has an associated password,the user must provide the password at the time the role is enabledwith the SET ROLE command. The two most common scenarios for this areas 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 theWITH ADMIN OPTION clause in the GRANTstatement. In this case, the user is allowed to grant the role toother users or roles. If you are able to grant a role to someonebecause you have the role WITH ADMIN OPTION, and then the role isrevoked from you, the cascaded grants remain. The situation isdifferent 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 adefault role. The exception is when the DEFAULT ROLE clause haspreviously been used in the CREATE USER or ALTER USER commands. Inthis case, the role may be granted as a non-default role. In orderfor the person granting the role to ensure that the role is a defaultrole, he or she must also have been granted the ALTER ANY USER systemprivilege.

6.1.7. Common Security Holes

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

6.1.7.1. Not changing privileged user passwords

All standard users created byOracle have associated passwords. The two primary DBA accounts, SYSand SYSTEM, have (unfortunately) well-documented passwords. Mostbreak-ins into Oracle databases are through one of these accounts,where the initial passwords provided with the installed systems havenever been changed.

6.1.7.2. Using the same password in all databases

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

6.1.7.3. The SCOTT/TIGER account

Most DBAs automatically create the SCOTT account with the well-knownpassword TIGER. This account has been used in demos for years byOracle and by Oracle Education (utilizing the EMP and DEPT tables).However, if this account exists, any grants made to PUBLIC are alsoavailable to the SCOTT account. If an unauthorized person accessesthe 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 userto read from and write to an operating system file from withinPL/SQL. The INIT.ORA parameter UTL_FILE_DIRspecifies a list of operating system directories you can use withUTL_FILE. Any PL/SQL procedure executed by any user of the databasecan read from or write to any file in the directory.

6.1.8. Auditing

After implementing a security policy, you need to audit actionswithin the database. Chapter 7, provides specificinformation on implementing an auditing policy.

Create Bookmark (Key: b)Create Bookmark
Create Note or Tag (Key: t)Create Note or Tag
Download (Key: d)Download
Email This Page (Key: e)Email This Page
PrintPrint

Html View (Key: h)Html View
Zoom Out (Key: -)Zoom Out
Zoom In (Key: +)Zoom In
Toggle to Full Screen (Key: f)
Previous (Key: p)Previous
Next (Key: n)Next