Oracle8i Administrator's Guide
Release 2 (8.1.6)

Part Number A76956-01





Go to previous page Go to next page

Establishing Security Policies

This chapter provides guidelines for developing security policies for database operation, and includes the following topics:

System Security Policy

This section describes aspects of system security policy, and includes the following topics:

Each database has one or more administrators who are responsible for maintaining all aspects of the security policy: the security administrators. If the database system is small, the database administrator may have the responsibilities of the security administrator. However, if the database system is large, a special person or group of people may have responsibilities limited to those of a security administrator.

After deciding who will manage the security of the system, a security policy must be developed for every database. A database's security policy should include several sub-policies, as explained in the following sections.

Database User Management

Database users are the access paths to the information in an Oracle database. Therefore, tight security should be maintained for the management of database users. Depending on the size of a database system and the amount of work required to manage database users, the security administrator may be the only user with the privileges required to create, alter, or drop database users. On the other hand, there may be a number of administrators with privileges to manage database users. Regardless, only trusted individuals should have the powerful privileges to administer database users.

User Authentication

Database users can be authenticated (verified as the correct person) by Oracle using database passwords, the host operating system, network services, or by Secure Sockets Layer (SSL).


To be authenticated using network authentication services or SSL, requires that you have installed Oracle Advanced Security. Refer to the Oracle Advanced Security Administrator's Guide for information about these types of authentication. 

For more information about user authentication and how it is specified, see "User Authentication".

Operating System Security

If applicable, the following security issues must also be considered for the operating system environment executing Oracle and any database applications:

Data Security Policy

Data security includes the mechanisms that control the access to and use of the database at the object level. Your data security policy determines which users have access to a specific schema object, and the specific types of actions allowed for each user on the object. For example, user SCOTT can issue SELECT and INSERT statements but not DELETE statements using the EMP table. Your data security policy should also define the actions, if any, that are audited for each schema object.

Your data security policy will be determined primarily by the level of security you wish to establish for the data in your database. For example, it may be acceptable to have little data security in a database when you wish to allow any user to create any schema object, or grant access privileges for their objects to any other user of the system. Alternatively, it might be necessary for data security to be very controlled when you wish to make a database or security administrator the only person with the privileges to create objects and grant access privileges for objects to roles and users.

Overall data security should be based on the sensitivity of data. If information is not sensitive, then the data security policy can be more lax. However, if data is sensitive, a security policy should be developed to maintain tight control over access to objects.

Some means of implementing data security include system and object privileges, and through roles. A role is a set of privileges grouped together that can be granted to users. Privileges and roles are discussed in Chapter 23, "Managing User Privileges and Roles".

Views can also implement data security because their definition can restrict access to table data. They can exclude columns containing sensitive data. Views are discussed in Chapter 18, "Managing Views, Sequences and Synonyms".

Another means of implementing data security is through fine-grained access control and use of an associated application context. Fine-grained access control is a feature of Oracle that allows you to implement security policies with functions, and to associate those security policies with tables or views. In effect, the security policy function generates a WHERE condition that is appended to a SQL statement, thereby restricting the users access to rows of data in the table or view. An application context is a secure data cache for storing information used to make access control decisions.

See Also:

For information about implementing fine-grained access control and an application context, see Oracle8i Application Developer's Guide - Fundamentals and Oracle8i Supplied PL/SQL Packages Reference. 

User Security Policy

This section describes aspects of user security policy, and includes the following topics:

General User Security

For all types of database users, consider the following general user security issues:

Password Security

If user authentication is managed by the database, security administrators should develop a password security policy to maintain database access security. For example, database users should be required to change their passwords at regular intervals, and of course, when their passwords are revealed to others. By forcing a user to modify passwords in such situations, 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 and server/server connections.


It is strongly recommended that you configure Oracle to encrypt passwords in client/server and server/server connections. Otherwise, a malicious user "snooping" on the network can grab an unencrypted password, and use it to connect to the database as another user, thereby "impersonating" that user. 

By setting the following values, you can require that the password used to verify a connection always be encrypted:

If enabled at both the client and server, passwords will not be sent across the network "in the clear", but will be encrypted using a modified DES (Data Encryption Standard) algorithm.

The DBLINK_ENCRYPT_LOGIN initialization parameter is used for connections between two Oracle servers (for example, when performing distributed queries). If you are connecting from a client, Oracle checks the ORA_ENCRYPT_LOGIN environment variable.

Whenever you attempt to connect to a server using a password, Oracle encrypts the password before sending it to the server. If the connection fails and auditing is enabled, the failure is noted in the audit log. Oracle then checks the appropriate DBLINK_ENCRYPT_LOGIN or ORA_ENCRYPT_LOGIN value. If it set to FALSE, Oracle attempts the connection again using an unencrypted version of the password. If the connection is successful, the connection replaces the previous failure in the audit log, and the connection proceeds. To prevent malicious users from forcing Oracle to re-attempt a connection with an unencrypted version of the password, you must set the appropriate values to TRUE.

