logon trigger - add disallow from specific servers

sdstuber helped me get my logon trigger to work
not I am asked to include not letting users login from specific servers
how do I do this?

I have tried.....

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;

	IF SYS_CONTEXT('USERENV','SERVER_HOST') in ('list of servers to disallow login from') 
	THEN RAISE_APPLICATION_ERROR(-20003,'Cannot connect to the database from this server');
	END IF;

    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            NULL;
    END;
END;
/

Open in new window


which does not work - does not compile either
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.

slightwv (䄆 Netminder) Commented:
From your previous question, check TCP_EXCLUDED_NODES:
http://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#CIHFGICB

>>IF SYS_CONTEXT('USERENV','SERVER_HOST') in ('list of servers to disallow login from')
      

Try:
IF UPPER(SYS_CONTEXT('USERENV','SERVER_HOST')) in ('SERVER1','SERVER2','SVR3')


replacing the strings with the server names.

I added UPPER just for grins.
0
bkreynolds48Author Commented:
I was told I could not user tcp_excluded_nodes at this point

I tried
IF UPPER(SYS_CONTEXT('USERENV','SERVER_HOST')) in ('SERVER1','SERVER2','SVR3')

did not work - could still login from the ('SERVER1')
just trying one server at a time

the trigger did compile though
0
slightwv (䄆 Netminder) Commented:
From SERVER1, post the results from:
select SYS_CONTEXT('USERENV','SERVER_HOST') from dual;

It might be a domain issue.

If it is, try this to strip off the domain:

IF regexp_substr(UPPER(SYS_CONTEXT('USERENV', 'HOST')),'[^\]+$') in  ('SERVER1','SERVER2','SVR3')
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

bkreynolds48Author Commented:
From SERVER1, post the results from:
select SYS_CONTEXT('USERENV','SERVER_HOST') from dual;

this returns the qc400 that the database resides on not the server I ran
sqlplus user/pwd@server1 from i.e

database is on qc400
ran the sqlplus from prod500

tried this
IF regexp_substr(UPPER(SYS_CONTEXT('USERENV', 'HOST')),'[^\]+$') in  ('SERVER1')

did not keep the user out from the remote server
0
slightwv (䄆 Netminder) Commented:
Sorry.  I meant:
From SERVER1, post the results from:
select SYS_CONTEXT('USERENV','HOST') from dual;


>>sqlplus user/pwd@server1 from i.e

I did not mean to do a remote database connect to server1.  I meant to physically log into server1.

HOST returns the machine you are logged in to.
0
bkreynolds48Author Commented:
select SYS_CONTEXT('USERENV','HOST') from dual;
returns qc400
0
slightwv (䄆 Netminder) Commented:
>>database is on qc400
>>ran the sqlplus from prod500

I might have mixed up the SYS_CONTEXT calls.

Try this:
IF UPPER(SYS_CONTEXT('USERENV','HOST')) in ('PROD500','SERVER2','SVR3')

This should keep people connecting from sqlplus on prod500 from not being able to connect.
0
bkreynolds48Author Commented:
Tried just the one server - did not keep user out from PROD500

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;

      IF UPPER (SYS_CONTEXT('USERENV','SERVER_HOST'))  in ('PROD500')
      THEN RAISE_APPLICATION_ERROR(-20003,'Cannot connect to the database from this server');
      END IF;

    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            NULL;
    END;
END;
/
0
slightwv (䄆 Netminder) Commented:
>>did not keep user out from PROD500

Check my changes...  I have the wrong call.

      IF UPPER (SYS_CONTEXT('USERENV','HOST'))  in ('PROD500')
0
bkreynolds48Author Commented:
changed this
IF UPPER (SYS_CONTEXT('USERENV','SERVER_HOST'))  in ('PROD500')
to this
  IF UPPER (SYS_CONTEXT('USERENV','HOST'))  in ('PROD500')
same result
did not deny access
0
slightwv (䄆 Netminder) Commented:
And the trigger was created in the database running on qc400 and you are logged into the Operating system on prod500 and remotely connecting to the database running on qc400?

Can you provide the names of the servers and databases in question?
0
slightwv (䄆 Netminder) Commented:
Think I found it...

To remove logins from the banned servers, does the ROLE still com into play?

If not, you need to move the SERVER check above the role check:

CREATE OR REPLACE TRIGGER trg_deny_remote_logins
    AFTER LOGON
    ON DATABASE
DECLARE
    v_dummy INTEGER;
BEGIN
      IF UPPER (SYS_CONTEXT('USERENV','SERVER_HOST'))  in ('PROD500')
      THEN RAISE_APPLICATION_ERROR(-20003,'Cannot connect to the database from this server');
      END IF;

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

Open in new window

0
bkreynolds48Author Commented:
created the trigger as sys in emptst on server qc400

server qc400
database emptst

server PROD500 -
connected using sqlplus sec_test2/password@emptst
0
slightwv (䄆 Netminder) Commented:
See if my change above works for you now.
0
bkreynolds48Author Commented:
Made the change same result

yes still need to have the users with that role denied access

the users with the role ARE denied access
0
slightwv (䄆 Netminder) Commented:
I'm not entirely following the requirements.

You have the basic logic.  You just need to work with it to meet your requirements.

For example:  You still have the 'Users must login from local server' check in the code.  This no longer seems to be the requirement.

What is the priority:  Logged in server or ROLE?

If you can explain the entire requirement maybe we can provide a cut/paste solution.

Let me see if I understand:
ANY user that is not a DBA level user from prod500 CANNOT log in no matter what.

ANY user that is not a DBA from any other server can log in if they have the 'TEST_SEC_ACCESS' role.

The requirement for local server only has been removed.
0
bkreynolds48Author Commented:
there are two requirements

the first requirement is

first
users with the test_sec_role can only  login to the database if they are on the server where the database is
qc440  is the server - this works in the trigger

the second requirement is
any user that is NOT a dba cannot login to the database from a specific remote server like PROD500

I am not making the requirements just trying to do what was asked of me

Thanks
0
slightwv (䄆 Netminder) Commented:
I'm just trying to understand the requirements.

If I do, this works for me.  It is the complete test case that creates the users to test with.

My development machine is MYDEV which is in the list.  The last two connects are performed both from MYDEV and another machine.

spool l
conn / as sysdba

drop role test_sec_access;
create role test_sec_access;

drop TRIGGER trg_deny_remote_logins;

CREATE OR REPLACE TRIGGER trg_deny_remote_logins
    AFTER LOGON
    ON DATABASE
DECLARE
    v_dummy INTEGER;
BEGIN
        IF regexp_substr(UPPER(SYS_CONTEXT('USERENV', 'HOST')),'[^\]+$') in ('MYDEV','b','c')
        THEN
            RAISE_APPLICATION_ERROR(-20001, 'Not a valid login server');
        END IF;

    BEGIN
        SELECT NULL
          INTO v_dummy
          FROM dba_role_privs
         WHERE granted_role = 'TEST_SEC_ACCESS' AND grantee = USER;

        IF regexp_substr(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;
/

show errors
--
drop user bob cascade;
drop user bill cascade;
--
create user bob identified by bob;
grant create session, test_sec_access to bob;
--
create user bill identified by bill;
grant create session to bill;
--
--
conn bob/bob@MYTESTDB
conn bill/bill@MYTESTDB

spool off

Open in new window

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:
that worked

thanks so much
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.