0

I am using the database as me.

enter image description here

The procedure exists as an object that belongs to me. enter image description here

Grant sucessful. enter image description here

But when I attempt to EXECUTE insert_into_purified; i get

Error starting at line : 469 in command -
BEGIN insert_into_purified; END;
Error report -
ORA-01031: insufficient privileges
ORA-06512: at "C##ELLIE.INSERT_INTO_PURIFIED", line 5
ORA-06512: at line 1
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

enter image description here

1

It seems the user lacked CREATE VIEW privilege.

I can reproduce the error, but not the part about needing to grant it to yourself, which doesn't make sense. If you could grant privileges to yourself there would be no security at all

Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 
Connected as sys AS SYSDBA

SQL> create user ellie identified by ellie quota unlimited on users;

User created


SQL> grant create session, create table, create procedure to ellie;

Grant succeeded


SQL> conn ellie/ellie
Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 
Connected as ellie

SQL> create or replace procedure insert_into_purified as
  2  begin
  3      execute immediate 'create or replace view demo_view as select * from dual';
  4  end insert_into_purified;
  5  /

Procedure created


SQL> exec insert_into_purified
begin insert_into_purified; end;

ORA-01031: insufficient privileges
ORA-06512: at "ELLIE.INSERT_INTO_PURIFIED", line 3
ORA-06512: at line 1

SQL> conn / as sysdba
Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 
Connected as sys AS SYSDBA

SQL> grant create view to ellie;

Grant succeeded


SQL> conn ellie/ellie
Connected to Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 
Connected as ellie

SQL> exec insert_into_purified

PL/SQL procedure successfully completed


SQL> desc demo_view
Name  Type        Nullable Default Comments 
----- ----------- -------- ------- -------- 
DUMMY VARCHAR2(1) Y                         
| improve this answer | |
  • 2
    It makes sense if c##ellie was granted the DBA role but not the create view privilege directly. In that case such a PL/SQL procedure fails, but c##ellie can also grant create view to self to solve this. – Balazs Papp Aug 15 at 13:43
  • That does indeed make sense. Thanks! – William Robertson Aug 16 at 9:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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