Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Script for revoking

Re: Script for revoking

From: Robert Prendin <rprendin_at_magi.com>
Date: Thu, 04 Jun 1998 23:35:19 GMT
Message-ID: <35772e0d.24319990@news.istar.ca>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US