About Schema Level Real Application Security Policy Administration

Describes introduction of schema level privileges for Real Application Security policy administration across different applications within the same schema.

Beginning with Oracle Database 12c Release 2 (12.2), Real Application Security introduces schema level privileges, which allows a policy administrator to create, update, and apply a policy in only the granted schema and administer policy enforcement within one application, thereby achieving separate management and enforcement of a policy across different applications within the same schema. This level of policy administration is essential in a Cloud computing scenario where each application may be running under one or more schemas. It then becomes highly desirable for a policy administrator to have the ability to manage and apply data security policies for each individual application in that environment.

Achieving Schema Level Data Security Policy Administration

To achieve schema level data security policy administration, the following new and changed features were introduced:

• The GRANT_SYSTEM_PRIVILEGE and REVOKE_SYSTEM_PRIVILEGE procedures were extended with the addition of the schema parameter to allow granting and revoking Real Application Security privileges on a particular schema to a database or application user as shown in the following syntax descriptions:

  XS_ADMIN_UTIL.GRANT_SYSTEM_PRIVILEGE (
    priv_name      IN VARCHAR2,
    user_name      IN VARCHAR2,
    user_type      IN PLS_INTEGER := XS_ADMIN_UTIL.PTYPE_DB,
    schema         IN VARCHAR2);

  XS_ADMIN_UTIL.REVOKE_SYSTEM_PRIVILEGE (
    priv_name      IN VARCHAR2,
    user_name      IN VARCHAR2,
    user_type      IN PLS_INTEGER := XS_ADMIN_UTIL.PTYPE_DB,
    schema         IN VARCHAR2);

  Where the schema parameter is the schema on which the privilege is granted. The value is NULL if the privilege is a system privilege.

• The system security class ADMIN_SEC_POLICY privilege is extended to schemas for policy management (Create, Read, Update, and Delete) operations. So a policy administrator can grant ADMIN_SEC_POLICY privilege on a particular schema to a user to manage policy artifacts within granted schemas and apply policy management for individual applications. The APIs that are affected by this enhancement include the Real Application Security administrator packages: XS_ACL, XS_DATA_SECURITY, and XS_SECURITY_CLASS

• A new system security class APPLY_SEC_POLICY privilege is added for policy enforcement to allow a policy administrator to enforce a policy within granted schemas within one application. The following data security APIs are checked before enforcing data security policies:

  • XS_DATA_SECURITY.APPLY_OBJECT_POLICY

  • XS_DATA_SECURITY.REMOVE_OBJECT_POLICY

  • XS_DATA_SECURITY.ENABLE_OBJECT_POLICY

  • XS_DATA_SECURITY.DISABLE_OBJECT_POLICY

• Auditing of GRANT_SYSTEM_PRIVILEGE procedure is provided with the audit action AUDIT_GRANT_PRIVILEGE.

• Auditing of REVOKE_SYSTEM_PRIVILEGE procedure is provided with the audit action AUDIT_REVOKE_PRIVILEGE.

• A new data dictionary view DBA_XS_PRIVILEGE_GRANTS is added to show all the Real Applicaton Security system or schema level privilege grants in the database.

• In addition, the following views are added: ALL_XS_SECURITY_CLASSES, ALL_XS_SECURITY_CLASS_DEP, ALL_XS_PRIVILEGES, ALL_XS_IMPLIED_PRIVILEGES, ALL_XS_ACLS, ALL_XS_ACES, ALL_XS_POLICIES, ALL_XS_REALM_CONSTRAINTS, ALL_XS_INHERITED_REALMS, ALL_XS_ACL_PARAMETERS, ALL_XS_COLUMN_CONSTRAINTS, ALL_XS_APPLIED_POLICIES, and DBA_XS_PRIVILEGE_GRANTS

This section includes the following topic: Setting Up and Enabling a Schema Level Data Security Policy.

Setting Up and Enabling a Schema Level Data Security Policy

Describes how to set up and enable a schema level data security policy for two application administrators.

The following set of examples describe how to set up and enable a schema level data security policy for two application administrators to administer two different schemas. Later, it will show how to disable the data security policy and revoke system privileges from these two application administrator users.

Create the application administrator users, then grant them the roles they need.

EXEC SYS.XS_PRINCIPAL.CREATE_USER(NAME => 'app_admin_user1', SCHEMA => 'HR');
EXEC SYS.XS_PRINCIPAL.SET_PASSWORD('app_admin_user1', 'PASSWORD');
EXEC SYS.XS_PRINCIPAL.GRANT_ROLES('app_admin_user1', 'XSCONNECT');

