Saturday, December 3, 2016

Create Users Like Another User In Oracle Database

How To Create Users Like Another User In Oracle Database At SQL Command Line, Not From OEM (Doc ID 1352212.1)

Solution 1:
exec  dbms_metadata.set_transform_param(dbms_metadata.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

select dbms_metadata.get_ddl( 'USER', '' ) from dual
    UNION ALL
    select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', '' ) from dual
    UNION ALL
    select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', '' ) from dual
    UNION ALL
    select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', '' ) from dual
    UNION ALL
select dbms_metadata.get_ddl('TABLESPACE','USER_DATA') from dual;


Solution 2:
To get the user's privileges you can query the following tables:

--  For Table privileges: DBA_TAB_PRIVS

-- For Column privileges: DBA_COL_PRIVS

-- For System privileges: DBA_SYS_PRIVS

-- For Roles:  DBA_ROLE_PRIVS


You can also use the DBMS_METADATA package:

-- For object privileges granted to a user :
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT',upper('&GRANTED_USER'))
FROM DUAL;

-- For system privileges granted to a user :
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',upper('&GRANTED_USER'))
FROM DUAL;

-- For roles granted to a user :
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT',upper('&GRANTED_USER'))
FROM DUAL;

The following scripts can be used to create a user identical to another user :

NOTE: The commands are provided as is and are not supported by Oracle.
You will have to verify yourself that the commands are applicable to your environment.

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...
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...
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...
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
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');

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

-- Set tablespace Quotas...
select 'alter user '||username||' quota '||
decode(max_bytes, -1, 'UNLIMITED', max_bytes)||
' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&&oldname');
NOTE:
The above generated commands must be executed to actually create the new user with its privileges


-- Set Default Role...
set serveroutput on
declare
defroles varchar2(4000);
begin
  for c1 in (select * from sys.dba_role_privs
             where grantee = upper('&&oldname')
             and default_role = 'YES'
            )
  loop
    if length(defroles) > 0 then
      defroles := defroles||','||c1.granted_role;
    else
      defroles := defroles||c1.granted_role;
    end if;
  end loop;
dbms_output.put_line('alter user &&newname default role '||defroles||';');
end;
/

.........To find the user details with out any error ......
select (case
        when ((select count(*)
               from   dba_users
               where  username = '&&Username') > 0)
        then  dbms_metadata.get_ddl ('USER', '&&Username')
        else  to_clob ('   -- Note: User not found!')
        end ) Extracted_DDL from dual
UNION ALL
select (case
        when ((select count(*)
               from   dba_ts_quotas
               where  username = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&&Username')
        else  to_clob ('   -- Note: No TS Quotas found!')
        end ) from dual
UNION ALL
select (case
        when ((select count(*)
               from   dba_role_privs
               where  grantee = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&&Username')
        else  to_clob ('   -- Note: No granted Roles found!')
        end ) from dual
UNION ALL
select (case
        when ((select count(*)
               from   dba_sys_privs
               where  grantee = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&&Username')
        else  to_clob ('   -- Note: No System Privileges found!')
        end ) from dual
UNION ALL
select (case
        when ((select count(*)
               from   dba_tab_privs
               where  grantee = '&&Username') > 0)
        then  dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&&Username')
        else  to_clob ('   -- Note: No Object Privileges found!')
        end ) from dual
/

No comments: