DBSecWorx: XS

Oracle XS_ACL Built-In Package Versions 11.2 - 21c

Security Advisory

This package is part of Oracle Database Real Application Security (RAS) and is used to create, alter, drop, and manage Access Control Lists.

The documentation for this package, in the Real Application Security Administrator's and Developer's Guide was clearly written to promote the twin concepts of "Security Through Obscurity" and "Security Through Confusion". The most basic principles of documenting code such as explaining the purpose of constants and how the use of different constants affects processing do not exist. Also, the person or persons that wrote the doc while clearly fluent in the English language apparently never passed a course named "How To Make Your Writing Comprehensible" because some of the sentences rise to the level of "Why does a mouse when it spins?"

From our standpoint here at DBSecWorx we are going to devote considerable energy into getting to the bottom of how to make constructive use of the package so check back from time-to-time.

That said, clearly XS_ACL relates to database security and Access Control Lists so make securing this package and monitoring any usage a very high priority.

Recommended Security Rules

NEVER

Grant access to this package to any user if you have not licensed Oracle Real Application Security
Grant access to this package except under strict controls

WITH GREAT CARE

Review any access control lists or privileges granted, altered, or revoked

CAUTIONS

This package is so poorly documented we recommend not using it at this time. It is likely positively brilliant but if you can't even find out what essential constants do how can you know that you made the best choices for your environment?

How Oracle Works

In the case of Real Application Security generally, and this built-in package specifically, the documentation is so confusing and inadequate that we must admit that we do not truly understand how it works and until we do we recommend not using it. We will update this monograph as we learn more and change our opinion of RAS when we not only understand how it works but are comfortable that we are aware of any weaknesses and how to defeat them.

XS_ACL Package Information

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Parent ACL Types
EXTENDED	PLS_INTEGER	1
CONSTRAINED	PLS_INTEGER	2
Principal's Types
PTYPE_XS	PLS_INTEGER	1
PTYPE_DB	PLS_INTEGER	2
PTYPE_DN	PLS_INTEGER	3
PTYPE_EXTERNAL	PLS_INTEGER	4
Parameter Value Types
TYPE_NUMBER	PLS_INTEGER	1
TYPE_VARCHAR	PLS_INTEGER	2

Data Types CREATE OR REPLACE TYPE XS$ACE_LIST AS VARRAY(1000) OF XS$ACE_TYPE; CREATE OR REPLACE TYPE XS$ACE_TYPE AS OBJECT ( -- Member Variables privilege_list XS$NAME_LIST, is_grant_ace NUMBER, is_invert_principal NUMBER, principal_name VARCHAR2(130), principal_type NUMBER, start_date TIMESTAMP WITH TIME ZONE, end_date TIMESTAMP WITH TIME ZONE, CONSTRUCTOR FUNCTION XS$ACE_TYPE ( privilege_list IN XS$NAME_LIST, granted IN BOOLEAN := TRUE, inverted IN BOOLEAN := FALSE, principal_name IN VARCHAR2, principal_type IN PLS_INTEGER := 1, start_date IN TIMESTAMP WITH TIME ZONE := NULL, end_date IN TIMESTAMP WITH TIME ZONE := NULL) RETURN SELF AS RESULT, MEMBER PROCEDURE set_privileges(privilege_list IN XS$NAME_LIST), MEMBER FUNCTION get_privileges RETURN XS$NAME_LIST, MEMBER PROCEDURE set_grant(granted IN BOOLEAN), MEMBER FUNCTION is_granted RETURN BOOLEAN, MEMBER PROCEDURE set_inverted_principal(inverted IN BOOLEAN), MEMBER FUNCTION is_inverted_principal RETURN BOOLEAN, MEMBER PROCEDURE set_principal(principal_name IN VARCHAR2), MEMBER FUNCTION get_principal RETURN VARCHAR2, MEMBER PROCEDURE set_principal_type (principal_type IN PLS_INTEGER), MEMBER FUNCTION get_principal_type RETURN PLS_INTEGER, MEMBER PROCEDURE set_start_date(start_date IN TIMESTAMP WITH TIME ZONE), MEMBER FUNCTION get_start_date RETURN TIMESTAMP WITH TIME ZONE, MEMBER PROCEDURE set_end_date(end_date IN TIMESTAMP WITH TIME ZONE), MEMBER FUNCTION get_end_date RETURN TIMESTAMP WITH TIME ZONE);

