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?
1 Answer
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.
-
I am going to go bang my head against a sufficiently thick block of concrete. I just had the owner of the procedure incorrect ... I was assuming it was someone else. Granting to the correct user solved my problem. Thanks. Feb 3, 2012 at 18:49