Creating tables metadata catalog table
Hi team,
We are trying to implement less privilege approach for particular developer group in our Oracle 19c data warehouse database. Currently developers do have select any table and there are many tables across many schemas with sensitive data.
One of ideas is to create for them table which will going to hold tables metadata for specific schemas only, so that they will not have any restrictions regarding viewing table DDL's.
I have created stored procedure like:
CREATE OR REPLACE PROCEDURE extract_ddl
AUTHID CURRENT_USER
as
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE test.get_ddl purge';
EXECUTE IMMEDIATE 'create table test.get_ddl as (SELECT owner, table_name,DBMS_METADATA.get_ddl (''TABLE'', table_name, owner) ddl