Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Mo.

Asked: September 08, 2002 - 11:38 pm UTC

Last updated: August 05, 2007 - 1:39 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom:

1. Is there anything in oracle to tie in the unix userid login to an oracle login? SOmeone was telling me that in informix they were setting privileges based on unix id?

2. Since you are in favor of implementng security in the database rather than the application, do not you think it is really hard to maintain? i.e creatin 200 oracle user accounts or would you just create one per group and use that as a login.

3. Still, with application userid/password I can set up different menu for each user and different functionality. This would be a nightmare to do using oracle userid/password if you want to grant selects,updates etc for packages and tables to each user.

Thank you,

and Tom said...

1) yes, it is called OS authentication.

Just

create user USERNAME identified externally;

then you can "sqlplus /"

It is what I use. I set my:

os_authent_prefix string ops$

to ops$ actually, so that when I

create user ops$tkyte identified by password;

I have the OPTION of either

a) using "sqlplus /"
b) "sqlplus ops$tkyte/password"

as I choose (eg: when logged in locally, I use /, coming in over the network -- I use the password)

2) I'll let my database instance I run interally speak for me:

ops$tkyte@ORA.WORLD> select count(*) from all_users;

COUNT(*)
----------
49068

3) we have this thing called "roles" that are groupings of privileges.


ops$tkyte@ORA.WORLD> select count(*) from dba_roles;

COUNT(*)
----------
143

I have 143 different roles that I grant to users as needed (most of the apps have admin screens that the admins for the APPS themselves just deal with this -- I actually don't create/grant anything myself)



Rating

  (21 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

unix login and security

A reader, September 09, 2002 - 2:44 pm UTC


unix login

mo, September 09, 2002 - 9:42 pm UTC

Tom:

1. I am truly confused on this answer. Is there any oracle documentation on this OS authentication and its benefits.

what does this do:
create user USERNAME identified externally;

and where do you define this:
os_authent_prefix string ops$

Is the idea here is to make your oracle userid/password the same as unix userid/password or windows userid/password.

2. OK I see that you set all of users at the database level through a menu rather than using SQL*plus? Correct

If you are doing this for a web application do you set up 143 DADs one for each role?

How do you determine what the user role is before he gets into the database? DO you create a basic connect generic userid/password to connect everyone and then find what his role is and then reconnect with that role?

Can you log the user in using his unix userid/login right away?

Thank you,



Tom Kyte
September 10, 2002 - 7:21 am UTC

1) amazingly enough -- yes, we actually document this stuff (that is tongue in cheek, pretty much everything is documented -- no one ever seems to read it though)

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/dba.htm#1064 http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/users.htm#682 http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76956/privs.htm#15555 <code>

are all pointers into the single Admin guide that covers such topics. Your OS specific guide may have further details (eg: the windows one does)

the idea is to make it so you don't have an oracle password at all. you log into the OS and the database trusts you are who the OS says you are.


2) a menu? No, I have a webpage. People go there and request an account, they get said account created and a password is generated and emailed to them. This account has CREATE SESSION and access to PUBLIC things.

Now, they can go to an application and if the application requires privileges beyond CREATE SESSION and PUBLIC, they use the application screens to request that access. That generates an EMAIL to the application owner who can either accept or reject such a request. If they accept, a role is granted and email sent. If they reject -- an email is sent with the reason why.

We only need one DAD -- one that doesn't have the username/password stored with it so the end user provides theirs.

Now, over the web, you will NOT be using OS authentication. The client of the database is the webserver, you want the client identified in the database to be the browser.


unix login

mo, September 18, 2002 - 10:45 pm UTC

TOm:

As a followup to this:
<quote>
On a user by user basis, you can use OS or External authentication.  For 
example, I've setup my OS_AUTHENT_PREFIX in my init.ora to be "ops$".  I have a 
Unix account "tkyte".  I've created a database user:

SQL> create user ops$tkyte identified EXTERNALLY;

Now, as long as I'm logged into Unix, I can simply:

$ sqlplus /

