How to write a query that obtains a script to remove the privilege of deleting records in some table of the user c##test_user to the users who have it?.
I tried this query:
SELECT GRANTEE, TABLE_NAME
FROM DBA_TAB_PRIVS
WHERE PRIVILEGE ='DELETE'
AND TABLE_NAME IN (SELECT TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = 'c##test_user');
The problem is that I do not know how to generate the script from the select. Any ideas?. Thank you
Do it like this
declare
cursor DeleteGrants is
SELECT GRANTEE, TABLE_NAME
FROM DBA_TAB_PRIVS
WHERE PRIVILEGE ='DELETE'
AND TABLE_NAME IN (SELECT TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = 'c##test_user');
begin
for aGrant in DeleteGrants loop
execute immediate 'REVOKE DELETE ON c##test_user.'||aGrant.TABLE_NAME||' FROM '||aGrant.GRANTEE;
end loop;
end;
In case you just like to output but not execute you can also make
SELECT 'REVOKE DELETE ON c##test_user.'||TABLE_NAME||' FROM '||GRANTEE||';' as cmd
FROM DBA_TAB_PRIVS
WHERE PRIVILEGE ='DELETE'
AND TABLE_NAME IN (SELECT TABLE_NAME
FROM ALL_TABLES
WHERE OWNER = 'c##test_user');
