Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Question related to security

RE: Question related to security

From: Meng, Dennis <Dennis.Meng_at_alcoa.com>
Date: Wed, 05 Mar 2003 06:35:24 -0800
Message-ID: <F001.0056099C.20030305063524@fatcity.com>


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

Sent: Wednesday, March 05, 2003 6:35 AM
To: Multiple recipients of list ORACLE-L

That would let the account see ANY table in the database, wouldn't it?  

Pat.

-----Original Message-----

Sent: Tuesday, March 04, 2003 6:05 PM
To: Multiple recipients of list ORACLE-L

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

Sent: Tuesday, March 04, 2003 4:05 PM
To: Multiple recipients of list ORACLE-L

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Meng, Dennis
  INET: Dennis.Meng_at_alcoa.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 - 08:35:24 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US