0

I want to create a trigger for CRE schema. I'm trying to create this trigger with SYS user but I have insufficient privileges error.

My trigger:

CREATE OR REPLACE TRIGGER CRE.TRIGGER_NAME
AFTER LOGON ON DATABASE
DECLARE
asd1 NUMBER;
asd2 NUMBER;
asd3 NUMBER;
asd4 NUMBER;
asd NUMBER;
qwe VARCHAR2(200);
qaz VARCHAR2(200);
ewq NUMBER;
zaq VARCHAR2(50);
xsw VARCHAR2(50);
BEGIN
CRE.SOME_PROCEDURE_NAME('S', SYSDATE);
END;
/

How can it possible with SYS user?

How can I fix it?

Best regards.

2

Even if you create the trigger as SYS, the owner must have the CREATE TRIGGER privilege:

GRANT CREATE TRIGGER TO CRE;

In addition to the above, to create an AFTER LOGON ON DATABASE trigger, the ADMINISTER DATABASE TRIGGER privilege is also required:

GRANT ADMINISTER DATABASE TRIGGER TO CRE;

| improve this answer | |
  • It works. Thank you Balazs. – jrdba Aug 4 at 1:57
0

Can the CRE account execute the "SOME_PROCEDURE_NAME" procedure?

You are creating a trigger that will be owned by the user CRE and, by default, it will be created with DEFINER Rights.

Anything and everything that you want to do within that trigger must be "possible" by the CRE user itself and any such permissions must be granted directly to the user. It cannot gain these permissions through the application of Roles (because the account exists independently of any Roles that it may or may not be granted to it).

| improve this answer | |
  • 1
    While this is true, this would result in a different error, and the trigger could be created without it, it just could not be compiled. Also CRE does not need extra privilege to execute its on procedure. – Balazs Papp Aug 3 at 11:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.