Oracle External Authentication

cutted from help :

EXTERNALLY Clause
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.


--------------------------------------------------------------------------------
Caution:
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
   IDENTIFIED EXTERNALLY
   DEFAULT TABLESPACE demo
   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
   IDENTIFIED EXTERNALLY
   DEFAULT TABLESPACE demo
   QUOTA 5M ON demo
   PROFILE app_user;    

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):
SQLNET.AUTHENTICATION_SERVICES=(NTS)

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
REMOTE_OS_AUTHENT=TRUE
in the init.ora and bounce the database.

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

Thanks everyone. Let me try these options.

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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

anand_2000v
EE Cleanup Volunteer