Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
    
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 '12 at 18:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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