EXEC SYS.XS_PRINCIPAL.CREATE_USER(NAME => 'app_admin_user2', SCHEMA => 'SH');
EXEC SYS.XS_PRINCIPAL.SET_PASSWORD('app_admin_user2', 'PASSWORD');
EXEC SYS.XS_PRINCIPAL.GRANT_ROLES('app_admin_user2', 'XSCONNECT');

The Real Application Security Administrator with either SYS or a user granted GRANT ANY PRIVILEGE grants the system privileges ADMIN_SEC_POLICY and APPLY_SEC_POLICY to each application administrator user on the respective HR and SH schemas to the Real Application Security user, PTYPE_XS.

EXEC SYS.XS_ADMIN_UTIL.GRANT_SYSTEM_PRIVILEGE('ADMIN_SEC_POLICY', 'app_admin_user1', SYS.XS_ADMIN_UTIL.PTYPE_XS, 'HR');

EXEC SYS.XS_ADMIN_UTIL.GRANT_SYSTEM_PRIVILEGE('APPLY_SEC_POLICY', 'app_admin_user1', SYS.XS_ADMIN_UTIL.PTYPE_XS, 'HR');

EXEC SYS.XS_ADMIN_UTIL.GRANT_SYSTEM_PRIVILEGE('ADMIN_SEC_POLICY', 'app_admin_user2', SYS.XS_ADMIN_UTIL.PTYPE_XS, 'SH');

EXEC SYS.XS_ADMIN_UTIL.GRANT_SYSTEM_PRIVILEGE('APPLY_SEC_POLICY', 'app_admin_user2', SYS.XS_ADMIN_UTIL.PTYPE_XS, 'SH');

Next, the policy administrator applies the desired object policy to a particular table in an application and enables it. Once created, then the policy administrator applies the data security policy EMPLOYEES_DS to the EMPLOYEES table in the HR schema.

BEGIN
  SYS.XS_DATA_SECURITY.ENABLE_OBJECT_POLICY(policy =>'EMPLOYEES_DS',
                                            schema=>'hr',
                                            object=>'employees');
END;
/

BEGIN
  SYS.XS_DATA_SECURITY.ENABLE_OBJECT_POLICY(policy =>'CUSTOMERS_DS',
                                            schema=>'sh',
                                            object=>'customers');
END;
/

Disabling the Data Security Policy and Revoking the System Privileges from the User

Describes how to disable data security policy and revoke the system privileges from the user.

How to Disable the Data Security Policy and Revoke the System Privileges from the User

To disable the EMPLOYEES_DS data security policy for the EMPLOYEES table in the HR schema, the policy administrator does the following:

BEGIN
  SYS.XS_DATA_SECURITY.DISABLE_OBJECT_POLICY(policy =>'EMPLOYEES_DS',
                                             schema=>'hr',
                                             object=>'employees');
END;
/

To disable the CUSTOMERS_DS data security policy for the CUSTOMERS table in the SH schema, the policy administrator does the following:

BEGIN
  SYS.XS_DATA_SECURITY.DISABLE_OBJECT_POLICY(policy =>'CUSTOMERS_DS',
                                             schema=>'sh',
                                             object=>'customers');
END;
/

To revoke the system privileges from application administrator users app_admin_user1 and app_admin_user2 not from the role policy_admin_role because there may be other policy administrators with this same role enabled, the Real Application Security Administrator with either SYS privilege or a user granted GRANT ANY PRIVILEGE privilege revokes the system privileges ADMIN_SEC_POLICY and APPLY_SEC_POLICY from application users app_admin_user1 and app_admin_user2 as follows:

EXEC SYS.XS_ADMIN_UTIL.REVOKE_SYSTEM_PRIVILEGE('APPLY_SEC_POLICY', 'app_admin_user1', SYS.XS_ADMIN_UTIL.PTYPE_XS, 'HR');
EXEC SYS.XS_ADMIN_UTIL.REVOKE_SYSTEM_PRIVILEGE('ADMIN_SEC_POLICY', 'app_admin_user1', SYS.XS_ADMIN_UTIL.PTYPE_XS, 'HR');

EXEC SYS.XS_ADMIN_UTIL.REVOKE_SYSTEM_PRIVILEGE('APPLY_SEC_POLICY', 'app_admin_user2', SYS.XS_ADMIN_UTIL.PTYPE_XS, 'SH');
EXEC SYS.XS_ADMIN_UTIL.REVOKE_SYSTEM_PRIVILEGE('ADMIN_SEC_POLICY', 'app_admin_user2', SYS.XS_ADMIN_UTIL.PTYPE_XS, 'SH');