No privileges when executing Oracle stored procedure

Question

I have a problem with an Oracle stored procedure that dynamically creates some materialized views.

The procedure first drops the materialized views if there are any with the same name of the ones that will be created. Then it creates the new materialized views.

Both the statements of drop and create are execute by means of the execute immediate command.

When I execute the procedure, it first drops the materialized views already created and fails to create the new ones because of an alleged lack of privileges.

Instead, if I run the code as a script, it perfectly works.

I tried to create a simpler stored procedure, but I get the same error.

Is there anyone able to explain why it happens or how to understand the reason?

It would help if you included your code, and the full error you get. And your privileges. You might have the drop privilege granted directly but the create privilege granted via a role? (This is similar, but there will be better duplicate targets...) Why are you dropping and creating objects at runtime at all though - that is not a normal pattern? Unless the view definition changes each time you can refresh it (if that isn't already automatic). Or perhaps an MV isn't the right solution for whatever you're doing. — Alex Poole
– Alex Poole, Commented Jan 7 at 17:59

Connor McDonald · Accepted Answer · 2025-01-12 12:26:31Z

0

You'll need to grant CREATE TABLE and CREATE MATERIALIZED VIEW to the account that owns the procedure.

answered Jan 12 at 12:26

Connor McDonald

11.8k1 gold badge14 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

No privileges when executing Oracle stored procedure

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related