Hi Tom,
Is there any way we can use a value in shell variable in sql where clause ?.
Thanks,
Sam

yup.

$ cat t.sh
#!/bin/csh -f

sqlplus / <<eof

select '$HOME' from dual;

eof

$ ./t.sh

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Mar 4 20:03:41 2002

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production

ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> 
'/EXPORT/HOME/TKYT<b>
------------------
/export/home/tkyte</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> ops$tkyte@ORA817DEV.US.ORACLE.COM> Disconnected from Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production 

My current assignment has a number of scripts which I am trying to tidy up and secure, so this was a very interesting question.

However, a recntly completed security review from the IT security dept. highlighted the "risk" of using os authenticated oracle users, and have recommended we remove any that exist! Given that this is the case, would you have any suggestions for wrapping up passwords somehow?

For example, there is a batch job which is kicked off by a unix script in the following style:

mybatchscript <password> <logfile_destination>

and this runs via cron - so anyone who can read this users crontab file can see the password. Also, if anyone happens to log in whilst the job is running (they would have to be pretty determined, since it runs at 4am ;-) ) would be able to grab the password from a ps listing.

I was considering putting the password in a file, making it read only for the user, and then amending the script to obtain the password from the file - at the moment I can't think of another way around this, which is a pity since someone will have to maintain this when the password is forced to change...

Tom,

i have a solaris server, where in i have 3 instances of oracle running. what i need from you is ....

when i login to the server, i need to be prompted with the instances of oracle that is running with numbering.when i choose to go into 3rd instance out of 1 or 2 or 3. i would type 3 and when i log in, i should be with the profile of the 3rd instance.

how should i set that shell script in the server. i have seen that functioality in one of the server.

do your site has the link to that script.?

thanks for your time,

tom,

