1 2 Previous Next 16 Replies Latest reply on Sep 30, 2015 7:17 AM by JohnWatson2

    Restrict User Login

    Manu.

      Dear All,

       

      We are developing applications using oracle forms, and we use database users as application users. So I need to know is there a way to restrict the login to database only from the application which we have made. Or we need to block the login to the database (may be for some users atleast) when they try to login through SQL developer or TOAD or SQLPlus or any other method / tools. We are using oracle 11g.

       

       

       

      Manu.

        • 1. Re: Restrict User Login
          Hemant Infy

          Hi Manu,

           

          you can use logon trigger for this.

          This trigger will restrict the unauthorized access.

           

          CREATE OR REPLACE TRIGGER SYS.TRIG_LOGON_BLK_DDL

          after LOGON ON DATABASE

          DECLARE

          --- declaration of local variables

          select module into cmodule from v$session where audsid=(select sys_context('USERENV','SESSIONID') from dual) and rownum<=1;

          ---- the above line will show if it is TOAD/SQL Developer or any other tool.

          select sys_context('USERENV','SESSION_USER') into usr from dual;

          select sys_context('USERENV','HOST') into chost from dual;

          select sys_context('USERENV','IP_ADDRESS') into cip from dual;

          select SYSDATE into clogin from dual;

            select sys_context('USERENV','OS_USER') into cosuser from dual;

           

          ---- then your logic to check whom to restrict.

          -- and finally message to the user.

           

          if blk then

            insert into ddl_block_audit                       -- this table tracks all access to the database.

            values

            (usr, chost,cip, cmodule, cosuser, clogin,'','','','','Blocked from Logon Trigger');

            commit;

              raise_application_error(-20001,usr||' - Blocked - please contact Admin');

          end if;

          /

           

          Regards,

          Hemant

          1 person found this helpful
          • 2. Re: Restrict User Login
            Manu.

            Dear Hemant,

             

            I tried your solution, but its not working correctly. The log table contains data with the message as blocked, but the login to DB is working.

             

             

             

            Manu.

            • 3. Re: Restrict User Login
              JohnWatson2

              I would do it with SQL*Net. Configure your listener with tcp.validnode_checking to reject all connections that do not come from your Forms server.

              --

              John Watson

              Oracle Certified Master DBA

              1 person found this helpful
              • 4. Re: Restrict User Login
                JuanM

                HI, you can configure a specific entry in the SERVICE_NAMES parameter for that application and the listener will accept the connection or not, without blocking users.

                http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams217.htm

                So, you need to configure a connect string for that application using service name

                ....(SERVICE_NAME=SERVICE_1)...

                Configuring Naming Methods

                 

                Regards,

                Juan M

                1 person found this helpful
                • 5. Re: Restrict User Login
                  mseberg

                  I like John's idea.

                   

                  Here's an example

                   

                  The Arup Nanda Blog: 06/01/2010 - 07/01/2010

                   

                  If you only specify invited nodes, all others will be excluded.

                   

                  tcp.validnode_checking = yes

                  tcp.invited_nodes =

                   

                  OR

                   

                  tcp.excluded_nodes =

                   

                   

                  So one or the other, not both.

                   

                  Best Regards

                   

                  mseberg

                  1 person found this helpful
                  • 6. Re: Re: Restrict User Login
                    spiderwoman

                    I tried your solution, but its not working correctly. The log table contains data with the message as blocked, but the login to DB is working.

                    Use sqlnet.ora.

                    In that file there is parameter called "tcp.invited_nodes" and you can list all the IP addresses that will login into DB server.

                    Most application server IP address can be listed.

                    This will disable ALL logins from any other servers.

                    • 7. Re: Restrict User Login
                      JuanM

                      Hi, remember that OP is telling us he want to restrict the access from that specific application. Not all applications for specific server.

                      Also, if he lock a user may another app will be affected if using the same user to connect to database.

                      For this kind of requirements the best choice is using SERVICE_NAMES, and configure the connect string of the app.

                       

                      Regards,

                      Juan M

                      • 8. Re: Restrict User Login
                        mseberg

                        Correct. But if he's running Oracle Forms having people back door the application isn't the best security idea.

                         

                        The logon trigger solution seems like more work with more issues.

                         

                        I still like John's idea.

                         

                        Best Regards

                         

                        mseberg

                        • 9. Re: Restrict User Login
                          JuanM

                          Ok, in addition to yours and John advise,

                           

                          Re reading again the original post, maybe the best security solution is change the password to user used by the app and not sharing to the "users(people)" and create customized oracle accounts and profiles and roles for users that connect via SQL*Plus, TOAD etc etc

                           

                           

                          Regards,

                          Juan M

                          • 10. Re: Restrict User Login

                            Manu. wrote:

                             

                            Dear All,

                             

                            We are developing applications using oracle forms, and we use database users as application users. So I need to know is there a way to restrict the login to database only from the application which we have made. Or we need to block the login to the database (may be for some users atleast) when they try to login through SQL developer or TOAD or SQLPlus or any other method / tools. We are using oracle 11g.

                             

                             

                             

                            Manu.

                            change the password & don't give it to the developers on Production DB.

                            • 11. Re: Restrict User Login
                              Manu.

                              Dear Hemant,

                               

                              When I searched about this, I found that this will not block the DBA privileged users. In our case, there is a possibility of  several DBA users. If the user has no DBA privilege, then that user login is blocking.

                               

                               

                              Manu.

                              • 12. Re: Restrict User Login
                                Manu.

                                Dear John,

                                 

                                This will work well in case of application server. But we have clients who are still using 6i programs and not going to convert to 11g in a near future. So its better if we can find a solution which suites for 6i clients as well as 11g clients.

                                 

                                 

                                Manu.

                                • 13. Re: Restrict User Login
                                  Hemant Infy

                                  Hi Manu,

                                   

                                  Yes correct ! , after hearing from you I checked from my side again.

                                  I could see users with below powerful privileges bypass the trigger restriction and able to login from TOAD :--

                                   

                                  1. DBA

                                  2. IMP_FULL_DATABASE

                                  3. EXP_FULL_DATABSE

                                   

                                  so unfortunately this option will not work .not sure if we can restrict a user with DBA privilege by logon trigger.

                                   

                                  Regards,

                                  Hemant

                                  • 14. Re: Restrict User Login
                                    Oratig-Oracle

                                    You can use ip based restriction using your sqlnet.ora

                                     

                                     

                                    tcp.validnode_checking = YES

                                    tcp.excluded_nodes = {list of IP addresses}

                                    tcp.invited_nodes = {list of IP addresses}

                                     

                                     

                                     

                                    If you look for a trigger

                                     

                                     

                                    Create table which will hold the ip and username.

                                     

                                    Explanation:

                                     

                                    --> this table will contain ip and username (database username)

                                    --> example IP=10.10.10.10 and scott

                                    --> only scott can be accessed from ip 10.10.10.10

                                    --> you should insert the details in the below table

                                     

                                    create table CHECK_USER_IP_LOGIN

                                    (username          varchar2(50),

                                    ip                varchar2(15))

                                    /

                                     

                                     

                                     

                                    ---> The below table will hold the information about unauthorised user

                                    ---> this table has to be created, data will be papulated by trigger

                                     

                                     

                                    create table access_vilation

                                    (os_user varchar2(25),

                                    ip_address varchar2(30),

                                    oracle_user varchar2(30),

                                    sysdat timestamp(0))

                                    /

                                     

                                     

                                    ---> trigger to find the access violation details

                                     

                                    CREATE OR REPLACE TRIGGER SYS.LOGON_ACCESS_VILA

                                    AFTER LOGON ON DATABASE

                                    BEGIN

                                    insert into access_vilation VALUES (

                                    sys_context('USERENV', 'OS_USER'),

                                    SYS_CONTEXT('USERENV','IP_ADDRESS'),

                                    sys_context('USERENV', 'SESSION_USER') ,

                                    sysdate);

                                    DELETE FROM access_vilation WHERE ORACLE_USER NOT IN (SELECT USERNAME FROM CHECK_USER_IP_LOGIN);

                                    DELETE FROM access_vilation

                                    WHERE (IP_ADDRESS IN (SELECT IP FROM CHECK_USER_IP_LOGIN)

                                    AND ORACLE_USER IN (SELECT USERNAME FROM CHECK_USER_IP_LOGIN));

                                    END;

                                    /

                                     

                                    ---> Trigger to restrict the session

                                     

                                     

                                    create or replace trigger user_ip_restrict

                                    AFTER LOGON ON DATABASE

                                    DECLARE

                                    ip_count INTEGER;

                                    user_count INTEGER;

                                    BEGIN

                                    SELECT COUNT(username) INTO user_count FROM CHECK_USER_IP_LOGIN

                                    WHERE lower(username)=lower(user);

                                    SELECT COUNT(ip) INTO ip_count FROM CHECK_USER_IP_LOGIN

                                    WHERE lower(username)=lower(user) AND ip=sys_context('USERENV','IP_ADDRESS');

                                    IF ( (user_count > 0 ) AND (ip_count = 0) ) THEN

                                    RAISE_APPLICATION_ERROR(-20001,'your are not authorised person.Please contact Admin');

                                    end if;

                                    END;

                                    /

                                     

                                     

                                     

                                    For DBA role you may have to check, these triggers may allow to login if you  have DBA permission.

                                     

                                     

                                    Thanks,

                                    Sundar

                                    1 2 Previous Next