Re: Converting DBA_TAB_GRANTS to GRANT statements ??

From: L. Carl Pedersen <carl.pedersen_at_dartmouth.edu>
Date: 21 Oct 92 02:32:04 GMT
Message-ID: <carl.pedersen-201092222512_at_kip-sn-10.dartmouth.edu>


In article <1992Oct19.123426.1_at_jaguar.uofs.edu>, littlec1_at_jaguar.uofs.edu (Chris Little) wrote:
>
>
> I would like to generate a grants script from data stored in the
> DBA_TAB_GRANTS table (i.e. convert each row in the table to the
> appropriate GRANT statement), and wonder if anybody out there has
> undertaken this task previously. If so, could you please send me
> the code? Thank you in advance

By looking at the view definition for DBA_TAB_GRANTS, I came up with the following:

SQL> create view granter as
  2 select 'grant '|| rtrim(

  3           decode( sum(ta.select$), 0, '', 'SELECT,' )||
  4           decode( sum(ta.insert$), 0, '', 'INSERT,' )||
  5           decode( sum(ta.delete$), 0, '', 'DELETE,' )||
  6           decode( sum(ta.update$), 0, '', 'UPDATE,' )||
  7           decode( sum(ta.references$), 0, '', 'REFER,' )||
  8           decode( sum(ta.alter$), 0, '', 'ALTER,' )||
  9           decode( sum(ta.index$), 0, '', 'INDEX' ),
 10            ',') || ' on ' || o.name ||
 11           ' to ' || ue.name || ';' statement,
 12         ue.name grantee,
 13         u.name owner,
 14         o.name object,
 15         max(ta.time) granted

 16 from sys.tabauth$ ta, sys.obj$ o, sys.user$ u, sys.user$ ue  17 where ta.obj# = o.obj# and
 18         ta.grantee# = ue.user# and
 19         u.user# = o.owner#

 20 group by ue.name, u.name, o.name
 21
SQL> / View created.

SQL> desc granter

 Name                            Null?    Type
 ------------------------------- -------- ----
 STATEMENT                                CHAR(120)
 GRANTEE                                  CHAR(30)
 OWNER                                    CHAR(30)
 OBJECT                                   CHAR(30)
 GRANTED                                  DATE

SQL> select statement from granter
  2 where object = 'DISBURSE_PENDING_FOR_ME' and owner = 'OPS$ADP_CARL';

STATEMENT



grant SELECT,INSERT,DELETE,UPDATE on DISBURSE_PENDING_FOR_ME to OPS$LN_ALICE;
grant SELECT,INSERT,DELETE,UPDATE on DISBURSE_PENDING_FOR_ME to OPS$LN_CAROL;
grant SELECT,INSERT,DELETE,UPDATE on DISBURSE_PENDING_FOR_ME to OPS$LN_DEBBIE;
grant SELECT,INSERT,DELETE,UPDATE on DISBURSE_PENDING_FOR_ME to OPS$LN_DENISE; 4 rows selected.

SQL> Ideally, you probably don't want to generate a statement for each row in DBA_TAB_GRANTS, because it has more rows than it needs. This view returns one statement for each unique combination of grantee and object.

I make NO guarantees that this will continue to work in future versions of ORACLE. It's worked for me for several years and works now in 6.0.36, but might well fail in ORACLE 7 - since it uses undocumented internal tables. Received on Wed Oct 21 1992 - 03:32:04 CET

Original text of this message