Oracle External Authentication

Posted on 2003-03-10
Medium Priority
Last Modified: 2008-02-01
Can any one explain how to setup a OS authenticated logon to the Oracle database. I would like to know the configurations that need to be done on the server and the workstaion.
Question by:gunavelu

Accepted Solution

jayashree_prasad earned 100 total points
ID: 8109316
You need to set the OS_AUTHENT_PREFIX variable in the init<SID>.ora file. By default this is set to OPS$ however, you can set it to any string.
For more details go to http://www.csee.umbc.edu/help/oracle8/network.815/a67766/01_intro.htm

Expert Comment

ID: 8109437
cutted from help :

Specify EXTERNALLY to create an external user. Such a user must be authenticated by an external service (such as an operating system or a third-party service). In this case, Oracle to relies on the login authentication of the operating system to ensure that a specific operating system user has access to a specific database user.

strongly recommends that you do not use IDENTIFIED EXTERNALLY with operating systems that have inherently weak login security. For more information, see Oracle9i Database Administrator's Guide.

Creating External Database Users: Examples
The following example creates an external user, who must be identified by an external source before accessing the database:

CREATE USER app_user1
   QUOTA 5M ON demo
   PROFILE app_user;

The user app_user1 has the following additional characteristics:

Default tablespace demo
Default temporary tablespace demo
5M of space on the tablespace demo and unlimited quota on the temporary tablespace of the database
Limits on database resources defined by the app_user profile
To create another user accessible only by the operating system account app_user2, prefix app_user2 by the value of the initialization parameter OS_AUTHENT_PREFIX. For example, if this value is "ops$", you can create the user ops$app_user2 with the following statement:

CREATE USER ops$external_user
   QUOTA 5M ON demo
   PROFILE app_user;    


Expert Comment

ID: 8110048
to set this up you need to do a number of things.

Firstly, as mentioned, you need to set up the OS_AUTHENT_PREFIX init.ora parameter. This is what the os usernames are prefixed by when the oracle user is checked. You can leave this set to the default OPS$ or change it to anything else you want. In my example I'll assume you leave it set to OPS$.

Secondly, you need to work out what oracle sees as the os username for the user you want to set this up for. The easiest way to do that is to log on to the client as that user and log on to oracle from there with a 'normal' oracle username with dba priviledges. Then query the osuser column in the v$session view to see what oracle sees as the os username for this user. If this is a Windows machine you're likely to see the domain name in there as well, so it's probably something like MYDOMAIN\MYUSER

Thirdly we create the user in the database, as previously mentioned:
CREATE USER "OPS$MYDOMAIN\MYUSER" IDENTIFIED EXTERNALLY (followed by settings for tablespaces, quota etc. if you want).
It is important that you put the full os username in and that you type it in uppercase and in quotes.

If you're on windows the 4th step is that we set the oracle environment up to accept external authentication (windows clients and servers need this). Set the following parameter in the sqlnet.ora (in %oracle_home%\network\admin - create the file if it's not there):

If you're doing all of this on the server itself you're finished. If you're trying to connect from a client there is a final step. You need to set
in the init.ora and bounce the database.

And that should be it - let us know if you have any problems

Author Comment

ID: 8113751
Thanks everyone. Let me try these options.
LVL 13

Expert Comment

ID: 9999343
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: jayashree_prasad {http:#8109316}

Please leave any comments here within the next seven days.

EE Cleanup Volunteer

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month9 days, 21 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question