Oracle Local OS Security Authentication Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.
 

---

Local OS Authentication

Many people aren't aware of the feature in the user creation process that can lead to security challenges. This is better explained through an example. Suppose the os_authent_prefix is ops$ and a user is created as

create user OPS$SCOTT identified by TIGER;

This will create a user named OPS$SCOTT as a regular user. However, the use of OPS$ in the name makes it externally identifiable too. The OS user SCOTT can login as

sqlplus ops$scott/tiger

Or

sqlplus /

Note that the account can be accessed either way. Sometimes it may be desirable, but mostly it will add to confusion and create security holes. You should preferably have users who are authenticated by either the database or the OS, not both. However, there is no way to prevent the user OPS$SCOTT from logging in without supplying a password.

To alleviate the confusion, never use the IDENTIFIED BY password clause when the intention is to use an externally identified account. When the identification has to be done in the database, don't use the ops$ in the name.

* If the os_authent_prefix is "" (Null), then the OS Authenticated accounts cannot log in using the password. For instance, if the parameter is null, and a user SCOTT is defined as identified externally, then SCOTT has to login with "sqlplus /". If the user is created as "identified by tiger", then it's not an OS authenticated user, it's a regular user who must supply a password to login. This explains why Oracle recommends setting the parameter to null.

If you must use OS Authorized accounts, make sure only the OS authorizes them, i.e. they can't login using a password. This setup will at least allow you to pursue security infrastructures where the OS authentication can be rigorously implemented. This can be accomplished by setting os_authent_prefix to "" (null).

Remote OS Authentication

The above discussion was for users defined in the same server where the database resides. What about users in a separate server or a client machine that connects to the database server? This is controlled by the parameter remote_os_authent. If set to TRUE, remote users can connect to the database without supplying a password.

The database DB1 is on a server named Server1 and the client machine is PC1. The user SCOTT is a PC user, not the user in the server Server1. However, when the user connects to the database

sqlplus /@db1

the connection is accepted if the remote_os_authent parameter is TRUE, otherwise it is rejected.

Note the potential security hole the parameter may open up. A user authenticated in some operating system, which could be as inherently insecure as a PC, can login to a database. Therefore, the practice of allowing such users should be very carefully examined, and perhaps be completely disallowed.

One circumstance where it may be allowed is a setup in which the clients and servers are isolated from the rest of the network, and the clients are somehow secured well enough to be trusted. However, for most purposes this parameter should be set to FALSE.

To disable this feature, place the following lines in the initialization file, or change the lines to the following if they already exist and bounce the database.

os_authent_prefix = ""
remote_os_authent = FALSE

Windows Domain Authorization

A somewhat secure way to authenticate users is to use Windows domain names as a part of the username. This is, however, applicable only to Windows NT/2000 server-based databases.

Typically, the Windows servers are authenticated by the domain in a setup where all the computers, including the database server, are members of a domain. To join the domain, the user must supply the domain user's password, not the local user's.

For instance, we have a server Server1 and a client machine PC1, both running Windows 2000, that are part of a domain CLAIMDOMAIN. The machine PC1, as well as the domain, could authenticate the user SCOTT. When the user logs in to the domain, the username is shown to the database as OPS$CLAIMDOMAIN\SCOTT, not just OPS$SCOTT. Note how the domain name is part of the user's name in the database. The user can be created as such.

create user "OPS$CLAIMDOMAIN\SCOTT"
identified externally;

Note the username is in uppercase. When the user SCOTT does not log on to the domain but rather logs on to the local PC, his domain will be PC1 (the name of the computer), so the username presented to the database will be OPS$PC1\SCOTT, not OPS$CLAIMDOMAIN\SCOTT. The database will reject the connection request.

This parameter is controlled by a value in the registry – osauth_prefix_domain under the key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE. If this is set to TRUE, which is the default for Oracle 8i and above, the domain information must be part of the username. Of course, if due to some reason this should not be enforced, the value can be made FALSE.

