How to Find / Retrieve / Get ACL details in Oracle Database

advertisements

_____________________________________________________________________________________________________________________

To retrieve the ACL (Access Control List) details in an Oracle database, you can use the following SQL script. This script queries the DBA_NETWORK_ACLS and DBA_NETWORK_ACL_PRIVILEGES views to get information about the ACLs and their privileges: An Access Control List (ACL) in Oracle is a list of network privileges for a particular host and a specific principal (user or role). 

ACLs control the network access rights of database users and roles. Each ACL is associated with a specific host, and you can grant or deny network privileges to specific principals for that host. ACLs are essential for managing network access to and from the Oracle database, especially when the database interacts with external services or applications over the network.

advertisements

 

SET SERVEROUTPUT ON
COLUMN acl_id FORMAT 999
COLUMN principal FORMAT A15
COLUMN is_grant FORMAT 999
COLUMN privilege FORMAT A10
COLUMN start_date FORMAT A20
COLUMN end_date FORMAT A20
COLUMN acl FORMAT A8
COLUMN host FORMAT A20
COLUMN lower_port FORMAT 999999
COLUMN upper_port FORMAT 999999
COLUMN description FORMAT A50

SELECT
  a.aclid,
  principal,
  is_grant,
  privilege,
  TO_CHAR(start_date, 'DD-MON-YY HH24:MI:SS') AS start_date,
  TO_CHAR(end_date, 'DD-MON-YY HH24:MI:SS') AS end_date,
  a.acl,
  host,
  lower_port,
  upper_port
FROM
  dba_network_acls a
JOIN
  dba_network_acl_privileges p
ON
  a.aclid = p.aclid;

The script joins the DBA_NETWORK_ACLS view with the DBA_NETWORK_ACL_PRIVILEGES view to retrieve detailed information about ACLs and their associated privileges. Here's what the script does: acl_id: The unique identifier for the ACL. principal: The principal (user or role) associated with the ACL. is_grant: Indicates whether the privilege is granted or denied. privilege: The specific privilege being granted or denied. start_date: The start date for the ACL privilege. end_date: The end date for the ACL privilege. host: The host for which the ACL applies. lower_port: The lower port range for the ACL. upper_port: The upper port range for the ACL. 

Sample Output

 

ACLID		 PRINCIPAL	 IS_GR PRIVILEGE  START_DATE	       END_DATE 	    ACL      HOST		  LOWER_PORT UPPER_PORT
---------------- --------------- ----- ---------- -------------------- -------------------- -------- -------------------- ---------- ----------
0000000080002760 ORACLE_OCM	 true  resolve						    /sys/acl localhost
											    s/oracle
											    -sysman-
											    ocm-Reso
											    lve-Acce
											    ss.xml

0000000080002724 GGSYS		 true  resolve						    NETWORK_ *
											    ACL_86B6
											    4B66DF95
											    012EE053
											    F706E80A
											    06B7

0000000080002724 GSMADMIN_INTERN true  resolve						    NETWORK_ *
		 AL									    ACL_86B6
											    4B66DF95
											    012EE053
											    F706E80A
											    06B7

_____________________________________________________________________________________________________________________

Website Stats