[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Revoke access on all tables

Posted on 2004-04-20
4
Medium Priority
?
1,859 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month17 days, 15 hours left to enroll

830 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