SQL*Plus: Release 8.1.5.0.0 - Production on Tue Jun 6 13:25:26 2000
(c) Copyright 1999 Oracle Corporation.  All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
With the Partitioning and Java options
PL/SQL Release 8.1.5.0.0 - Production
ops$tkyte@DEV8I.WORLD> 


The database picks up my username from the OS -- not from the command line.  It 
trusts that I am TKYTE (ops$tkyte in the database as I've set it up) since I'm 
logged into the Unix system.
</quote>

1.  What is the benefit of using unix account for an oracle login? IS this used in web or more for client server applications?

2. How can a web user use this since he does not login to unix? I guess he connects to a web server which logs in to the unix database? Do not you login in to database directly from the web? 

3. For each unix account you do:
SQL> create user ops$tkyte identified EXTERNALLY;

you always have to use "ops$xxx" which is the string you used in the init.ora.

4. Are you creating an oracle account without a password? or oracle is trusting that your unix password is enough?

5.  What is ops$ stands for.  Can it be replace with any string like "dev" or else?

THank you,
 

Tom Kyte
September 19, 2002 - 7:33 am UTC

1) I use it for convienence and security.

$ sqlplus scott/tiger

well, it is trivial for someone to ps -aef | grep sqlplus and see my credentials.  sqlplus / -- they get nothing

In my scripts, run in the background (backups and what not), we use sqlplus /, we don't HAVE any passwords encoded in there.

2) cannot, there is a man in the middle.  the db client is the webserver, not the browser.

3) if you set it to ops$, otherwise use what it is set to.

4) creating an oracle account that is identified by the OS, not by Oracle -- the OS tells us the identity

5) OPS = OPerating System.  you can use what you like, ops$ is the convention. 

Trapping a user name

Prashant Munshi, May 02, 2003 - 5:58 am UTC

Through a web page created through a procedure a user is logging in. He is supposed to provide some comments or message like a post say. I want to trap the username who posted such message as the chances are he can use some offending language. Can I trap the username?

Tom Kyte
May 02, 2003 - 8:17 am UTC

you wrote the application didn't you?

If you make them log in -- you know who they are -- you should be providing this identity to all other procedures in your application. I cannot tell you how to "get" this data since i don't know how YOU are passing this data.



How about NT Authentication

rae, June 19, 2003 - 6:29 am UTC

hi,

i'm currently implementing this OS authentication for our system. basically, those processes in Unix will login to oracle database via the "sqlplus /".
i'm just concerned, say, i have a unix user "SUPERMAN", then i create the oracle user "SUPERMAN" which is identified externally. in effect, as long as i'm logged in to unix using this "SUPERMAN", i just do sqlplus / then i'm already logged in to the database. but what about, if i have an NT user "SUPERMAN"... i can configure this so that i can also log in to oracle database right? but i only Unix to be able to login to oracle using this name. any advise? thanks.

Tom Kyte
June 19, 2003 - 8:04 am UTC

you won't have remote_os_authent set so the nt user will not be able to log in over the network.

remote_os_authent

rae, June 20, 2003 - 2:16 am UTC

hi tom,

thanks for taking time out to follow up on my query.

currently, the value of our "remote_os_authent" is TRUE. so that means i'll just set it to FALSE right? but please verify if my understanding is correct for this scenario... say the oracle server is in SYS_SERVER domain. then in NT_SERVER domain, there is an NT user "SUPERMAN". if NT_SERVER is a trusted domain of SYS_SERVER, then "SUPERMAN" can still login using OS authentication even if remote_os_authent is FALSE.

another thing is, can you point me to a link where it tells me how I can configure my NT/Win2k to be able to login to oracle through OS authentication? i just want to play around with the value of remote_os_authent and other parameters and check whether i can login to oracle or not.

appreciate your help on this.


Tom Kyte
June 20, 2003 - 4:54 pm UTC

check out the handy dandy windows NT Oracle admin guide:

Authenticating Database Users with Windows
</code> http://docs.oracle.com/docs/cd/A87860_01/doc/win.817/a73008/ch7.htm#1036322 <code>



impact of remote_os_authent

rae, July 22, 2003 - 3:28 am UTC

hi tom,

