Solved

Revoke access on all tables

Posted on 2004-04-20
4
1,753 Views
Last Modified: 2007-12-19
I have some 2000 tables with different previlages granted on them can use one statment like below to revoke all of them

revoke all on <tablename> from <username>;

I will have to execute above statement for each table, instaed can i use something like ALL or anyother keywords.

Thanks,
rajeev
0
Comment
Question by:rajeevrc
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 250 total points
ID: 10872101
you cannot do it with ALL
but you can do it with one script:


here is the short one:

set termout off
set feedback off
set heading off
set trimspool on
set linesize 200
set pagesize 0

spool revoke_all.sql

select 'revoke all on ' || table_name|| '  from <user_name> ;' from all_tables where owner not in ('SYS', 'SYSTEM');

spool off
@revoke_all.sql


save this in a file , say test.sql

and call this file in sqlplus

SQL> @test.sql



that will be it


0
 
LVL 1

Expert Comment

by:ora-dba
ID: 10872564
The above solution will work fine. Another way to do this would be with a PL/SQL block

declare
  user_name varchar2(30) := 'SCOTT'; -- the name of the user that you like to revoke privileges from
begin
  for rec in (select table_name
                 from all_tables
                 where owner not in ('SYS','SYSTEM')) loop
    execute immediate 'revoke all on '||rec.table_name||' from '||user_name;
  end loop;
end;
/
0
 
LVL 8

Expert Comment

by:annamalai77
ID: 10874729
hi there

well u can use a pl/sql block to solve ur problem for all the users. well what ora-dba has given , u have to hard code the username.

u can go by seazodiac method since it will be easy to do it.
also u can get the username at runtime also.

regards
annamalai

0
 
LVL 1

Expert Comment

by:ora-dba
ID: 10876695
Dear annamalai,
you can easily change my code so that the username doesn't have to be hard coded:

define user_name = "SCOTT"

begin
  for rec in (select table_name
                 from all_tables
                 where owner not in ('SYS','SYSTEM')) loop
    execute immediate 'revoke all on '||rec.table_name||' from &user_name';
  end loop;
end;
/
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle SQL 6 73
Oracle - SQL Where clause causing Invalid Number Error 4 46
use lov values 2 63
Field name with special character (Ñ) in Oracle 11 88
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question