10 Oracle Database Real Application Security SQL Functions

COLUMN_AUTH_INDICATOR Function

The COLUMN_AUTH_INDICATOR function checks whether the specified table column is authorized on a particular table row. If the current application user is authorized by data security policies to access the column value of the current row, or if the column is not protected by any data security policies, then it returns 1. If the application user is not authorized, it returns 0.

Syntax

COLUMN_AUTH_INDICATOR(col)
RETURN BOOLEAN;

Parameters

Example

SELECT po_number, project_id, region, 
 DECODE(COLUMN_AUTH_INDICATOR(price), 0, 'xxxxxx', 1, price) price  
 FROM purchaseorder  
 WHERE po_number 
 BETWEEN 10000 and 10003;

XS_SYS_CONTEXT Function

The XS_SYS_CONTEXT function provides quick access to session attributes in the current application session without incurring the overhead that results from using the PL/SQL APIs. The SYS_XS_CONTEXT function definition mirrors that of the SYS_CONTEXT function and can be described as application session counterpart to SYS_CONTEXT. XS_SYS_CONTEXT returns the requested namespace and attribute. If they do not exist, then it returns NULL.

Table 10-2 lists the attributes in predefined namespace XS$SESSION.

To retrieve the name of the currently attached Real Application Security session application user, you can use the following form of the XS_SYS_CONTEXT function:

XS_SYS_CONTEXT('XS$SESSION', 'SESSION_XS_USER')

The function returns NULL if no Real Application Security session is currently attached to the database session. The function returns the currently attached Real Application Security session application user even if it is called from within the body of a definer's rights unit, like a definer's rights view.

To retrieve the identifier (ID) for the currently attached Real Application Security session application user, you can use the following form of the XS_SYS_CONTEXT function:

XS_SYS_CONTEXT('XS$SESSION', 'SESSION_XS_USER_GUID')

The function returns NULL if no Real Application Security session is currently attached to the database session. The function returns the currently attached Real Application Security session application user ID even if it is called from within the body of a definer's rights unit, like a definer's rights view.

Syntax

XS_SYS_CONTEXT(
 namespace   IN VARCHAR2 
 attribute   IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Example

SELECT XS_SYS_CONTEXT('XS$SESSION', 'SESSION_ID') FROM DUAL; 

ORA_CHECK_ACL Function

The ORA_CHECK_ACL function checks whether an application user has the queried application privileges according to a list of ACLs. Oracle Database uses this function automatically when the application user runs a query on a table that has data security policy enabled. If the specified application privileges have been granted to the application user, ORA_CHECK_ACL returns 1. If they are not granted to the application user, then it returns 0.

Syntax

ORA_CHECK_ACL(
 acls        IN RAW,
 (privileges IN VARCHAR(128))+)
return NUMBER;

Parameters

Examples

The following example uses ORA_CHECK_ACL to check whether the application user has been granted the P1 and P2 application privileges in the ACL1 ACL.

SELECT ORA_CHECK_ACL(TO_ACLID('ACL1'),'P1', 'P2') INTO ACLRESULT FROM DUAL;

ORA_GET_ACLIDS Function

The ORA_GET_ACLIDS function returns a list of ACL IDS associated with an object instance of data security policy enabled tables for the current application user. Oracle Database evaluates every dynamic data realm constraint rule, because ORA_GET_ACLIDS captures all ACL identifiers that are associated with the matching data realm constraints, if access to the current row has been granted. If the data realm constraints are from detail tables in a master-detail relationship, ORA_GET_ACLIDS retrieves the ACL identifiers from the master table as well as the detail table. If multiple data security policies have been applied to a table, ORA_GET_ACLIDS returns the ACLs associated with each policy.

Syntax

ORA_GET_ACLIDS (
 table_alias  IN VARCHAR2, 
 (privileges IN VARCHAR(128))+)
RETURN RAW;

Parameters

Example

SELECT ORA_GET_ACLIDS(t, 'SELECT', 'VIEW_LOC') from SCOTT.DEPT t;

ORA_CHECK_PRIVILEGE Function

The ORA_CHECK_PRIVILEGE function checks whether the specified privileges have been granted to an application user. If the specified privileges have been granted to the application user, ORA_CHECK_PRIVILEGE returns 1. This function only works for system privileges, such as CREATE_SESSION. If the system privileges are not granted to the application user, then it returns 0.

Syntax

ORA_CHECK_PRIVILEGE(
 (privs IN VARCHAR(128))+)
return NUMBER;

Parameters

Examples

The following example uses ORA_CHECK_PRIVILEGE to check whether the application user has been granted the CREATE_SESSION system privilege.

SELECT ORA_CHECK_PRIVILEGE('CREATE_SESSION') FROM DUAL;

TO_ACLID Function

The TO_ACLID function returns the ACL IDs of the ACL names supplied to it.

Syntax

TO_ACLID(
 (acls IN VARCHAR(128))+)
return NUMBER;

Parameters

Examples

The following example uses the TO_ACLID function to return the ACL ID for ACL1.

SELECT ORA_CHECK_ACL(TO_ACLID('ACL1'),'P1', 'P2') INTO ACLRESULT FROM DUAL;