Dependencies

DBA_XS_ACES	DBMS_UTILITY	XS_ADMIN_INT
DBMS_ASSERT	XS$ACE_LIST	XS_ADMIN_UTIL
DBMS_NETWORK_ACL_ADMIN	XS$ACE_TYPE	XS_SECURITY_CLASS_INT
DBMS_SFW_ACL_ADMIN	XS_ACL_INT

Documented Yes: In the Real Application Security Administrator's & Developer's Guide

Exceptions

Error Code	Reason
ORA-28104	Input value for the entity name is not valid
ORA-46152	XS Security - invalid ACE specified
ORA-46215	XS entity by the name <string> did not exist.

First Available 11.2

Security Model Owned by SYS with EXECUTE granted to PUBLIC and DBSFWUSER

According to the Oracle docs, for a user to administer objects in their own schema requires the RESOURCE role.
Another good reason to never grant the RESOURCE role to any user.

The docs further state that the RESOURCE role and the XS_RESOURCE application role include the ADMIN_SEC_POLICY privilege but we have yet to verify this statement. The docs say this is required to administer schema objects in the schema, whatever that is supposed to mean, as well as administering the policy artifacts within the granted schema to achieve policy management within an application. Based on the preceding statement perhaps the intention is security through abuse of the English language. We will try to unravel this and write it in clear and meaningful sentences.

The docs also state: "Users can administer policy enforcement on the schema if they have been granted APPLY_SEC_POLICY privilege. With this privilege, the user can administer policy enforcement within granted schemas to achieve policy management within an application. We have no idea what this means either but, again, will try to get it translated into English.

Source {ORACLE_HOME}/rdbms/admin/xsacl.sql

Subprograms

ADD_ACL_PARAMETER	GRANT_PRIVILEGE	SET_DESCRIPTION
APPEND_ACES	REMOVE_ACES	SET_PARENT_ACL
CREATE_ACL	REMOVE_ACL_PARAMETERS	SET_SECURITY_CLASS
DELETE_ACL	REVOKE_PRIVILEGE

ADD_ACL_PARAMETER

Add a numeric parameter value

Overload 1 xs_acl.add_acl_parameter( acl IN VARCHAR2, policy IN VARCHAR2, parameter IN VARCHAR2, value IN NUMBER);

TBD

Add a string parameter value

Overload 2 xs_acl.add_acl_parameter( acl IN VARCHAR2, policy IN VARCHAR2, parameter IN VARCHAR2, value IN VARCHAR2);

exec xs_acl.add_acl_parameter('DBSECWORXACL','XPOLICY','GEO', 'EMEA');

APPEND_ACES

Append one ACE to the ACL

Overload 1 xs_acl.append_aces( acl IN VARCHAR2, ace IN XS$ACE_TYPE);

DECLARE 

       atype xs$ace_type;

      BEGIN 

       
      atype := xs$ace_type(privilege_list=>xs$name_list('"SELECT"'), 

                             granted=>TRUE,

                             principal_name=>'DBA',

                             principal_type=>xs_acl.ptype_db);

       
      xs_acl_append_aces('DBSECWORXACL', atype);

      END;

      /

Append ACEs to the ACL

Overload 2 xs_acl.append_aces( acl IN VARCHAR2, ace_list IN XS$ACE_LIST);

TBD

CREATE_ACL

Create an Access Control List xs_acl.create_acl( name IN VARCHAR2, ace_list IN XS$ACE_LIST, sec_class IN VARCHAR2 := NULL, parent IN VARCHAR2 := NULL, inherit_mode IN PLS_INTEGER := NULL, description IN VARCHAR2 := NULL);

