Oracle SYS User Security Protection 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.
 

---

SYS User Protection

The equivalent of a super user inside the Oracle database is SYS, who can perform any activity such as startup, shutdown, creating users, etc. Naturally, this user account must be protected very carefully, otherwise, a malicious person with the knowledge of this account can shutdown the entire database. However, under certain configurations, the password of the user need not be entered to login as SYS. If the UNIX user's group is DBA, the user can login as SYS using the following command

sqlplus "/ as sysdba"

Voila! The user is connected as sys without even knowing the password! Note how dangerous it is to leave the passwords of people who login to UNIX under group DBA unsecured. Therefore, be very careful in assigning UNIX users to the DBA group. Typically, you would create two groups:

* dba

* oinstall

There should be separate users for the Oracle software installation and the Oracle account owner. In addition, you should also create separate users to manage the database, such as startup, shutdown, etc. These should not be controlled from the software owner or other Oracle users. Besides these users, the DBA group privileges should never be granted to anyone.

* Tip: The Oracle user should have the installed software only, nothing else; the other tasks, like starting up, shutting down, etc. should be performed by the individual dba user.

Showing the Process

When a user enters into sqlplus, the most likely way is issuing

sqlplus scott/tiger

Assuming, of course, that the user is SCOTT and the password is TIGER. However, if on another terminal, a user enters the following command

ps –aef|grep sqlplus

He will see this

sqlplus scott/tiger

The password of SCOTT is revealed. Clearly, this is a security hole. The users must be instructed never to use the password directly on the command line.

Either the following command should be used

sqlplus SCOTT

Oracle should prompt for a password which would then be entered,

Or

sqlplus /nolog
SQL> connect SCOTT/TIGER

Some other ways to connect from a program include

sqlplus /nolog << EOF
connect SCOTT/TIGER
… your sql statements go here
EOF

Similar warnings exist for other Oracle utilities such as export, import, SQL loader, and RMAN. Some programs written in Pro*C or Java may need the parameter to be passed in the command line. These also expose the password, and should be strictly prohibited. The programs or utilities will prompt for passwords if not supplied, and the password may be fed from a file via redirection (using the "<" character).

Sometimes we have found it useful to store the password in some secured file owned by the software owner, and readable only by the owner. Although it is not absolutely secure by any means, it's nevertheless a deterrent to theft.

Sometimes DBAs, the very people in charge of security, also make mistakes that expose the password. A common example is using the TKPROF tool. The DBA might give a command like this

tkprof tracefile.trc tkout.out explain=sys/changed

Note the use of the sys password for the explain clause. Although the explain clause needs a password, it can be given later during the prompt. In any case, unless the sys owned objects are explained, the user sys should never be used to explain the plan. Rather the application user should be used to explain the plan.

Some Tips for Password Management

We need to have a solution to securely pass the password to the program to make it automated. Here are a few ways.

One option is to create an environmental variable, say, USERPASS passed to script as

sqlplus –s judy/$USERPASS @report

This is still vulnerable to attack via the /usr/ucb/ps command. The problem with environmental variables is they are visible to all users on the system, without the user having to hack into the environment. Executing

/usr/ucb/ps uxgaeww

shows all the environmental variables and the values used by all the users currently logged in to the system, including root's. The /usr/ucb/ps call is present for compatibility with BSD.

This variable may be set in a file that could be hidden. It's simple but not very secure. Another option is to create a file of passwords named .passlist . Note the period at the beginning. This makes the file invisible in a routine examination. This file has a permission set as 600, i.e. no privileges to anyone other than the owner. Here are the contents of the file

judy 5ucc355
nathan fr33w!113y

and so on. As you see, it has the usernames and passwords of all users. Next we will create a shell script to use this file named .retrieve_password.sh. Note the period at the beginning of the file. It makes it invisible in a regular ls –l command, too. Here is how the script looks.

fgrep $1 $HOME/.passlist | cut –d " " –f2

When the user issues sqlplus, he or she would issue

.retrieve_password.sh | sqlplus –s judy @report

The program will retrieve the password and feed it to the sqlplus executable. An execution of ps –aef on the UNIX prompt will not show the password.

* Tip:  Make sure that no one types any kind of password in the command line. If passwords need to be passed from the command line, use a secured file to store the password and then use redirection to feed it to the program.

User Access Control

Once the machine is physically secured and the firewalls are protected, the next security vulnerability comes in the area of database access. The users need access to the database to perform their job functions.

When users wish to connect, the database makes sure that they are indeed authorized to access, a process known as Authentication. This can occur in several ways – the users could be defined as users in the database and then authenticated, or they may have been authenticated elsewhere and their credentials are passed on to the database as valid.

There are two ways the database authenticates users:

* By password

* By OS authentication

Let us discuss how they are different.

By Password

A user is created in the database with a password as in the following SQL command.

create user judy
identified by judypass;

This creates a user in the database, specifically in the data dictionary table USER$ owned by the user SYS. When the user wishes to connect to the database, he or she can do that with the user ID and the password as defined. The method of connection will vary from tool to tool. For instance in SQL*Plus, the user would connect as

connect judy/judypass

This is the most common use of authentication.

By Operating System

Oracle provides another method to connect users using the authentication provided by the operating system, known as ops$ accounts. In this case, the following parameters in the initialization file control the authentication process.

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
os_authent_prefix                    string      OPS$
remote_os_authent                    boolean     TRUE
os_roles                             boolean     TRUE
remote_os_roles                      boolean     TRUE

Note the use of ops$ as the value of the operating system prefix. The user is created as

Create user ops$judy
Identified externally;

In this case, the user JUDY exists in the operating system and is authenticated there using a password, token card, or some other means. Oracle does not authenticate Judy, as indicated by the use of externally identified keywords. When Judy decides to connect, she issues

connect /

Note there is no password. Oracle assumes that the OS has properly authenticated Judy, so there is no need to authenticate further to access the database.

This is reminiscent of the old days, where the world was much more secure and servers were still islands unconnected by the internet. Hackers typically needed to get to the physical network to do the damage. The advent of the internet changed the rule. A hacker halfway around the world can launch an attack while sipping coffee in his basement. The intent of user access control is to place as many barriers to unauthorized access as possible.

It is probably unnecessary to show how dangerous the above practice is. If a malicious user ever gets into the operating system, getting into the database is easy. Of course, the security aspects of the underlying OS make the call – if it is substantially secure, then this type of authentication can be trusted. Otherwise, getting into the database is less of a challenge for a hacker.

Therefore, the OS Authorized accounts should be used with extreme caution. The usual rule of thumb is granting just enough privileges to the account to do the job, no more than that. Moreover, powerful accounts with sweeping privileges, such as DBA, alter system, etc. should never be able to be identified externally.

There are two types of authentication used externally – one is done on the same server, and the other is done outside the server. There are different levels of security requirements for each, so they will be discussed separately.

 

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.