Forum Stats

  • 3,872,065 Users
  • 2,266,376 Discussions
  • 7,911,043 Comments

Discussions

DBMS JOBS fails inside DDL trigger

854200
854200 Member Posts: 10
edited Apr 27, 2011 6:04PM in SQL & PL/SQL
Hi,

I am posting this again, since I need a solution, to rectify the trigger in production.


Dom Brooks , thanks for replying, but can you let me know how to modify the trigger? the DBMS_JOBS part.
I am working on a project to create roles for each schema in the DB and assign the tables in those schemas to the roles. So suppose a new table is added, then the roles will become redundant. So I have created the following trigger

I have a DDL Trigger through which I am granting SELECT on newly created tables to existing read-only roles
CREATE OR REPLACE TRIGGER DBADMIN.grant_permission_trigger
AFTER CREATE
ON DATABASE
DECLARE
v_owner dba_tables.owner%TYPE;
v_role dba_roles.role%TYPE;
v_object dba_tables.table_name%TYPE;
v_object_type dba_objects.object_type%TYPE;
n_temp_int INTEGER DEFAULT 0 ;
l_job NUMBER;
n_temp dba_tables.temporary%TYPE;
BEGIN
--selecting owner
SELECT ora_dict_obj_owner INTO v_owner FROM DUAL;

v_role := v_owner || '_S';

--selecting table name
SELECT ora_dict_obj_name INTO v_object FROM DUAL;

SELECT ORA_DICT_OBJ_TYPE INTO v_object_type FROM DUAL;

IF (v_object_type = 'TABLE')
THEN
SELECT COUNT (1)
INTO n_temp_int
FROM dba_roles
WHERE role = v_role;

-- if a role does not exist already, then create a role
IF n_temp_int != 0
THEN
SELECT temporary
INTO n_temp
FROM dba_tables
WHERE table_name = v_object;

IF (n_temp = 'N')
THEN
DBMS_JOB.submit (
job => l_job,
what => 'BEGIN EXECUTE IMMEDIATE ''GRANT SELECT ON '
|| v_owner
|| '.'
|| v_object
|| ' TO '
|| v_role
|| '''; END;'
);
END IF;
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
/


Please someone let me know how to modify this.
Tagged:

Answers

  • Peter Gjelstrup
    Peter Gjelstrup Member Posts: 4,128 Gold Trophy
  • kdwolf
    kdwolf Member Posts: 103
    Is it something you've been looking for?

    CREATE OR REPLACE TRIGGER DBADMIN.grant_permission_trigger
    AFTER CREATE
    ON DATABASE
    DECLARE
    v_owner dba_tables.owner%TYPE;
    v_role dba_roles.role%TYPE;
    v_object dba_tables.table_name%TYPE;
    v_object_type dba_objects.object_type%TYPE;
    n_temp_int INTEGER DEFAULT 0 ;
    l_job NUMBER;
    n_temp dba_tables.temporary%TYPE;
    BEGIN
    --selecting owner
    v_owner := ora_dict_obj_owner;
    v_role := v_owner || '_S';

    --selecting table name
    v_object := ora_dict_obj_name;
    v_object_type := ORA_DICT_OBJ_TYPE;

    IF (v_object_type = 'TABLE')
    THEN
    SELECT COUNT (1)
    INTO n_temp_int
    FROM dba_roles
    WHERE role = v_role;

    -- if a role does not exist already, then create a role
    IF n_temp_int != 0
    THEN
    SELECT temporary
    INTO n_temp
    FROM dba_tables
    WHERE table_name = v_object;

    IF (n_temp = 'N')
    THEN
    DBMS_JOB.submit (
    job => l_job,
    what => 'BEGIN EXECUTE IMMEDIATE ''GRANT SELECT ON '
    || RTRIM(v_owner)
    || '.'
    || LTRIM(v_object)
    || ' TO '
    || v_role
    || '''; END;'
    );
    END IF;
    END IF;
    END IF;
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    NULL;
    END;
    /
This discussion has been closed.