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 <[EMAIL PROTECTED]>
.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:[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sergey V Dolgov
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
