logon trigger only allow from host

CREATE OR REPLACE TRIGGER trg_deny_remote_logins
    AFTER LOGON
    ON DATABASE
BEGIN
    IF UPPER(SYS_CONTEXT('USERENV', 'HOST')) != UPPER(SYS_CONTEXT('USERENV', 'SERVER_HOST'))
    THEN
        RAISE_APPLICATION_ERROR(-20001, 'Users must login from local server');
    END IF;
END;
/

Select allOpen in new window

switching off the listener or not setting a listener for that database is easier ...

stuber,

I created the trigger and then tried logging in from my pc
was able to login with no issues
the trigger created with no errors.


geert_gruwez - can't turn listener off as apps connect to the database

was able to login with no issues

did you login as SYS?  or anyone with the DBA role?  LOGON triggers don't fire for those accounts.  This is to prevent a trigger from making a database unusable because even admins can't get in.

can't turn listener off as apps connect to the database

are those apps connecting from a different machine?  if so, a trigger to block access may not be what you're really wanting

My manager said
create a login trigger that will allow logins only from the host
we have a role xxx_sec assigned to a few users

she wants anyone with that role to only be able to login from the host
those with that role do not have dba

she also wants this to apply to sys and system - but from what you said - that will not work

she also wants this to apply to sys and system - but from what you said - that will not work

for users with SYSDBA privilege (i.e. SYS) the logon trigger doesn't even fire

for users with DBA role (SYSTEM and possibly others), the trigger does fire, but if it generates an exception, the DBA role causes the exception to be ignored.

Between these two rules, it's not possible to use a logon trigger to prevent SYS or SYSTEM from logging in.

you could create a job that continually looks for invalid sessions and kills them.
or, submit such a job immediately after logon so that each invalid session sets up its own process to eventually kill it.  

You can't kill your own session, so you have to do with something external like a job.

easier, just change the SYSTEM password and lock the account then create a new user with a new role that has all the privileges of DBA, but isn't actually "DBA".  That role, with that name is special.  If you create your own role, Oracle won't know it's special and the trigger will be able to block them

What about setting remote_login_passwordfile to none?

Not 100% sure, but I believe the behavior there is that SYS could not connect remotely.  SYSTEM could connect but not as SYSDBA.

I'm a little confused myself but wanted to throw this out as another possibility:
Configure the sqlnet.ora file to restrict the ip addresses that are allowed to connect.

This will allow app servers to still be able to initiate connections.  Now if users can log into the app server then they could still connect but that is a different issue.

Check out TCP.INVITED_NODES in the sqlnet.ora file:
http://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#CIHJDJII

I was looking at tcp.invited_nodes - will have to test that
also testing remote_login_passwordfile

besides, on the host, if you know how, you don't need any roles
just "/ as sysdba" to logon

and if that has been disabled by the sqlnet.ora, it's possible to make an own sqlnet.ora and use that, restarting the database could be a bit drastic but it's a possibilty

allowing someone on the host actually negates 99% of the actions you can take against them when admin or the account allowed to logon would have to be very limited
but then again, what would be the point in allowing them on the host

some companies ask specialised hacking&security companies to test their measures
(not many asked the nsa, but why wait on an invite ... ?)

a good countermeisure is to setup auditing
here some production databases automatically send a mail to a group upon login of certain accounts + audit all the action + send a mail after logoff

those dba accounts are assigned to specific groups.
we timed it with someone who shouldn't log on, it took about 20 secs for the manager to standup and come to me to ask why that person was logging on with their account and how in god's name did he get the password ...

Is there a way to make this logon trigger work for  just those users assigned to a specific role?

yes, as along as that role isn't DBA, and those uses don't have DBA in addition to your specific role.


use the same thing as what I posted above, except check

dba_role_privs where granted_role = 'YOUR_SPECIFIC_ROLE' and grantee = USER

to see if your user is in the role.  The role will still fire all other users (except SYS) but you can choose to simply exit or do some other action for users that aren't in your special role

where do I put that in the trigger? those with this role do not have dba privs
is this correct?

CREATE OR REPLACE TRIGGER trg_deny_remote_logins
    AFTER LOGON
    ON DATABASE
BEGIN
IF granted_role ='SEC_ROLE"
and
    IF UPPER(SYS_CONTEXT('USERENV', 'HOST')) != UPPER(SYS_CONTEXT('USERENV', 'SERVER_HOST'))
    THEN
        RAISE_APPLICATION_ERROR(-20001, 'Users must login from local server');
    END IF;
END;
/

no, you need to check

dba_role_privs where granted_role = 'YOUR_SPECIFIC_ROLE' and grantee = USER

CREATE OR REPLACE TRIGGER trg_deny_remote_logins
    AFTER LOGON
    ON DATABASE
DECLARE
    v_dummy INTEGER;
BEGIN
    BEGIN
        SELECT NULL
          INTO v_dummy
          FROM dba_role_privs
         WHERE granted_role = 'YOUR_SPECIFIC_ROLE' AND grantee = USER;

        IF UPPER(SYS_CONTEXT('USERENV', 'HOST')) != UPPER(SYS_CONTEXT('USERENV', 'SERVER_HOST'))
        THEN
            RAISE_APPLICATION_ERROR(-20001, 'Users must login from local server');
        END IF;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            NULL;
    END;
END;
/

I created the above trigger with the where clause for granted_role and grantee - it raises the application error for anyone not a dba - does not work for a user with that granted role

grant select on dba_role_privs to the owner of the trigger

if the owner is a DBA or has other roles that allow access, it's irrelevant

I created the trigger as sys

you said
if the owner is a DBA or has other roles that allow access, it's irrelevant

what does that mean
the trigger won't work?

privileges granted through roles don't apply inside pl/sql

So being DBA (which is a role) doesn't help.

the granted role is to a non dba user -
I created two users one with the granted role one without the granted role
neither can logon

when logging in as a test_user who does not have the sec_role assigned

Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: Users must login from local server
ORA-06512: at line 17

get the same error when logging in as the user with the sec_role assigned

what does your trigger look like?

CREATE OR REPLACE TRIGGER trg_deny_remote_logins
    AFTER LOGON
    ON DATABASE
DECLARE
    v_dummy INTEGER;
BEGIN
    BEGIN
        SELECT NULL
          INTO v_dummy
          FROM dba_role_privs
         WHERE granted_role = 'TEST_SEC_ACCESS' AND grantee = 'SEC_TEST';

        IF UPPER(SYS_CONTEXT('USERENV', 'HOST')) != UPPER(SYS_CONTEXT('USERENV', 'SERVER_HOST'))
        THEN
            RAISE_APPLICATION_ERROR(-20001, 'Users must login from local server');
        END IF;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            NULL;
    END;
END;
/

I changed the trigger to be

grantee = USER:

still same result
user with the role not assigned still denied access

CREATE OR REPLACE TRIGGER trg_deny_remote_logins
    AFTER LOGON
    ON DATABASE
DECLARE
    v_dummy INTEGER;
BEGIN
    BEGIN
        SELECT NULL
          INTO v_dummy
          FROM dba_role_privs
         WHERE granted_role = 'TEST_SEC_ACCESS' AND grantee = USER;

        IF UPPER(SYS_CONTEXT('USERENV', 'HOST')) != UPPER(SYS_CONTEXT('USERENV', 'SERVER_HOST'))
        THEN
            RAISE_APPLICATION_ERROR(-20001, 'Users must login from local server');
        END IF;
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            NULL;
    END;
END;
/

sorry - it did work - I typed something wrong

thanks for your patience