RE: Question related to security

[Meng, Dennis]

Title: RE: 9i OCP Upgrade Exam - Again

I created the procedure as system by put third party user as owner....

-----Original Message-----
From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 05, 2003 11:35 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Question related to security

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 ...

 

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: Wednesday, March 05, 2003 9:35 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Question related to security

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) ;
   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 security

That 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 security

grant 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 security

Hi 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?

 

Thanks

 

Dennis