Oracle | XS_PRINCIPAL

Oracle XS_PRINCIPAL Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Real Application Security Principal Management

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
User's Status
ACTIVE	PLS_INTEGER	1
INACTIVE	PLS_INTEGER	2
UNLOCK	PLS_INTEGER	3
EXPIRED	PLS_INTEGER	4
LOCKED	PLS_INTEGER	5
Dynamic Role Scopes
SESSION_SCOPE	PLS_INTEGER	0
REQUEST_SCOPE	PLS_INTEGER	1
Verifier Types
XS_SHA512	PLS_INTEGER	1
XS_SALTED_SHA1	PLS_INTEGER	2

Dependencies

DBMS_RXS_LIB	XS$NAME_LIST	XS_ADMIN_INT
DBMS_XS_PRINCIPALS	XS$ROLE_GRANT_LIST	XS_ADMIN_UTIL
DUAL	XS$ROLE_GRANT_TYPE	XS_PRINCIPAL_INT
PLITBLM

Documented Yes

First Available 12.1

PRAGMA PRAGMA SUPPLEMENTAL_LOG_DATA(default, AUTO);

Security Model Owned by SYS with EXECUTE granted to PUBLIC

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

Related System Privileges

ALTER ANY ROLE	CREATE ROLE	CREATE_USER
ALTER USER

Subprograms

ADD_PROXY_TO_DBUSER	GRANT_ROLES	SET_EFFECTIVE_DATES
ADD_PROXY_USER	REMOVE_PROXY_FROM_DBUSER	SET_GUID
CREATE_DYNAMIC_ROLE	REMOVE_PROXY_USERS	SET_PASSWORD
CREATE_ROLE	REVOKE_ROLES	SET_PROFILE
CREATE_USER	SET_ACL	SET_USER_SCHEMA
DELETE_PRINCIPAL	SET_DESCRIPTION	SET_USER_STATUS
ENABLE_BY_DEFAULT	SET_DYNAMIC_ROLE_DURATION	SET_VERIFIER
ENABLE_ROLES_BY_DEFAULT	SET_DYNAMIC_ROLE_SCOPE

ADD_PROXY_TO_DBUSER

Add a proxy user to a database user xs_principal.add_proxy_to_dbuser( database_user IN VARCHAR2, proxy_user IN VARCHAR2, is_external IN BOOLEAN := FALSE);

exec xs_principal.add_proxy_to_dbuser('SEC_MGR', 'UWPROXY');

ADD_PROXY_USER

Add a proxy user to a lightweight user. Proxy_user will proxy to and act on behalf of target_user. If the target_roles is null, only xspublic and xsswitch default roles will be enable for the proxy user.

Overload 1 xs_principal.add_proxy_user( target_user IN VARCHAR2, proxy_user IN VARCHAR2, target_roles IN xs$name_list);

TBD

Add proxy user to a target user with all with all default enabled roles of target user

Overload 2 xs_principal.add_proxy_user( target_user IN VARCHAR2, proxy_user IN VARCHAR2);

exec xs_principal.add_proxy_user('SEC_USER', 'UWPROXY');

CREATE_DYNAMIC_ROLE

Creates a new dynamic application role that can be dynamically enabled or disabled by an application based on the criteria defined by the application xs_principal.create_dynamic_role( name IN VARCHAR2, duration IN PLS_INTEGER := NULL, scope IN PLS_INTEGER := SESSION_SCOPE, description IN VARCHAR2 := NULL, acl IN VARCHAR2 := NULL);

exec xs_principal.create_dynamic_role('DYN_ROLE', 60, description=>'RAS Dynamic Role');

      

      set linesize 141

      col description format a79

      

      SELECT name, duration, system_defined, scope, description

      FROM dba_xs_dynamic_roles;

CREATE_ROLE

Creates a new application role xs_principal.create_role( name IN VARCHAR2, enabled IN BOOLEAN := FALSE, start_date IN TIMESTAMP WITH TIME ZONE := NULL, end_date IN TIMESTAMP WITH TIME ZONE := NULL, guid IN RAW := NULL, external_source IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL);

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

      

      set linesize 141

      col name format a20

      col start_date format a10

      col end_date format a10

      col description format a90

      

      SELECT name, default_enabled, start_date, end_date, description

      FROM dba_xs_roles;

