Delphi32.com -- The Premier Source for Delphi Developers!

Using the Windows Login as Login to Oracle

December 7, 1997

It is sometimes desireable to be able to connect to Oracle, using the network user. This way the user has to login only once and remember only one password.

Not all versions of Oracle support this, or more correctly: some to and some don't, depending on the platform. For instance, ver. 7.1 does not support this feature on NT. Check your Oracle documentation for this.

Most of the stuff you have to consider lies with Oracle.

Firstly, check the os_authent_prefix parameter. This is usually set to 'OPS$'. You have so have some Oracle DB tool to see the active parameters, for example the SQLDBA program. After login (user with DBA privilege) you type show parameters os_authent_prefix which will display the setting. The initorcl.ora will also contain this parameter if it is set to anything besides 'OPS$'.

You can also see the parameter setting from SQLPlus with this query (again, you have to be connected as an user with DBA rights:


   select substr(name,1,30) name,
     substr(value,1,10) value 
     from v$parameter
     where upper(name) like 'OS_%';

All users that you want to be able to connect directly have then to be defined in a special way: User names must be prefixed with the os_authent_prefix and use the identified externally clause. That's it, basically. If you have an user named 'SCOTT' (everybody has!), you'd create him with:


   create user OPS$scott identified externally

IF the os_authent_prefix is set to this value.

Hint:
Some people prefer to set the os_authent_prefix parameter to an empty string:


   os_authent_prefix=""

This has the advantage of keeping user names 'clean' which makes table columns with user information (created_by, etc.) easier to maintain. And it's easier to change modes for each user with the alter user command.

If you need to see which users are identified externally, run


   select username from dba_users
     where password='EXTERNAL';

while connected to a DBA account.

To connect to this user he obviously has to logged on to the network first. Then you just set the USER NAME and PASSWORD parameters to empty strings and connect.

From prompts that take the connect info on one line, such as the connect command in SQLPlus you'd type:


   connect /@myserver

Hits/month	2,500,000+
Downloads (Since May 2000)	7,393,709
Total Files	6,023
Forum msgs	7,670
Articles/FAQs	70+/900+

Kylix

Books

Top Selling Software at Amazon

Site Search: