Insufficient privileges when using create user in procedure Oracle-CodePudding

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

CodePudding user response：

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;