(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 ?

For now, I will only focus on 12cR1 and will not detail what are ACL/ACE nor how they work. I studied two different approaches : with and without Data Pump. This blog post is about the non-Data Pump method and is divided into two parts : what to do before and after duplication.

1) Before duplicate : I want to generate a script that can recreate the ACE … after the duplication.

To do so, I use the APPEND_HOST_ACE procedure from DBMS_NETWORK_ACL_ADMIN package.

DECLARE

   l_stmt VARCHAR2(1000) := '' ;
   CURSOR c_host_aces
   IS
      SELECT host,
	  lower_port,
	  upper_port,
	  ace_order,
	  start_date,
	  end_date,
	  decode(grant_type,'GRANT','TRUE','DENY','FALSE') grant_type_dec,
	  decode(inverted_principal,'YES','TRUE','NO','FALSE') inverted_principal_dec,
	  principal,
	  decode(principal_type,'APPLICATION','xs_acl.ptype_xs','DATABASE','xs_acl.ptype_db','EXTERNAL','xs_acl.ptype_external') principal_type_dec,
	  privilege
      FROM dba_host_aces ;
	  
BEGIN

   dbms_output.put_line(q'[BEGIN]');

   FOR rec in c_host_aces
   LOOP
          
	 l_stmt := l_stmt||q'[DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (]'||chr(10) ;
	 l_stmt := l_stmt||q'[   host         => ']'||rec.host||q'[', ]'||chr(10) ;
	 l_stmt := l_stmt||q'[   lower_port   => ']'||rec.lower_port||q'[', ]'||chr(10) ;
	 l_stmt := l_stmt||q'[   upper_port   => ']'||rec.upper_port||q'[', ]'||chr(10) ;
	 l_stmt := l_stmt||q'[   ace          => xs$ace_type( ]'||chr(10) ;
	 l_stmt := l_stmt||q'[      privilege_list => xs$name_list(']'||rec.privilege||q'['),]'||chr(10) ;
	 l_stmt := l_stmt||q'[      principal_name => ']'||rec.principal||q'[',]'||chr(10) ;
	 l_stmt := l_stmt||q'[      principal_type => ]'||rec.principal_type_dec||q'[,]'||chr(10) ;
	 l_stmt := l_stmt||q'[      granted        => ]'||rec.grant_type_dec||q'[,]'||chr(10) ;
	 l_stmt := l_stmt||q'[      inverted       => ]'||rec.inverted_principal_dec||q'[,]'||chr(10) ;
	 l_stmt := l_stmt||q'[      start_date     => ']'||rec.start_date||q'[',]'||chr(10) ;
	 l_stmt := l_stmt||q'[      end_date       => ']'||rec.end_date||q'[']'||chr(10) ;
	 l_stmt := l_stmt||q'[   )]'||chr(10) ;
	 l_stmt := l_stmt||q'[); ]'||chr(10) ;
	 
	 dbms_output.put_line(l_stmt);
	 l_stmt := '';
	 
   END LOOP; 
   
   dbms_output.put_line(q'[END;]');
   dbms_output.put_line(q'[/]');
   
END;
/

Here is an example output of this procedure :

BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
   host         => 'my.1dev.host',
   lower_port   => '',
   upper_port   => '',
   ace          => xs$ace_type(
      privilege_list => xs$name_list('HTTP'),
      principal_name => 'MY_USER_1',
      principal_type => xs_acl.ptype_db,
      granted        => TRUE,
      inverted       => FALSE,
      start_date     => '',
      end_date       => ''
   )
);

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE (
   host         => 'my.2dev.host',
   lower_port   => '',
   upper_port   => '',
   ace          => xs$ace_type(
      privilege_list => xs$name_list('HTTP'),
      principal_name => 'MY_USER_2',
      principal_type => xs_acl.ptype_db,
      granted        => TRUE,
      inverted       => FALSE,
      start_date     => '',
      end_date       => ''
   )
);

END;
/

Parts of APPEND_HOST_ACE procedure are quite self-explanatory, but I struggled a lot with xs$ace_type, especially in understanding principal_type. I used the DECODE function to get the principal_type, but I am not sure it is useful in my case … I definitely have to do some more research about xs$ace_type.

