DBSecWorx: DBMS_XS

Oracle DBMS_XS_SESSIONS Built-In Package Versions 12.1 - 19.3

Security Advisory

This package is part of Oracle Database Real Application Security (RAS) and is used to manage sessions and security. Based on that small bit of information alone you may be, like we are, amazed in a not very good way, that execute has been granted to PUBLIC. It should be revoked.

Clearly DBMS_XS_SESSIONS relates to database security so make securing this package and monitoring any usage a very high priority.

Recommended Security Rules

NEVER

Let any user or schema without documented justification or escalated privileges gain access to this package by revoking EXECUTE from PUBLIC

WITH GREAT CARE

Identify legitimate requirements for access to this package and grant EXECUTE explicitly to only justified schemas
Query the data dictionary after EXECUTE has been revoked from PUBLIC to verify the equivalence created is the equivalence approved by IT management and your CISO

CAUTIONS

Some usage may be in the form of dynamic SQL so carefully verify usage requirements in source code as well as in DBA_DEPENDENCIES

How Oracle Works

We were left nearly speechless when we found, new in Oracle 12c, a built-in package that is part of Real Application Security with EXECUTE granted to PUBLIC. It was as though no one in Redwood Shores could figure how to grant minimum privileges to an application only when required. Why would a user with no privilege other than CREATE SESSION need EXECUTE on a package containing objects with names like DELETE NAMESPACE and DESTROY SESSION? Perhaps we should contact us and ask for help.

DBMS_XS_SESSIONS Package Information

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
Defined operation codes passed into namespace event handling functions
attribute_first_read_operation	INTEGER	1
modify_attribute_operation	INTEGER	2
Bit values that identify events of interest for a particular attribute in a namespace that has an event handling function
attribute_first_read_event	INTEGER	1
modify_attribute_event	INTEGER	2
Define return codes that can be returned by a namespace event handling function
event_handling_succeeded	INTEGER	0
event_handling_failed	INTEGER	1
The following constants are used as input into the add/delete/enable_global_callback procedure
create_session_event	PLS_INTEGER	1
attach_session_event	PLS_INTEGER	2
guest_to_user_event	PLS_INTEGER	3
proxy_to_user_event	PLS_INTEGER	4
revert_to_user_event	INTEGER	5
enable_role_event	INTEGER	6
disable_role_event	INTEGER	7
enable_dynamic_role_event	INTEGER	8
disable_dynamic_role_event	INTEGER	9
detach_session_event	INTEGER	10
terminate_session_event	PLS_INTEGER	11
direct_login_event	PLS_INTEGER	12
direct_logoff_event	PLS_INTEGER	13

Data Types CREATE OR REPLACE TYPE DBMS_XS_NSATTR AS OBJECT ( --- Member variables namespace VARCHAR2(130), attribute VARCHAR2(4000), attribute_value VARCHAR2(4000), --- Constructor for DBMS_XS_NSATTR type. Only namespace name is mandatory CONSTRUCTOR FUNCTION DBMS_XS_NSATTR( namespace IN VARCHAR2, attribute IN VARCHAR2 DEFAULT NULL, attribute_value IN VARCHAR2 DEFAULT NULL) RETURN SELF AS RESULT);

Dependencies

DBMS_XS_NSATTR	DBMS_XS_SESSIONS_FFI	XS$LIST
DBMS_XS_NSATTRLIST	PLITBLM	XS$NAME_LIST

Documented Yes: In both the Packages and Types and in the Real Application Security Administrator's & Developer's Guide docs

Exceptions

Error Code	Reason
ORA-46094	XS user not effective
ORA-46210	Source user is not anonymous in user assignment operation

First Available 12.1

Security Model Owned by SYS with EXECUTE granted to PUBLIC

For attaching to a RAS session, the executing user requires ATTACH_SESSION privilege. If dynamic roles are specified ADMINISTER_SESSION privilege is required. If namespaces are specified, appropriate privilege (MODIFY_NAMESPACE, MODIFY_ATTRIBUTE) on the namespaces or ADMIN_ANY_NAMESPACE system privilege is required.

>>>

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/xssess.sql

Subprograms

