Oracle オブジェクト権限取得(テーブルなどのGRANT)

get_ddl_grant_objects_privs

-- #!/bin/sh
-- #@(#) \$Id get_ddl_grant_objects_privs.sh, v1.0
-- #
-- # su - oracle
-- # export ORACLE_SID=xxxx
-- # export ORACLE_HOME=/opt/app/oracle/product/12.2.0
-- #
-- # ../sql/get_ddl_grant_objects_privs.sh
-- #
-- #export NLS_LANG=JAPANESE_JAPAN.AL32UTF8
-- ${ORACLE_HOME}/bin/sqlplus / as sysdba << EOF
--
-- オブジェクト権限取得(テーブルなどのGRANT)
--
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET LINESIZE 100
SET PAGESIZE 50000
SET TAB OFF
SET TERMOUT OFF
SET TRIMSPOOL ON
-- SPOOL pass includes CONNECT_IDENTIFIER, USER_NAME and SYSDATE
COLUMN USER_NAME    NEW_VALUE USER_NAME
COLUMN SYSTEM_DATE  NEW_VALUE SYSTEM_DATE
SELECT USER || '_' AS USER_NAME
     , TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS SYSTEM_DATE
FROM DUAL ;
SPOOL get_ddl_grant_objects_privs_&&_CONNECT_IDENTIFIER._&&USER_NAME&&SYSTEM_DATE..sql
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- SPOOL get_ddl_grant_objects_privs_\&&_CONNECT_IDENTIFIER._\&&USER_NAME\&&SYSTEM_DATE..sql
COLUMN GRANTS  FORMAT A100
SELECT
    '-- FROM ' || TP.GRANTOR || ' or OWNER:' || TP.OWNER || CONST.LF
 || 'GRANT ' || RPAD(TP.PRIVILEGE, 10) || ' ON ' || RPAD(TP.TABLE_NAME, 30)
 || ' TO ' || TP.GRANTEE || ' ;' AS GRANTS
FROM(
    SELECT COLUMN_VALUE AS OWNER FROM TABLE(PKG_USERS_SCHEMA.GET_USERS_SCHEMA) -- ユーザーが作成したスキーマのみ
    ) MS
INNER JOIN DBA_TAB_PRIVS TP  -- http://docs.oracle.com/cd/E57425_01/121/REFRN/GUID-4AC57A96-FF55-4788-9301-AFED23AE3934.htm  reference
ON  TP.GRANTEE = MS.OWNER
ORDER BY
    TP.GRANTEE
  , TP.OWNER
  , TP.TABLE_NAME
  , TP.PRIVILEGE ;
SPOOL OFF
COLUMN GRANTS  CLEAR
SET ECHO ON
SET FEEDBACK ON
SET HEADING ON
SET LINESIZE 80
SET TERMOUT ON
-- exit
-- EOF
-- #export NLS_LANG=en_US
-- # ---------------------- End of get_ddl_grant_objects_privs.sh ------------------------------

参考
Oracle 純粋にユーザー側のスキーマのみを取得
Oracle 定数