Q

Script to revoke access from user

I'm trying to write a script to revoke all access from any given user. I've done the following so far, but can't figure out all the errors -- can you help?

I'm trying to write a script to revoke all access from any given user. I've done the following so far, but can't figure out all the errors -- can you help?
VARIABLE g_message varchar2(50);
ACCEPT p_ID PROMPT 'Please enter the desired ID to revoke privileges:'

BEGIN
for rec in (Select distinct privilege, grantee, Table_name
From dba_tab_privs
Where grantee = p_ID;) loop

revoke rec.privilege on rec.Table_name from rec.grantee;
END;
/
print g_message;
Hi,

The problem that you are having with your anonymous PL/SQL block is that the revoke command is not valid in PL/SQL.

Therefore, you will have to execute it dynamically using the EXECUTE IMMEDIATE statement. The statement below shows how to use the execute immediate statement:

      execute immediate ('revoke '||rec.privilege||' on
'||rec.table_name||' from '||rec.grantee);
Also you will need an "End Loop." Your block should look like this:
    begin
    for rec in (select distinct privilege, grantee, table_name
                    from dba_tab_privs
                    where grantee = p_id)
    loop
       execute immediate ('revoke '||rec.privilege||' on
'||rec.table_name||' from '||rec.grantee);
    end loop;
end;
Note: To view your PL/SQL errors you can use the following statement:
      select * from user_errors;
This was first published in March 2007

Dig deeper on Using Oracle PL-SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close