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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

765 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