Create trigger after schema table create to update grant select to role/users
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';