Grant select on all tables is
not what we were looking for exactly because of the reason brought up by Pat.
The following solution provided by Jacques solved the problem
-
Execute Immediate is for Oracle 8.1 and later.
In 7.3.4 you need to use the dbms_sql package for dynamic SQL.
The procedure would be something like:
In 7.3.4 you need to use the dbms_sql package for dynamic SQL.
The procedure would be something like:
create procedure third_party_app_user.grant_priv
(table_name_in in varchar2, privileges_in in
varchar2, grantee_in varchar2)
as
c_dynsql pls_integer ;
ignore pls_integer ;
begin
c_dynsql := dbms_sql.open_cursor ;
dbms_sql.parse (c_dynsql,
'grant ' || privileges_in || ' on ' ||
table_name_in || ' to ' || grantee_in,
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 ;
/
-----Original Message-----
From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 05, 2003 6:35 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Question related to securityThat would let the account see ANY table in the database, wouldn't it?Pat.-----Original Message-----
From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 04, 2003 6:05 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Question related to securitygrant select any table to <your developer>/Shouldn't this work?Raj-------------------------------------------------------------
Rajendra dot Jamadagni at espn dot com
Any views expressed here are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !!-----Original Message-----
From: Meng, Dennis [mailto:[EMAIL PROTECTED]
Sent: Tuesday, March 04, 2003 4:05 PM
To: Multiple recipients of list ORACLE-L
Subject: Question related to securityHi all -I have an interesting problem at hand - we have a request from a developer that ask to access some tables owned by a user generated by third party app. Since I don't have the password for this user and system/sys do not have admin option on these tables, I can't grant select to the developer. Changing password for this app can be tricky and will be used as last resort. My question is is there anything else I can do to meet this request?ThanksDennis