RE: dbms_sys_sql

[Seley, Linda]

Title: RE: dbms_sys_sql

I do understand, but I don't want to.  When this code goes live we'll be creating approx 1000 user ids.  Right now we're a small company.  Hopefully we'll grow (soon!).  I don't want to have 1000+ copies of the procedure.  Hence the reluctance to go that way. 

 

Thanks!

 

Linda

-----Original Message-----
From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]
Sent: Friday, May 18, 2001 6:11 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: dbms_sys_sql

 

> -----Original Message-----
> From: Seley, Linda [mailto:[EMAIL PROTECTED]]
> 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