i'm using oracle v7x. i have changed the parameter remote_os_authent to FALSE. Now i'm connecting through unix authentication. I just want to ask why "@database_name" won't work in shell script (e.g. $userId@$database_name)? And also, why "AT DATABASE_NAME" won't work in Pro*C (e.g. EXEC SQL AT DB_NAME Select.....)?
Previously, when I'm connecting using user/password@database.. the @database_name and AT DATABASE_NAME works.

Thanks.

Tom Kyte
July 22, 2003 - 8:24 am UTC

it has nothing to do with remote_os_authent, that would affect ONLY

sqlplus /@database_name


you provide no error messages, no error text -- no inputs = no outputs. I can only guess you've changed other things as well

impact of remote_os_authent

rae, July 23, 2003 - 2:10 am UTC

Hi Tom,

Yes, it does not work for sqlplus /@database_name in shell script.
Regarding Pro*C... the program that I have below does not work... but if I remove all the statement "AT DB_NAME" in my code e.g.

EXEC SQL SELECT shUserName, shPassword INTO :VANuser_var, :VANpassword_var From SH_LOGIN Where shProcessName = :ProgramName;

then it works. We used to have this "AT DB_NAME" because we have multiple environments in one server. And it used to work except now that we changed the remote_os_authent to false.

-----------------------------
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <stdarg.h>
#include <strings.h>
#include <sqlca.h>
#include <sqlcpr.h>
#include <time.h>
#include "vanapi.h"

char * USERID ="/";
char *tempArr;
char v_Password[20];

EXEC SQL INCLUDE sqlda;
EXEC SQL DECLARE DB_NAME DATABASE;

int GetUserPwd (char *ORADataBase, char *ProgramName, char **tmpRec)
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR VANuser_var[LEN_VAN_USER];
VARCHAR VANpassword_var[LEN_VAN_PWD];
int b;
int r;
int s;
EXEC SQL END DECLARE SECTION;

short vantiveuser_ind;
short vantivepwd_ind;

tempArr = (char *)calloc(LEN_VAN_USER, sizeof(char));

EXEC SQL CONNECT :USERID AT DB_NAME USING :ORADataBase;
EXEC SQL WHENEVER SQLERROR DO return ( 1 );

EXEC SQL AT DB_NAME SELECT shUserName, shPassword INTO :VANuser_var, :VANpassword_var From SH_LOGIN Where shProcessName = :ProgramName;

VANuser_var.arr[VANuser_var.len] = '\0';
VANpassword_var.arr[VANpassword_var.len] = '\0';

strcpy(tempArr, (char *) VANuser_var.arr);
tmpRec[0] = strdup(tempArr);
strcpy(tempArr, (char *) VANpassword_var.arr);
tmpRec[1] = strdup(tempArr);

return (0);
} /*end GetUserPwd*/


Tom Kyte
July 23, 2003 - 7:53 am UTC

well, of course it won't work for sqlplus /@dbname -- that is the entire sole purpose of remote_os_authent

it should be false

/@dbname won't work -- should not work -- if it does work it is generally a huge security issue.

My car "does not work" -- why not? I'll not give you any diagnostic error messages, none of the information from the dashboard. I won't tell you about any funny noises or anything. now "guess" :)

get my point?

reader

reader, September 10, 2003 - 2:01 am UTC

Hi TOm

I want to spool a txt file.The platform is unix. I want
to use a path in the sql script but can not be hard coded.
like spool path/file1.txt. I try to use &1 to pass in or
export a environment, but both are not successful. Can you give me a idea?

Thank you!

Tom Kyte
September 10, 2003 - 2:32 pm UTC

spool /tmp/&1..txt


works??

reader

reader, September 10, 2003 - 3:28 pm UTC

Tom

Thanks.

I hope the following works
spool &1./filename.txt

&1 can be pasted in as a path like /tmp
But this does not work. Where am I wrong?



Tom Kyte
September 10, 2003 - 8:17 pm UTC

ops$tkyte@ORA920LAP> @test /tmp
ops$tkyte@ORA920LAP> spool &1./filename.txt
ops$tkyte@ORA920LAP> prompt foo
foo
ops$tkyte@ORA920LAP> spool off
ops$tkyte@ORA920LAP> !cat /tmp/filename.txt
ops$tkyte@ORA920LAP> prompt foo
foo
ops$tkyte@ORA920LAP> spool off


