| 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
I've written up the procedures to do what you need. The trick is that the owner of the procedure sp_create_user must have some basic priveleges. You don't need (and can't really use) the as_currentUserName parameter for anything. The procedure will always run with the base privs of the person who created it.
I set up a new account called test_dba and granted only the priveleges absolutely necessary to create the procedure sp_create_user that can drop, create, and grant connect to user. You would:
create user test_dba identified by test_dba;
grant connect to test_dba with admin option; -- ADMIN OPTION is Important
grant create procedure to test_dba; -- so it can create sp_create_user
grant create user to test_dba; -- so it can create a new user
grant drop user to test_dba; -- so it can drop existing users.
Once your account has those priveleges (it does not need create procedure directly, it can get that from the resource or dba role), you can write a procedure that looks like:
CREATE OR REPLACE PROCEDURE sp_create_user
(as_username IN varchar2, as_userpw IN varchar2, as_usertype IN varchar2 )
l_cnt number;
begin
select count(*) into l_cnt
from all_users
where username = upper(as_username);
if ( l_cnt = 1 ) then
execute_immediate_proc( 'drop user ' || as_username || ' cascade' );
dbms_output.put_line( 'User ' || as_username || ' dropped' );
end if;
execute_immediate_proc( 'create user ' || as_username ||
' identified by ' || as_userpw );
dbms_output.put_line( 'User ' || as_username || ' created' );
execute_immediate_proc( 'grant connect to ' || as_username );
dbms_output.put_line( 'User ' || as_username || ' now has connect priv' );
end;
/
My implementation of execute_immediate_proc is below (it makes for nicer code if you wrap the dbms_sql calls in a procedure to hide all of the inputs/calls needed). Basically, the above routine could now be run by other people (given that they had execute on it) to create/drop users and give them connect.
If you want the procedure to give grants other then just connect, remember that your account (test_dba in this example) must be given that grant first with the ADMIN OPTION (so it can grant it to others in turn).
Hope this helps...
create or replace function execute_immediate( stmt in varchar2 )
return number
as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_sql.close_cursor( exec_cursor );
return rows_processed;
when others then
if dbms_sql.is_open(exec_cursor) then
dbms_sql.close_cursor(exec_cursor);
end if;
raise;
create or replace procedure execute_immediate_proc( stmt in varchar2 ) as
l_dummy number;
begin
l_dummy := execute_immediate( stmt );
end;
/
On Fri, 03 Oct 1997 02:51:37 -0600, Mike Sweetman <sweetman_at_lsil.com> 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
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |