Re: creating users in oracle9i stored procedure

Hi!

Since nothing mentioned, assuming Owners right: Each and every statement inside a procedure is executed with the system privileges and object privileges that the owner has been granted directly. If the owner has the privileges only through some roles, f ex the role DBA, that won't do. Because roles are not active when stored pl/sql objects are executed.

To find the offending commands, do this: Start sql*plus as the owner
Issue 'set role none'
Execute the commands one by one.
Since the error message reports line 11, it seems to be inside the
'MY_APPLICATION_SPECIFIC_COMMANDS'.
Then give the necessary grants to the owner.

And hey, put these ones on your to-do-list: -(High priority) When creating the user, assign it to proper default and temporary tablespaces
-Assign the necessary tablespace quotas, don't use unlimited tablespace -Don't grant 'connect', grant 'create session' and additional necessary privileges

Regards,
Njål Ekern
naekern_at_hotmail.com
n.a.ekern_at_usit.uio.no

I am sorry for posting this the second time. The only answer I got is that there is a kind of grant problem It did not give me so much

In previous to 9i releases of database
I used stored procedures to register user accounts. It looks like

create procedure (....)
as begin
  execute immediate 'create user '||vloginname||' identified by
'change_it' ;

  execute immediate 'grant connect,unlimited tablespace to
'||vloginname;

  MY_APPLICATION_SPECIFIC_COMMANDS
end;

In 9i this no longer works. Oracle reports access rights error.

ORA-01031: niewystarczaj?ce uprawnienia
ORA-06512: przy "PROMO.ADD_PROMO_USER", linia 11

It happens even when both the procedure owner (that has DBA granted) and caller are SYS.

The question is: How to create user in stored procedure in Oracle9iNTEE ?

Regards,

        Marcin Received on Wed Jan 23 2002 - 01:42:57 CST