Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Script for revoking
Good question. There is actually a way of creating a sql statement out
of a select statement which can be spooled to a file and simply run as
a script with no need to edit the file...It will look something like this...
set head off
set pagesize 0
set feedback off
set echo off
EXAMPLE 1
select 'grant select on '|| table_name || ' to YATFG with grant
option;' from all_tables where owner ='USERNAME';
EXAMPLE 2
select 'revoke all on '|| table_name || ' from YATFG;' from
all_tables where owner ='USERNAME';
set head on
set pagesize 10000
set feedback on
The results will look something like this..
SQL> /
grant select on TEST to yatfg with grant option ;
grant select on TEST_1 to yatfg with grant option ;
Good Luck,
Robert Prendin
Oracle DBA
-Michael Abbey Systems International-
Stephen Mulcahy <smulcahy_at_smsat.ilo.dec.com> wrote:
>Hi,
>
>I'm trying to figure out how to automate some of our privilege granting
>procedures by turning them into sql scripts .. for instance, I have a user
>in the database who owns a bunch of tables. If I want to set permissions
>on this users tables to a known state I have to run a scrip of the form ..
>
>revoke all on tablefoo from otheruser;
>...
>
>for each table/user in the database ideally (just to be sure .. is this
>being excessively paranoid?). At the moment I build up a list of these
>revokes by doing selects from the data dictionaries, spooling the results
>to a file and editing the file into the above format.
>
>What I'd like to is create a procedure or set of procedure which will
>automatically build up this list and optionally do the revoking. What I'm
>not clear on is how to mix commands like revoke with sql type commands ..
>is it even possible?
>
>Thanks for any advice,
>
>-stephen
>
>
>
>
>----
>Stephen Mulcahy, Digital Equipment International, smulcahy_at_smsat.ilo.dec.com
>The views expressed are the author's and do not necessarily reflect the
>official position of Digital Equipment Corporation
Received on Thu Jun 04 1998 - 18:35:19 CDT