No privileges when executing Oracle stored procedure - Stack Overflow

admin管理员组

文章数量:1133730

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?

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?

• oracle-database
• stored-procedures
• privileges
• materialized-views
• sql-grant

Share Improve this question Follow edited Jan 7 at 18:00 marc_s 754k183183 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Jan 7 at 17:32 ennezetaquennezetaqu 2511 silver badge44 bronze badges 1

• 3 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 Commented Jan 7 at 17:59

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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

本文标签： No privileges when executing Oracle stored procedureStack Overflow