| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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.
<SPAN
class=979084523-18052001>
<SPAN
class=979084523-18052001>Thanks!
<SPAN
class=979084523-18052001>
<SPAN
class=979084523-18052001>Linda
<FONT face=Tahoma
size=2>-----Original Message-----From: Jacques Kilchoer
[mailto:Jacques.Kilchoer_at_quest.com]Sent: Friday, May 18, 2001 6:11
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
dbms_sys_sql
> -----Original Message----- >
From: Seley, Linda [<A
href="mailto:LSeley_at_IQNavigator.com">mailto:LSeley_at_IQNavigator.com]
> Sent: vendredi, 18. mai 2001 15:11 <FONT
size=2>> To: Multiple recipients of list ORACLE-L <FONT
size=2>> 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 <FONT
size=2>> don't want passwords anywhere in my scripts, I want to
> connect once then run <FONT
size=2>> 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) <FONT
size=2>as c_dynsql pls_integer ;
ignore pls_integer ; <FONT
size=2>begin c_dynsql :=
dbms_sql.open_cursor ; dbms_sql.parse
(c_dynsql, sql_statement, dbms_sql.native) ; <FONT
size=2> ignore := dbms_sql.execute (c_dynsql) ; <FONT size=2> dbms_sql.close_cursor (c_dynsql) ; <FONT size=2>exception when others then
 |
 |