CREATE_USER

Creates a new application user xs_principal.create_user( name IN VARCHAR2, schema IN VARCHAR2 := NULL, status IN PLS_INTEGER := ACTIVE, start_date IN TIMESTAMP WITH TIME ZONE := NULL, end_date IN TIMESTAMP WITH TIME ZONE := NULL, guid IN RAW := NULL, external_source IN VARCHAR2 := NULL, description IN VARCHAR2 := NULL acl IN VARCHAR2 := NULL);

SQL> conn sys@pdbdev as sysdba

      Enter password:

      Connected.

      

      CREATE USER sec_mgr

      IDENTIFIED BY oracle1

      DEFAULT TABLESPACE uwdata

      TEMPORARY TABLESPACE temp

      * QUOTA UNLIMITED ON uwdata;

      

      User created.

      

      GRANT create user TO sec_mgr;

      

      Grant succeeded.

      

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

      

      PL/SQL procedure successfully completed.

      

      exec sys.xs_principal.set_password('sec_user', 'oracle2');

      

      PL/SQL procedure successfully completed.

      

      col name format a12

      col profile format a8

      col account_status format a15

      col description format a44

      

      SELECT name, roles_default_enabled, status, account_status, profile, direct_logon_user, description

      FROM dba_xs_users;

      

      -- note that XS$GUEST does not appear in a query of DBA_USERS

DELETE_PRINCIPAL

Delete the principal xs_principal.delete_principal( principal IN VARCHAR2, delete_option IN PLS_INTEGER := xs_admin_util.default_option);

conn sys@pdbdev as SYSDBA

      

      col description format a66

      

      SELECT name, type, description

      FROM dba_xs_principals

      ORDER BY 1;

      

      exec xs_principal.delete_principal('RAS_ROLE');

ENABLE_BY_DEFAULT

Enables/disables the role by default. This API only works on regular roles xs_principal.enable_by_default( role IN VARCHAR2, enabled IN BOOLEAN := TRUE);

SELECT name, default_enabled

      FROM dba_xs_roles;

      

      exec xs_principal.enable_by_default('RAS_ROLE', FALSE);

      

      SELECT name, default_enabled

      FROM dba_xs_roles;

ENABLE_ROLES_BY_DEFAULT

Enables/disables all directly granted roles for a user by default and inspite of its name this API only works on users xs_principal.enable_roles_by_default( user IN VARCHAR2, enabled IN BOOLEAN := TRUE);

exec xs_principal.enable_roles_by_default('SEC_USER');

GRANT_ROLES

Grant a role to a principal

Overload 1 xs_principal.grant_roles( grantee IN VARCHAR2, role IN VARCHAR2, start_date IN TIMESTAMP WITH TIME ZONE:= NULL, end_date IN TIMESTAMP WITH TIME ZONE:= NULL);

exec xs_principal.grant_roles('SEC_USER', 'RAS_ROLE', SYSDATE, SYSDATE+1);

Grant a list of roles to a principal

Overload 2 xs_principal.grant_roles( grantee IN VARCHAR2, role_list IN sys.xs$role_grant_list);

TBD

REMOVE_PROXY_FROM_DBUSER

Remove a proxy user from db user xs_principal.remove_proxy_from_dbuser( database_user IN VARCHAR2, proxy_user IN VARCHAR2);

exec xs_principal.grant_roles('SEC_USER', 'UWPROXY');

REMOVE_PROXY_USERS

Remove all existing proxy users from a target user

Overload 1 xs_principal.remove_proxy_users(target_user IN VARCHAR2);

exec xs_principal.remove_proxy_users('SEC_USER');

Remove a proxy user from a target user

Overload 2 xs_principal.remove_proxy_users( target_user IN VARCHAR2, proxy_user IN VARCHAR2);

exec xs_principal.remove_proxy_users('SEC_USER', 'UWPROXY');

REVOKE_ROLES

Revoke all roles from a principal
Overload 1 xs_principal.revoke_roles(grantee IN VARCHAR2);

exec xs_principal.revoke_roles('SEC_USER');

Revoke a role from a principal

Overload 2 xs_principal.revoke_roles( grantee IN VARCHAR2, role IN VARCHAR2);

exec  xs_principal.grant_roles('SEC_USER', 'RAS_ROLE', SYSDATE, SYSDATE+1);

      

      PL/SQL procedure successfully completed.

      

      exec xs_principal.revoke_roles('SEC_USER', 'RAS_ROLE');

      

      PL/SQL procedure successfully completed.

Revoke a list of roles from a principal

Overload 3 xs_principal.revoke_roles( grantee IN VARCHAR2, role_list IN sys.xs$name_list);

TBD

SET_ACL

Set Access Control List xs_principal.set_acl( principal IN VARCHAR2, acl IN VARCHAR2);

exec xs_principal.set_acl('XS_USER_EXT', 'mlib-org-permissions.xml');

SET_DESCRIPTION

Set the description of a principal xs_principal.set_description( principal IN VARCHAR2, description IN VARCHAR2);

exec xs_principal.set_description('XS_USER_EXT', 'RAS Role Description');

SET_DYNAMIC_ROLE_DURATION

Update the duration of a dynamic role xs_principal.set_dynamic_role_duration( role IN VARCHAR2, duration IN PLS_INTEGER);

exec xs_principal.set_dynamic_role_duration('DYN_ROLE', 30);

SET_DYNAMIC_ROLE_SCOPE

Update the scope attribute of a dynamic role xs_principal.set_dynamic_role_scope( role IN VARCHAR2, scope IN PLS_INTEGER);

exec xs_principal.set_dynamic_role_scope('DYN_ROLE', xs_principal.session_scope);

SET_EFFECTIVE_DATES

Update effective date of a user/role xs_principal.set_effective_dates( principal IN VARCHAR2, start_date IN TIMESTAMP WITH TIME ZONE:= NULL, end_date IN TIMESTAMP WITH TIME ZONE:= NULL);

exec xs_principal.set_effective_dates('SEC_USER', SYSDATE, SYSDATE+1);

      

      col start_date format a36

      col end_date format a36

      

      SELECT name, start_date, end_date

      FROM dba_xs_users

      ORDER BY 1;

      

      NAME      START_DATE                           END_DATE

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

      SEC_USER  05-FEB-20 08.37.00.000000 AM +00:00  04-FEB-21 08.37.00.000000 AM +00:00

SET_GUID

The guid only can be set if the principal is from an external source and the previous guid is NULL xs_principal.set_guid( principal IN VARCHAR2, guid IN RAW);

SELECT name, guid, type, external_source EXT_SRC, description

      FROM dba_xs_principals;

      

      exec xs_principal.set_guid('XS_USER_EXT', sys_guid());

SET_PASSWORD

Set the user password xs_principal.set_password( user IN VARCHAR2, password IN VARCHAR2, type IN PLS_INTEGER := XS_SHA512, opassword IN VARCHAR2 := NULL); PRAGMA SUPPLEMENTAL_LOG_DATA(set_password, NONE);

See CREATE_USER demo above

SET_PROFILE

Set the user profile xs_principal.set_profile( user IN VARCHAR2, profile IN VARCHAR2);

exec xs_principal.set_profile('sec_user', 'cis_profile');

SET_USER_SCHEMA

Update the schema that a lightweight user owns. Only applies to LW users xs_principal.set_user_schema( user IN VARCHAR2, schema IN VARCHAR2);

exec xs_principal.set_user_schema('sec_user', 'HR');

SET_USER_STATUS

Set/modify the user status that a lightweight user owns xs_principal.set_user_status( user IN VARCHAR2, status IN PLS_INTEGER);

exec xs_principal.set_user_status('sec_user', xs_principal.active);

SET_VERIFIER

Sets or modifies an application user account verifier. The procedure directly inserts the verifier and the value of the type parameter into the dictionary table enabling admins to migrate users into RAS with knowledge of the verifier and not the password. xs_principal.set_verifier( user IN VARCHAR2, verifier IN VARCHAR2, type IN PLS_INTEGER := XS_SHA512); PRAGMA SUPPLEMENTAL_LOG_DATA(set_verifier, NONE);

SELECT standard_hash('ZZYZX'') FROM dual;

      

      STANDARD_HASH('ZZYZX')

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

      04867015BE89A96E9FEE095F87B606595306D6AB

      

      exec xs_principal.set_verifier('SEC_USER', standard_hash('ZZYZX'));

Morgan's Library Page Footer

This site is maintained by Dan Morgan. Last Updated:

DBSecWorx