seems to work for me. 

How to trap SQL*PLUS / TOAD logins?

Arul kumar, November 24, 2003 - 10:32 am UTC

Tom,

Thanks for the info.

Could you please tell us how to trap "non-application" logins in production environment.

For example, one of my application has a one to one mapping of application userids and database userids (password same!). Now, in such a scenario, with little more knowledge any application user could get hold of tools like TOAD / SQL*PLUS and connect to the database.

I would like to identify such LOGINs and disconnect them. I tried 8i - Database - LOGON Trigger(using SYS_CONTEXT and V$Session info). It did work, but the problem is that "any executable" could be "renamed" as the application name. Isnt it ??


Many Thanks

Tom Kyte
November 24, 2003 - 10:40 am UTC

that is correct -- there is no way to do this.

if you are using 3 tier apps, you can limit logons to specific machines -- effectively locking it down.

if you are client server, you will basically have to permit them. You've basically disabled security on your system with your current user/pass approach.

Unix and SQLPLUS

Reader, November 26, 2003 - 2:14 pm UTC

Hi Tom,

I have a simple shell script to call the sql script like this:
sqlplus username/password @test.sql

I want to track the error if the above is not successful using the $? in the shell script.

Two questions:
1, If the username/password(or database is down) is not correct how to track the error( I use the Korn shell, I can not track the error in the shell script) ?

2, If the test.sql is not there can I have a way to track inseated of checking the file if exist in the shell script ?

Thanks!

A reader










Tom Kyte
November 26, 2003 - 3:20 pm UTC

sqlplus /nolog and put the connect in the script -- then you can exit on failure.

like this:


sqlplus /nolog <<EOF
whenever sqlerror exit failure
connect bob/mary
EOF
echo $?

sqlplus /nolog <<EOF
whenever sqlerror exit failure
connect /
EOF
echo $?



my output was 1 and 0....

you use script to see of files exist (i sort of like the EOF thing tho, keeps it all in one file)


Simple Yes or No

Fred, January 19, 2004 - 10:41 am UTC

So if the server has REMOTE_OS_AUTHENT=FALSE and both server and client has SQLNET.AUTHENTICATION_SERVERS=(NTS) then the client should NOT be able to connect, right?

Tom Kyte
January 19, 2004 - 11:03 am UTC

not a simple yes or no.

the client should be able to connect locally.
maybe not remotely, but locally sure (they are not "remote" after all).

URL doesn't work

Marcio, May 26, 2004 - 12:26 pm UTC

You had asked to point out any url seems doesn't work.
I think this doesn't
</code> http://download-west.oracle.com/docs/cd/A87860_01/doc/win.817/a73008/ch7.htm#1036322 <code>

re: spooling files (to 'reader')

Tak Tang, May 27, 2004 - 8:38 am UTC

I have found that you can use os environment variables in the filename of a
spool command :-

SET OUTPUT_DIR=D:\sales\reports

then in the script, I could have

col today new_value today
select to_char(sysdate,'YYYYMMDD') as today
from dual
/

spool %OUTPUT_DIR%\summary_&today..txt

Note that I have used two dots. The first tells SQL*Plus where the substitution
variable name ends, and the second is a dot that I want in the file name.

Works on both unix and windows.

I had a problem a while ago, with the filename being too long (after variable expansion). The limit seemed to be about 250 characters, and was with the windows version of SQL*Plus.


-- begin windows example --

D:\>set OUTPUT_DIR=D:\SALES\REPORTS

D:\>echo %OUTPUT_DIR%
D:\SALES\REPORTS

D:\>sqlplus /

SQL*Plus: Release 9.2.0.4.0 - Production on Thu May 27 12:57:28 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

12:57:29 TANGT@DEVC> col today new_value today
12:57:39 TANGT@DEVC> select to_char(sysdate,'YYYYMMDD') as today
12:57:45 2 from dual
12:57:47 3 /


TODAY
--------
20040527

