I am trying to create a user in an Oracle package, function or procedure using an EXECUTE IMMEDIATE command.
Framing the Problem
My “administrator” account is called MY_ADMIN. It has DBA, CONNECT roles and CREATE SESSION permissions. The ultimate goal is to execute a simple CREATE USER command, similar to this…
CREATE USER JDEAUX IDENTIFIED BY HelloWorld123;
When I run this command in a SQL Developer worksheet, in the MY_ADMIN context, you can see that it creates the user. So, we know that MY_ADMIN can create new users. We have the correct syntax and permissions.
Creating a procedure
If I then create a script in the worksheet, it too works. Here you can see that the procedure sets the P_USER_NEW variable, then creates a dynamic SQL string to generate the “CREATE USER JDEAUX IDENTIFIED BY HelloWorld123” command and then executes it successfully.
Note: I dropped the JDEAUX account before running this script.
If we look in our log file, we can see the command that it executed. It is the same syntax as the manual example.
The Problem
When I create a function using the above script, moving the P_USER_NEW to be the sole input parameter, it fails.
This function produces a “ORA-01031: insufficient privileges” error. You can see here that the CREATE USER command is exactly the same as the script above, but it fails for lack of permissions. You can see in the left column, the user context is the MY_ADMIN account that successfully did a manual create and a scripted create above.
Question
It seems that the MY_ADMIN account can execute a direct command and/or a scripted EXECUTE IMMEDIATE command, but not an EXECUTE IMMEDIATE inside a function. We know the function runs because the log file is being updated with both the proposed SQL and with the error message.
Is there some additional permission that I can grant? If so, to which account?
Rob