?
Solved

Revoke access on all tables

Posted on 2004-04-20
4
Medium Priority
?
1,801 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 1000 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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup
Suggested Courses

765 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