I have a Unix account named TEST but would like to find out how I can set it up so when it runs PL SQL procedures from the Unix prompt (SQLPLUS /@DBNAME @SQL_FILE) it connects to Oracle as a different Oracle account such as QUERY account. Please advice?
In the SQL_FILE use connect QUERY as the first statment.
rest of the script....
I would prefer using Auto Authentication without having to specify the logon account and password. We have many scripts that we would have to modify and we definetely do not want to hard-code the account/password.
Thanks for your reply.
create one sql file as sreddy mentioned and call it in your scripts if you do not want to hard code in every script.
What you are looking for is called OS Authentication. OS Authentication assumes that since you have logged into the OS that you will use the same username to login to the database.
First, create a user with an OPS$ prefix that corresponds to your username. For example, my unix username is jeffh and my oracle username is OPS$JEFFH:
create user ops$jeffh identified externally
temporary tablespace temp
default tablespace users;
grant connect, resource to ops$jeffh;
Then, you have to futz with some init.ora parameters. The three relevent lines are below. (Note: os_authent_prefix is commented out)
Then, bounce you instance and you can login using:
Click Here to Expand Forum to Full Width