| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help : DBMS_SQL and CREATE USER Problems
--------------F8C1DD6CD92804A651C59859 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit
Hi Mike!
Sure, it's possible, but make sure, that:
1) the user which is calling your procedure has the system privileges to
create the user, alter any user.
2) the user which is calling your procedure has received the object
privileges, which he will grant to the new user, with the grant
option.
Even if the proc-caller will temporarely receive the same rights as the
proc-owner !!! The proc-caller is executing dynamic sql and so, his own
system- and object-priviliges will be checked !!!!!
Scincerly yours
T.Hitzfeld
Oracle DBA
Mike Sweetman wrote:
> Hi
>
> I've Oracle v7.2 (if it helps) and am trying to write a procedure to
> create a new user, and setup their grants and synonyms, because using
> a
> script is a bit klunky. I appreciate
> that the privileges of the person that created the procedure apply
> when the procedure is run, even if this is another user, but am
> experiencing difficulty in both finding exaples across the net showing
>
> this sort of operation, and in solving the problem myself using the
> oracle CD ROMs and Oracle Press books I have.
>
> I imagine that this is a fairly typical operation that needs to be
> performed, as I currently have done it using sed and a standard script
>
> where I throw in the new user name and password, and then run the
> script
> using sqlplus.
>
> Thanks In Advance
>
> Mike.
>
> |Mike Sweetman +-----+ Tel : +44 (0)1344 413155 |
> |Science Systems Ltd | | Fax : +44 (0)1344 413186 |
>
> Save energy ==> :-))
>
> -----------------
>
> Thus far:-
>
> CREATE OR REPLACE PROCEDURE sp_create_user (as_currentusername IN
> varchar2, as_username IN varchar2, as_userpw IN varchar2, as_usert
> ype IN varchar2 ) as
> li_cid integer;
> BEGIN
>
> --
> -- Drop The User
> -- Does the user already exist - how do you find out ?
> --
>
> li_cid := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(li_cid, 'grant drop user to ' || as_currentusername,
>
> dbms_sql.v7);
> DBMS_SQL.CLOSE_CURSOR(li_cid);
>
> li_cid := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(li_cid, 'drop user ' || as_username || ' cascade',
> dbms_sql.v7);
> DBMS_SQL.CLOSE_CURSOR(li_cid);
>
> --
> -- Create The User
> --
>
> li_cid := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(li_cid, 'grant create user to ' ||
> as_currentusername,
> dbms_sql.v7);
> DBMS_SQL.CLOSE_CURSOR(li_cid);
>
> li_cid := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(li_cid, 'create user ' || as_username || '
> identified by
> ' || as_userpw, dbms_sql.v7);
> DBMS_SQL.CLOSE_CURSOR(li_cid);
>
> --
> -- Give User Connect
> -- Connect is a role - and roles don't apply - what would be
> equivalent ?
> -- Failure occurs during the following section (should it be
> uncommented)
> --
>
> -- li_cid := DBMS_SQL.OPEN_CURSOR;
> -- DBMS_SQL.PARSE(li_cid, 'grant create session to ' ||
> as_currentusername, dbms_sql.v7);
> -- DBMS_SQL.CLOSE_CURSOR(li_cid);
> --
> -- li_cid := DBMS_SQL.OPEN_CURSOR;
> -- DBMS_SQL.PARSE(li_cid, 'grant connect to ' || as_username,
> dbms_sql.v7);
> -- DBMS_SQL.CLOSE_CURSOR(li_cid);
>
> --
> -- Create the grants on tables
> --
>
> -- etc
>
> end;
>
> -------------------==== Posted via Deja News
> ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
--------------F8C1DD6CD92804A651C59859 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit
<HTML>
Hi Mike!
<BR>Sure, it's possible, but make sure, that:
<BR>1) the user which is calling your procedure has the system privileges
to create the user, alter any user.
<BR>2) the user which is calling your procedure has received the object
privileges, which he will grant to the new user, with the grant
<BR> option.
<BR>Even if the proc-caller will temporarely receive the same rights as
the proc-owner !!! The proc-caller is executing dynamic sql and so, his
own system- and object-priviliges will be checked !!!!!
<P><B><FONT SIZE=+2>Scincerly yours</FONT></B> <BR><B><FONT SIZE=+2>T.Hitzfeld</FONT></B> <BR>Oracle DBA
<P>Mike Sweetman wrote:
<BLOCKQUOTE TYPE=CITE>Hi
<P>I've Oracle v7.2 (if it helps) and am trying to write a procedure to <BR>create a new user, and setup their grants and synonyms, because using <BR>a <BR>script is a bit klunky. I appreciate <BR>that the privileges of the person that created the procedure apply <BR>when the procedure is run, even if this is another user, but am <BR>experiencing difficulty in both finding exaples across the net showing <BR>this sort of operation, and in solving the problem myself using the <BR>oracle CD ROMs and Oracle Press books I have. <P>I imagine that this is a fairly typical operation that needs to be <BR>performed, as I currently have done it using sed and a standard script <BR>where I throw in the new user name and password, and then run the<BR>script
<P>Thanks In Advance
<P>Mike.
<P>|Mike Sweetman +-----+ Tel : +44 (0)1344 413155 | <BR>|Science Systems Ltd | | Fax : +44 (0)1344 413186 |
<P>Save energy ==> :-))
<P>-----------------
<P>Thus far:-
<P>CREATE OR REPLACE PROCEDURE sp_create_user (as_currentusername IN <BR>varchar2, as_username IN varchar2, as_userpw IN varchar2, as_usert <BR>ype IN varchar2 ) as <BR>li_cid integer; <BR>BEGIN <P>-- <BR>-- Drop The User
<P> li_cid := DBMS_SQL.OPEN_CURSOR; <BR> DBMS_SQL.PARSE(li_cid, 'grant drop user to ' || as_currentusername, <BR>dbms_sql.v7); <BR> DBMS_SQL.CLOSE_CURSOR(li_cid);
<P> li_cid := DBMS_SQL.OPEN_CURSOR;
<BR> DBMS_SQL.PARSE(li_cid, 'drop user ' || as_username ||
' cascade',
<BR>dbms_sql.v7);
<BR> DBMS_SQL.CLOSE_CURSOR(li_cid);
<P>-- <BR>-- Create The User <BR>-- <P> li_cid := DBMS_SQL.OPEN_CURSOR; <BR> DBMS_SQL.PARSE(li_cid, 'grant create user to ' || <BR>as_currentusername,
<P> li_cid := DBMS_SQL.OPEN_CURSOR; <BR> DBMS_SQL.PARSE(li_cid, 'create user ' || as_username || '
<BR>identified by <BR>' || as_userpw, dbms_sql.v7); <BR> DBMS_SQL.CLOSE_CURSOR(li_cid); <P>-- <BR>-- Give User Connect <BR>-- Connect is a role - and roles don't apply - what would be <BR>equivalent ? <BR>-- Failure occurs during the following section (should it be<BR>uncommented)
<P>-- li_cid := DBMS_SQL.OPEN_CURSOR; <BR>-- DBMS_SQL.PARSE(li_cid, 'grant create session to ' || <BR>as_currentusername, dbms_sql.v7); <BR>-- DBMS_SQL.CLOSE_CURSOR(li_cid); <BR>-- <BR>-- li_cid := DBMS_SQL.OPEN_CURSOR; <BR>-- DBMS_SQL.PARSE(li_cid, 'grant connect to ' || as_username, <BR>dbms_sql.v7); <BR>-- DBMS_SQL.CLOSE_CURSOR(li_cid); <P>--
<P>-- etc
<P>end;
<P>-------------------==== Posted via Deja News ====-----------------------<BR> http://www.dejanews.com/ Search, Read, Post to Usenet</BLOCKQUOTE> </HTML>
--------------F8C1DD6CD92804A651C59859-- Received on Sat Oct 04 1997 - 00:00:00 CDT
 |
 |