Database Administration (MOSC)

MOSC Banner

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center