2010-10-11

How to copy the grants given to a user to another

Suppose that you have a user USER_BASE that grant rights to USER1 and you want to grant those same rights to USER2.
You can use the following script in USER_BASE (change USER1 and USER2 literals for your user names) and run the output in USER_BASE.

SELECT 'grant '
|| privilege
|| ' ON '
|| owner
|| '.'
|| table_name
|| ' TO '
|| 'USER2'
|| CASE WHEN grantable = 'YES' THEN ' WITH GRANT OPTION' END
|| CASE WHEN hierarchy = 'YES' THEN ' WITH HIERARCHY OPTION' END
|| ';'
FROM all_tab_privs_made
WHERE grantor = USER AND grantee = 'USER1'

No comments:

Post a Comment

Os comentários são moderados.
The comments are moderated.