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.
Clone User
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Clone User
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:
I hope this helps!
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);
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Clone User
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
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
Thank you!
Joshua
Joshua
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Clone User - UPDATE!
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Clone User
Hi All,
I made the following corrections to the above scripts:
This line:
was change to:
Thanks #Victorgm for letting me know about the error!
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 || ';'
Code: Select all
SELECT 'GRANT ' || name || ' TO <<newuser>>' || CASE WHEN INSTR(all_roles, name || '*') > 0 THEN ' WITH ADMIN OPTION' ELSE '' END || ';'
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.