1

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 1

2

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.

2
  • 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.
    – demongolem
    Feb 3, 2012 at 18:49
  • I got bit by this for a different reason (different situation) but similar... I had granted UPDATE and READ to a table in the procedure that it was updating, but changed the privilege from READ to SELECT and the error went away. I suppose due to READ not having SELECT ... FOR UPDATE abilities. Dec 8, 2023 at 0:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.