RSS

User Authentication(Part 1/4): Create users in oracle and authenticate by OS

03 Sep

Password! Password! Password!

Today with so many accounts and passwords for each of those accounts. Password management has become the primary focus for any database management team. DBA are burdened with additional responsibility which can be minimized and done with reduced time and cost.

Oracle provides some really good features for authentication, which are as follows:

1. Basic Authentication:
a. Database user authenticated by password
b. Database user authenticated by OS.
2. Strong Authentication
3. Enterprise User Security
4. Proxy Authentication

This post is Part 1 of 4 in the series on User Authentication in Oracle.

DBA are most needed when the user accounts get locked due to incorrect login attempts. When the user is a privileged user then it becomes utmost important to safeguard them against this unplanned lockout and mitigate the possibility of unproductive business hours.
In large organizations you can imagine the number of calls received by helpdesk and support team for resetting the password and request for unlocking accounts.

We are all familiar with basic user authentication in oracle which is mostly by password.
Following screen shows the user “whizdba” created and authenticated by password.

Basic Authentication - By Password

Database user authenticated by password

This type of authentication is very common and used widely. The advantage with this type of authentication is that database user can be audited, each user has a schema associated to them. On the other side the disadvantage is that the user can connect to only database where the account is created. The user is bind to one database server and any time there is need for the user to connect to different server, location the account has to be migrated or new account has to be created.

Another type of basic authentication provided by oracle is authentication by OS. The steps for setting up the user for this type is:

Step 1: Set the parameter os_authent_prefix. The default parameter value is ops$
Step 2: Create database user ops$whizdba with ‘identified externally’
Step 3: Create os user whizdba on the server where database resides.
Step 4: Set environment variables for the whizdba user in .bash_profile file
Step 5: Test and Connect to the database using OS authentication.

I have tried to demonstrate the above step with an example user “whizdba” as shown below

Step 1: Setting the parameter “os_authent_prefix” to ops$. In my case its already set. You can set it to different value using ALTER SYSTEM command.

Basic Authentication by OS

Step 1: Set parameter os_authent_prefix

Step 2: Create database user ops$whizdba with ‘identified externally’

Create database user "whizdba" identified externally

Step 2: Create database user "whizdba" identified externally

Step 3: Create os user whizdba on the server where database resides.

Step 3: Create os user "whizdba" on the server where database resides.

Step 3: Create os user "whizdba" on the server where database resides.

Step 4: Set environment variables for the whizdba user in .bash_profile file. Basic variables which need to be set are ORACLE_BASE, ORACLE_HOME, ORACLE_SID and PATH to ORACLE bin directory.

Step 4: Set environment variable in .bash_profile file of whizdba

Step 4: Set environment variable in .bash_profile file of whizdba

Step 5: Test and Connect to the database using OS authentication. Externally authenticated user connect to the database as : sqlplus / (as shown in the below screenshot)

Step 5: Test and Connect to user whizdba using OS Authentication

Step 5: Test and Connect to database using OS Authentication

Authenticating oracle database user externally by OS can be used in situation where scripting and batch job is performed by a user heavily. The advantage and disadvantage of this user remains same as that of basic password protected database user. The security concern here is that anyone can connect to the database from remote machine by creating an OS user with the same name as the externally authenticated database user. Hence getting unintended access to the database server. 

This calls for the need for stronger authentication techniques which I would discuss in my next post: User Authentication(Part 2/4): Strong Authentication

 
Leave a comment

Posted by on 3 September, 2011 in Oracle

 

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: