Database Administration (MOSC)

MOSC Banner

After Create on database trigger not working, help?

edited Mar 30, 2015 2:52PM in Database Administration (MOSC) 2 commentsAnswered

I am attempting to create an AFTER CREATE on database trigger to assign DML privileges to different schemas.

For the test I have 3 roles:  RoleNum1, RoleNum2, RoleNum3

create role RoleNum1;

Create role RoleNum2;

Create role RoleNum3;


I have multiple schemas in a database (In the test I am using only 2 schemas)

schemao1
schemao2

A procedure is called by the trigger to assign the DML permissions to the roles:

create or replace PROCEDURE execute_app_ddl (p_ddl IN VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE p_ddl;
EXCEPTION
           WHEN OTHERS THEN
                RAISE_APPLICATION_ERROR(-20000, p_ddl);
END execute_app_ddl;


create or replace TRIGGER SCHEMA_GRANT_Test1

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