RE: dbms_sys

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Fri, 18 May 2001 16:17:01 -0700
Message-ID: <F001.0030868A.20010518161051@fatcity.com>

> -----Original Message-----
> From: Seley, Linda [mailto:LSeley_at_IQNavigator.com]
> Sent: vendredi, 18. mai 2001 15:11
> To: Multiple recipients of list ORACLE-L
> Subject: RE: dbms_sys_sql
>
>
> I am working on scripts that will create tables and users, grant
> permissions, etc. every night on a clean copy of our db. Without
> dbms_sys_sql, I would have to connect to other users to do
> the grants. I
> don't want passwords anywhere in my scripts, I want to
> connect once then run
> everything from this one user. dbms_sys_sql takes in a
> userid and sql text
> (plus a couple of other things) and runs the sql as that user.

Of course, you realize that as long as you have "create any procedure" privilege, you can create a procedure under the other user's name and grant privileges that way. As a matter of fact, I am doing that myself right this minute.

Example:

create or replace procedure userB.exec_sql_statement    (sql_statement in varchar2)
as
   c_dynsql pls_integer ;
   ignore pls_integer ;
begin

   c_dynsql := dbms_sql.open_cursor ;
   dbms_sql.parse (c_dynsql, sql_statement, dbms_sql.native) ;
   ignore := dbms_sql.execute (c_dynsql) ;
   dbms_sql.close_cursor (c_dynsql) ;

exception

   when others then
      if dbms_sql.is_open (c_dynsql)
      then
        dbms_sql.close_cursor (c_dynsql) ;
     end if ;
     raise ;

end ;
/
exec userB.exec_sql_statement ('grant select on table to &user with grant option') ...
drop procedure userB.exec_sql_statement ;

Using dbms_sys_sql is better because you don't have to create and drop a database object. On the other hand, using the method mentioned above, you don't have to depend on an "undocumented" feature.

Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com Received on Fri May 18 2001 - 18:17:01 CDT