a generated schema can be easily assigned to the application.
The following query lists statements for objects of a schema that can be created as synonyms or views in a new schema.
Tables and views are mapped to views with identical names. This allows APEX programs to check the column names and types. The list must still be filtered to the actually required objects to keep the privileges minimal.
this query on catalog views generates the required statements to publish a database schema in a second schema. This is the result of my own research:
--------------------------------------------------------------------------------
WITH PA AS ( -- Parameters
SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') || '_APP' GRANTEE,
SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') OWNER
FROM DUAL
),
PKEY_Q AS (
-- Add primary key constraints to views. This will enable to APEX Builder to detect the Primary key for forms and reports
SELECT
C.TABLE_NAME, C.OWNER TABLE_OWNER,
', CONSTRAINT ' || Dbms_Assert.Enquote_Name(C.CONSTRAINT_NAME) || ' PRIMARY KEY ('
|| LISTAGG(Dbms_Assert.Enquote_Name(C.COLUMN_NAME), ',') WITHIN GROUP (ORDER BY C.POSITION)
|| ') RELY DISABLE' PKEY_CONS
FROM SYS.USER_CONSTRAINTS B
JOIN SYS.USER_CONS_COLUMNS C ON C.TABLE_NAME = B.TABLE_NAME AND C.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND C.OWNER = B.OWNER
WHERE B.CONSTRAINT_TYPE = 'P'
AND B.OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')
AND B.TABLE_NAME NOT LIKE 'DR$%$_' -- skip fulltext index
AND B.TABLE_NAME NOT LIKE 'BIN$%' -- this table is in the recyclebin
GROUP BY C.TABLE_NAME, C.OWNER, C.CONSTRAINT_NAME
),
COLS_Q AS (
-- List of Column alias names for views
SELECT TABLE_NAME, LISTAGG(Dbms_Assert.Enquote_Name(COLUMN_NAME), ', ') WITHIN GROUP (ORDER BY COLUMN_ID) TAB_COLUMNS
FROM SYS.USER_TAB_COLUMNS
GROUP BY TABLE_NAME
),
STATS_Q AS (
-- views
-------------------------
SELECT 'GRANT ' || PRIVS || ' ON ' || S.OWNER || '.' || S.VIEW_NAME || ' TO ' || PA.GRANTEE GRANT_STAT,
'CREATE OR REPLACE VIEW ' || PA.GRANTEE || '.' || S.VIEW_NAME
|| ' (' || S.TAB_COLUMNS || S.PKEY_CONS || ')'
|| ' AS SELECT * FROM ' || S.OWNER || '.' || S.VIEW_NAME CREATE_STAT,
'REVOKE ' || PRIVS || ' ON ' || S.OWNER || '.' || S.VIEW_NAME || ' FROM ' || PA.GRANTEE REVOKE_STAT,
'DROP VIEW ' || PA.GRANTEE || '.' || S.VIEW_NAME DROP_STAT,
S.OWNER, S.VIEW_NAME OBJECT_NAME,
'VIEW' OBJECT_TYPE,
'VIEW' DEST_OBJECT_TYPE,
ADMIN_GRANT_STAT
FROM (
SELECT CASE WHEN DG.FOREIGN_DEPS_CNT > 0
THEN NVL(DG.PRIVILEGE, 'READ')
ELSE
'SELECT' ||
CASE WHEN TR.PRIVS IS NOT NULL
THEN ', ' || TR.PRIVS
ELSE T.UPDATABLE || T.INSERTABLE || T.DELETABLE
END
END PRIVS,
NG.ADMIN_GRANT_STAT,
T.VIEW_NAME, T.OWNER,
P.PKEY_CONS, C.TAB_COLUMNS
FROM (
SELECT V.VIEW_NAME, T.OWNER,
MAX(CASE WHEN UPDATABLE = 'YES' THEN ', UPDATE' END) UPDATABLE,
MAX(CASE WHEN INSERTABLE = 'YES' THEN ', INSERT' END) INSERTABLE,
MAX(CASE WHEN DELETABLE = 'YES' THEN ', DELETE' END) DELETABLE
FROM SYS.USER_UPDATABLE_COLUMNS T
JOIN SYS.USER_VIEWS V ON V.VIEW_NAME = T.TABLE_NAME
GROUP BY V.VIEW_NAME, T.OWNER
) T LEFT OUTER JOIN ( -- grantable dependent object privileges
-- when a view is accessing other foreign schema view, then is view has READ-only access
SELECT NAME, COUNT(*) FOREIGN_DEPS_CNT,
LISTAGG(PRIVILEGE, ', ') WITHIN GROUP (ORDER BY PRIVILEGE) PRIVILEGE
FROM (
SELECT DISTINCT D.NAME, PRI.PRIVILEGE
FROM SYS.USER_DEPENDENCIES D
JOIN SYS.ALL_TAB_PRIVS PRI
ON PRI.table_Schema = D.REFERENCED_OWNER
AND PRI.table_Name = D.REFERENCED_NAME
AND PRI.grantee IN ('PUBLIC', SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'))
WHERE D.TYPE = 'VIEW'
AND D.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'SYNONYM')
AND D.REFERENCED_OWNER NOT IN ('PUBLIC', SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')) -- dependent on object in foreign schema
AND PRI.GRANTABLE = 'YES'
)
GROUP BY NAME
) DG ON T.VIEW_NAME = DG.NAME
LEFT OUTER JOIN ( -- not grantable dependent object privileges
SELECT NAME, COUNT(*) FOREIGN_DEPS_CNT,
LISTAGG(CASE WHEN NOT_GRANTABLE > 0
THEN 'GRANT ' || PRIVILEGE || ' ON ' || REFERENCED_OWNER || '.' || REFERENCED_NAME
|| ' TO ' || SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') || ' WITH GRANT OPTION;'
END, chr(10)) WITHIN GROUP (ORDER BY PRIVILEGE) ADMIN_GRANT_STAT
FROM (
SELECT D.NAME, D.REFERENCED_OWNER, D.REFERENCED_NAME,
LISTAGG(PRI.PRIVILEGE, ', ') WITHIN GROUP (ORDER BY PRIVILEGE) PRIVILEGE,
COUNT(*) NOT_GRANTABLE
FROM SYS.USER_DEPENDENCIES D
JOIN SYS.ALL_TAB_PRIVS PRI
ON PRI.table_Schema = D.REFERENCED_OWNER
AND PRI.table_Name = D.REFERENCED_NAME
AND PRI.grantee IN ('PUBLIC', SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'))
WHERE D.TYPE = 'VIEW'
AND D.REFERENCED_TYPE IN ('VIEW', 'TABLE', 'SYNONYM')
AND D.REFERENCED_OWNER NOT IN ('PUBLIC', SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')) -- dependent on object in foreign schema
AND PRI.GRANTABLE = 'NO'
GROUP BY D.NAME, D.REFERENCED_OWNER, D.REFERENCED_NAME
)
GROUP BY NAME
) NG ON T.VIEW_NAME = NG.NAME
LEFT OUTER JOIN ( -- updatable views with INSTEAD OF trigger
SELECT REGEXP_REPLACE(
LISTAGG(TRIGGERING_EVENT, ', ') WITHIN GROUP (ORDER BY TRIGGERING_EVENT),
'\sOR\s', ', ') PRIVS,
T.TABLE_NAME VIEW_NAME
FROM SYS.USER_TRIGGERS T
WHERE TRIGGER_TYPE = 'INSTEAD OF'
AND BASE_OBJECT_TYPE = 'VIEW'
GROUP BY TABLE_NAME
) TR ON T.VIEW_NAME = TR.VIEW_NAME
LEFT OUTER JOIN PKEY_Q P ON T.VIEW_NAME = P.TABLE_NAME
LEFT OUTER JOIN COLS_Q C ON T.VIEW_NAME = C.TABLE_NAME
) S, PA
WHERE PA.GRANTEE IS NOT NULL
UNION ALL
-- normal tables
----------------
SELECT
'GRANT ' || PRIVS || ' ON '
|| PA.OWNER || '.' || TABLE_NAME || ' TO ' || PA.GRANTEE GRANT_STAT,
'CREATE OR REPLACE VIEW ' || PA.GRANTEE || '.' || T.TABLE_NAME
|| ' (' || T.TAB_COLUMNS || T.PKEY_CONS || ')'
|| ' AS SELECT * FROM ' || PA.OWNER || '.' || T.TABLE_NAME CREATE_STAT,
'REVOKE ' || PRIVS || ' ON '
|| PA.OWNER || '.' || TABLE_NAME || ' FROM ' || PA.GRANTEE REVOKE_STAT,
'DROP VIEW ' || PA.GRANTEE || '.' || T.TABLE_NAME DROP_STAT,
PA.OWNER, T.TABLE_NAME OBJECT_NAME,
'TABLE' OBJECT_TYPE,
'VIEW' DEST_OBJECT_TYPE,
'' ADMIN_GRANT_STAT
FROM (
SELECT T.TABLE_NAME,
CASE WHEN READ_ONLY = 'NO'
AND NOT EXISTS ( -- when this table is part of materialized view, then only SELECT allowed
SELECT 1
FROM USER_OBJECTS MV
WHERE MV.OBJECT_NAME = T.TABLE_NAME
AND MV.OBJECT_TYPE = 'MATERIALIZED VIEW'
) THEN 'SELECT, UPDATE, INSERT, DELETE '
ELSE 'SELECT '
END PRIVS,
P.PKEY_CONS, C.TAB_COLUMNS
FROM SYS.USER_TABLES T
LEFT OUTER JOIN PKEY_Q P ON T.TABLE_NAME = P.TABLE_NAME
LEFT OUTER JOIN COLS_Q C ON T.TABLE_NAME = C.TABLE_NAME
WHERE T.IOT_NAME IS NULL -- skip overflow tables of index organized tables
AND T.TABLE_NAME NOT LIKE 'DR$%$_' -- skip fulltext index
) T, PA
WHERE PA.GRANTEE IS NOT NULL
AND NOT EXISTS ( -- this table is not part of materialized view log
SELECT --+ NO_UNNEST
1
FROM SYS.ALL_MVIEW_LOGS MV
WHERE MV.LOG_TABLE = T.TABLE_NAME
AND MV.LOG_OWNER = PA.OWNER
)
UNION ALL
-- Function, Procedure, Package, Type
-------------------------------
SELECT 'GRANT EXECUTE ON ' || PA.OWNER || '.' || T.OBJECT_NAME || ' TO ' || PA.GRANTEE GRANT_STAT,
'CREATE OR REPLACE SYNONYM ' || PA.GRANTEE || '.' || OBJECT_NAME
|| ' FOR ' || PA.OWNER || '.' || OBJECT_NAME CREATE_STAT,
'REVOKE EXECUTE ON ' || PA.OWNER || '.' || T.OBJECT_NAME || ' FROM ' || PA.GRANTEE REVOKE_STAT,
'DROP SYNONYM ' || PA.GRANTEE || '.' || OBJECT_NAME DROP_STAT,
PA.OWNER, T.OBJECT_NAME,
T.OBJECT_TYPE,
'SYNONYM' DEST_OBJECT_TYPE,
'' ADMIN_GRANT_STAT
FROM SYS.USER_OBJECTS T, PA
WHERE PA.GRANTEE IS NOT NULL
AND T.OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE','TYPE')
UNION ALL -- Sequences
SELECT 'GRANT SELECT ON ' || PA.OWNER || '.' || T.SEQUENCE_NAME || ' TO ' || PA.GRANTEE GRANT_STAT,
'CREATE OR REPLACE SYNONYM ' || PA.GRANTEE || '.' || SEQUENCE_NAME
|| ' FOR ' || PA.OWNER || '.' || SEQUENCE_NAME CREATE_STAT,
'REVOKE SELECT ON ' || PA.OWNER || '.' || T.SEQUENCE_NAME || ' FROM ' || PA.GRANTEE REVOKE_STAT,
'DROP SYNONYM ' || PA.GRANTEE || '.' || SEQUENCE_NAME DROP_STAT,
PA.OWNER, T.SEQUENCE_NAME OBJECT_NAME,
'SEQUENCE' OBJECT_TYPE,
'SYNONYM' DEST_OBJECT_TYPE,
'' ADMIN_GRANT_STAT
FROM SYS.USER_SEQUENCES T, PA
WHERE PA.GRANTEE IS NOT NULL
)
SELECT S.OWNER, S.OBJECT_NAME, S.OBJECT_TYPE, S.DEST_OBJECT_TYPE,
S.ADMIN_GRANT_STAT,
case when EXISTS (
SELECT 1
FROM SYS.ALL_DEPENDENCIES D
WHERE D.OWNER = PA.GRANTEE
AND D.NAME = S.OBJECT_NAME
and D.REFERENCED_OWNER= S.OWNER
and D.REFERENCED_NAME = S.OBJECT_NAME
AND D.REFERENCED_TYPE = S.OBJECT_TYPE
) or EXISTS (
SELECT 1
FROM SYS.ALL_OBJECTS OBJ
WHERE OBJ.OWNER = PA.GRANTEE
AND OBJ.OBJECT_NAME = S.OBJECT_NAME
AND OBJ.OBJECT_TYPE = S.DEST_OBJECT_TYPE
) then 'Y' else 'N'
end DEST_OBJECT_EXISTS,
case when EXISTS (
SELECT 1
FROM SYS.ALL_SYNONYMS SYN
WHERE SYN.SYNONYM_NAME = S.OBJECT_NAME
AND SYN.OWNER = 'PUBLIC'
) then 'Y' else 'N'
end CONFLICTING_OBJECT_EXISTS,
PA.GRANTEE DEST_SCHEMA,
S.GRANT_STAT, S.CREATE_STAT, S.REVOKE_STAT, S.DROP_STAT
FROM STATS_Q S, PA;
