Oracle7 Server Administrator's Guide
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 administrator's 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.
Secure Connections with Encrypted Passwords
To better protect the confidentiality of your password, Oracle7 can be configured to use encrypted passwords for client/server and server/server connections.
You can require that the password used to verify a connection always be encrypted by setting the following values:
- Set the ORA_ENCRYPT_LOGIN environment variable to TRUE on the client machine.
- Set the DBLINK_ENCRYPT_LOGIN server initialization parameter to TRUE.
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 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 18 - 1.
Figure 18 - 1. User Roles
This plan addresses the following potential situations:
- If accountants subsequently need a role for a new database application, that application's role can be granted to the ACCOUNTANT role, and all users in the accounting department will automatically receive the privileges associated with the new database application. The application's role does not need to be granted to individual users requiring use of the application.
- Similarly, if the accounting department no longer requires the need for a specific application, the application's role can be dropped from the ACCOUNTANT role.
- If the privileges required by the ACCTS_RECEIVABLE or ACCTS_PAYABLE applications change, the new privileges can be granted to, or revoked from, the application's role. The security domain of the ACCOUNTANT role, and all users granted the ACCOUNTANT role automatically reflect the privilege modification.
When possible, utilize roles in all possible situations to make end-user privilege management efficient and simple.
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 give a user the powerful privileges to modify a database in many ways. Therefore, privileges for these usernames are extremely sensitive, and should only be available to select database administrators.
See Also: The passwords for these accounts can be modified using the procedures described in "Altering Users" ![[*]](jump.gif)
.
Protection for Administrator Connections
Only database administrators should have the capability to connect to a database with administrator privileges. Connecting as SYSDBA or SYSOPER gives a user unrestricted privileges to do anything to a database (such as startup, shutdown, and recover) or the objects within a database (such as create, drop, and delete from).
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:
- an administrator responsible for object creation and maintenance
- an administrator responsible for database tuning and performance
- a security administrator responsible for creating new users, granting roles and privileges to database users
- a database administrator responsible for routine database operation (for example, startup, shutdown, backup)
- an administrator responsible for emergency situations, such as database recovery
- new, inexperienced database administrators needing limited capabilities to experiment with database management
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:
- If a database administrator's job description changes to include more responsibilities, that database administrator can be granted other administrative roles corresponding to the new responsibilities.
- If a database administrator's job description changes to include fewer responsibilities, that database administrator can have the appropriate administrative roles revoked.
- The data dictionary always stores information about each role and each user, so information is available to disclose the task of each administrator.
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:
- the control desired over a database's space usage
- the control desired over the access paths to schema objects
- the database used to develop applications--if a test database is being used for application development, a more liberal development policy would be in order
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:
- CREATE system privileges are usually granted to application developers so that they can create their own objects. However, CREATE ANY system privileges, which allow a user to create an object in any user's domain, are not usually granted to developers. This restricts the creation of new objects only to the developer's user account.
- Object privileges are rarely granted to roles used by application developers. This is often impractical because granting object privileges via roles often restricts their usability in the creation of other objects (primarily views and stored procedures). It is more practical to allow application developers to create their own objects for development purposes.
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:
- the tablespaces in which the developer can create tables or indexes
- the quota for each tablespace accessible to the developer
See Also: Both limitations can be set by altering a developer's security domain. For more information, see "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:
- creating roles for an application and managing the privileges of each application role
- creating and managing the objects 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 that 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.