Skip to Main Content

SQL & PL/SQL

DBMS JOBS fails inside DDL trigger

854200Apr 27 2011 — edited Apr 27 2011
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 25 2011
Added on Apr 27 2011
2 comments
91 views