Differences

This shows you the differences between two versions of the page.

--- create_user_like [2018/12/08 12:49] – created 0.0.0.0
+++ create_user_like [2024/04/20 09:52] (current) – external edit 127.0.0.1
@@ Line 1: / Line 1: @@
-====== Create_User_like... ======
+I believe the original script was devised by Frank Naude
-Originally by Frank Naude
 <code>
 set pages 0 feedback off verify off lines 500
@@ Line 8: / Line 6: @@
-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: "
+accept oldname prompt "Enter user to model new user from: "
+col generated_password new_value generated_password nopri;
+select dbms_random.string('a',7)||'#'||dbms_random.string('x',8) generated_password
+from dual
+/
-COLUMN  generated_password  NEW_VALUE  generated_password noprint;
-select get_random_password(p_password_format=>'LLLNNN?L') as generated_password  from dual;
 prompt ###                                ________
 prompt ### New Username is           ==>  &&newname.
@@ Line 28: / Line 28: @@
 select 'create user &&newname identified by &&generated_password.'||
        ' default tablespace '||default_tablespace||
-       ' temporary tablespace '||temporary_tablespace||' profile '||
+       ' temporary tablespace '||temporary_tablespace||
-       profile||';'
+       ' profile '||profile||';'
 from   sys.dba_users
-where  username = upper('&&oldname');
+where  username = upper('&&oldname')
+/
 -- Grant Roles...
-select 'grant '||granted_role||' to &&newname'||
+select 'grant '||granted_role||' to &&newname'||decode(admin_option, 'YES', ' with admin option')||';'
-       decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
 from   sys.dba_role_privs
-where  grantee = upper('&&oldname');
+where  grantee = upper('&&oldname')
+/
 -- Grant System Privs...
-select 'grant '||privilege||' to &&newname'||
+select 'grant '||privilege||' to &&newname'||decode(admin_option, 'YES', ' with admin option')||';'
-       decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
 from   sys.dba_sys_privs
-where  grantee = upper('&&oldname');
+where  grantee = upper('&&oldname')
+/
 -- Grant Table Privs...
 select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
 from   sys.dba_tab_privs
-where  grantee = upper('&&oldname');
+where  grantee = upper('&&oldname')
+/
 -- Grant Column Privs...
-select 'grant '||privilege||' on '||owner||'.'||table_name||
+select 'grant '||privilege||' on '||owner||'.'||table_name||'('||column_name||') to &&newname;'
-       '('||column_name||') to &&newname;'
 from   sys.dba_col_privs
-where  grantee = upper('&&oldname');
+where  grantee = upper('&&oldname')
+/
--- Tablespace Quotas...
-select 'alter user &&newname quota '||
+-- Set tablespace Quotas...
-        decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
+select 'alter user '||username||' quota '||decode(max_bytes, -1, 'unlimited', max_bytes)||' on '||tablespace_name||';'
-        ' on '||tablespace_name||';'
+from   sys.dba_ts_quotas
-from  sys.dba_ts_quotas
+where  username = upper('&&oldname')
-where username = upper('&&oldname');
+/
 -- Set Default Role...
 set serveroutput on
 declare
-  defroles varchar2(4000);
+defroles varchar2(4000);
 begin
-  for c1 in (select * from sys.dba_role_privs
+    for c1 in (
-              where grantee = upper('&&oldname')
+              select *
-                and default_role = 'YES'
+              from   sys.dba_role_privs
-  ) loop
+              where  grantee      = upper('&&oldname')
-      if length(defroles) > 0 then
+              and    default_role = 'YES'
-         defroles := defroles||','||c1.granted_role;
+              )
-      else
+    loop
-         defroles := defroles||c1.granted_role;
+        if length(defroles) > 0 then
-      end if;
+            defroles := defroles||','||c1.granted_role;
-  end loop;
+        else
-  dbms_output.put_line('alter user &&newname default role '||defroles||';');
+            defroles := defroles||c1.granted_role;
+        end if;
+    end loop;
+    dbms_output.put_line('alter user &&newname default role '||defroles||';');
 end;
 /
 spool off
-pause   Press cntrl-d if not correct, press <enter> to create the new user
+pause Press CTRL-C if not correct or ENTER to create the new user
 @create_user_&&newname..sql
 </code>