Script problem !

[cruser3]

I have a script which is supposed to create a new user based on an exisitng user's privileges but after running it it does not create a new user! Run it on Per. Orcl 9i for win 98 as system/manager!

Can anyone try it out and let me know what's the problem ?
--------->
rem ---------------------------------------------------------------
rem Filename: cr8like.sql
rem Purpose: Script to create a new user (with privs) like an existing
rem database user.
rem ---------------------------------------------------------------

set pages 0 feed off veri off lines 500

accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
accept psw prompt "Enter new user's password: "

-- Create user...(1)
select 'create user &&newname identified by &&psw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||
' profile '||profile||';'
from sys.dba_users
where username = upper('&&oldname');

-- Grant Roles...(2)
select 'grant '||granted_role||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');

-- Grant System Privs...(3)
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');

-- Grant Table Privs...(4)
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname');

-- Grant Column Privs...(5)
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');

-- Set Default Role...(6)
select 'alter user &&newname default role '|| granted_role ||';'
from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES';

----------------

[abhaysk]

The script merely generates the DDL commands, but dosent get executed.
U need to execute. Use Execute Immediate.

Abhay.

[cruser3]

Any idea how to use Exec. immdte ? Can u post for just the first statement of the script?

[rotem_fo]

Try something like that:

set pages 0 feed off veri off lines 500

declare
v_str varchar2(3200);
v_deftbs varchar2(200);
v_tmptbs varchar2(200);
v_profile varchar2(200);
begin
select default_tablespace, temporary_tablespace, profile
into v_deftbs, v_tmptbs, v_profile
from dba_users
where username = upper('&oldname');

v_str := 'create user &newname identified by &psw '||
' default tablespace ' || v_deftbs || ' temporary ' ||
' tablespace ' || v_tmptbs || ' profile ' || v_profile;

EXECUTE IMMEDIATE v_str;
end;
/

[LKBrwn_DBA]

Also, in order to -- Grant Table Privs...(4) and -- Grant Column Privs...(5), you may have to 'connect' as the OWNER of these tables.

[cruser3]

Thanks for the suggestions but is there a way to make this work in SQLplus w/o NDS or DYN_SQL?

[gnagesh]

Originally posted by cruser3
Thanks for the suggestions but is there a way to make this work in SQLplus w/o NDS or DYN_SQL?

spool into .sql and execute it.

hope this helps

[cruser3]

gnagesh's suggestion is the only one that seems possible to run w/o adding to the original script any NDS 0r DYN_SQL or anything else unless one can manipulate the sqlplus buffer in some sort of way. As a C++ prog. I can only think of simulating it as an object with methods to execute the SQL !! However any other opinion is welcome !

[davey23uk]

easiest option is to spool and then execute it, you only need to add 3 lines to your script

spool crea.sql
..
..
..

spool off

@cre.sql