Privilege Management

Security administrators should consider issues related to privilege management for all types of users. For example, in a database with many usernames, it may be beneficial to use roles (which are named groups of related privileges that you grant to users or other roles) to manage the privileges available to users. Alternatively, in a database with a handful of usernames, it may be easier to grant privileges explicitly to users and avoid the use of roles.

Security administrators managing a database with many users, applications, or objects should take advantage of the benefits offered by roles. Roles greatly simplify the task of privilege management in complicated environments.

End-User Security

Security administrators must also define a policy for end-user security. If a database is large with many users, the security administrator can decide what groups of users can be categorized, create user roles for these user groups, grant the necessary privileges or application roles to each user role, and assign the user roles to the users. To account for exceptions, the security administrator must also decide what privileges must be explicitly granted to individual users.

Using Roles for End-User Privilege Management

Roles are the easiest way to grant and manage the common privileges needed by different groups of database users.

Consider a situation where every user in the accounting department of a company needs the privileges to run the ACCTS_RECEIVABLE and ACCTS_PAYABLE database applications. Roles are associated with both applications, and contain the object privileges necessary to execute those applications.

The following actions, performed by the database or security administrator, address this simple security situation:

  1. Create a role named ACCOUNTANT.

  2. Grant the roles for the ACCTS_RECEIVABLE and ACCTS_PAYABLE database applications to the ACCOUNTANT role.

  3. Grant each user of the accounting department the ACCOUNTANT role.

This security model is illustrated in Figure 21-1.

Figure 21-1 User Role

Text description of securea.gif follows.

Text description of the illustration securea.gif.

This plan addresses the following potential situations:

When possible, utilize roles in all possible situations to make end-user privilege management efficient and simple.

Using a Directory Service for End-User Privilege Management

You can also manage users and their authorizations centrally, in a directory service, through the enterprise user and enterprise role features of Oracle Advanced Security. See Oracle Advanced Security Administrator's Guide for information about this functionality.

Administrator Security

Security administrators should have a policy addressing administrator security. For example, when the database is large and there are several types of database administrators, the security administrator may decide to group related administrative privileges into several administrative roles. The administrative roles can then be granted to appropriate administrator users. Alternatively, when the database is small and has only a few administrators, it may be more convenient to create one administrative role and grant it to all administrators.

Protection for Connections as SYS and SYSTEM

After database creation, immediately change the passwords for the administrative SYS and SYSTEM usernames to prevent unauthorized access to the database. Connecting as SYS and SYSTEM gives a user the powerful privileges to modify a database in many ways. Connecting as SYS allows a user to alter data dictionary tables. Therefore, privileges for these usernames are extremely sensitive, and should only be available to select database administrators.

The passwords for these accounts can be modified using the procedures described in "Altering Users".

Protection for Administrator Connections

Only database administrators should have the capability to connect to a database with administrator privileges (for example, connect as SYSDBA/SYSOPER). Connecting as SYSOPER gives a user the ability to perform basic operational tasks (such as startup, shutdown, and recover); connecting as SYSDBA gives the user these abilities plus unrestricted privileges to do anything to a database or the objects within a database (such as create, drop, and delete). Connecting as SYSDBA places a user in the SYS schema, where they can alter data dictionary tables

Using Roles for Administrator Privilege Management

Roles are the easiest way to restrict the powerful system privileges and roles required by personnel administrating of the database.

Consider a scenario where the database administrator responsibilities at a large installation are shared among several database administrators, each responsible for the following specific database management jobs:

In this scenario, the security administrator should structure the security for administrative personnel as follows:

  1. Six roles should be defined to contain the distinct privileges required to accomplish each type of job (for example, DBA_OBJECTS, DBA_TUNE, DBA_SECURITY, DBA_MAINTAIN, DBA_RECOV, DBA_NEW).

  2. Each role is granted the appropriate privileges.

  3. Each type of database administrator can be granted the corresponding role.

This plan diminishes the likelihood of future problems in the following ways:

Application Developer Security

Security administrators must define a special security policy for the application developers using a database. A security administrator may grant the privileges to create necessary objects to application developers. Alternatively, the privileges to create objects may only be granted to a database administrator, who receives requests for object creation from developers.

Application Developers and Their Privileges

Database application developers are unique database users who require special groups of privileges to accomplish their jobs. Unlike end users, developers need system privileges, such as CREATE TABLE, CREATE PROCEDURE, and so on. However, only specific system privileges should be granted to developers to restrict their overall capabilities in the database.

The Application Developer's Environment: Test and Production Databases

In many cases, application development is restricted to test databases and not allowed on production databases. This restriction ensures that application developers do not compete with end users for database resources, and that they cannot detrimentally affect a production database.

After an application has been thoroughly developed and tested, it is permitted access to the production database and made available to the appropriate end users of the production database.

Free Versus Controlled Application Development

The database administrator can define the following options when determining which privileges should be granted to application developers:

Free Development 

An application developer is allowed to create new schema objects, including tables, indexes, procedures, packages, and so on. This option allows the application developer to develop an application independent of other objects. 

Controlled Development 

An application developer is not allowed to create new schema objects. All required tables, indexes, procedures, and so on are created by a database administrator, as requested by an application developer. This option allows the database administrator to completely control a database's space usage and the access paths to information in the database. 

Although some database systems use only one of these options, other systems could mix them. For example, application developers can be allowed to create new stored procedures and packages, but not allowed to create tables or indexes. A security administrator's decision regarding this issue should be based on the following:

Roles and Privileges for Application Developers

Security administrators can create roles to manage the privileges required by the typical application developer. For example, a typical role named APPLICATION_DEVELOPER might include the CREATE TABLE, CREATE VIEW, and CREATE PROCEDURE system privileges. Consider the following when defining roles for application developers:

Space Restrictions Imposed on Application Developers

While application developers are typically given the privileges to create objects as part of the development process, security administrators must maintain limits on what and how much database space can be used by each application developer. For example, as the security administrator, you should specifically set or restrict the following limits for each application developer:

Both limitations can be set by altering a developer's security domain. This is discussed in "Altering Users".

Application Administrator Security

In large database systems with many database applications (for example, precompiler and Forms applications), you might want to have application administrators. An application administrator is responsible for the following types of tasks:

Often, an application administrator is also the application developer who designed the application. However, these jobs might not be the responsibility of the developer and can be assigned to another individual familiar with the database application.

Password Management Policy

Database security systems depend on passwords being kept secret at all times. Still, passwords are vulnerable to theft, forgery, and misuse. To allow for greater control over database security, Oracle's password management policy is controlled by DBAs.

Account Locking

When a particular user exceeds a designated number of failed login attempts, the server automatically locks that user's account. DBAs specify the permissible number of failed login attempts using the CREATE PROFILE statement. DBAs also specify the amount of time accounts remain locked.

In the following example, the maximum number of failed login attempts for the user ASHWINI is 4, and the amount of time the account will remain locked is 30 days; the account will unlock automatically after the passage of 30 days.

ALTER USER ashwini PROFILE prof;

If the DBA does not specify a time interval for unlocking the account, PASSWORD_LOCK_TIME assumes the value specified in a default profile (see "Creating Profiles"). If the DBA specifies PASSWORD_LOCK_TIME as UNLIMITED, then the system security officer must explicitly unlock the account.

After a user successfully logs into an account, that user's unsuccessful login attempt count, if there is one, is reset to 0.

The security officer can also explicitly lock user accounts. When this occurs, the account cannot be unlocked automatically; only the security officer should unlock the account.

See Also:

For more information about the CREATE PROFILE statement, see the Oracle8i SQL Reference

Password Aging and Expiration

DBAs use the CREATE PROFILE statement to specify a maximum lifetime for passwords. When the specified amount of time passes and the password expires, the user or DBA must change the password. The following statement indicates that ASHWINI can use the same password for 90 days before it expires:

ALTER USER ashwini PROFILE prof;

DBAs can also specify a grace period using the CREATE PROFILE statement. Users enter the grace period upon the first attempt to log in to a database account after their password has expired. During the grace period, a warning message appears each time users try to log in to their accounts, and continues to appear until the grace period expires. Users must change the password within the grace period. If the password is not changed within the grace period, the account expires and no further logins to that account are allowed until the password is changed. Figure 21-2 shows the chronology of the password lifetime and grace period.

Figure 21-2 Chronology of Password Lifetime and Grace Period

Text description of secure2.gif follows.

Text description of the illustration secure2.gif.

For example, the lifetime of a password is 60 days, and the grace period is 3 days. If the user tries to log in on any day after the 60th day (this could be the 70th day, 100th day, or another; the point here is that it is the first login attempt after the password lifetime), that user receives a warning message indicating that the password is about to expire in 3 days. If the user does not change the password within three days from the first day of the grace period, the user's account expires. The following statement indicates that the user must change the password within 3 days of its expiration:

ALTER USER ashwini PROFILE prof;

The security officer can also explicitly expire the account. This is particularly useful for new accounts.

Password History

DBAs use the CREATE PROFILE statement to specify a time interval during which users cannot reuse a password.

In the following statement, the DBA indicates that the user cannot reuse her password for 60 days.