ADD_GLOBAL_CALLBACK	DELETE_NAMESPACE	REAUTH_SESSION
ASSIGN_USER	DESTROY_SESSION	RESET_ATTRIBUTE
ATTACH_SESSION	DETACH_SESSION	SAVE_SESSION
CREATE_ATTRIBUTE	DISABLE_ROLE	SET_ATTRIBUTE
CREATE_NAMESPACE	ENABLE_GLOBAL_CALLBACK	SET_INACTIVITY_TIMEOUT
CREATE_SESSION	ENABLE_ROLE	SET_SESSION_COOKIE
DELETE_ATTRIBUTE	GET_ATTRIBUTE	SWITCH_USER
DELETE_GLOBAL_CALLBACK	GET_SESSIONID_FROM_COOKIE

ADD_GLOBAL_CALLBACK

Registers a PL/SQL procedure as the event handler with the session operation specified by the event_type parameter dbms_xs_sessions.add_global_callback( event_type IN PLS_INTEGER, callback_schema IN VARCHAR2, callback_package IN VARCHAR2, callback_procedure IN VARCHAR2);

conn sys@pdbdev as sysdba

      

      CREATE OR REPLACE PACKAGE sec_mgr.sess_pkg AUTHID CURRENT_USER AS

        PROCEDURE logoff_proc;

      END sess_pkg;

      /

      

      CREATE OR REPLACE PACKAGE BODY sec_mgr.sess_pkg AS

        PROCEDURE logoff_proc IS

        BEGIN

          NULL;

        END logoff_proc;  

      END sess_pkg;

      /

      

      exec dbms_xs_sessions.add_global_callback(dbms_xs_sessions.direct_logoff_event, 
      'SEC_MGR', 'SESS_PKG', 'LOGOFF_PROC');

      

      exec dbms_xs_sessions.enable_global_callback(dbms_xs_sessions.direct_logoff_event, 
      TRUE, 'SEC_MGR', 'SESS_PKG', 'LOGOFF_PROC');

      

      exec dbms_xs_sessions.enable_global_callback(dbms_xs_sessions.direct_logoff_event, 
      FALSE, 'SEC_MGR', 'SESS_PKG', 'LOGOFF_PROC');

      

      exec dbms_xs_sessions.delete_global_callback('dbms_xs_sessions.direct_logoff_event, 
      'SEC_MGR', 'SESS_PKG', 'LOGOFF_PROC');

ASSIGN_USER

Assigns a named application user to the currently attached anonymous application session dbms_xs_sessions.assign_user( username IN VARCHAR2, is_external IN BOOLEAN DEFAULT FALSE, enable_dynamic_roles IN xs$name_list DEFAULT NULL, disable_dynamic_roles IN xs$name_list DEFAULT NULL, external_roles xs$name_list DEFAULT NULL, authentication_time IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, namespaces IN dbms_xs_nsattrlist DEFAULT NULL,);

SQL> exec sys.xs_principal.create_user('SEC_USER', 'HR', start_date=>SYSDATE, end_date=>SYSDATE+30);

      

      DECLARE

       sessID RAW(16);

      BEGIN

        dbms_xs_sessions.create_session('SEC_USER', sessID);

        dbms_output.put_line(sessID);

        dbms_xs_sessions.attach_session(sessID);

        dbms_xs_sessions.assign_user('UW_USER');

        dbms_xs_sessions.detach_session(TRUE);

        dbms_xs_sessions.destroy_session(sessID);

      END;

      /

ATTACH_SESSION

Attach to an already created RAS session specified by the sessionid dbms_xs_sessions.attach_session( sessionid IN RAW, enable_dynamic_roles IN xs$name_list DEFAULT NULL, disable_dynamic_roles IN xs$name_list DEFAULT NULL, external_roles IN xs$name_list DEFAULT NULL, authentication_time IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, namespaces IN dbms_xs_nsattrlist DEFAULT NULL);

See CREATE_SESSION Demo Below

CREATE_ATTRIBUTE

Creates a new custom attribute in the specified namespace in the currently attached application session dbms_xs_sessions.create_attribute( namespace IN VARCHAR2, attribute IN VARCHAR2, value IN VARCHAR2 DEFAULT NULL, eventreg IN PLS_INTEGER DEFAULT NULL);

