Applying Additional Application Privileges to a Column

By default, access to rows is protected by the ACL associated with the data realm. In addition, you can protect a particular column with custom application privileges.

To protect a column for table T, add a list of column constraints to the data security policy that will be applied to table T.

Note:

Starting from Oracle Database 23ai, the absolute maximum number of columns in a table is 1000 or 4096, depending on the value of the MAX_COLUMNS initialization parameter.

When this parameter is set to STANDARD, the maximum number of columns allowed in a database table or view is 1000.
When this parameter is set to EXTENDED, the maximum number of columns allowed in a database table or view is 4096.

For tables approaching 4096 columns (1000 in case of STANDARD), there is a limitation on the number of columns that can be protected as Real Application Security uses an internal virtual column to compute and store the authorization indicator. The sum of the number of columns and the number of protected columns should not exceed 4096 (1000 in case of STANDARD). Number of table columns + Number of protected table columns <=4096. For example, if a table has 4094 columns, up to and including two protected columns are allowed; If the number of columns to be protected exceeds the number allowed, an ORA-28113: policy predicate has error is returned.

For example, the PRODUCT_INFORMATION table in the OE schema contains the LIST_PRICE column. If you want to restrict the display of product prices to specific categories, you can apply an additional application privilege to the LIST_COLUMN table, so that only the sales representative who has logged in can see the product list prices for the categories they manage.

Example: Column with an Additional Application Privilege That Has Been Applied shows a column constraint that protects the LIST_PRICE column with the ACCESS_PRICE application privilege.

Before you add the column constraint, a SELECT statement on the following columns from the OE.PRODUCT_INFORMATION table for products in categories 13 and 14 shows the following output:

PRODUCT_ID	PRODUCT_NAME	CATEGORY_ID	LIST_PRICE
3400	HD 8GB /SE	13	389
3355	HD 8GB /SI	13	NULL
2395	32MB Cache /M	14	123
1755	32MB Cache /NM	14	121
…	…	…	…

After the column constraint is applied, the sales representatives who are responsible for category 13 products see the following output:

PRODUCT_ID	PRODUCT_NAME	CATEGORY_ID	LIST_PRICE
3400	HD 8GB /SE	13	389
3355	HD 8GB /SI	13	NULL
2395	32MB Cache /M	14	NULL
1755	32MB Cache /NM	14	NULL
…	…	…	…

Conversely, sales representatives responsible for category 14 products see this output:

PRODUCT_ID	PRODUCT_NAME	CATEGORY_ID	LIST_PRICE
3400	HD 8GB /SE	13	NULL
3355	HD 8GB /SI	13	NULL
2395	32MB Cache /M	14	123
1755	32MB Cache /NM	14	121
…	…	…	…

In these examples, the list price for product 3355 is NULL. To enable a mid-tier application to distinguish between the true value of authorized data, which could include NULL, and an unauthorized value that is always NULL, use the COLUMN_AUTH_INDICATOR SQL function to check if the column value in a row is authorized. You can mask the unauthorized data with a value different from NULL by modifying the SELECT statement to include a DECODE or CASE function that contains the COLUMN_AUTH_INDICATOR SQL function.

Example: Checking Authorized Data and Masking NULL Values shows a SELECT statement that uses the COLUMN_AUTH_INDICATOR function to check authorized data and the DECODE function to replace NULL with the value restricted.

Afterward, the masked value appears in place of NULL. For example, if our category 13 sales representative logs on and searches for product list prices, they see the following output:

PRODUCT_ID	PRODUCT_NAME	CATEGORY_ID	LIST_PRICE
3400	HD 8GB /SE	13	389
3355	HD 8GB /SI	13	NULL
2395	32MB Cache /M	14	restricted
1755	32MB Cache /NM	14	restricted
…	…	…	…

See Also:

Oracle Database Real Application Security Data Dictionary Views for information about the column constraints data dictionary views, which list existing tables that use column level security
“COLUMN_AUTH_INDICATOR Function”
Example: Structure of a Data Security Policy for an example of a column constraint element within a data security policy.
Configuring OCI and JDBC Applications for Column Authorization if your applications use either Oracle Call Interface (OCI) or JDBC

Example: Column with an Additional Application Privilege That Has Been Applied

column_cons :=
  XS$COLUMN_CONSTRAINT_LIST(
    XS$COLUMN_CONSTRAINT_TYPE(column_list=> XS$LIST('LIST_PRICE'),
                          privilege=> 'ACCESS_PRICE'));

Example: Checking Authorized Data and Masking NULL Values

SELECT PRODUCT_ID, PRODUCT_NAME, CATEGORY_ID
DECODE(COLUMN_AUTH_INDICATOR(LIST_PRICE), 0, 'restricted', 1, LIST_PRICE) LIST_PRICE
FROM PRODUCT_INFORMATION
WHERE CATEGORY_ID = 13;