Prerequisites to debug PL/SQL in Oracle Database 12c with SQL Developer

ladybug-862107_640

(Picture by Nata-Ap [CC BY-SA 3.0], via Pixabay)

Why I am so obsessed with ACL ? I have no idea ! Actually it is the other way round, ACL are hunting me.
This morning, I helped a fellow developer who wanted to debug his new PL/SQL procedure with SQL Developer.

His first attempt generated errors :

Connecting to the database test_debugger.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( 'his.host', 'random_port' )
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
This session requires DEBUG CONNECT SESSION and DEBUG ANY PROCEDURE user privileges.
Process exited.
Disconnecting from the database test_debugger.

I issued this command, thinking it would fix the problem :

GRANT DEBUG CONNECT SESSION TO HIS_SCHEMA ;

But he encountered errors again :

Connecting to the database test_debugger.
Executing PL/SQL: CALL DBMS_DEBUG_JDWP.CONNECT_TCP( 'his.host', 'random_port' )
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.DBMS_DEBUG_JDWP", line 68
ORA-06512: at line 1
Process exited.
Disconnecting from the database test_debugger.

Continue reading “Prerequisites to debug PL/SQL in Oracle Database 12c with SQL Developer”

Advertisements

Fun with Access Control Entries in Oracle 12cR1 – Part 2 : ACE and Data Pump

Porte d'Aix, Marseille

(Picture by Tiia Monto [CC BY-SA 3.0], via Wikimedia Commons)

As explained in my previous blog post, in an attempt to develop a solid script to handle ACE (who says I am a quibbler ?), I also played with Data Pump to export and import ACE/ACL in Oracle 12cR1.
I found out that OBJECT_PATH=NETWORK_ACL exists in database_export_objects. I could not get many information about that, so I got confirmation from My Oracle Support that NETWORK_ACL was the right OBJECT_PATH to use.

This blog post consists in a quick reminder on how to export and import ACE/ACL with Data Pump.

Continue reading “Fun with Access Control Entries in Oracle 12cR1 – Part 2 : ACE and Data Pump”

Fun with Access Control Entries in Oracle 12cR1 – Part 1 : A way to extract ACE without Data Pump

Château de Versailles, Grille royale.

(Picture by Miguel Hermoso Cuesta (Own work) [CC BY-SA 3.0], via Wikimedia Commons)

My work environment has approximately 100 production databases and 400 non-production databases. We developed a self-service web application (with APEX 😉 ) that offers developers the right to clone databases. We have some development databases with ACL, concerning development hosts, which are relevant only in development environment. And of course, some production databases with ACL, concerning production hosts, which are relevant only in production environment.

What happens when we duplicate a production database on a development database ? The ACL from the production database are now on the development database. And the development ACL are gone, obviously.

What can we do to keep those development ACL on the development database after a duplication ?

Continue reading “Fun with Access Control Entries in Oracle 12cR1 – Part 1 : A way to extract ACE without Data Pump”