create trigger for "Not allow any other connection into database other than user having DBA priviledge"

Question

I want to create one trigger but i have to create trigger regarding for this

Not allow any other connection into database other than user having DBA priviledge

Answer 1

The correct way to implement such a restriction is to run

ALTER SYSTEM ENABLE RESTRICTED SESSION;

which prevents non-DBAs from connecting to the database.

But, as the exercise is to implement a trigger the solution could be this:

CREATE OR REPLACE TRIGGER T_LOGON 
    AFTER LOGON ON DATABASE

BEGIN
    IF not DBMS_SESSION.IS_ROLE_ENABLED('DBA') THEN
        raise_application_error(-20001, 'You are not permitted to logon');
    end if;
END;
/

Actually you can make it even shorter:

CREATE OR REPLACE TRIGGER T_LOGON 
    AFTER LOGON ON DATABASE

BEGIN
    raise_application_error(-20001, 'You are not permitted to logon');
END;
/

Because as DBA you have privilege ADMINISTER DATABASE TRIGGER which allows you to logon regardless of errors thrown by a login trigger as a failsafe.