BEFORE LOGON Trigger Notes

The BEFORE LOGON trigger is a special type of trigger in Oracle, and it is part of the logon trigger functionality. It is used to define actions that are executed before a user successfully logs into the Oracle database. This type of trigger can be used for administrative tasks such as controlling user access, auditing logon attempts, enforcing security policies, or setting up session-level parameters.

Overview of BEFORE LOGON Trigger

• Purpose: The BEFORE LOGON trigger is executed before the user has logged in successfully to the Oracle database. It is often used to control or manage the login process by executing actions before any session begins.
• Scope: It is a database trigger that fires whenever a logon attempt is made, but before the login is actually allowed to proceed.
• For example, you might use it to check whether a user is allowed to log on based on certain criteria or to set up session-specific environment variables.
• When it Fires: The trigger fires before the user is authenticated and the session is established. This allows for actions such as restricting certain users from logging in or performing security checks.

Syntax of BEFORE LOGON Trigger

The syntax for creating a BEFORE LOGON trigger is as follows:

CREATE OR REPLACE TRIGGER trigger_name

BEFORE LOGON ON DATABASE

DECLARE

    -- Optional variable declarations

BEGIN

    -- Trigger logic here

END;

• trigger_name: The name of the trigger.
• BEFORE LOGON: This specifies that the trigger should fire before a user logs on to the database.
• ON DATABASE: This refers to the database as the scope for the trigger. The BEFORE LOGON trigger can only be created at the database level.
• DECLARE and BEGIN...END: Used to declare variables (if needed) and to write the logic of the trigger.

Key Features of BEFORE LOGON Trigger

1. Fires Before Authentication:
• The BEFORE LOGON trigger executes before the user is authenticated, which means the trigger can perform actions like denying access, logging an attempt, or initializing session settings.
2. Only One Trigger per Database:
• Unlike other types of triggers (e.g., AFTER DELETE), there can only be one BEFORE LOGON trigger for each database.
3. Restricted Actions:
• Access to session-specific information (such as USER or SESSION variables) is not available, because the session has not been created yet. You cannot execute SQL queries or access the session state in the same way you can in other types of triggers.
4. No Session Access:
• Since the trigger runs before the logon, you can't access or modify the session data (like session variables or privileges) directly.
5. Error Handling:
• You can use RAISE_APPLICATION_ERROR to prevent logon or raise custom errors in case of login failure. If an error is raised, the login attempt will fail, and the user will not be able to log in.

Example 1: Deny Logon Based on Username

You may want to prevent certain users from logging in to the database. You can accomplish this with a BEFORE LOGON trigger.

CREATE OR REPLACE TRIGGER prevent_admin_logon

BEFORE LOGON ON DATABASE

DECLARE

BEGIN

    IF USER = 'ADMIN' THEN

        RAISE_APPLICATION_ERROR(-20001, 'Admin users cannot log in.');

    END IF;

END;

Explanation:

• This trigger prevents a user with the username 'ADMIN' from logging into the database.
• If a user with the name 'ADMIN' tries to log in, the trigger raises an error (RAISE_APPLICATION_ERROR), and the login attempt is denied with a custom error message.

Example 2: Logging Failed Logins

You might want to log all failed login attempts for audit purposes. Here’s how you could create a trigger to log the login attempt to a log table.

CREATE OR REPLACE TRIGGER log_failed_logins

BEFORE LOGON ON DATABASE

DECLARE

    v_attempt_time TIMESTAMP := SYSTIMESTAMP;

BEGIN

    INSERT INTO login_attempts (username, attempt_time)

    VALUES (USER, v_attempt_time);

   

    -- Optionally, check for conditions and deny logon

    -- If certain criteria are met, raise an exception

    IF USER = 'SUSPENDED_USER' THEN

        RAISE_APPLICATION_ERROR(-20002, 'User account is suspended.');

    END IF;

END;

Explanation:

• The trigger logs the username and the time of the login attempt into the login_attempts table.
• It also includes logic to block users with specific criteria, such as a user with the username SUSPENDED_USER.

Example 3: Restrict Logon Based on Time of Day

You might want to restrict users from logging in during certain hours. For example, allow logins only during business hours (9:00 AM to 5:00 PM).

CREATE OR REPLACE TRIGGER restrict_logon_time

BEFORE LOGON ON DATABASE

DECLARE

    v_current_time TIMESTAMP := SYSTIMESTAMP;

BEGIN

    IF TO_CHAR(v_current_time, 'HH24') < 9 OR TO_CHAR(v_current_time, 'HH24') > 17 THEN

        RAISE_APPLICATION_ERROR(-20003, 'Logon is allowed only between 9:00 AM and 5:00 PM.');

    END IF;

END;

Explanation:

• The trigger checks the current time (SYSTIMESTAMP) and denies the logon if the time is outside the business hours of 9:00 AM to 5:00 PM.

Example 4: Enforce IP-based Logon Restrictions

You might want to enforce logon restrictions based on the user's IP address. Here’s how you can block logins from certain IP addresses.

CREATE OR REPLACE TRIGGER restrict_ip_logon

BEFORE LOGON ON DATABASE

DECLARE

    v_ip_address VARCHAR2(15);

BEGIN

    SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS') INTO v_ip_address FROM dual;

 

    IF v_ip_address = '192.168.1.100' THEN

        RAISE_APPLICATION_ERROR(-20004, 'Logins from this IP address are not allowed.');

    END IF;

END;

Explanation:

• The trigger retrieves the IP address of the machine trying to log in using the SYS_CONTEXT function and the USERENV namespace.
• If the IP address matches a blocked address, the trigger raises an error, preventing the logon.

Use Cases for BEFORE LOGON Trigger

1. Audit Logon Attempts: Track when and who attempts to log into the database. This is useful for security and auditing purposes.
2. Enforce Login Policies: You can restrict users from logging in based on specific conditions, such as their username, the time of day, or their IP address.
3. Session Initialization: Although you can’t modify session-specific parameters directly in a BEFORE LOGON trigger, you can use this trigger for setting up conditions or performing validations before the session begins.
4. Deny Access to Suspended or Locked Accounts: Automatically reject login attempts from users with suspended or locked accounts, providing a proactive security measure.
5. Enforce Licensing or Subscription Rules: Prevent logins if certain conditions (e.g., license expiration, overuse of a resource, or subscription limits) are not met.

Restrictions and Considerations

1. No Session Access: Since the trigger fires before the session is established, you cannot access session-specific information such as USERENV or SESSION context variables, except for the user that is logging in.
2. One Trigger Per Database: You can only have one BEFORE LOGON trigger per Oracle database. If multiple administrators or developers try to implement similar logic, they will need to combine their logic into a single trigger.
3. Performance Considerations: Since the trigger fires on every login attempt, make sure the logic inside is efficient. If complex operations are needed, consider moving them to a stored procedure or minimizing database access during the logon process.
4. Error Handling: If an exception is raised in a BEFORE LOGON trigger (e.g., RAISE_APPLICATION_ERROR), the user will not be able to log in, and they will see the custom error message. Ensure that the error messages are clear and helpful to avoid confusion.