Re: Grant Re-Generating Script ?

From: Steve Dodsworth <Steven_Dodsworth_at_qsp.co.uk>
Date: 1996/10/15
Message-ID: <53vsos$dsa_at_orion.qsp.co.uk>#1/1

In <53tsae$leh_at_butch.lmsc.lockheed.com>, jchoi_at_sunnyvale.is.lmsc.lockheed.com writes:
>Is there a script that would regenerate all the GRANTs
>that I currently have in the database?
>
>We are migrating DB and have been dropping lots of tables.
>
>--
>John K. Choi
>Lockheed Martin Missiles and Space
>jchoi_at_sunnyvale.is.lmsc.lockheed.com
>+1 408-756-2047

John,
try this:

---

accept tabowner prompt 'Enter Table Owner      : '
accept tab_name prompt 'Enter Table Name       : '
accept privname prompt 'Enter Privilege [ISUD] : '
accept xgrantee prompt 'Enter Grantee name     : '

spool cr_grnt.out

select 'grant '|| privilege
       ||' on '|| table_name
       ||' to '|| grantee
       || decode(grantable, 'YES', 'WITH GRANT OPTION;',';')
from  dba_tab_privs
where owner      like upper('&tabowner%')
and   table_name like upper('&tab_name%')

and privilege like upper('&privname%') and grantee like upper('&xgrantee%') /

spool off

---

I don't think any thing is missing, if so - let me know.

Bye,
Steve

---

| any similarity 'tween my opinions and that |
|  of my employers are purely hypothetical   |
|     and should give no cause for alarm     |
 --------------------------------------------

Received on Tue Oct 15 1996 - 00:00:00 CEST