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