Oracle  XS_PRINCIPAL
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.
Purpose Real Application Security
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
DUAL XS$ROLE_GRANT_LIST XS_ADMIN_UTIL
XS$NAME_LIST XS_ADMIN_INT XS_PRINCIPAL_INT
Documented Yes
First Available 12cR1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/xsprin.sql
Related System Privileges
ALTER ANY ROLE CREATE ROLE CREATE_USER
ALTER USER    
Subprograms
 
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', '???');
 
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', '???');
 
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);
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 a35
col end_date format a35
col description format a30

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);
SQL> conn sys@pdbdev as sysdba
Enter password:
Connected.

SQL> CREATE USER sec_mgr
  2  IDENTIFIED BY oracle1
  3  DEFAULT TABLESPACE uwdata
  4  TEMPORARY TABLESPACE temp
  5* QUOTA UNLIMITED ON uwdata;

User created.

SQL> GRANT create user TO sec_mgr;

Grant succeeded.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

SQL> col name format a12
SQL> col profile format a8
SQL> col account_status format a15
SQL> col description format a44

SQL> SELECT name, roles_default_enabled, status, account_status, profile, direct_logon_user, description
  2  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);
SQL> conn sys@pdbdev as SYSDBA

SQL> col description format a66

SQL> SELECT name, type, description
  2  FROM dba_xs_principals
  3  ORDER BY 1;

SQL> 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);
SQL> SELECT name, default_enabled
  2  FROM dba_xs_roles;

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

SQL> SELECT name, default_enabled
  2  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 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);
 
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', '???');
 
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', '???');
 
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);
SQL> exec xs_principal.grant_roles('SEC_USER', 'RAS_ROLE', SYSDATE, SYSDATE+1);

PL/SQL procedure successfully completed.

SQL> 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 xs$name_list);
TBD
 
SET_DESCRIPTION
Set the description of a principal xs_principal.set_description(
principal   IN VARCHAR2,
description IN VARCHAR2);
exec xs_principal.set_description('SEC_USER', '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', 3);
 
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);

SQL> col start_date format a36
SQL> col end_date format a36

SQL> SELECT name, start_date, end_date
  2  FROM dba_xs_users
  3  ORDER BY 1;

NAME      START_DATE                           END_DATE
--------- ------------------------------------ ------------------------------------
SEC_USER  22-DEC-15 07.21.00.000000 AM +00:00  23-DEC-15 07.21.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);
SQL> exec xs_principal.set_guid('EXTERNAL_DBMS_AUTH', 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);
SQL> SELECT standard_hash('ZZYZX'') FROM dual;

STANDARD_HASH('ZZYZX')
----------------------------------------
04867015BE89A96E9FEE095F87B606595306D6AB

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

Related Topics
DBMS_XS_PRINCIPALS
DBMS_XS_SESSIONS
DBMS_XS_SESSIONS_FFI
DBMS_XS_SIDP
DBMS_XS_SYSTEM
DBMS_XS_SYSTEM_FFI
Packages
XS_ACL
XS_ADMIN_UTIL
XS_DATA_SECURITY
XS_DATA_SECURITY_UTIL
XS_DIAG
XS_DIAG_INT
XS_NAMESPACE
XS_SECURITY_CLASS
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved