Insufficient privileges when using create user in procedure Oracle

Question

I try to create user and grant for them some privileges. I try to create without using procedure:

CREATE USER User1 IDENTIFIED BY password;

It works fine. But for example, i have thousands of users. So I created a procedure to do it:

CREATE OR REPLACE PROCEDURE CreateUser AS 
BEGIN
FOR u IN ( SELECT id FROM User )
 LOOP
   EXECUTE IMMEDIATE 'CREATE USER User_'||d.id || ' IDENTIFIED BY password';
   EXECUTE IMMEDIATE 'GRANT SELECT ON UserInfo_'||d.id||' TO User_'||d.id;
 END LOOP;
END

But it throws an error:

ORA-01031: insufficient privileges

How can I handle this problem? Thanks for helping in advance

The reason is, inside a PL/SQL block you have only the privileges which are granted directly to you. Privileges which you received via a role (e.g. DBA) do not apply inside a PL/SQL block. — Wernfried Domscheit
– Wernfried Domscheit, Commented Mar 13, 2022 at 14:17

Thomas Carlton · Accepted Answer · 2022-03-13 13:07:37Z

1

You need to make sure the user running the procedure has the privileges to create users.

Assuming the user that will run the procedure is MyUser, you need to run :

GRANT CREATE USER to MyUser;

answered Mar 13, 2022 at 13:07

Thomas Carlton

6,11414 gold badges91 silver badges169 bronze badges

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

Comments

Himanshu Kandpal · Accepted Answer · 2022-03-13 20:56:24Z

0

if your SQL is directly (i.e not in a procedure ) but not working from a procedure/function it means that you have the Grants through a role. For a statement to work in a procedure or a function we need direct Grants and not through the role.

You can check that you have the Create user by a role by doing the following and trying your SQL, if it fails it means you have access through a role.

SET role none;
CREATE USER User1 IDENTIFIED BY password;

answered Mar 13, 2022 at 20:56

Himanshu Kandpal

1,67611 silver badges18 bronze badges

Collectives™ on Stack Overflow

Insufficient privileges when using create user in procedure Oracle

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related