Skip to content

Instantly share code, notes, and snippets.

@den-kozlov

den-kozlov/oracle_copy_table_grants.sql

Last active July 23, 2021 15:29
Show Gist options
  • Save den-kozlov/88d7a7418f8a759d736d58bbe84abfd8 to your computer and use it in GitHub Desktop.
Save den-kozlov/88d7a7418f8a759d736d58bbe84abfd8 to your computer and use it in GitHub Desktop.
Download ZIP
ORACLE: copy grants for one table to another
Raw
oracle_copy_table_grants.sql
SELECT LISTAGG (
'GRANT '
|| PRIVILEGE
|| ' ON '
|| :TARGET_TABLE_OWNER
|| '.'
|| :TARGET_TABLE_NAME
|| ' TO '
|| GRANTEE,
'; ' || CHR (10))
WITHIN GROUP (ORDER BY GRANTEE, PRIVILEGE) || ';'
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = :SOURCE_TABLE_NAME
AND OWNER = :SOURCE_TABLE_OWER;
@andreasneuman
Copy link

Another way to copy table in Oracle in this article https://www.devart.com/dbforge/oracle/studio/oracle-copy-table.html

@den-kozlov
Copy link
Author

Another way to copy table in Oracle in this article https://www.devart.com/dbforge/oracle/studio/oracle-copy-table.html

Thanks, but this gist is not about copying table's data, but to create a scripts that grants same privileges to the same users/groups as for source table.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment