Oracle-L: Re: System tables for generating the object privileges

From: Thomas Day <tday6_at_csc.com>
Date: Wed, 22 May 2002 06:53:30 -0800
Message-ID: <F001.00467957.20020522065330@fatcity.com>

I just spent 6 months doing, among other things, just this. DBA_TAB_PRIVS should probably be more accurately called DBA_OBJ_PRIVS as it has all objects' privs in it, now just tables.

Here's an example. It produces spooled output that can be run to recreate the existing privs.

SELECT 'GRANT '||privilege||' on SCHEMA.'||TABLE_NAME||' TO '||grantee||

     decode(grantable,'YES',' with grant option;',';')
     FROM dba_tab_privs
      WHERE OWNER = 'SCHEMA'

By changing the where clause to select on GRANTEE you can get all objects across schemae.

select
'grant '||
privilege||
' ON '||owner||'.'||
TABLE_NAME||
' to WHAZZUP? '||

decode(grantable,'YES',' with grant option;',';') from dba_tab_privs where grantee = 'WHAZZUP?'

enjoy

                                                                                           
                    Sergey V                                                               
                    Dolgov <dsv          To:     Multiple recipients of list ORACLE-L      
                    @pptus.oilnet        <ORACLE-L_at_fatcity.com>                            
                    .ru>                 cc:                                               
                    Sent by: root        Subject:     Re: System tables for generating the 
                                         object privileges                                 
                                                                                           
                    05/21/2002                                                             
                    11:08 PM                                                               
                    Please                                                                 
                    respond to                                                             
                    ORACLE-L

Hello Vasu,

You can find much in dba_tab_privs there is information about procedures, views, tables, synonyms in it.

Wednesday, May 22, 2002, 9:03:22 AM, you wrote:

VR> Hello All,
VR>    I am trying to generate a script to grant privileges on the DB
VR> objects by extracting them from system tables. I know that the

following
VR> system tables can be used to get the system, role privileges, and table

VR> and column access privileges.     dba_sys_prives
VR>     dba_roll_prives
VR>     dba_tab_privs
VR>     dba_col_privs

VR> I am not able to find the system tables to get the privileges assigned

VR> to users on stored procedures, views and synonyms etc. Can you please VR> throw some light on this?

VR> Thanks for your help.

VR> Vasu

--

Best regards,

 Sergey                            mailto:dsv_at_pptus.oilnet.ru

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Sergey V Dolgov
INET: dsv_at_pptus.oilnet.ru

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Thomas Day
INET: tday6_at_csc.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message