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;
The problem that you are having with your anonymous PL/SQL block is that the revoke command is not valid in PL/SQL....
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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;
Dig Deeper on Using Oracle PL-SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.