CODEWITHSUNDEEP
sqloracle-databaseplsqloracle12c
Python241820
Python241820

Reputation: 1527

Generating a script [ORACLE]

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

Upvotes: 1

Views: 62

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

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');

Upvotes: 3

Related Questions