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,
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,
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?
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.
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.
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.
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*/
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!
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?
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
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
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?
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
May 26, 2004 - 4:00 pm UTC
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"?
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...
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
August 05, 2007 - 1:39 pm UTC
a bit of "context" would be useful