its not running... sorry i dont know how to run it. it says ./coraenv[33]: syntax error at line 39 : `set' unexpected

i tried in different servers.. but same error. the files are exactly the same in different servers. is it like we need to run it with setting anything ?

Thanks ,.

tom.

sorry, for my ignorance. i ran the script and it asks me this ...

$ csh coraenv
ORACLE_SID = [DEVEENGC] ? y
ORACLE_HOME = [/u01/app/oracle/product/8.1.5] ? y
$

if i say no also it says samething...

$ csh coraenv
ORACLE_SID = [DEVEENGC] ? n
ORACLE_HOME = [/u01/app/oracle/product/8.1.5] ? n

please let me know ... where/what i am missing.. should i have to add anything in profile?

thanks a lot... tom.

We have a script called oramenu which lets us choose the instance we want when we log in. We put it under /var/opt/oracle on Solaris 8.

We have a line in /etc/profile to invoke oramenu at login.

. /var/opt/oracle/oramenu

Here is the oramenu script

OPTION=0
VALID=0
while [ $VALID -ne 1 ] ; do
VALID=1
echo "\n Please choose an option\n"
echo " 1. DVLP database (11.5.2)"
echo " 2. TEST database (11.5.6)"
echo "\n Enter a number (E to exit): \c"
read OPTION
case $OPTION in
1) . /u02/app/applmgr/dvlpappl/APPSORA.env;;
2) . /u02/app/applmgr/testappl/APPSORA.env;;
e|E) exit;;
*) VALID=0 ;;
esac;
done
export PS1="|`uname -n`|$LOGNAME|$TWO_TASK> "

Tom,

I want to be able to write a UNIX script that will automate the process of exp/imp for transportable tablespaces. This is one of the rare situations in which you have to invoke exp/imp as sysdba.

I am unable to come up with a shell trick to hide the password for an 'as sysdba' exp login (e.g. echo passwd | sqlplus trick)

I see that you recommend using OS Authentication for automated scripts. However, in order to use OS Authentication for a sysdba login, does that mean that I won't be able to database password file authentication for sysdba logins? It seems that the two are mutually-exclusive since REMOTE_LOGIN_PASSWORDFILE must be set to NONE or EXCLUSIVE, respectively.

I don't want to limit my ability to administrate this database remotely via a SQL*Net connection, but I need to script this exp/imp routine.

Please help. Am I missing something?

The only thing different than the response given, is creating roles. Granting execute privs to run procedures, is no different than the table grants themselves. If user1 can run A B C, then likely he has as well the grants for the tables involved in A B C as well. Add the execute priv on the procedure to the associated role the user has. It requires alot less maintenance.

Tom ,
I got ur points about os authentication being a secured way of connecting to the database.
I have created a user to be authenticated externally. I can easily connect to sqlplus using only the userid as long as i m connected as the same os user . But when i tried to invoke exp(or imp) , it always ask for a password . Probably i m not using the right syntax - Can you please tell what should be the right syntax if we want to use exp from an os authent user so that exp will not ask for the password .

Thanks for your help as always.

Hi Tom,

You have described about OS authentication which I have been able to understand to some extent. But I am still not very clear about what all authentication methods exists. Which authentication method should be used in which scenerio.

Can you please explain me the following :

1. Like OS Authentication, which other authentication methods exist and which authentication method is best suitable for which scenerio?

2. I understand that there can be 2 types of users.
i) One who is directly logged into the server (where the database is installed (may be a DBA or a simple database user).

ii) One who is not logged directly logged into the server but connects through net8 (sqlplus)(may be a DBA or a simple database user).

What are the different ways that these users can connect to oracle.

3. Where does password file authentication fits in ?
4. I heard something about OPS$ accounts? What are these?

I am actually a little confused about what are the different ways of authentication, when should these be used, how to setup these authentication methods.

Your help is required to help clear these doudbts.

Many Thanks in advance.

if you want to know everything about it -- suggest you look at the Oracle10g security book in the "links I like" -- David Knox covers it in great detail.

and as always, the >>>> concepts guide <<<< is a great place to peek 

it actually answers #1.

#2 is not "accurate".  there are users - period.  how the connect (over network or fork/exec) is not really relevant.  they are just USERS

#3 is all about "as sysdba" and "as sysoper" -- we can use a password file for remote (over the network) authentications when the database is not up and running.  so when you "connect scott/tiger as sysdba", Oracle can verify scott has been granted sysdate and uses the password tiger by using this password file.  otherwise, it is not used.  


#4 -- ops$ is OPerating System $
              ^^        ^

it is the typical prefix we use for ops$ accounts -- operating system identified, OS authenticated accounts.



 

Hi Tom,
Thanks a lot for the information. I went through a couple of documentations and tried to clear my doudbts about various authentication methods. I still have a couple of clarifications and would like your assistance to clear these. The answers would be brief and not much time taking (though the questions look lengthy) :


1. There are 2 methods of connecting to a database?
a) Authentication through database when the database is up & running
b) Authentication through OS (local or remote users) when using "as sysdba".

2. In order to connect through OS authentication localy, you need to connect like
sqlplus> connect / as sysdba

3. In order to connect locally as sysdba, you need to be logged on to the server as a user who is part of the SYSDBA or SYSOPER group (on unix the dba group).

4. Where will I be attempted to be authenticated if I use
sqlplus> connect /

5. REMOTE_AUTHENT & REMOTE_LOGIN_PASSWORDFILE parameters have no effect on connecting to database locally. Meaning, whatever the values of these parameters, if I am a member of the "dba" group, I can always connect as
sqlplus > connect / as sysdba

6. REMOTE_AUTHENT & REMOTE_LOGIN_PASSWORDFILE parameters, as the name suggests, effects only the remote users (those who attempt to connect "as sysdba" or "as sysoper" from remote machines).

7. We can specify both parameters REMOTE_AUTHENT=TRUE|FALSE & REMOTE_LOGIN_PASSWORDFILE=NONE|EXCLUSIVE|SHARED in the init.ora file?

8. REMOTE_AUTHENT=FALSE means users connecting remotely (as "sysdba" or "sysoper") shall NOT be OS authentication? I am not sure how they will be able to connect remotely without specifying a connect string?

9. REMOTE_AUTHENT=TRUE means users connecting remotely (as "sysdba" or "sysoper") shall be OS authentication? But, I am not sure how they will be able to connect remotely without specifying a connect string?

sqlplus> ????

10. REMOTE_LOGIN_PASSWORDFILE=NONE means users connecting remotely (as "sysdba" or "sysoper") will NOT be authenticated via the password file?

11. REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE means users connecting remotely (as "sysdba" or "sysoper") will be authenticated via the password file? user SYS as well as other users with sysdba or sysoper priviledges can be authenticated via the passwordfile?

Something like ?
sqlplus> connect scott/tiger@server1 as sysdba

12. For (11), should the user (scott in this case) also have a unix account on the database server machine and also be a part of the dba group?

13. what if I specify REMOTE_AUTHENT=TRUE and REMOTE_LOGIN_PASSWORDFILE=NONE?

15. If you are a remote user and is connecting (as "sysdba" or "sysoper") over a non secured network (tcp/ip), you can only be identified by a passwordfile?


Thanks in advance
Ashok Khatri

Hi Tom,

From above:
"You would code:
sqlplus / <<EOF
execute begin_foo_backup;
exit
EOF
where begin_foo_backup is a procedure owned by someone.."

This work greate for me.
But suppose i need to: select * from emp and put the result in emp.lst file, how this could be done using the above method ?
Thanks.

Hi Tom,
Im sorry that i didnt explain my self properly.
Suppose i want to use your approch and use a procedure that return the result set (using ref cursor or whatever) to unix file, how it can be done ?

Thanks and sorry again.