Clone User

Moderator: NorbertKrupa

Post Reply
User avatar
Josh
Intermediate
Intermediate
Posts: 106
Joined: Thu Jan 26, 2012 9:38 pm

Clone User

Post by Josh » Tue Jan 29, 2013 2:22 pm

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.
Thank you!
Joshua

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1815
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Clone User

Post by JimKnicely » Wed Jan 30, 2013 6:05 pm

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!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
Josh
Intermediate
Intermediate
Posts: 106
Joined: Thu Jan 26, 2012 9:38 pm

Re: Clone User

Post by Josh » Fri Feb 01, 2013 12:30 am

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:
Thank you!
Joshua

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1815
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Clone User - UPDATE!

Post by JimKnicely » Tue Dec 31, 2013 2:10 pm

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);
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1815
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Clone User

Post by JimKnicely » Fri Jan 08, 2016 4:24 pm

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!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica Database Administration”