-
Script problem !
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';
----------------
-
The script merely generates the DDL commands, but dosent get executed.
U need to execute. Use Execute Immediate.
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
script prob.!
Any idea how to use Exec. immdte ? Can u post for just the first statement of the script?
-
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;
/
It is better to ask and appear ignorant, than to remain silent and remain ignorant.
Oracle OCP DBA 9i,
C++, Java developer
-
Also, in order to -- Grant Table Privs...(4) and -- Grant Column Privs...(5), you may have to 'connect' as the OWNER of these tables.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Thanks - but ?
Thanks for the suggestions but is there a way to make this work in SQLplus w/o NDS or DYN_SQL?
-
Re: Thanks - but ?
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
Nagesh
-
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 !
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|