CODEWITHSUNDEEP
oracle-database
Lê Nhật Minh
Lê Nhật Minh

Reputation: 67

Insufficient privileges when using create user in procedure Oracle

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

Upvotes: 1

Views: 945

Answers (2)

Himanshu Kandpal
Himanshu Kandpal

Reputation: 1616

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;

Upvotes: 0

Thomas Carlton
Thomas Carlton

Reputation: 5976

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;

Upvotes: 1

Related Questions