7 Replies Latest reply on May 25, 2017 1:01 PM by thatJeffSmith-Oracle

    ACL for SQL Developer

    SherrieK

      Oracle RDBMS 12.1.0.2

      SQL Developer 3.2.20.10

       

      In our development environment, our development team would like to login using SQL Developer and run the debugger.  There are 10 developers, with SQL Developer installed on their desktop. Anybody who connects as 'SCOTT' should be able to run the debugger on one of their procedures.  The error ORA-24247 network access denied by access control list (ACL). 

       

      Each of these desktops is in the same domain.

       

      I tried adding this ACE:

      BEGIN

          DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (

              HOST         => '*.my.domain',

              lower_port   => NULL,

              upper_port   => NULL,

              ace          => xs$ace_type (privilege_list   => xs$name_list ('jdwp'),

                                           principal_name   => 'SCOTT',

                                           principal_type   => xs_acl.ptype_db));

      END;

       

      The only thing that works is to hard code the IP address.  I opened an Oracle SR and am told that an IP is required, it is not possible to use anything else.

       

      I could see in a bigger shop than mine that this would be maintenance nightmare.  If you carry your laptop around and log in at different locations, you'll still be in the same domain but have a different IP.  Somebody gets a new workstation, there is a new IP.  It seems crazy to me. 

       

      SQL Developer is meant to be Oracle's development tool, how can it be so hard to run the debugger! 

       

      "The connection works by "word matching" , so it takes your IP and tries to match it with what you have included in the ACLs.

      That is why your IP works : the IP matches with what you have in the ACL.

      When you try to use the "domain/host based ACL, the matching is done between your IP and domain/host which do not match ( numbers vs words/characters) .

      You would think that the DNS would take care of the name translation for the ACL, but the connection does not reach the DNS server, it is abruptly stopped by the ACLs.

      It was suggested that you implement IP based ACLs for each of the developers that need to use that debug tool."

       

       

      Does anyone have a different insight into this?  I feel like I'm missing something.

       

      Sherrie

        • 1. Re: ACL for SQL Developer
          BPeaslandDBA

          SQL Developer 3.2.20.10

           

          The first thing I would do is to upgrade to a more recent version. SQLDev 4.2 is out now.

           

            HOST    
          => '*.my.domain',

           

          Have you tried using the wild card in the IP instead? For example:

           

          HOST=>192.168.1.*

           

          In our ACL's we had to do that to make it easier so that I didn't to manage ACLs for all IPs.

           

          That being said, I've never needed to setup an ACL just for debugging a stored proc.

           

          Cheers,
          Brian

          • 2. Re: ACL for SQL Developer
            SherrieK

            Hi Brian,

            I was looking at the wrong thing, SQL Developer version is actually 4.2.0.17.089, the latest.

             

            Yes, I've tried the wildcards, the only thing that works is the explicit IP at the time they are trying to use it.  It's the same from TOAD using their debugger. 

            The developers have a lot of packages/procedures, PL/SQL code, and really like to use the debugger when they are troubleshooting. 

             

            You'd think that just running a debugger wouldn't require an  ACL, but 12c security changes make this a requirement.  It seems crazy, and I'm getting this answer from Oracle support.  I keep thinking I'm missing something because it should be a given that a development tool comes with a debugger without these extra security steps. 

             

            Thanks,

            Sherrie

            • 3. Re: ACL for SQL Developer
              BPeaslandDBA

              Have you tried to post this on the SQL Developer space? They might have more insight.

               

              Cheers,
              Brian

              • 4. Re: ACL for SQL Developer
                SherrieK

                I will do that, thank you.  My original SR with support was under SQL Developer space, and they had a different way of looking at this, more the functionality of how to use SQL Developer.  I will post this over there as well.

                 

                Thank,

                Sherrie

                • 5. Re: ACL for SQL Developer
                  thatJeffSmith-Oracle

                  It's required for the debugger b/c the debugger actually reaches OUT of the database to connect to your developer boxes.

                   

                  It's not a SQL Developer issue per se, it's a core db issue. My guess is the domain wildcard isn't matching up to what the db sees when it tries to connect to your developer's box. The request includes an IP address, so I'd try IP wildcarding like BPeaslandDBA suggested.

                   

                  If that doesn't work, ask Support for the instructions to enable the probe debugger. It doesn't require ACL privs, BUT it provides significantly less functionality.

                  • 6. Re: ACL for SQL Developer
                    SherrieK

                    Yes, the connection uses "word matching" by taking your IP and matching it to the ACL and never gets to DNS for resolution.

                     

                    But!  The good news is that you were right a few posts above - just using nn.n.* works, so I don't have to take it down to the nn.nn.nn.nn level, which is what I was getting from support.

                    That works for me, a whole lot less maintenance. 

                     

                    Thanks, it helps to talk this through with someone. 

                    Have a good day!

                     

                    Sherrie

                    • 7. Re: ACL for SQL Developer
                      thatJeffSmith-Oracle

                      I think you can wildcard it as far down as you need. Glad to hear you got it working.