Re: Converting DBA_TAB_GRANTS to GRANT statements ??
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