0

I have a user called DEPLOY defined with DBA role.
When logged on via SQL Developer the user can drop a view as follows:

DROP VIEW APP_USER.VIEW_NAME_VW CASCADE CONSTRAINTS

If I put the same statement inside of a package owned by DEPLOY, then it fails with 'ORA-00942: table or view does not exist'.

So it appears that the privileges granted via the DBA role do not extend to code executed by the package.
Is this correct?

1 Answer 1

2

Privileges granted via any role, not just the DBA role, are not available in a definer's rights stored procedure. Assuming you want a definer's rights procedure, you'd need to grant the deploy user permission to drop the role via a direct grant. Most likely in this case since you're fine with deploy having the DBA role

grant drop any view
   to deploy;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.