col acl format a45

      col owner format a20

      col privilege format a20

      col security_class format a20

      

      SELECT acl, owner, privilege, security_class

      FROM dba_xs_aces

      ORDER BY 1;

      

      DECLARE

       alist xs$ace_list;

      BEGIN 

        alist := xs$ace_list(

                   xs$ace_type(privilege_list=>xs$name_list('"SELECT"','VIEW_SENSITIVE_INFO'),

                               granted=>TRUE, 

                               principal_name=>'CSR'),

                   xs$ace_type(privilege_list=>xs$name_list('UPDATE_INFO'),

                               granted=>TRUE,

                               principal_name=>'MGR'));

        xs_acl.create_acl('DBSECWORXACL', alist, 'SECPRIVS', description=>'Data Access');

      END;

      /

      

      SELECT acl, owner, privilege, security_class

      FROM dba_xs_aces

      WHERE acl = 'DBSECWORXACL';

      

      ACL           OWNER  PRIVILEGE            SECURITY_CLASS

      ------------- ------ -------------------- ---------------

      DBSECWORXACL  SYS    SELECT               SECPRIVS

      DBSECWORXACL  SYS    VIEW_SENSITIVE_INFO  SECPRIVS

      DBSECWORXACL  SYS    UPDATE_INFO          SECPRIVS

DELETE_ACL

Delete an ACL xs_acl.delete_acl( acl IN VARCHAR2, delete_option IN PLS_INTEGER := XS_ADMIN_UTIL.DEFAULT_OPTION);

exec xs_acl.delete_acl('DBSECWORXACL');

GRANT_PRIVILEGE (new 21c)

Grant a privilege xs_acl.grant_privilege( acl IN VARCHAR2, privilege IN VARCHAR2, principal IN VARCHAR2, principal_type IN BINARY_INTEGER);

TBD

REMOVE_ACES

Remove all ACEs from the ACL xs_acl.remove_aces(acl IN VARCHAR2);

exec xs_acl.remove_aces('DBSECWORXACL');

REMOVE_ACL_PARAMETERS

Remove all parameters
Overload 1 xs_acl.remove_acl_parameters(acl IN VARCHAR2);

exec xs_acl.remove_acl_parameters('DBSECWORXACL');

Remove a single parameter
Overload 2 xs_acl.remove_acl_parameters( acl IN VARCHAR2, parameter IN VARCHAR2);

exec xs_acl.remove_acl_parameters('DBSECWORXACL', 'GEO');

Remove a policy associated parameter

Overload 3 xs_acl.remove_acl_parameters( acl IN VARCHAR2, policy IN VARCHAR2, parameter IN VARCHAR2);

exec xs_acl.remove_acl_parameters('DBSECWORXACL', 'XPOLICY', 'GEO');

REVOKE_PRIVILEGE (new 21c)

Revoke a granted privilege xs_acl.revoke_privilege( acl IN VARCHAR2, privilege IN VARCHAR2, principal IN VARCHAR2, principal_type IN BINARY_INTEGER);

TBD

SET_DESCRIPTION

Set an ACL description xs_acl.set_description( acl IN VARCHAR2, description IN VARCHAR2);

exec xs_acl.set_description('DBSECWORXACL', 'DBSecWorx Secure ACL');

SET_PARENT_ACL

Sets the parent ACL xs_acl.set_parent_acl( acl IN VARCHAR2, parent IN VARCHAR2, inherit_mode IN PLS_INTEGER);

exec xs_acl.set_parent_acl('DSECWORXACL','SYSTEMACL', xs_acl.extended);

SET_SECURITY_CLASS

Sets the security class xs_acl.set_security_class( acl IN VARCHAR2, sec_class IN VARCHAR2);

col acl format a45

      col owner format a20

      col privilege format a20

      col security_class format a20

      

      SELECT acl, owner, privilege, security_class

      FROM dba_xs_aces

      WHERE acl = 'DBSECWORXACL';

      

      ACL           OWNER  PRIVILEGE            SECURITY_CLASS

      ------------- ------ -------------------- ---------------

      DBSECWORXACL  SYS    SELECT               SECPRIVS

      DBSECWORXACL  SYS    VIEW_SENSITIVE_INFO  SECPRIVS

      DBSECWORXACL  SYS    UPDATE_INFO          SECPRIVS

      

      exec xs_acl.set_security_class('DBSECWORXACL', 'SYSTEM');

      

      SELECT acl, owner, privilege, security_class

      FROM dba_xs_aces

      WHERE acl = 'DBSECWORXACL';

      

      ACL           OWNER  PRIVILEGE            SECURITY_CLASS

      ------------- ------ -------------------- ---------------

      DBSECWORXACL  SYS    SELECT               SYSTEM

      DBSECWORXACL  SYS    VIEW_SENSITIVE_INFO  SYSTEM

      DBSECWORXACL  SYS    UPDATE_INFO          SYSTEM