The next statement shows that the number of password changes the user must make before her current password can be used again is 3.



If you specify PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX, you must set the other to UNLIMITED or not specify it at all. 

Password Complexity Verification

Oracle's password complexity verification routine can be specified using a PL/SQL script (UTLPWDMG.SQL), which sets the default profile parameters.

The password complexity verification routine performs the following checks:

Password Verification Routine Formatting Guidelines

DBAs can enhance the existing password verification complexity routine or create their own password verification routines using PL/SQL or third-party tools.

The DBA-authored PL/SQL call must adhere to the following format:

routine_name (
userid_parameter IN VARCHAR(30),
password_parameter IN VARCHAR (30),
old_password_parameter IN VARCHAR (30)

After a new routine is created, it must be assigned as the password verification routine using the user's profile or the system default profile.


The password verify routine must be owned by SYS.

The following sample script sets default password resource limits and provides minimum checking of password complexity. You can use this sample script as a model when developing your own complexity checks for a new password.

This script sets the default password resource parameters, and must be run to enable the password features. However, you can change the default resource parameters if necessary.

The default password complexity function performs the following minimum complexity checks:

This function must be created in SYS schema, and you must connect sys/<password> AS sysdba before running the script.

(username varchar2, 
   password varchar2, 
   old_password varchar2) 
   RETURN boolean IS  
   n boolean; 
   m integer; 
   differ integer; 
   isdigit boolean; 
   ischar  boolean; 
   ispunct boolean; 
   digitarray varchar2(20); 
   punctarray varchar2(25); 
   chararray varchar2(52); 
   digitarray:= '0123456789'; 
   chararray:= 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
--Check if the password is same as the username 
IF password = username THEN 
   raise_application_error(-20001, 'Password same as user'); 
--Check for the minimum length of the password 
IF length(password) < 4 THEN 
   raise_application_error(-20002, 'Password length less than 4'); 
--Check if the password is too simple. A dictionary of words may be 
--maintained and a check may be made so as not to allow the words 
--that are too simple for the password.
IF NLS_LOWER(password) IN ('welcome', 'database', 'account', 'user', 
   'password', 'oracle', 'computer', 'abcd') 
   THEN raise_application_error(-20002, 'Password too simple');
--Check if the password contains at least one letter,
--one digit and one punctuation mark. 
--1. Check for the digit 
--You may delete 1. and replace with 2. or 3.
m := length(password); 
FOR i IN 1..10 LOOP  
  FOR j IN 1..m LOOP  
    IF substr(password,j,1) = substr(digitarray,i,1) THEN 
        GOTO findchar; 
    END IF; 
IF isdigit = FALSE THEN 
  raise_application_error(-20003, 'Password should contain at least one \
  digit, one character and one punctuation'); 
--2. Check for the character 

FOR i IN 1..length(chararray) LOOP  
  FOR j IN 1..m LOOP  
    IF substr(password,j,1) = substr(chararray,i,1) THEN 
         GOTO findpunct; 
       END IF; 
    END LOOP; 
IF ischar = FALSE THEN 
  raise_application_error(-20003, 'Password should contain at least one digit,\
    one character and one punctuation'); 
--3. Check for the punctuation 

FOR i IN 1..length(punctarray) LOOP  
  FOR j IN 1..m LOOP  
    IF substr(password,j,1) = substr(punctarray,i,1) THEN 
         GOTO endsearch; 
       END IF; 
IF ispunct = FALSE THEN raise_application_error(-20003, 'Password should \
 contain at least one digit, one character and one punctuation'); 
--Check if the password differs from the previous password by at least 3 letters 
IF old_password = '' THEN 
  raise_application_error(-20004, 'Old password is null');
--Everything is fine; return TRUE ;    
differ := length(old_password) - length(password); 
IF abs(differ) < 3 THEN 
  IF length(password) < length(old_password) THEN 
    m := length(password); 
    m:= length(old_password); 
  END IF; 
  differ := abs(differ); 
  FOR i IN 1..m LOOP 
    IF substr(password,i,1) != substr(old_password,i,1) THEN 
             differ := differ + 1; 
    END IF; 
  IF differ < 3 THEN 
    raise_application_error(-20004, 'Password should differ by at \ 
      least 3 characters'); 
    END IF; 
  END IF; 
--Everything is fine; return TRUE ;    

Auditing Policy

Security administrators should define a policy for the auditing procedures of each database. You may, for example, decide to have database auditing disabled unless questionable activities are suspected. When auditing is required, the security administrator must decide what level of detail to audit the database; usually, general system auditing is followed by more specific types of auditing after the origins of suspicious activity are determined. Auditing is discussed in Chapter 24, "Auditing Database Use".

Go to previous page Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.