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

Leave a reply

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.

Let’s start with a brand new database which contains GSMADMIN_INTERNAL default ACE only :

SQL> SELECT * FROM dba_host_acls;
HOST  LOWER_PORT  UPPER_PORT  ACL                                           ACLID             ACL_OWNER
*                             NETWORK_ACL_62E7D27B4CB7CF00E0533330790A088A  000000008000279C  SYS



SQL> SELECT * FROM dba_host_aces;
HOST  LOWER_PORT  UPPER_PORT  ACE_ORDER  START_DATE  END_DATE  GRANT_TYPE  INVERTED_PRINCIPAL  PRINCIPAL          PRINCIPAL_TYPE  PRIVILEGE
*                             1                                GRANT       NO                  GSMADMIN_INTERNAL  DATABASE        RESOLVE

Now let’s add a new ACE …

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

PL/SQL procedure successfully completed.

… check that this action created the underlying ACL and of course the corresponding ACE …

SQL> SELECT * FROM dba_host_acls;
HOST  LOWER_PORT  UPPER_PORT  ACL                                           ACLID             ACL_OWNER
my.test.host                  NETWORK_ACL_6439E21BBB4A9AA1E0533330790A0726  00000000800027B6  SYS
*                             NETWORK_ACL_62E7D27B4CB7CF00E0533330790A088A  000000008000279C  SYS



SQL> SELECT * FROM dba_host_aces;
HOST  LOWER_PORT  UPPER_PORT  ACE_ORDER  START_DATE  END_DATE  GRANT_TYPE  INVERTED_PRINCIPAL  PRINCIPAL          PRINCIPAL_TYPE  PRIVILEGE
*                             1                                GRANT       NO                  GSMADMIN_INTERNAL  DATABASE        RESOLVE
my.test.host                  1                                GRANT       NO                  MY_USER            DATABASE        HTTP

… and finally export it with Data Pump (Remember that using FULL=Y is mandatory in this case.) :

# expdp SYSTEM@testdb directory=DATA_PUMP_DIR logfile=export_network_acl.log dumpfile=export_network_acl.dp full=y include=NETWORK_ACL

Export: Release 12.1.0.2.0 - Production on Fri Feb 2 12:30:26 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01":  SYSTEM/********@testdb directory=DATA_PUMP_DIR logfile=export_network_acl.log dumpfile=export_network_acl.dp full=y include=NETWORK_ACL
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW"               6.281 KB      47 rows
. . exported "SYS"."NACL$_ACE_EXP"                       10.02 KB       2 rows
. . exported "SYS"."NACL$_HOST_EXP"                      6.984 KB       2 rows
. . exported "SYS"."NACL$_WALLET_EXP"                        0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
  /my/path/to/export/testdb/export_network_acl.dp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Fri Feb 2 12:30:49 2018 elapsed 0 00:00:17

Now let’s remove the ACE, with parameter remove_empty_acl => TRUE to remove the underlying ACL if no corresponding ACE exists anymore :

SQL> BEGIN
DBMS_NETWORK_ACL_ADMIN.REMOVE_HOST_ACE (
   host         => 'my.test.host',
   lower_port   => '',
   upper_port   => '',
   ace          => xs$ace_type(
      privilege_list => xs$name_list('HTTP'),
      principal_name => 'MY_USER',
      principal_type => xs_acl.ptype_db,
      granted        => TRUE,
      inverted       => FALSE,
      start_date     => '',
      end_date       => ''
   ),
   remove_empty_acl  => TRUE
);
END;
/

PL/SQL procedure successfully completed.

… and check again :

SQL> SELECT * FROM dba_host_acls;
HOST  LOWER_PORT  UPPER_PORT  ACL                                           ACLID             ACL_OWNER
*                             NETWORK_ACL_62E7D27B4CB7CF00E0533330790A088A  000000008000279C  SYS



SQL> SELECT * FROM dba_host_aces;
HOST  LOWER_PORT  UPPER_PORT  ACE_ORDER  START_DATE  END_DATE  GRANT_TYPE  INVERTED_PRINCIPAL  PRINCIPAL          PRINCIPAL_TYPE  PRIVILEGE
*                             1                                GRANT       NO                  GSMADMIN_INTERNAL  DATABASE        RESOLVE

My ACE and ACL are now gone.

Finally it is time to import them back :

# impdp SYSTEM@testdb directory=DATA_PUMP_DIR logfile=import_network_acl.log dumpfile=export_network_acl.dp

Import: Release 12.1.0.2.0 - Production on Fri Feb 2 12:32:35 2018

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  SYSTEM/********@testdb directory=DATA_PUMP_DIR logfile=import_network_acl.log dumpfile=export_network_acl.dp
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."KU$_EXPORT_USER_MAP"                 6.281 KB      47 rows
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."NACL$_ACE_IMP"                       10.02 KB       2 rows
. . imported "SYS"."NACL$_HOST_IMP"                      6.984 KB       2 rows
. . imported "SYS"."NACL$_WALLET_IMP"                        0 KB       0 rows
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOU/MARKER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Fri Feb 2 12:32:46 2018 elapsed 0 00:00:04

After the import, my ACE and ACL are back :

SQL> SELECT * FROM dba_host_acls;
HOST          LOWER_PORT  UPPER_PORT  ACL                                           ACLID             ACL_OWNER
my.test.host                          NETWORK_ACL_643A12361980F0EBE0533330790AA58A  00000000800027BB  SYS
*                                     NETWORK_ACL_62E7D27B4CB7CF00E0533330790A088A  000000008000279C  SYS



SQL> SELECT * FROM dba_host_aces;
HOST  LOWER_PORT  UPPER_PORT  ACE_ORDER  START_DATE  END_DATE  GRANT_TYPE  INVERTED_PRINCIPAL  PRINCIPAL          PRINCIPAL_TYPE  PRIVILEGE
*                             1                                GRANT       NO                  GSMADMIN_INTERNAL  DATABASE        RESOLVE
my.test.host                  1                                GRANT       NO                  MY_USER            DATABASE        HTTP

I was puzzled by the SYS.KU$_EXPORT_USER_MAP table containing 47 rows. So I took a chance with My Oracle Support but I was not lucky enough to get an answer about the usage of this table 😉 I am more than interested if you have any information !

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out /  Change )

Cancel

Connecting to %s