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?
ORA-01031 Insufficient Privileges Raised From Within Function
oracleoracle-sql-developerpermissions
Related Question
- ORA-01017 connecting to example schemas on Oracle VM appliance from Windows host
- Oracle log miner permission: which permission authorize user to use logMiner as scheduled JOB
- Oracle: Not able to create tablespace from stored procedure
- ERROR: ORA-01031: insufficient privileges
- Cannot create view in oracle 12c
- ORA 01031 Insufficient privileges on GRANT SELECT on ALL_CATALOG to a user as system user
Best 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.
Assuming that fails, as I expect it will, you would need to grant the owner of the function the
CREATE USER
privilege directlyThen, you should be able to create users even with roles disabled and the stored procedure should work.