logon trigger only allow from host

I need to create a logon trigger that only allows access to the database from the server that the database resides on - not say from my pc or something like toad

oracle 11.2.0.3
aix 6.1
LVL 1
bkreynolds48Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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;
/

Open in new window

0
Geert GOracle dbaCommented:
switching off the listener or not setting a listener for that database is easier ...
0
bkreynolds48Author Commented:
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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sdstuberCommented:
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
0
bkreynolds48Author Commented:
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
0
sdstuberCommented:
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
0
johnsoneSenior Oracle DBACommented:
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.
0
slightwv (䄆 Netminder) Commented:
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
0
bkreynolds48Author Commented:
I was looking at tcp.invited_nodes - will have to test that
also testing remote_login_passwordfile
0
Geert GOracle dbaCommented:
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 ...
0
bkreynolds48Author Commented:
Is there a way to make this logon trigger work for  just those users assigned to a specific role?
0
sdstuberCommented:
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
0
bkreynolds48Author Commented:
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;
/
0
sdstuberCommented:
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;
/
0
bkreynolds48Author Commented:
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
0
sdstuberCommented:
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
0
bkreynolds48Author Commented:
I created the trigger as sys
0
bkreynolds48Author Commented:
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?
0
sdstuberCommented:
privileges granted through roles don't apply inside pl/sql

So being DBA (which is a role) doesn't help.
0
bkreynolds48Author Commented:
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
0
bkreynolds48Author Commented:
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
0
sdstuberCommented:
what does your trigger look like?
0
bkreynolds48Author Commented:
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;
/
0
sdstuberCommented:
you changed the where clause

from

         WHERE granted_role = 'YOUR_SPECIFIC_ROLE' AND grantee = USER;

to

         WHERE granted_role = 'TEST_SEC_ACCESS' AND grantee = 'SEC_TEST';


put USER back in for GRANTEE
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bkreynolds48Author Commented:
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;
/
0
bkreynolds48Author Commented:
sorry - it did work - I typed something wrong
0
bkreynolds48Author Commented:
thanks for your patience
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.