This content has been marked as final. Show 4 replies
You can grant 'drop any table' privilege and after try to create before ddl trigger and write here your logic. Andit is not recommended in production environments to grant powerful roles like DBA role or privileges like 'drop any table ' .I think a procedure is a good way.
Look at 'Truncate In Other Schemas'
It's possible to create a Procedure which do the job. Then, you may grant EXECUTE Privileges to the User you want. You'll find an example below:
More over, on this Procedure you may add a test so that it doesn't execute the TRUNCATE Table from specific Schema. It may be something like that:
Hope this help.
SELECT username INTO usr FROM user_users; IF usr IN ('SYS','SYSTEM', ... ) THEN RAISE forbidden_user; END IF;