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 last published in March 2007

Dig Deeper on Using Oracle PL-SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close