Re: Question: running a SQLPlus script from the UNIX shell with passwords??

From: Snewber <snew_at_snew.com>
Date: Thu, 10 Mar 2005 16:26:56 +1000
Message-ID: <d0opbg$pgt$1@bunyip2.cc.uq.edu.au>

No, because the procedure is run as the user, so only the privileges that the user has are used.

This method is good when you need to grant SUID on tables (that you don't own) to other users but you don't have the password for the account. However, in 9i you can grant SUID on other user's objects without having the admin privilege on the object which is what you would expect when you are sysdba (I think).

lambu999_at_yahoo.com wrote:
> Snewber,
>
> Thanks for sharing your method. Arent we giving the power of SYSDBA to
> the user for a fleeting moment. One day some user may find out when
> they run this particular piece of code they can update any table, which
> in turn can lead to possible issues?
>
> Thanks,
> Ram.
>
>
> Snewber wrote:
>

>>I don't like playing with hashvalues of users. What if they go to log

>
> in
>

>>while it is changed or something goes wrong and the hashvalue doesn't

>
>

>>get set back to it's original value? This might not be a problem, and

>
>

>>the script would probably execute very fast, but still I personally
>>aren't in favour of it.
>>
>>Another method that is handy is to create a procedure, owned by the

>
> user
>

>>who you need to run it as, and then execute that procedure. This will

>
>

>>then run the script as that user. It is useful for granting

>
> privileges
>

>>to other users, especially in 8i, when you don't have the owner's

>
> password.
>

>>Here is a sample:
>>
>>sqlplus -s "/ as sysdba" << EOF
>>
>>CREATE OR REPLACE
>>PROCEDURE $ora_user.dba_execute_as_me (sql_stmt VARCHAR2) IS
>>BEGIN
>>         execute immediate sql_stmt;
>>END dba_execute_as_me;
>>/
>>
>>spool /temp/dba_execute_as_me.log
>>exec $ora_user.dba_execute_as_me ( '$sql_stmt' ) ;
>>spool off
>>
>>DROP PROCEDURE $ora_user.dba_execute_as_me ;
>>
>>exit
>>EOF
>>

>
>
Received on Thu Mar 10 2005 - 00:26:56 CST