12:57:48 TANGT@DEVC> spool %OUTPUT_DIR%\summary_&today..txt
12:58:00 TANGT@DEVC> select *
12:58:16 2 from dual
12:58:20 3 /

D
-
X

12:58:20 TANGT@DEVC> spool off
12:58:22 TANGT@DEVC> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

D:\>cd %OUTPUT_DIR%

D:\sales\reports>dir
Volume in drive D is STAGE
Volume Serial Number is 1EE8-0442

Directory of D:\sales\reports

27/05/2004 12:57 <DIR> .
27/05/2004 12:57 <DIR> ..
27/05/2004 12:58 118 summary_20040527.txt
1 File(s) 118 bytes
2 Dir(s) 5,304,483,840 bytes free

D:\sales\reports>type summary*

summary_20040527.txt


12:58:00 TANGT@DEVC> select *
12:58:16 2 from dual
12:58:20 3 /

D
-
X

12:58:20 TANGT@DEVC> spool off

D:\sales\reports>


Takmeister


re: unix login and security (to Mo)

Tak Tang, May 27, 2004 - 8:41 am UTC

Mo. On one of our HP Unix based applications (which has run Oracle 7 and 8), our oracle account names are exactly the same as our unix account names. The oracle accounts are set up as 'identified externally'.

On our other HP-UX system, the oracle accounts are set up with the 'ops$' prefix, but we are also able to set a password.

create user ops$tangt
identified by taktangspassword
default tablespace ...

I am able to login as

sqlplus /

and I can login as

sqlplus ops$tangt/taktangspassword

It seems that the 'ops$' implies 'identified externally'.

Using the 'ops$' can be handy if you sometimes need to login to the database without logging in to unix first. eg sqlplus copy command (now deprecated)

Takmeister


re: unix login and security

Tak Tang, May 27, 2004 - 10:07 am UTC

Before I confuse someone, (and I know I'll confuse someone, because I confused ME!) . . .

In my prior post I said we had a system where the oracle accounts were called exactly the same as the unix account names. This DOES NOT MEAN that you can login without a password simply by setting IDENTIFIED EXTERNALLY.

The init.ora parameter 'os_authent_prefix' is set to a null string, so the oracle accounts are co-incidentally the same as the unix accounts. Unfortunately, it means that an account cannot be both os authenticated (as required by the application), and have a password. This drives my colleagues (who use TOAD) barmy.

The default 'os_authent_prefix' is 'ops$'.

Takmeister


Oracle Account

Jayesh, February 08, 2005 - 5:07 pm UTC

Is it compulsory to use the account name as "oracle" for oracle installation on a Unix/Solaris box or can I use any name instead of "oracle" say "xyz"?

Tom Kyte
February 09, 2005 - 1:48 am UTC

use whatever you like

Just be carefull with os_authent_prefix

Pierre, April 09, 2005 - 11:47 am UTC

On many databases I have seen an OPS$ORACLE account identified externally (even in production servers).
This account was mostly roled as DBA and often the os_authent_prefix=TRUE. For your enjoyment, connect to a different server where the OS account ORACLE exists or where you can create it, copy the TNSNAMES entry for the remote database (where the OPS$ORACLE exists and os_authent_prefix=TRUE) in your TNSNAMES then sqlplus /@X.
Note: Change X for the TNSNAMES entry alias...
Hoo Haa, prod data...

Tom Kyte
April 09, 2005 - 12:19 pm UTC

no, os_authent_prefix would be 'ops$' for ops$, perhaps you meant the not so good REMOTE_OS_AUTHENT?  That I would not use.

That I have said time and again -- do not use 

Just another note

Pierre, April 09, 2005 - 11:51 am UTC

In previous entry, I use ORACLE (OPS$ORACLE), all you really need is to know any OPS$ account, it's just the OPS$ORACLE usually has the dba role attached to it...

Cheers

Error Message

Kyle, August 03, 2007 - 10:04 am UTC

"OpsConCloseRemoteProxyAuthUserSession"
Has anybody seen this error before it would be very helpful if somebody could explain the error. Thanks
Tom Kyte
August 05, 2007 - 1:39 pm UTC

a bit of "context" would be useful

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library