Grant permission to trigger from owner account

Question

I have a trigger STUDENT_DATA_UPDATE in my two oracle accounts. One is owner account and another is user account. Below are the details.

Owner account which has all the privileges:

<connection-url>jdbc:oracle:thin:@eu.national.com:15001/STUD</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>NATIONAL_OWN</user-name>
<password>********</password>

USER account which has no privileges:

<connection-url>jdbc:oracle:thin:@eu.national.com:15001/STUD</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>NATIONAL_USR</user-name>
<password>********</password>

I am able to change ,Disable or enable the trigger from OWNER account which has permissions. But i am not able to do any changes to the trigger from USER account. My question here is, I want to grant the permission to change the trigger from NATIONAL_OWN to NATIONAL_USR account which can disable and enable the same trigger in it.

Answer 1

A user can only enable or disable another user's trigger if they are granted the powerful alter any trigger privilege, which is probably overkill and dangerous. You could restrict it with a DDL trigger but that just gets more complicated.

You could create wrapper procedures, possibly in a package, e.g. in the NATIONAL_OWN schema:

create package trigger_pkg as
  procedure disable_trigger;
  procedure enable_trigger;
end trigger_pkg;
/

create package body trigger_pkg as
  procedure disable_trigger is
  begin
    execute immediate 'alter trigger STUDENT_DATA_UPDATE disable';
  end disable_trigger;

  procedure enable_trigger is
  begin
    execute immediate 'alter trigger STUDENT_DATA_UPDATE enable';
  end enable_trigger;
end trigger_pkg;
/

grant execute on trigger_pkg to NATIONAL_USR;

Then as NATIONAL_USR you can do:

begin
    NATIONAL_OWN.trigger_pkg.disable_trigger;
end;
/

and

begin
    NATIONAL_OWN.trigger_pkg.enable_trigger;
end;
/

You could have one procedure with an enable/disable flag instead of you prefer.

Is there a way where i could actually call these procedures disable_trigger and enable_trigger from a java code.

You can use a CallableStatement, either with the anonymous block syntax:

stmt = conn.prepareCall("begin NATIONAL_OWN.trigger_pkg.disable_trigger; end;");
stmt.execute();

or the ANSI syntax:

stmt = conn.prepareCall("{ call NATIONAL_OWN.trigger_pkg.disable_trigger }");
stmt.execute();