When I run a
CREATE USER statement within Sql Developer, the user is created just fine. When I try to call a function which performs
CREATE USER (followed by a bunch of other statements) I get the ORA-01031 error when that statement is executed. On another machine this function is apparently completing successfully. I have checked all the roles and system privileges but nothing stands out. What could it be?
When I run a
When you have permission to perform an action at the command line but not within a definer's rights stored procedure (and I hope you're using a stored procedure here, not a stored function), the problem is almost certainly that the privilege you need has been granted through a role, not directly to the user that owns the procedure. Inside a definer's rights stored procedure, only privileges that have been granted directly to the owner of the procedure can be used-- privileges granted through a role (such as
DBA) cannot be used.
You can verify that the problem is, in fact, that the privilege is granted through a role by disabling roles and then trying to create the user, i.e.
SQL> set role none; SQL> create user ...
Assuming that fails, as I expect it will, you would need to grant the owner of the function the
CREATE USER privilege directly
SQL> grant create user to <<owner of procedure>>
Then, you should be able to create users even with roles disabled and the stored procedure should work.