| Oracle® Database Concep
ts 10g Release 1 (10.1) Part Number B10743-01 |
|
|
View PDF |
| Oracle® Database Concep
ts 10g Release 1 (10.1) Part Number B10743-01 |
|
|
View PDF |
This chapter provides an over view of Oracle database security.
This chapter contains the following topics:
Overview of Access R estrictions on Tables, Views, Synonyms, or Rows
|
See Also: Oracle Datab ase Security Guide for more detailed information on everything in this chapter |
Database security entails allowing or disallowing user actions on the database and the objects within it. Oracle uses schemas and security domains to control access to data and to restrict the use of various data base resources.
Oracle provides comprehensive discretionary access control. < a id="sthref2650" name="sthref2650">Discretionary access control regulates all user access to named objects through priv ileges. A privilege is permission to access a named object in a prescribed manner; for example, permission to query a table. Privileg es are granted to users at the discretion of other users.
Eac h Oracle database has a list of user names. To access a database, a user must use a database application and attempt a connection wit h a valid user name of the database. Each user name has an associated password to prevent unauthorized use.
Each user has a security domain&# x2014;a set of properties that determine such things as:
The actions (privileges and roles) available to the user
The tablespace quotas (available disk space) for the user
The sys tem resource limits (for example, CPU processing time) for the user
Each property that contributes to a user's secur ity domain is discussed in the following sections.
A privilege is a right to run a particular type of SQL statement. Some examples of privileges include the right to:
Connect to the database (creat e a session)
Create a table in your schema
Select rows from someone else's table
Run someone else's stored procedure
Oracle provides for easy and controlled privilege management through roles. Roles are named groups of related privileges that you grant to users or other roles.
You can direct and limit t he use of disk space allocated to the database for each user, including default and temporary tablespaces and tablespace quotas.
Each user is associated wi th a default tablespace. When a user creates a table, index, or cluster and no tablespace is specified to physically contain the schema object, the user's default tablespace is used if the user has the privilege to create the schema object and a quo ta in the specified default tablespace. The default tablespace provides Oracle with information to direct space use in situations whe re schema object's location is not specified.
Each user has a temporary tablespace. When a user runs a SQL statement that requires the creation of temporary segments (such as the creation of an index), the user's temporary tablespace is use d. By directing all users' temporary segments to a separate tablespace, the temporary tablespace can reduce I/O contention among temp orary segments and other types of segments.
Oracle can limit the collective amount of disk space available to the objects in a schema. Quotas (space limits) can be set for each tablespace available to a user. This permits sele ctive control over the amount of disk space that can be consumed by the objects of specific schemas.
Each user is assigned a profile that specifies limitations on several system resources available to the user, including the following:
Number of concurrent sessions the user can establish
CPU processing time available for the user's session and a single call to Oracle made by a SQL statement
Amount of logical I/O available for the user's session and a single call to Oracle made by a SQL statement
Amount of idle time available for the user's session
Amount of connect time availab le for the user's session
Password restrictions:
Account locking after mu ltiple unsuccessful login attempts
Password expiration and grace period
Password reuse and complexity restrictions
Authentication means verifying the identity of someone (a user, device, or other entity) who wa nts to use data, resources, or applications. Validating that identity establishes a trust relationship for further interactions. Auth entication also enables accountability by making it possible to link access and actions to specific identities. After authentication, authorization processes can allow or limit the levels of access and action permitted to that entity.
For simplicity, the same authentication method is generally used for all database users, but Oracle allows a single database instance to use any or all metho ds. Oracle requires special authentication procedures for database administrators, because they perform special database operations. Oracle also encrypts passwords during transmission to ensure the security of network authentication.
To validate the identity of database users and prevent unauthorized use of a database user name, you can authenticate using any combination of the methods described in the following sections:
Some operating systems let Oracle use information they maintain to authenticate users, with the following benefits:
Once authenticated by the operating s ystem, users can connect to Oracle more conveniently, without specifying a user name or password. For example, an operating-system-authenticated user can invoke SQL*Plus and skip the user name and password prompts by entering the following:
SQLPLUS /
With control over user authentication cent ralized in the operating system, Oracle need not store or manage user passwords, though it still maintains user names in the database .
Audit trails in the database and operating system use the same user names.
When an operating system is used to authenticate database users, managing distributed database environ ments and database links requires special care.
Oracle supports the following methods of authentication by the network:
Note:
These methods require Oracle Database Enterprise Edition with the Oracle Advance d Security option.I f network authentication services are available to you (such as DCE, Kerberos, or SESAME), then Oracle can accept authentication from the network service. If you use a network authentication service, then some special considerations arise for network roles and datab ase links.
< a id="sthref2687" name="sthref2687">Authenticat ion systems based on public key cryptography issue digital certificates to user clients, which use them to authenticate directly to s ervers in the enterprise without directly involving an authentication server. Oracle provides a public key infrastructure (PKI) for u sing public keys and certificates, consisting of the following components:
Authentication and secure ses sion key management using Secure Sockets Layer (SSL).
Oracle Call Interface (OCI) and PL/SQL functions t o sign user-specified data using a private key and certificate, and verify the signature on data using a trusted certificate.
Trusted certificates, identifying third-party entities that are trusted as signers of user certificates when an identity is being validated as the entity it claims to be.
Oracle wallets, which are data structures that contain a user private key, a user certificate, and the user's set of trust points (trusted certificate authorities).
Oracle Wallet Manager, a standalone Java app lication used to manage and edit the security credentials in Oracle wallets.
X.509v3 certificates obtained from (and signed by) a trusted entity, a certificate authority outside of Oracle.
Oracle Internet Dire ctory to manage security attributes and privileges for users, including users authenticated by X.509 certificates. It enforces attrib ute-level access control and enables read, write, or update privileges on specific attributes to be restricted to specific named user s, such as administrators.
Oracle Enterprise Security Manager, provides centralized privilege management to make administration easier and increase your level of security. This lets you store and retrieve roles from Oracle Internet Directory.
Oracle Enterpris e Login Assistant, a Java-based tool to open and close a user wallet to enable or disable secure SSL-based communications for an appl ication.
Oracle can authenticate users attempting to connect to a d atabase by using information stored in that database.
To set up Oracle to use databas e authentication, create each user with an associated password that must be supplied when the user attempts to establish a connection . This prevents unauthorized use of the database, since the connection will be denied if the user provides an incorrect password. Ora cle stores a user's password in the data dictionary in an encrypted format to prevent unauthorized alteration, but a user can change the password at any time.
Database authentication includes the following facilities:
To protect password confidentiality, Oracle lets you use encrypted passwords during network (client/server and server/server) c onnections. If this functionality is enabled on the client and server machines, then Oracle encrypts the passwords using a modified D ES (Data Encryption Standard) algorithm before sending them across the network. This is strongly recommended, to protect your passwor ds from network intrusion.
Oracle can lock a user's account after a specified number of consecutive failed log-in attempts. You can con figure the account to unlock automatically after a specified time interval or to require database administrator intervention to be un locked. The database administrator can also lock accounts manually, so that they must be unlocked explicitly by the database administ rator.
The database admini strator can specify a lifetime for passwords, after which they expire and must be changed before account login is again permitted. A grace period can be established, during which each attempt to login to the database account receives a warning message to change the password. If it is not changed by the end of that period, then the account is locked. No further logins to that account are allowed w ithout assistance by the database administrator.
The database administrator can also set the password state to expired, causin g the user's account status to change to expired. The user or the database administrator must then change the password before the use r can log in to the database.
The password history option checks each newly specified password to ensure that a password is not reused for a specified amount of time or for a specified number of password changes.
Complexity verification checks that each password is complex enough to provide reasonable protection against intruders who try to break into the system by guessing passwords.
The Oracle default password complexity verification routine checks that ea ch password meet the following requirements:
Be a minimum of four characters in length
Not equal the userid
Include at least one alphabet character, one numeric character, and one punctuation mark
Not match any word on an internal list of simple words like welcome, account, database , user, and so on
Differ from the previous password by at least three characters
In a multitier environment, Oracle controls the security of middle-tier applications by lim iting their privileges, preserving client identities through all tiers, and auditing actions taken on behalf of clients. In applicati ons that use a heavy middle tier, such as a transaction processing monitor, the identity of the client connecting to the middle tier must be preserved. Yet one advantage of a middle tier is connection pooling , which allows multiple users to access a data server without each of them needing a separate connection. In such environmen ts, you must be able to set up and break down connections very quickly.
For these environments, Oracle database administrators can use the Oracle Call Interface (OCI) to create lightweight sessions, allowing database password authentication f or each user. This preserves the identity of the real user through the middle tier without the overhead of a separate database connec tion for each user.
You can create lightweight sessions with or without passwords. However, if a middle tier is outside or on a firewall, then security is better when each lightweight session has its own password. For an internal application server, lightweig ht sessions without passwords might be appropriate.
The Secure Socket Layer (SSL) protocol is an application layer protocol. It can be used f or user authentication to a database, independent of global user management in Oracle Internet Directory. That is, users can use SSL to authenticate to the database without implying anything about their directory access. However, to use the enterprise user functiona lity to manage users and their privileges in a directory, the user must use SSL to authenticate to the database. A parameter in the i nitialization file governs which use of SSL is expected.
Database ad ministrators perform special operations (such as shutting down or starting up a database) that should not be performed by normal data base users. Oracle provides a more secure authentication scheme for database administrator user names.
You can choose between operating system authentication or password files to authenticate database administrators. Figure 20-1 illustra tes the choices you have for database administrator authentication schemes. Different choices apply to administering your database lo cally (on the machine where the database resides) and to administering many different database machines from a single remote client.< /p>
Figure 20-1 Database Administrator Authentication Methods
Operating system authentication for a database adminis trator typically involves placing his operating system user name in a special group or giving it a special process right. (On UNIX sy stems, the group is the dba group.)
The database uses password files to keep track of database user names that have bee
n granted the SYSDBA and SYSOPER privileges, enabling the following operations:
SYSOPER lets database administrators perform STARTUP, SHUTDOWN, ALTER DATABASE
OPEN/MOUNT, ALTER DATABASE BACKUP, ARCHIVE LOG, and RESTRICTED SESSION privilege.SYSDBA contains all system privileges with ADMIN OPTION, and the S
YSOPER system privilege. Permits CREATE DATABASE and time-based recovery.
|
Authorization primarily includes two processes:
Permitting only certain users to access, process, or alter data
Applying varying limitations on users' access or actions . The limitations placed on (or removed from) users can apply to objects, such as schemas, tables, or rows; or to resources, such as time (CPU, connect, or idle times).
This section introduces the basic concepts and mechanisms for placing or removin g such limitations on users, individually or in groups.
This is very useful in large, multiuser systems, where system resources are expensive. Excessive consumption of resources b y one or more users can detrimentally affect the other users of the database.
Manage a user's resource limits and password management preferences with his or her profile—a named set of resource limits that you c an assign to that user. Each database can have an unlimited number of profiles. The security administrator can enable or disable the enforcement of profile resource limits universally.
If you set resource limits, then a slight degradation in performance occur s when users create sessions. This is because Oracle loads all resource limit data for the user when a user connects to a database.
Resource limits and profiles are d iscussed in the following sections:
Oracle can limit the use of several types of system resources, including CPU time and logical reads. In general, you can co ntrol each of these resources at the session level, the call level, or both.
Each time a user connects to a d atabase, a session is created. Each session consumes CPU time and memory on the computer that runs Oracle. You can set several resour ce limits at the session level.
If a user exceeds a session-level resource limit, then Oracle terminates (rolls back) the curr
ent statement and returns a message indicating that the session limit has been reached. At this point, all previous statements in the
current transaction are intact, and the only operations the user can perform are COMMIT, ROLLBACK, or disc
onnect (in this case, the current transaction is committed). All other operations produce an error. Even after the transaction is com
mitted or rolled back, the user can accomplish no more work during the current session.
Each time a SQL statement is run, several step s are taken to process the statement. During this processing, several calls are made to the database as part of the different executi on phases. To prevent any one call from using the system excessively, Oracle lets you set several resource limits at the call level.< /p>
If a user exceeds a call-level resource limit, then Oracle halts the processing of the statement, rolls back the statement, an d returns an error. However, all previous statements of the current transaction remain intact, and the user's session remains connect ed.
When SQL statements and other types of calls are made to Oracle, an amount of CPU time is necessary to process the call. Average cal ls require a small amount of CPU time. However, a SQL statement involving a large amount of data or a runaway query can potentially c onsume a large amount of CPU time, reducing CPU time available for other processing.
To prevent uncontrolled use of CPU time, limit the CPU time for each call and the total amount of CPU time used for Oracle calls during a session. Limits are set and measured in CPU one-hundredth seconds (0.01 seconds) used by a call or a session.
Input/output (I/ O) is one of the most expensive operations in a database system. SQL statements that are I/O intensive can monopolize memory and disk use and cause other database operations to compete for these resources.
To prevent single sources of excessive I/O, Oracle le ts you limit the logical data block reads for each call and for each session. Logical data block reads include data block reads from both memory and disk. The limits are set and measured in number of block reads performed by a call or during a session.
Oracle also provides for the limitation of several other resources at the session level:
You can limit the number of concurrent sessions for each user. Each user can create only up to a pr edefined number of concurrent sessions.
You can limit the idle time for a session. If the time between Oracle calls for a session reaches the idle time limit, then the current transaction is rolled back, the session is aborted, and the resources of the session are return ed to the system. The next call receives an error that indicates the user is no longer connected to the instance. This limit is set a s a number of elapsed minutes.
Shortly after a session is aborted because it has exce eded an idle time limit, the process monitor (PMON) background process cleans up after the aborted session. Until PMON completes this process, the aborted session is still counted in any session/user resource limit.
You can limit the elapsed connect time for each session. If a session's duration exceeds the elapsed time limit, then the current transaction is rolled back, the session is dropped, and the resources of the session are returned to the system. Thi s limit is set as a number of elapsed minutes.
Oracle does not constantly monitor the elapsed idle time or elapsed connection time. Doing so would reduce system performance. Instead, it checks every few minutes. Therefore, a session can exceed this limit slig htly (for example, by five minutes) before Oracle enforces the limit and aborts the session.
You can limit the amount of private SGA space (used for private SQL areas) for a session. This limit is only impo rtant in systems that use the shared server configuration. Otherwise, private SQL areas are located in the PGA. This limit is set as a number of bytes of memory in an instance's SGA. Use the characters K or M to specify kilobytes or megabytes.
|
See Also: Oracle Database Administrator's Guide for instructions about enabling and disabling resource limits |
In the context of system resources, a profile is a named set of specified resource limits that can be assigned to a valid user name in an Ora cle database. Profiles provide for easy management of resource limits. Profiles are also the way in which you administer password pol icy.
Different profiles can be created and assigned individually to each user of the database. A default profile is present fo r all users not explicitly assigned a profile. The resource limit feature prevents excessive consumption of global database system re sources.
You need to create and manage user profiles only if resource limits are a requirement of your database security policy. To use profiles, first categorize the related types of users in a database. Just as roles are used to manage the privileges of related users, profiles are used to manage the resource limits of relat ed users. Determine how many profiles are needed to encompass all types of users in a database and then determine appropriate resourc e limits for each profile.
Before creating profiles and setting the resource limits associated with them, determine appropriate values f or each resource limit. You can base these values on the type of operations a typical user performs. Usually, the best way to determi ne the appropriate resource limit values for a given user profile is to gather historical information about each type of resource usa ge.
You can gather statistics for other limi ts using the Monitor feature of Oracle Enterprise Manager (or SQL*Plus), specifically the Statistics monitor.
A
Grant privile ges to users so that they can accomplish tasks required for their job. Grant privileges only to users who absolutely require them. Ex cessive granting of unnecessary privileges can compromise security. A user can receive a privilege in two different ways:
You can grant privileges to users explicitly. For example, you can explicitly grant the privilege to insert records
into the employees table to the user SCOTT.
You can grant privileges to a role (a named group of privileges), and then grant the role to one or more users. For example,
you can grant the privileges to select, insert, update, and delete records from the employees table to the role named scott and brian.
Because roles allow for easier and better management of privileges, you should generally grant privileges to roles and not to specific users.
< p>There are two distinct categories of privileges:|
|
A system privile ge is the right to perform a particular action, or to perform an action on any schema objects of a particular type. For example, the privileges to create tablespaces and to delete the rows of any table in a database are system privileges. There are over 100 distinct system privileges.
A schema object privilege is a privilege or right to perform a particular action o n a specific schema object:
Different object privileges are available for different types of schema objects. For example, the
privilege to delete rows from the departments table is an object privilege.
Some schema objects, such as clusters, indexes, triggers, and database links, do not have associated object privileges. Their use
is controlled with system privileges. For example, to alter a cluster, a user must own the cluster or have the ALTER CLUSTER system privilege.
A schema object and its synonym are equivalent with respect to privileges. That is, the object privileges granted for a table, view, sequence, procedure, function, or package apply whether referencing the base object by name or using a synonym.
Granting object privileges on a table, view, sequence, procedure, function, or package to a synonym for the object has the same effect as if no synonym were used . When a synonym is dropped, all grants for the underlying schema object remain in effect, even if the privileges were granted by spe cifying the dropped synonym.
|
See Also:< /strong> Oracle Database Security Guide for more information about schema object privileges |
Managing and contr olling privileges is made easier by using r oles, which are named groups of related privileges that you grant, as a group, to users or other roles. Within a database, each role name must be unique, different from all us er names and all other role names. Unlike schema objects, roles are not contained in any schema. Therefore, a user who creates a role can be dropped with no effect on the role.
Roles ease the administration of end-user system and schema object privileges. How ever, roles are not meant to be used by application developers, because the privileges to access schema objects within stored program matic constructs must be granted directly.
These following properties of roles enable easier privilege management within a database:
| Prope rty | Description |
|---|---|
| Reduced p rivilege administration | Rather than granting the same set of privileges explicitly to several users, you can grant the privileges for a group of related users to a role, and then only the role needs to be granted to each member of the group. |
| Dynamic privileg e management | If the privileges of a group must change, then only the privileges of the role need to be modified. The security domains of all users granted the group's role automatically reflect the changes made to th e role. |
| Selective availability of privileg es | You can selectively enable or disable the roles granted to a user. This allows s pecific control of a user's privileges in any given situation. |
| Application awareness | The data dictionary records which roles exist, so you can design applications to query the dictionary and automatically enable (or disable) selective roles when a user attem pts to run the application by way of a given user name. |
| Application-specific security | You can protect role use with a passwor d. Applications can be created specifically to enable a role when supplied the correct password. Users cannot enable the role if they do not know the password. |
Database administrators often create roles for a database application. The DBA grants a secure application role all privileges necessary to run the application. The DBA then gr ants the secure application role to other roles or users. An application can have several different roles, each granted a different s et of privileges that allow for more or less data access while using the application.
The DBA can create a role with a password to prevent unauthorized use of the privileges gra nted to the role. Typically, an application is designed so that when it starts, it enables the proper role. As a result, an applicati on user does not need to know the password for an application's role.
See Also:
Ora cle Database Application Developer's Guide - Fundamentals for instructions for enabling roles from an applicationIn general, you create a role to serve one of two purposes:
To man age the privileges for a database application
To manage the privileges for a user group
Figure 20-2 and the sections that follow describe the two uses of roles.
You grant an application role all privileges necessary to run a given database application. Then, you grant the secure application role to other roles or to specific users. An application can have several different roles, with each role assigned a different set of privileges th at allow for more or less data access while using the application.
Database roles have the following functionality:
A role can be granted system or schema object privileges.
A role can be granted to other roles. However, a role cannot be granted to
itself and cannot be granted circularly. For example, role A cannot be granted to role B if role B has previously been granted to role A.
Any role can be granted to any database user.
Each role granted to a user is, at a given time, either enabled or disabled. A user's security domain includes the privileges of all roles currently enabled for the user and excludes the privileges of any roles currently disabled for the user. Oracle allows database applications and users to enable and disable roles to provide selective availability of privileges.
An indirectly granted role is a role granted to a role. It can be explicitly enabled or disabled for a user. However, by enabling a role that contains other ro les, you implicitly enable all indirectly granted roles of the directly granted role.
In some environments, you can administer database security using the operating system. The operating system can be u sed to manage the granting (and revoking) of database roles and to manage their password authentication. This capability is not avail able on all operating systems.
Oracle provides secure application roles, which are roles that can only be enabled by authorized PL/SQL pa ckages. This mechanism restricts the enabling of such roles to the invoking application.
Security is strengthened when passwor ds are not embedded in application source code or stored in a table. Instead, a secure application role can be created, specifying wh ich PL/SQL package is authorized to enable the role. Package identity is used to determine whether privileges are sufficient to enabl e the roles. Before enabling the role, the application can perform authentication and customized authorization, such as checking whet her the user has connected through a proxy.
Because of the restriction that users cannot change security domain inside definer 's right procedures, secure application roles can only be enabled inside invoker's right procedures.
This sect ion describes restrictions associated not with users, but with objects. The restrictions provide protection regardless of the entity who seeks to access or alter them.
You provide this protection by designing and using policies to restrict access to specific tables, views, synonyms, or rows. These policies invoke functions that you design to specify dynamic predicates establishing the rest rictions. You can also group established policies, applying a policy group to a particular application.
Having established suc h protections, you need to be notified when they are threatened or breached. Given notification, you can strengthen your defenses or deal with the consequences of inappropriate actions and the entities who caused them.
Fine-grained access control lets you use functions to implement security policies and to associate those security policies with tables, views, or synonyms. The database server automatically enforces your security policies, no matter how the data is accessed (f or example, by ad hoc queries).
You can:
Use different policies for SELECT, IN
SERT, UPDATE, and DELETE (and INDEX, for row level security policies).
Use security policies only where you need them (for example, on salary information).
Use mo re than one policy for each table, including building on top of base policies in packaged applications.
Distinguish policies between different applications, by using policy groups. Each policy group is a set of policies that belong to an application. The database administrator designates an application context, called a driving context, to indicate the policy group in effect. When tables, views, or synonyms are accessed, the fine-grained access control engine looks up the driving context to determi ne the policy group in effect and enforces all the associated policies that belong to that policy group.
The PL/SQL package DBMS_RLS let you administer your security policies. Using this package,
you can add, drop, enable, disable, and refresh the policies (or policy groups) you create.
|
See Also:
|
|
See Also:
|
This section describes aspects of user security policy, and contains the following topics:
For all types of database users, consider password security and privilege management.
If user authentication is managed by the database, then security administrators should develop a password security policy to maintain database access security. For example, database users must change their passwords at regular intervals. By forcing a user to modify passwords, unauthorized database access can be reduced. To better protect the confidentiality of your password, Oracle can be configured to use encrypted passwords for client/server a nd server/server connections.
Also consider issues related to privilege management for all types of u sers. For example, a database with many users, applications, or objects, would benefit from using roles to manage the privileges avai lable to users. Alternatively, in a database with a handful of user names, it might be easier to grant privileges explicitly to users and avoid the use of roles.
Security administrators must define a policy for end-user security. If a database has many users, then t he security administrator can decide which groups of users can be categorized into user groups, and then create user roles for these groups. The security administrator can grant the necessary privileges or application roles to each user role, and assign the user rol es to the users. To account for exceptions, the security administrator must also decide what privileges must be explicitly granted to individual users.
Roles are the easiest way to grant and manage the common privileges needed by different groups of database users. You can also manage users and their authoriz ations centrally, in a directory service, through the < /a>enterprise user and enterprise role features of Oracle Advanced Security.
Security administrators should have a policy addressing database administrator security. For example, when the database is large and there are several types of database administrators, the security administrator might decide to group r elated administrative privileges into several administrative roles. The administrative roles can then be granted to appropriate admin istrator users. Alternatively, when the database is small and has only a few administrators, it might be more convenient to create on e administrative role and grant it to all administrators.
After database creation, and i
f you used the default passwords for SYS and SYSTEM, immediately change the passwords for the SYSTEM administrative user names. Connecting as SYS or SYSTEM gives a user po
werful privileges to modify a database.
If you have installed options that have caused other administrative user names to be c reated, then such user name accounts are initially created locked.
Only database administrators should have the capability to connect to a databa se with administrative privileges. For example:
CONNECT username/password AS SYSDBA/SYSOPER < /pre>Connecting as
< /div>SYSOPERgives a user the ability to perform basic operational tasks (such asSTARTUP,SHUTDOWN, and recovery operations). Connecting asSYSDBAgives the user these abilities plus unrestricte d privileges to do anything to a database or the objects within a database (including,CREATE,DROP, andDELETE). SYSDBAputs a user in theSYSschema, where they can alter data dictionary tables.
Security administrators must define a special security policy for the application developers u sing a database. A security administrator could grant the privileges to create necessary objects to application developers. Or, alter natively, the privileges to create objects could be granted only to a database administrator, who then receives requests for object c reation from developers.
Database application developers are unique database users who re
quire special groups of privileges to accomplish their jobs. Unlike end users, developers need system privileges, such as CREAT
E TABLE, CREATE PROCEDURE, and so on. However, only specific system privileges should b
e granted to developers to restrict their overall capabilities in the database.
In many cases, application development is restricted to test databases and is not allowed on production databases. This restriction ensures that application developers do not compete with e nd users for database resources, and that they cannot detrimentally affect a production database. After an application has been thoro ughly developed and tested, it is permitted access to the production database and made available to the appropriate end users of the production database.
Security administrators can create roles to manage the privileges required by the typical application developer.
While application developers are typ ically given the privileges to create objects as part of the development process, security administrators must maintain limits on wha t and how much database space can be used by each application developer. For example, the security administrator should specifically set or restrict the following limits for each application developer:
The tablespaces in which the develo per can create tables or indexes
The quota for each tablespace accessible to the developer
Both limitations can be set by altering a developer's security domain.
In large database systems with many database applications, con sider assigning application administrators responsible for the following types of tasks:
Creating roles for an application and managing the privileges of each application role
Creating and managing the object s used by a database application
Maintaining and updating the application code and Oracle procedures and packages, as necessary
Often, an application administrator is also the application developer who designed an applic ation. However, an application administrator could be any individual familiar with the database application.
Database security systems dependent on passwords require that passwords be kept secret at all times. But, passwords are vulnerable to theft, forgery, and misus e. To allow for greater control over database security, Oracle's password management policy is controlled by DBAs and security office rs through user profiles.
Security administrators should define a policy for the auditing procedures of each database. You may decide to have database auditing disabled unless questionable activities are suspected. When auditing is r equired, decide what level of detail to audit the database; usually, general system auditing is followed by more specific types of au diting after the origins of suspicious activity are determined. Auditing is discussed in the following section.
Auditing is t he monitoring and recording of selected user database actions. It can be based on individual actions, such as the type of SQL stateme nt run, or on combinations of factors that can include name, application, time, and so on. Security policies can cause auditing when specified elements in an Oracle database are accessed or altered, including content.
Auditing is generally used to:
Enable future accountability for current actions taken in a particular schema, table, or row, or affecting specifi c content
Investigate suspicious activity. For example, if an unauthorized user is deleting data from ta bles, then the security administrator could audit all connections to the database and all successful and unsuccessful deletions of ro ws from all tables in the database.
Monitor and gather data about specific database activities. For exam ple, the database administrator can gather statistics about which tables are being updated, how many logical I/Os are performed, or h ow many concurrent users connect at peak times.
Oracle allows audit options to be focused or broad. You can audit:
Successful statement executions, unsuccessful statement executions, or both
Statement executio ns once in each user session or once every time the statement is run
Activities of all users or of a spe cific user
Oracle auditing enables the use of several different mechanisms, with the following features:
Table 20-1 Types of Auditing
Audit records include information such as the operation that was audited, t he user performing the operation, and the date and time of the operation. Audit records can be stored in either a data dictionary tab le, called the database audit trail, or in operating system files, called an operating system audit trail.
The database audit trail is a single table named SYS.AUD$
in the SYS schema of each Oracle database's data dictionary. Several predefined views are provided to help you use the
information in this table.
Audit trail records can contain different types of information, depending on the events audited and the auditing options set. The following information is always included in each audit trail record, if the information is meaningful to the particular audit action:
User name
Instance number
Process identifier
Session identifier
Terminal identifier
Name of the schema object accessed
Operation performed or attempted
Completion code of the operation
Date and time stamp
Syste m privileges used
Auditing is site autonomous. An instance audits only the statements issued by dire ctly connected users. A local Oracle node cannot audit actions that take place in a remote database. Because remote connections are e stablished through the user account of a database link, statements issued through the database link's connection are audited by the r emote Oracle node.
Oracle allows audit trail records to be directed to an operating system audit trail if the operating sy stem makes such an audit trail available to Oracle. If not, then audit records are written to a file outside the database, with a for mat similar to other Oracle trace files.
Oracle allows certain actions that are always audited to continue, even when the operating system audit trail (or the operating system file containing audit records) is unable to record the audit record. The u sual cause of this is that the operating system audit trail or the file system is full and unable to accept new records.
Syste m administrators configuring operating system auditing should ensure that the audit trail or the file system does not fill completely . Most operating systems provide administrators with sufficient information and warning to ensure this does not occur. Note, however, that configuring auditing to use the database audit trail removes this vulnerability, because the Oracle database server prevents au dited events from occurring if the audit trail is unable to accept the database audit record for the statement.
Th e operating system audit trail is encoded, but it is decoded in data dictionary files and error messages.
AUDIT_ACTIONS data dictionary table d
escribes these codes.Privileges used describes any system privileges used to perform t
he operation. The SYSTEM_PRIVILEGE_MAP table describes all of these codes.
Completi on code describes the result of the attempted operation. Successful operations return a value of zero, and unsuccessful oper ations return the Oracle error code describing why the operation was unsuccessful.
|
See Also:
|
Some database-related actions are always recorded into the operating system audit trail regardless of whether database au diting is enabled:
At instance startup, an audit record is generated that details the operating system u ser starting the instance, the user's terminal identifier, the date and time stamp, and whether database auditing was enabled or disa bled. This information is recorded into the operating system audit trail, because the database audit trail is not available until aft er startup has successfully completed. Recording the state of database auditing at startup also acts as an auditing flag, inhibiting an administrator from performing unaudited actions by restarting a database with database auditing disabled.
At instance shutdown, an audit record is generated that details the operating system user shutting down the instance, the user's terminal identifier, the date and time stamp.
During connections with administrator privileges, an audi t record is generated that details the operating system user connecting to Oracle with administrator privileges. This record provides accountability regarding users connected with administrator privileges.
On operating systems that do not make an au dit trail accessible to Oracle, these audit trail records are placed in an Oracle audit trail file in the same directory as backgroun d process trace files.
Any authorized database user can set hi s own audit options at any time, but the recording of audit information is enabled or disabl ed by the security administrator.
When auditing is enabled in the database, an audit record is generated during the execute ph ase of statement execution.
SQL statements inside PL/SQL program units are individual ly audited, as necessary, when the program unit is run.
The generation and insertion of an audit trail record is independent of a user's transaction being committed. That is, even if a user's transaction is rolled back , the audit trail record remains committed.
Statement and privilege audit options in effect at the time a database user connects to the database remain in effect for the duratio n of the session. Setting or changing statement or privilege audit options in a session does not cause effects in that session. The m odified statement or privilege audit options take effect only when the current session is ended and a new session is created. In cont rast, changes to schema object audit options become effective for current sessions immediately.
Operations by the SYS user and by users connected through SYSDBA or SYSOPER can be fully audited with the AUDIT_SYS_OP
ERATIONS initialization parameter. Successful SQL statements from SYS are audited indiscriminately. The audit rec
ords for sessions established by the user SYS or connections with administrative privileges are sent to an operating sys
tem location. Sending them to a location separate from the usual database audit trail in the SYS schema provides for gre
ater auditing security.
|
See Also:
|
|
 Copyright © 1993, 2003 Oracle Corporation All Rights Reserved. |
|
