Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Question related to security
My problem is I just took his requirements into considerations ...
got it.
Raj
-----Original Message-----
Sent: Wednesday, March 05, 2003 3:39 PM
To: 'ORACLE-L_at_fatcity.com'
Cc: Jamadagni, Rajendra
Only UserA can grant access on a table belonging to UserA (unless another
user was granted access with admin option.)
However, UserB can create a procedure in the schema of UserA if UserB has
CREATE ANY PROCEDURE system privilege.
The procedure created will run with the privileges of UserA, and will be
able to grant access on tables belonging to UserA.
-----Original Message-----
<mailto:Rajendra.Jamadagni_at_espn.com> ]
Sent: mercredi, 5. mars 2003 09:35
To: Multiple recipients of list ORACLE-L
I am surprised ... which user did you run this as? You mention in your original email that you don't jave the password for the owner and sys/system don't have admin rights ...
Can you solve the puzzle? If you don't have privs with grant option, how could you grant it to someone else? plus this being dbms_sql, you need to have those privs directly granted to you ...
-----Original Message-----
<mailto:Dennis.Meng_at_alcoa.com> ]
Sent: Wednesday, March 05, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
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:
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) ;
when others
then
if dbms_sql.is_open (c_dynsql) then dbms_sql.close_cursor (c_dynsql) ; end if ; raise ;
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Mar 05 2003 - 14:48:47 CST
- text/plain attachment: ESPN_Disclaimer.txt
![]() |
![]() |