BEGIN

        dbms_xs_sessions.dbms_xs_sessions.create_attribute('UWNS', 'item_type', 'generic');

        dbms_xs_sessions.dbms_xs_sessions.delete_attribute('UWNS', 'item_type');

      END;

      /

CREATE_NAMESPACE

Creates a new namespace in the currently attached application session dbms_xs_sessions.create_namespace(namespace IN VARCHAR2);

-- log in and attach to a RAS session

exec dbms_xs_sessions.create_namespace('UWNS');

      

      exec dbms_xs_sessions.delete_namespace('UWNS');

CREATE_SESSION

Create a RAS session with specified 128 char case sensitive username string dbms_xs_sessions.create_session( username IN VARCHAR2, sessionid OUT NOCOPY RAW, is_external IN BOOLEAN DEFAULT FALSE, is_trusted IN BOOLEAN DEFAULT FALSE, namespaces IN dbms_xs_nsattrlist DEFAULT NULL, cookie IN VARCHAR2 DEFAULT NULL);

DECLARE

       sessID RAW(16);

      BEGIN

        xs_principal.create_user('SEC_USER', 'HR', start_date=>SYSDATE, end_date=>SYSDATE+30);

        dbms_xs_sessions.create_session('SEC_USER', sessID);

        dbms_output.put_line(sessID);

        dbms_xs_sessions.attach_session(sessID);

        dbms_xs_sessions.detach_session(TRUE);

        dbms_xs_sessions.destroy_session(sessID);

      END;

      /

      

      4973DC2F46B643F6913A7C5D99AF78CF

      

      PL/SQL procedure successfully completed.

DELETE_ATTRIBUTE

Deletes the specified attribute and its associated value from the specified namespace in the currently attached session dbms_xs_sessions.delete_attribute( namespace IN VARCHAR2, attribute IN VARCHAR2);

See CREATE_ATTRIBUTE Demo Above

DELETE_GLOBAL_CALLBACK

Deletes the global callback procedure for the session event specified by event_type dbms_xs_sessions.delete_global_callback( event_type IN PLS_INTEGER, callback_schema IN VARCHAR2 DEFAULT NULL, callback_package IN VARCHAR2 DEFAULT NULL, callback_procedure IN VARCHAR2 DEFAULT NULL);

See ADD_GLOBAL_CALLBACK Demo Above

DELETE_NAMESPACE

Delete the specified namespace from the currently attached RAS session dbms_xs_sessions.delete_namespace(namespace IN VARCHAR2);

See CREATE_NAMESPACE Demo Above

DESTROY_SESSION

Implicitly detaches all traditional sessions from the application session and destroys the specified session dbms_xs_sessions.destroy_session( sessionid IN RAW, force IN BOOLEAN DEFAULT FALSE);

See ASSIGN_USER Demo Above

DETACH_SESSION

Detaches the current traditional database session from the application session to which it is attached dbms_xs_sessions.detach_session(abort IN BOOLEAN DEFAULT FALSE);

See ASSIGN_USER Demo Above

DISABLE_ROLE

Disables a real application role from the specified application session dbms_xs_sessions.disable_role(role IN VARCHAR2);

exec xs_principal.create_role('RAS_ROLE', TRUE, SYSDATE, SYSDATE+30, description=>'RAS Test Role');

      exec dbms_xs_sessions.enable_role('RAS_ROLE');

      exec dbms_xs_sessions.disable_role('RAS_ROLE');

ENABLE_GLOBAL_CALLBACK

Enables or disables the global callback for the session event specified by event_type dbms_xs_sessions.enable_global_callback( event_type IN PLS_INTEGER, enable IN BOOLEAN DEFAULT TRUE, callback_schema IN VARCHAR2 DEFAULT NULL, callback_package IN VARCHAR2 DEFAULT NULL, callback_procedure IN VARCHAR2 DEFAULT NULL);

See ADD_GLOBAL_CALLBACK Demo Above

ENABLE_ROLE

Enables a real application role in the currently attached application session dbms_xs_sessions.enable_role(role IN VARCHAR2);

