Oracle7 Administrator's Reference for UNIX
Managing Security
Oracle7 uses several features of your UNIX operating system to provide a secure environment for users. The features include file ownership, group accounts, and the ability of a program to change its user ID upon execution.
The two-task architecture of Oracle7 improves security by dividing work (and address space) between the user program and the oracle program. All database access is achieved through the shadow process and special authorizations on the oracle program.
This section discusses the following security-related issues:
Groups and Security
To ensure greater security on your Oracle7 database, create user groups at the operating system level. Groups are controlled by the UNIX file /etc/group. Oracle programs are divided into two sets for security purposes: those executable by all (other, in UNIX terms), and those executable by DBAs only. A recommended approach to security is:
- Before installing the Oracle Server, create a database administrators' group (dba) and assign the root and oracle software owner IDs to this group. Programs executable by dba only have permission 710. Server Manager system-privileged commands are assigned automatically to the dba group upon installation.
- Add an oracle group of authorized users to allow a subset of UNIX users limited access to Oracle7. Give Oracle utilities the oracle group ID. Publicly executable programs, such as SQL*Plus, should be executable by this group. Set the permissions on the utilities to 710 to grant execute permissions to this group, but not other.
- Grant permission 711 to programs executable by other. Restrict this permission to programs that do not affect database security.
Although you can assign any name to the database administrators' group, dba is the default group name, and the convention used in this document. If you change this group name, the Oracle Installer relinks the kernel automatically during Installation. If you have multiple databases with the same $ORACLE_HOME (a configuration which Oracle Corporation strongly discourages), they should have the same database administrators' group. These restrictions do not apply to the group name for ordinary users (known as the oracle group).
Note: Even though both the oracle software owner and root user should belong to the dba group, the oracle software owner should not be a member of the root group. The root user should be the only member of the root group.
Security for Oracle Server Utilities
Protect the Oracle7 executables from unauthorized use. The method you use depends on your environment and whether you use single-task utilities. The following are suggestions for protecting Oracle7 executables:
- Keep all programs in the $ORACLE_HOME/bin directory and give ownership to the oracle software owner.
- Give all user utilities (sqlplus, exp, imp) a protection of 711 so all users on the machine can access the Oracle Server.
- Give all DBA utilities (such as Server Manager) a protection of 700 to restrict the use of these utilities to the DBA username, usually the oracle software owner.
Oracle Server Roles and UNIX Groups
Another method of managing site security when accessing Oracle7 locally is to map Oracle roles to UNIX groups at the operating system level. This tightens security for local access.
The format for specifying Oracle Server roles on UNIX is:
ora_sid_role[_d|_a]
where:
sid | is your system identifier |
role | is the name of the Oracle Server role |
d | indicates that this role is the default (optional) |
a | indicates that this role is granted with the WITH ADMIN option (optional). You can grant this role to other roles only, not to other users. |
All entries for Oracle roles are made in the /etc/group file. The following is a sample /etc/group file:
ora_test_osoper_d:NONE:1:jim,mary,scott
ora_test_osdba_a:NONE:3:pat
ora_test_role1:NONE:4:bob,jane,tom,mary,jim
bin:NONE:5:root,bin,sys
dba:NONE:6:root,oracle,dba
root:NONE:7:root
To enable these roles for a given database:
- set the initsid.ora parameter OS_ROLES to true
The Oracle role OSDBA can therefore be assigned to users not included in the UNIX dba group.
See Also: Your UNIX system administration documentation for more information on UNIX groups.
Security for Server Manager Commands
If you do not have SQL*Plus, you can use Server Manager to make SQL queries. However, be careful how you assign access to Server Manager. The following system-privileged statements should not be accessible to anyone but the oracle software owner and the dba group users, since they grant special operating system privileges:
Warning: System-privileged statements can damage your database if used incorrectly. Note that non-dba group users can connect as INTERNAL if they have the necessary password.
Security for Database Files
The same user ID used to install Oracle7 should own the database files. The default user ID is the oracle software owner. Set the authorizations on these files to permission 0600: read/write (rw) by owner only, with no write authorizations for group or other users.
The oracle software owner should own the directories containing the database files. For added security, revoke read permission from group and other users.
To access the protected database files, the oracle program must have its set user ID (setuid) bit on. To set this bit, enter:
$ chmod 6751 $ORACLE_HOME/bin/oracle
The authorization for the oracle program is similar to the following:
-rwsr-s--x 1 oracle dba 443578 Mar 10 23:03\ oracle
Note: The Oracle Installer sets the user ID for you.
The s in the user execute field means when you execute the oracle program, it has an effective user ID of oracle, regardless of the actual user ID of the person invoking it.
Network Security
There are three additional network security issues to consider :
- Using Passwords on the Network
- DBA Privileges Over the Network
Using Passwords on the Network
Remote users on the network can enter their passwords in clear text, or encrypted. When you use clear text, passwords can be picked up by unauthorized users, resulting in a breach of security. SQL*Net version 2 supports encrypted passwords.
DBA Privileges Over the Network
To control DBA privileges over the network choose one of the following options:
- set remote DBA access to denied in the /etc/listener.ora file
- set a special password in orapwd for DBA privileges
Automatic (ops$) Logins
Oracle7 supports automatic logins (operating system authorized logins) over the network.
UNIX treats a dollar sign ($) as the beginning of an environment variable. Therefore, when you specify an operating system authorized (ops$) login on the command line or in a script, first escape the $ with a backslash (\). For example, user ID scott should specify ops\$scott when logging in remotely.
Automatic logins are not allowed for the root user ID.
Attention: Automatic logins by PC, Apple MacIntosh, and OS/2 users are not secure. Anyone can edit the Oracle configuration file and change their user ID. For security reasons, if users of these systems are logging in over the network, Oracle Corporation strongly recommends you disable the ops$ logins in the listener.ora file.
Enabling Automatic Logins for SQL*Net Version 2
Automatic and remote DBA logins are not controlled by SQL*Net. They are controlled by the Oracle7 Server and configured using parameters in the initsid.ora file. Although automatic logins are supported, they are disabled by default. To enable them, set the REMOTE_OS_AUTHENT initialization parameter to true, then start up the database.
Because oracle controls these logins, you no longer need to run the SQL*Net version 2 tnslsnr as setuid to root. However, if a SQL*Net version 1 database is contacted by a SQL*Net version 2 client, the tnslsnr runs with root privileges. The listener on the intermediate node runs with root privileges.
See Also: Configuring SQL*Net Version 2 .
To perform an automatic login with SQL*Net version 2, create a user called daemon in your /etc/passwd file. The daemon user must not have an ops$ account in any of the local databases, nor be in any of the DBA groups. That is, there should be no ops$daemon account that would allow an outside user to intrude into your local database.
DBA Group ID Keywords
To enable and control remote logins, configure the following keywords in the /etc/listener.ora file:
DBA_GROUP | Use this keyword if the name is constant for all instances serviced by the listener. |
DBA_GROUP_sid | Use this keyword for each ORACLE_SID if the listener services more than one $ORACLE_HOME, and the group IDs are different. |
OPS_DOLLAR_LOGIN_ALLOWED
OPS_DOLLAR_LOGIN_DENIED
| Use these keywords to control remote login. OPS_DOLLAR_LOGIN_DENIED is the default. |
REMOTE_DBA_OPS_ALLOWED
REMOTE_DBA_OPS_DENIED
| Use these keywords to control remote DBA access. REMOTE_DBA_OPS_DENIED is the default. |
If the DBA group ID for the database accessed is not the default name (dba), you can specify a non-default name.
Set remote login and remote DBA access parameters to the individual $ORACLE_SIDs of databases on the network, or specify all sids at once. For example, either of the following statements are valid:
PARAMETER=ALL_SIDS
PARAMETER=sid1[, sidn...]
To see which privileges are assigned to the sids, enter:
$ lsnrctl status
Checking Order
The system checks remote login parameters in the following order:
- parameters that deny access
- parameters that permit access
- the default value (denied)
These privileges are implemented by manipulating the user ID and group ID of the shadow process forked by the SQL*Net version 2 listener. For example:
- If OPS_DOLLAR_LOGIN_DENIED is true for a particular instance, or if the user ID as reported by the client-side operating system has no account on the database host machine, the user ID and group ID are found in the /etc/passwd file under the entry for daemon.
- If both OPS_DOLLAR_LOGIN_ALLOWED and REMOTE_DBA_OPS_ALLOWED are true for a particular ORACLE_SID, and if the user ID as reported by the client operating system does have an account on this system, the user ID and group ID are found in /etc/passwd for this user ID.
- If OPS_DOLLAR_LOGIN_ALLOWED is true for a particular ORACLE_SID, but REMOTE_DBA_OPS_ALLOWED is false, then, if the user ID has DBA privileges, the process has the user ID and group ID of daemon. Otherwise, the process has the user ID and group ID of this user.
Note: REMOTE_DBA_OPS_ALLOWED is false by default. Oracle Corporation recommends that you do not change this value. When this parameter is set to false, users with DBA privileges cannot make operating system authorized logins over the network. They can, however, proceed with ordinary (password-protected) network logins.
Security and Remote PC Users
You can access or administer a database from a remote machine, such as a personal computer, without operating system accounts. User validation is accomplished by using an Oracle7 password file, created and managed by the orapwd utility. You can also use password file validation on systems that support operating system accounts.
Local password files reside in the $ORACLE_HOME/dbs directory and contain the username and password information for a single database. If there are multiple $ORACLE_HOME directories on a machine, each has a separate password file.
Running orapwd
The orapwd utility exists in $ORACLE_HOME/bin and is run by the oracle software owner.
Invoke orapwd by entering:
$ orapwd file=fname password=password entries=max_users
fname | is the name of the file where password information is written. The name of the file must be orapwsid, and you must supply the full pathname. Its contents are encrypted and not user-readable. This parameter is mandatory. |
password | is the initial password you selected for INTERNAL and SYS. You can change this password after you create the database using an ALTER USER statement. This parameter is mandatory. |
max_users | is the maximum number of users allowed to connect to the database as SYSDBA or SYSOPER. This parameter is mandatory only if you want this password file to be EXCLUSIVE. |
Attention: You must create a new password file if you ever need to increase the maximum number of users. Therefore, set max_users to a higher number than you expect to require.
See Also: Chapter 1, "The Oracle7 Database Administrator", in the Oracle7 Server Administrator's Guide for more information on the orapwd utility.
Access to a Database from a Remote PC
When the Oracle7 password file exists, networked PC users can access this database as INTERNAL. Non-privileged users can connect to the database by invoking an Oracle application that uses the database. Privileged users who want to perform DBA functions on the database can enter the appropriate Server Manager command from their PC, adding the dba user password. For example:
SVRMGR> connect internal/dba_password
To connect as OPERATOR, use the same command with the OPERATOR password.
Remote Authentication
The following initsid.ora parameters control the behavior of remote connections through non-secure protocols:
REMOTE_OS_AUTHENT | enables or disables ops$ connections |
OS_AUTHENT_PREFIX | used by ops$ accounts |
REMOTE_OS_ROLES | enables or disables roles through remote connections |
Attention: If REMOTE_OS_AUTHENT is set to true, users who are members of the dba group on the remote machine are able to connect as INTERNAL without a password.