Page 1 of 1

Clone User

Posted: Tue Jan 29, 2013 2:22 pm
by Josh
Hi there,

Is there an easy way to create a new user with the exact same attributes as a current user? Well, everything except the password ;)

Thank you.

Re: Clone User

Posted: Wed Jan 30, 2013 6:05 pm
by JimKnicely
Hi Josh,

You should be able to use SQL to generate SQL statements that when executed will create another user based on some other user ...

This code should help out and get you started. Just replace the following variables:
  • <<newuser>> with the user you want to create
    <<newuser_password>> with the password of the new user you want to created
    <<user2clone>> with the user you want to clone

Code: Select all

SELECT 'CREATE USER <<newuser>> IDENTIFIED BY ''<<newuser_password>>'' ACCOUNT ' ||
          DECODE(is_locked, 't', 'lock', 'unlock') ||
          DECODE(memory_cap_kb, 'unlimited', '', ' MEMORYCAP ''' || memory_cap_kb, 'K''') ||
          ' PROFILE ' || profile_name ||
          ' RESOURCE POOL ' || resource_pool ||
          DECODE(run_time_cap, 'unlimited', '', ' RUNTIMECAP ''' || run_time_cap, 'K''') ||
          DECODE(temp_space_cap_kb, 'unlimited', '', ' TEMPSPACECAP ''' || temp_space_cap_kb, 'K''') || ';'
  FROM users
 WHERE user_name = '<<user2clone>>'
 UNION ALL
SELECT 'GRANT USAGE ON RESOURCE POOL ' || resource_pool || ' TO <<newuser>> ;'
  FROM users
 WHERE user_name = '<<user2clone>>'
 UNION ALL
SELECT 'GRANT ' || name || ' TO <<newuser>>' || CASE WHEN INSTR(all_roles, name || '*') > 0 THEN ' WITH GRANT OPTION' ELSE '' END || ';'
  FROM users
  JOIN roles
    ON INSTR(all_roles, name) > 0
 WHERE user_name = '<<user2clone>>'
 UNION ALL
(SELECT 'GRANT ' || name || ' ON ' || DECODE(name, 'USAGE', 'SCHEMA ', object_schema || '.') || object_name || ' TO <<newuser>>' ||
          CASE WHEN INSTR(privileges_description, name || '*') > 0 THEN ' WITH ADMIN OPTION' ELSE '' END || ';'
   FROM grants
   JOIN (SELECT 'USAGE'
          UNION
         SELECT 'INSERT'
          UNION
         SELECT 'SELECT'
          UNION
         SELECT 'UPDATE'
          UNION
         SELECT 'DELETE'
          UNION
         SELECT 'REFERENCES') AS foo (name)
     ON INSTR(privileges_description, name) > 0
    AND object_name <> 'general'
  WHERE grantee = '<<user2clone>>'
  ORDER BY 1 DESC);
I hope this helps!

Re: Clone User

Posted: Fri Feb 01, 2013 12:30 am
by Josh
Hey, that's pretty smart, Jim! Thank you! I like how you handled the 'WITH GRANT OPTION".

I've used MySQL a lot and it has a nice command called SHOW GRANTS FOR USER command that gives us the info. your query provides, only I think yours does better :lol:

Re: Clone User - UPDATE!

Posted: Tue Dec 31, 2013 2:10 pm
by JimKnicely
We can include the schema SEARCH_PATH also:

Code: Select all

SELECT 'CREATE USER <<newuser>> IDENTIFIED BY ''<<newuser_password>>'' ACCOUNT ' ||
          DECODE(is_locked, 't', 'lock', 'unlock') ||
          DECODE(memory_cap_kb, 'unlimited', '', ' MEMORYCAP ''' || memory_cap_kb, 'K''') ||
          ' PROFILE ' || profile_name ||
          ' RESOURCE POOL ' || resource_pool ||
          DECODE(run_time_cap, 'unlimited', '', ' RUNTIMECAP ''' || run_time_cap, 'K''') ||
          DECODE(temp_space_cap_kb, 'unlimited', '', ' TEMPSPACECAP ''' || temp_space_cap_kb, 'K''') ||
          ' SEARCH_PATH ' || search_path || ';'
  FROM users
 WHERE user_name = '<<user2clone>>'
 UNION ALL
SELECT 'GRANT USAGE ON RESOURCE POOL ' || resource_pool || ' TO <<newuser>> ;'
  FROM users
 WHERE user_name = '<<user2clone>>'
 UNION ALL
SELECT 'GRANT ' || name || ' TO <<newuser>>' || CASE WHEN INSTR(all_roles, name || '*') > 0 THEN ' WITH ADMIN OPTION' ELSE '' END || ';'
  FROM users
  JOIN roles
    ON INSTR(all_roles, name) > 0
 WHERE user_name = '<<user2clone>>'
 UNION ALL
(SELECT 'GRANT ' || name || ' ON ' || DECODE(name, 'USAGE', 'SCHEMA ', object_schema || '.') || object_name || ' TO <<newuser>>' ||
          CASE WHEN INSTR(privileges_description, name || '*') > 0 THEN ' WITH GRANT OPTION' ELSE '' END || ';'
   FROM grants
   JOIN (SELECT 'USAGE'
          UNION
         SELECT 'INSERT'
          UNION
         SELECT 'SELECT'
          UNION
         SELECT 'UPDATE'
          UNION
         SELECT 'DELETE'
          UNION
         SELECT 'REFERENCES') AS foo (name)
     ON INSTR(privileges_description, name) > 0
    AND object_name <> 'general'
  WHERE grantee = '<<user2clone>>'
  ORDER BY 1 DESC);

Re: Clone User

Posted: Fri Jan 08, 2016 4:24 pm
by JimKnicely
Hi All,

I made the following corrections to the above scripts:

This line:

Code: Select all

SELECT 'GRANT ' || name || ' TO <<newuser>>' || CASE WHEN INSTR(all_roles, name || '*') > 0 THEN ' WITH GRANT OPTION' ELSE '' END || ';'
was change to:

Code: Select all

SELECT 'GRANT ' || name || ' TO <<newuser>>' || CASE WHEN INSTR(all_roles, name || '*') > 0 THEN ' WITH ADMIN OPTION' ELSE '' END || ';'
Thanks #Victorgm for letting me know about the error!