See DISABLE_ROLE Demo Above

GET_ATTRIBUTE

Gets the value of the specified attribute in the namespace in the currently attached session dbms_xs_sessions.get_attribute( namespace IN VARCHAR2, attribute IN VARCHAR2, value OUT NOCOPY VARCHAR2);

DECLARE

       attrlist xs$ns_attribute_list;

      BEGIN

        attrlist := xs$ns_attribute_list();

        attrlist.extend(2);

        attrlist(1) := xs$ns_attribute('desc', 'general');

        attrlist(2) := xs$ns_attribute(name=>'item_no',

        attribute_events => xs_namespace.firstread_event);

      

        sys.xs_namespace.create_template('POAttrs', attrlist, 'SH', 'order', 'fulfillment', 'sys.ns_unrestricted_acl', 'Purchase Order');

      END;

      /

      

      DECLARE

       attrVal dba_xs_session_ns_attributes.attribute%TYPE;

      BEGIN

        sys.dbms_xs_sessions.get_attribute('POAttrs','item_no',attrVal);

      END;

      /

GET_SESSIONID_FROM_COOKIE

Get SID for the specified cookie. Raises an exception if no session with specified cookie exists dbms_xs_sessions.get_sessionid_from_cookie( cookie IN VARCHAR2, sessionid OUT NOCOPY RAW);

See SET_SESSION_COOKIE Demo Below

REAUTH_SESSION

Updates the last authentication time for the specified session ID as the current time. Applications must call this procedure when it has reauthenticated an application user. dbms_xs_sessions.reauth_session(sessionid IN RAW DEFAULT NULL);

exec dbms_xs_sessions.reauth_session('4973DC2F46B643F6913A7C5D99AF78CF');

RESET_ATTRIBUTE

Resets the value of an attribute to its default value (if present) or to NULL in the namespace in the current attached session dbms_xs_sessions.reset_attribute( namespace IN VARCHAR2, attribute IN VARCHAR2);

exec dbms_xs_sessions.reset_attribute('UWNS', 'item_no');

SAVE_SESSION

Persist the changes done in currently attached Triton session to the metadata table. It can only be performed from an attached session. dbms_xs_sessions.save_session;

dbms_xs_sessions.save_session;

SET_ATTRIBUTE

Sets the value for the specified attribute to the specified value in the namespace in the currently attached session dbms_xs_sessions_ffi.set_attribute( namespace IN VARCHAR2, attribute IN VARCHAR2, value IN VARCHAR2);

exec dbms_xs_sessions_ffi.set_attribute('UWNS', 'item_type', 'generic');

SET_INACTIVITY_TIMEOUT

Sets the inactivity timeout (in minutes) for the session which is the maximum period of inactivity allowed before the session can be terminated and resource be reclaimed dbms_xs_sessions.set_inactivity_timeout( time IN NUMBER, sessionid IN RAW DEFAULT NULL);

exec dbms_xs_sessions.set_inactivity_timeout('4973DC2F46B643F6913A7C5D99AF78CF', 10);

SET_SESSION_COOKIE

Set the cookie, which must be a unique string, for the session specified by sessionid dbms_xs_sessions.set_session_cookie( cookie IN VARCHAR2, sessionid IN RAW DEFAULT NULL);

DECLARE

       sessID RAW(16);

      BEGIN

        xs_principal.create_user('SEC_USER', 'HR', start_date=>SYSDATE, end_date=>SYSDATE+30);

        dbms_xs_sessions.create_session('SEC_USER', sessID);

        dbms_xs_sessions.set_session_cookie('dbsxcookie', 
      sessID);

      

        dbms_xs_sessions.get_session_cookie('dbsxcookie', sessID);

        dbms_output.put_line(sessID);

      END;

      /

SWITCH_USER

Switch / proxy from current user to another user in currently assigned RAS session dbms_xs_sessions.switch_user ( username IN VARCHAR2, keep_state IN BOOLEAN DEFAULT FALSE, namespaces IN dbms_xs_nsattrlist DEFAULT NULL) ;

exec dbms_xs_sessions.switch_user ('SEC_USER', TRUE, 'UWNS');