(ref) System Event Triggers


System Event Triggers

About:
  • Created on either a schema or the database
  • A SCHEMA trigger is created on a schema and fires whenever the user who owns it is the current user and initiates the triggering event.
  • A DATABASE trigger is created on the database and fires whenever any database user initiates the triggering event.

Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} database_event ON {database | schema}
[DECLARE]
declaration_statements;
BEGIN
execution_statements;
[EXCEPTION ...]
END [trigger_name];
/


Valid events are listed below.
  • DDL Events: ALTER, ANALYZE, ASSOCIATE STATISTICS, AUDIT, COMMENT, CREATE, DISASSOCIATE STATISTICS, DROP, GRANT, NOAUDIT, RENAME, REVOKE, TRUNCATE, DDL
  • Database Events: AFTER STARTUP, BEFORE SHUTDOWN, AFTER DB_ROLE_CHANGE, AFTER SERVERERROR, AFTER LOGON, BEFORE LOGOFF, AFTER SUSPEND.


Trigger on Schema
- The trigger fires to stop any drop of DB objects belonging to the HR schema.
CREATE OR REPLACE TRIGGER stop_drop_tg
 BEFORE DROP ON hr.SCHEMA
BEGIN
  RAISE_APPLICATION_ERROR (
    num => -20000,
    msg => 'Cannot drop object');
END;
/

Trigger on Database
- When user connects, the trigger runs the connecting procedure in the user_connecting package.
- Before logoff the procedure disconnecting is executed.
CREATE OR REPLACE TRIGGER connect_tg
AFTER LOGON ON DATABASE
BEGIN
user_connection.connecting(sys.login_user);
END;
/

CREATE OR REPLACE TRIGGER disconnect_tg
BEFORE LOGOFF ON DATABASE
BEGIN
user_connection.disconnecting(sys.login_user);
END;
/

Providing Fine-Grained Access Control

As suggested in the examples above, you can also use LOGON triggers to run a package associated with an application context.
An application context captures session-related information about the user who is logging in to the database. From there, your application can control how much access this user has, based on his or her session information.

Note:
If you have very specific logon requirements, such as preventing users from logging in from outside the firewall or after work hours, consider using Oracle Database Vault instead of LOGON triggers. With Oracle Database Vault, you can create custom rules to strictly control user access.

No comments:

Post a Comment