Oracle 12c not sufficient privileges on trigger creation

Question

I want to execute code on user IDK:

CREATE OR REPLACE TRIGGER sal_trig
  AFTER UPDATE OF status ON TAB1
  FOR EACH ROW
  WHEN (new.status = 'ZAK')
CALL log_sal(1, 5, 8);

I have following grants:

GRANT CREATE PROCEDURE TO IDK;
GRANT CREATE SEQUENCE TO IDK;
GRANT CREATE TABLE TO IDK;
GRANT CREATE TRIGGER TO IDK;
GRANT CREATE TYPE TO IDK;
GRANT UNLIMITED TABLESPACE TO IDK;
GRANT SELECT ON TAB1 TO IDK;

What grants i need more? I won't get update/delete/insert on TAB1. I am getting error: not sufficient privileges.

I created procedure from user IDK:

CREATE OR REPLACE PROCEDURE log_sal (
  emp_id NUMBER,
  old_sal NUMBER,
  new_sal NUMBER
)
AS LANGUAGE JAVA
NAME 'CaseWatch.logSal(int, float, float)';

@Mureinik `log_sal` is procedure created by me. I have updated the question with the code. — tryingHard, Jun 20 '18 at 14:12
@Mureinik `procedure` created by user - `IDK`. But `IDK` does not own table `TAB1`. He can make select on it via `grant`. — tryingHard, Jun 20 '18 at 14:14

score 4 · Accepted Answer · answered Jun 20 '18 at 16:22

According to your comments the TAB1 table created by different User, so the table is in a different schema, this is the main key. When you want to grant privileges to create a trigger on a table in different schema then you need to use:

GRANT CREATE ANY TRIGGER TO IDK;

CREATE TRIGGER => in fact giving permission to create a database trigger in the grantee's schema, in your case if TAB1 created by IDK then this privilege is enough.

Regarding the CREATE ANY TRIGGER here you can find some more interesting info:

Oracle 12c not sufficient privileges on trigger creation

1 Answers1