Re: Stored Procedure: GRANT command

You can grant a role to a user with 'admin' options on that role, so they can grant that role to another user but not any other role.

You need to grant the 'with admin' option to the users you are creating.

Regards

James

http://www.jamesbelton.co.uk

"VdWal" <vdwal_at_tref.nl> wrote in message news:8fqrci$79cem$1_at_reader1.wxs.nl...
> Hi,
>
> We are using Oracle 7.3 , forms 5 on NT
>
> We have one 'master user' defined in our appl.
> He has the privilege 'grant any role' and the role 'PREV_ROLE'
>
> In our application it is possible to add new users. In the 'add user' form
> the forms_ddl command to add a user is placed. This works fine for the
> master adding new users but not for other (new) users who want to add new
> users.
>
> After the new user is added by the master user the new user gets a certain
> role, 'PREV_ROLE'.
> So new users have PREV_ROLE. But they do not have 'grant any role'.
>
> But if a new user himself adds a new user he/she does not have the
> privileges to give te new user PREV_ROLE. (Lack of 'grant any role') So
 new
> users themselfs cannot create new users...
>
> I tried to do the following:
> I created a strored procedure in the server with owner 'master user' and
> parameter the name of the new user. When a new user is added by another
 user
> this procedure is called. Inside the procedure I execute the command
 'grant
> PREV_ROLE to <new user>'. This does not work. The word 'grant' is not
> accepted by PL/SQL. How can I execute DDL commands inside stored
 procedures?
> In forms something like 'Forms_DDL'exists, but how is the syntax for the
> server?
>
> Any advice is VERY welcome!
>
> Thanks Stephan
>
>
>
>
>
Received on Tue May 16 2000 - 00:00:00 CDT