How does this help? If a rogue user creates a user called SCOTT in his PC and tries to connect to the server, the server will not accept the connection request. The rogue user must log on to the network domain, but that will be another hurdle to overcome. If remote authentications must be allowed, the security can be strengthened using this technique. However, if possible, try to disallow remote authentication altogether.

Group Management

The other set of parameters pertain to group management. In the past, to facilitate the management of roles in the database, the DBA could use some help from the OS in setting the necessary privileges. Based on the group the user belonged to, his or her role inside the database was set properly. This was added as a convenience, but it increased the chances of hacking into the database, and these parameters should therefore also be set to false.

os_roles = FALSE
remote_os_roles = FALSE

* Always set os_authent_prefix  to a null value ""

* Always give the least amount of privileges to an account externally identified.

* Never name a user with ops$ as the starting letters where the authentication method is database.

* Do not let remote users connect to the database without the password. Always set remote_os_authent to FALSE

Oracle Created Accounts

After Oracle is installed and a database is created, several users are created that may not necessarily be required by the application. In Oracle 9i, at least they are prompted to have a non-default password and most of them are locked. But in Oracle 8i and prior versions they are not. These accounts, such as the user DBSNMP, may have sweeping privileges and provide a back-door security hole. These must be identified and shutdown.

First, find out the users and their status in the database.

select username, account_status
from dba_users
/

SYS                  OPEN
SYSTEM               OPEN
DBSNMP               OPEN
XDB                  OPEN
OUTLN                OPEN
SCOTT                OPEN
OEM_ANANDA           OPEN
OPS$ARUP             OPEN
QS_OS                EXPIRED & LOCKED
QS_CBADM             EXPIRED & LOCKED
QS_ADM               EXPIRED & LOCKED
ODM_MTR              EXPIRED & LOCKED
ANONYMOUS            EXPIRED & LOCKED
WMSYS                EXPIRED & LOCKED
RMAN                 OPEN
PERFSTAT             OPEN
OPS$ANANDA           OPEN
CTXSYS               EXPIRED & LOCKED
MDSYS                EXPIRED & LOCKED
ORDPLUGINS           EXPIRED & LOCKED
ORDSYS               EXPIRED & LOCKED
WKSYS                EXPIRED & LOCKED
ODM                  EXPIRED & LOCKED
WKPROXY              EXPIRED & LOCKED
QS_CB                EXPIRED & LOCKED
QS_CS                EXPIRED & LOCKED
HR                   EXPIRED & LOCKED
OE                   EXPIRED & LOCKED
PM                   EXPIRED & LOCKED
SH                   EXPIRED & LOCKED
QS                   EXPIRED & LOCKED
QS_WS                EXPIRED & LOCKED
QS_ES                EXPIRED & LOCKED
ARUP1                OPEN
PCOLLINS             OPEN
HOLYCROSS            OPEN
CLAIM_SCHEMA         OPEN
NATHAN               OPEN
JUDY                 OPEN
ANANDA               OPEN

Note the accounts that are in OPEN status. Are they necessary? If you can't drop them, just expire their passwords and lock their accounts

alter user scott expire password account lock
/

The accounts that should draw some attention are discussed below.

• OUTLN – This is used for stored outlines and is necessary, you can't drop it. However, if you don't use the stored outline functionality, the best option is to lock it and expire its password.

• PERFSTAT – Generally used for STATSPACK as the report collection user. The default password is typically the same, PERFSTAT. It should be changed to something else.

• SCOTT – This is the demo user with the password TIGER. Although the privileges granted to this user are very little, there is no reason this account should be present in a production database. This user should be dropped.

• DBSNMP – This account is used by Oracle Intelligent Agent and the password defaults to dbsnmp, the same as the username, a huge security hole. This is important in securing any database and needs a separate discussion as explained below.

• TRACESVR – This user is required for Oracle Trace, which is generally not used in many organizations. In Oracle 9i, it is supported, but Oracle10g may not have it. The default password of this user is TRACE, and should be changed.

 

This is an excerpt from the book "Oracle Privacy Security Auditing". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.