Below a little script that will allow to create a user like another one in an easy way<\/p>\n\n\n\n
<\/p>\n\n\n\n
begin\r\n-- This pl\/sql block creates a user like another one\r\n\r\n\r\ndeclare v_username varchar2(30) := '<username_like>';\r\nv_username_new varchar2(30) := '<new_user>';\r\nv_pwd varchar2(30):= 'ChangeMe123___';\r\ndefroles varchar2(500);\r\nuser_exists integer;\r\nv_stmt varchar2(500);\r\n\r\nbegin \r\n\r\n\r\nselect count(*) into user_exists from dba_users where username=v_username;\r\nexecute immediate 'drop user ' || v_username_new;\r\n\r\nif user_exists> 0 then\r\n\r\nselect 'create user ' || v_username_new || ' identified by \"' || v_pwd || '\" default tablespace '||default_tablespace|| ' temporary tablespace '||temporary_tablespace||' profile '|| profile || ' PASSWORD EXPIRE' into v_stmt\r\nfrom sys.dba_users\r\nwhere username = upper(v_username);\r\ndbms_output.put_line (v_stmt);\r\nexecute immediate v_stmt;\r\n\r\n-- grant roles\r\nbegin\r\nfor c0 in (select 'grant '||granted_role||' to ' || v_username_new || ' '|| \r\ndecode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION') as stmt\r\nfrom sys.dba_role_privs\r\nwhere grantee = upper(v_username))\r\nloop \r\ndbms_output.put_line ('v_stmt2' || c0.stmt);\r\nexecute immediate c0.stmt;\r\nend loop;\r\nexception when no_data_found then \r\ndbms_output.put_line ('no data found');\r\nend;\r\n\r\n-- grant system privs\r\nbegin\r\nfor c0 in (select 'grant '||privilege||' to '|| v_username_new || ' ' ||\r\ndecode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION') as stmt\r\nfrom sys.dba_sys_privs\r\nwhere grantee = upper(v_username))\r\nloop\r\ndbms_output.put_line (c0.stmt);\r\n execute immediate c0.stmt;\r\nend loop;\r\nexception when no_data_found then \r\ndbms_output.put_line ('');\r\nend;\r\n\r\n-- grant table privs\r\nbegin\r\nfor c0 in (select 'grant '||privilege||' on '||owner||'.'||table_name||' to ' || v_username_new as stmt\r\nfrom sys.dba_tab_privs\r\nwhere grantee = upper(v_username)\r\nand type != 'DIRECTORY')\r\nloop\r\ndbms_output.put_line (c0.stmt);\r\nexecute immediate c0.stmt;\r\nend loop;\r\nexception when no_data_found then \r\ndbms_output.put_line ('');\r\nend;\r\n\r\n-- grant directory privs\r\nbegin\r\nfor c0 in (select 'grant '||privilege||' on directory '||owner||'.'||table_name||' to ' || v_username_new as stmt\r\nfrom sys.dba_tab_privs\r\nwhere grantee = upper(v_username)\r\nand type = 'DIRECTORY')\r\nloop\r\ndbms_output.put_line (c0.stmt);\r\nexecute immediate c0.stmt;\r\nend loop;\r\nexception when no_data_found then \r\ndbms_output.put_line ('');\r\nend;\r\n\r\n-- tbs quotas\r\nbegin\r\nfor c0 in (select 'alter user '||username||' quota '||\r\ndecode(max_bytes, -1, 'UNLIMITED', max_bytes)||\r\n' on '||tablespace_name as stmt\r\nfrom sys.dba_ts_quotas\r\nwhere username = upper(v_username))\r\nloop\r\ndbms_output.put_line (c0.stmt);\r\nexecute immediate c0.stmt;\r\nend loop;\r\nexception when no_data_found then \r\ndbms_output.put_line ('');\r\nend;\r\n\r\n--default role\r\n\r\nfor c1 in (select granted_role as grrole from dba_role_privs \r\n where grantee = upper(v_username)\r\n and default_role = 'YES'\r\n )\r\n loop\r\n execute immediate 'alter user ' || v_username_new || ' ' || ' default role '|| c1.grrole;\r\n end loop;\r\n \r\n end if;\r\nend;\r\nend;\r\n\/\r\n\r\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"Below a little script that will allow to create a user like another one in an easy way<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-247","post","type-post","status-publish","format-standard","hentry","category-non-classe"],"_links":{"self":[{"href":"https:\/\/oracledba.fr\/index.php?rest_route=\/wp\/v2\/posts\/247","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oracledba.fr\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oracledba.fr\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oracledba.fr\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oracledba.fr\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=247"}],"version-history":[{"count":3,"href":"https:\/\/oracledba.fr\/index.php?rest_route=\/wp\/v2\/posts\/247\/revisions"}],"predecessor-version":[{"id":250,"href":"https:\/\/oracledba.fr\/index.php?rest_route=\/wp\/v2\/posts\/247\/revisions\/250"}],"wp:attachment":[{"href":"https:\/\/oracledba.fr\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=247"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oracledba.fr\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=247"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oracledba.fr\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=247"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}