After duplicate : I want to drop the existing ACE and associated ACL and recreate them as they were before.

To do so, I use the REMOVE_HOST_ACE procedure from DBMS_NETWORK_ACL_ADMIN package.

DECLARE

   l_stmt VARCHAR2(1000) := '' ;
   CURSOR c_host_aces
   IS
      SELECT host,
	  lower_port,
	  upper_port,
	  ace_order,
	  start_date,
	  end_date,
	  decode(grant_type,'GRANT','TRUE','DENY','FALSE') grant_type_dec,
	  decode(inverted_principal,'YES','TRUE','NO','FALSE') inverted_principal_dec,
	  principal,
	  decode(principal_type,'APPLICATION','xs_acl.ptype_xs','DATABASE','xs_acl.ptype_db','EXTERNAL','xs_acl.ptype_external') principal_type_dec,
	  privilege
      FROM dba_host_aces ;
	  
BEGIN

   FOR rec in c_host_aces
   LOOP
          
	 l_stmt := l_stmt||q'[BEGIN]'||chr(10) ;
	 l_stmt := l_stmt||q'[DBMS_NETWORK_ACL_ADMIN.REMOVE_HOST_ACE (]'||chr(10) ;
	 l_stmt := l_stmt||q'[   host         => ']'||rec.host||q'[', ]'||chr(10) ;
	 l_stmt := l_stmt||q'[   lower_port   => ']'||rec.lower_port||q'[', ]'||chr(10) ;
	 l_stmt := l_stmt||q'[   upper_port   => ']'||rec.upper_port||q'[', ]'||chr(10) ;
	 l_stmt := l_stmt||q'[   ace          => xs$ace_type( ]'||chr(10) ;
	 l_stmt := l_stmt||q'[      privilege_list => xs$name_list(']'||rec.privilege||q'['),]'||chr(10) ;
	 l_stmt := l_stmt||q'[      principal_name => ']'||rec.principal||q'[',]'||chr(10) ;
	 l_stmt := l_stmt||q'[      principal_type => ]'||rec.principal_type_dec||q'[,]'||chr(10) ;
	 l_stmt := l_stmt||q'[      granted        => ]'||rec.grant_type_dec||q'[,]'||chr(10) ;
	 l_stmt := l_stmt||q'[      inverted       => ]'||rec.inverted_principal_dec||q'[,]'||chr(10) ;
	 l_stmt := l_stmt||q'[      start_date     => ']'||rec.start_date||q'[',]'||chr(10) ;
	 l_stmt := l_stmt||q'[      end_date       => ']'||rec.end_date||q'[']'||chr(10) ;
	 l_stmt := l_stmt||q'[   ),]'||chr(10) ;
	 l_stmt := l_stmt||q'[   remove_empty_acl  => TRUE]'||chr(10) ;
	 l_stmt := l_stmt||q'[); ]'||chr(10) ;
	 l_stmt := l_stmt||q'[END;]'||chr(10) ;
	 
	 -- You can use "execute immediate" to effectively drop the ACE, or simply use "dbms_output.put_line" to generate a script to drop them later.
	 execute immediate l_stmt;
	 l_stmt := '';
	 
   END LOOP; 
   
END;
/

I used remove_empty_acl => TRUE to completely remove the existing ACL before recreating them.

And now I can recreate the former ACE with the script I previously generated during the first step.

DBMS_NETWORK_ACL_ADMIN package exists in 11g and 12c, but many of its subprograms have been deprecated in 12c. What I really like in 12c is that you don’t have to explicitly create or drop the ACL, you can just manage the ACE.

I have a lot to study to fully understand how ACL and ACE work in 12c (and in 11g too, as I still have a lot of 11g databases for legacy applications). I also opened a Service Request on MOS because I have many questions regarding documentation and dictionnary views about ACL and ACE. I came accross views for which I could not find many documentation, like XDS_ACE and NACL$_ACE_EXP. But I can already tell you that NACL$_ACE_EXP is exported by Data Pump ! As said at the begining of this blog post, I also ran some tests to manage ACE with Data Pump.

I hope I can give you more information soon, and would love to receive your comments and your experience about ACE/ACL management.