Ask the Expert

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?
VARIABLE g_message varchar2(50);
ACCEPT p_ID PROMPT 'Please enter the desired ID to revoke privileges:'

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;
print g_message;

    Requires Free Membership to View


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:
    for rec in (select distinct privilege, grantee, table_name
                    from dba_tab_privs
                    where grantee = p_id)
       execute immediate ('revoke '||rec.privilege||' on
'||rec.table_name||' from '||rec.grantee);
    end loop;
Note: To view your PL/SQL errors you can use the following statement:
      select * from user_errors;

This was first published in March 2007

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: