PL/SQL (MOSC)

MOSC Banner

Create trigger after schema table create to update grant select to role/users

edited May 16, 2018 10:56AM in PL/SQL (MOSC) 5 commentsAnswered ✓

Good-day,

I am using the following procedure which works when executed per the following:

CREATE or replace PROCEDURE SELECT_ALL_ODS_PROCEDURE AS

BEGIN

DECLARE

  CURSOR cur_objects IS

    SELECT

      owner       AS obj_owner,

      object_name AS obj_name,

      object_type AS obj_type

    FROM

      SYS.dba_objects

    WHERE

      owner IN ('ODS')

      AND object_type = 'TABLE'

      AND status = 'VALID'

      AND object_name NOT LIKE 'SYS/_%' ESCAPE '/';

  v_count NUMBER := 0;

  v_fail  NUMBER := 0;

BEGIN

  FOR obj IN cur_objects LOOP

  BEGIN

    EXECUTE IMMEDIATE 'GRANT SELECT ON '||obj.obj_owner||'.'||obj.obj_name||' TO SELECT_ALL_ODS';

Tagged:

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