About Using OCI to Retrieve Column Authorization Indicators

Oracle Call Interface (OCI) applications can access database tables that have data security policies enabled and then test columns for authorization indicators.

• If the column is determined to be unauthorized to the user, a null column value is returned to the user with indicator “unauthorized”.

• If the column authorization cannot be determined, the evaluated column (or column expression) value will be returned to the user along with the indicator “unknown.” If any of the underlying table columns involved in the top column expression evaluation is unauthorized, the authorization indicator can be “unknown” and a null value will be used as the underlying column value for expression valuation.

• If the column is determined as authorized to the user, the evaluated column value and indicator will be returned to the user without authorization indicator.

The OCI return code is to communicate column authorization information to the user. To obtain the authorization information for a column, you must provide a return-code buffer when the column buffer is bound or defined. After the column data is returned to the user buffer, you can check the return code associated with the column for authorization information. The column authorization indicator is applicable to define variables or out-bind variables defined by the application. The return code buffer does not have to be provided if the application is not retrieving the column authorization indicator.

This section describes the following topics:

• Example of Obtaining the Return Code

• About Using the Return Code and Indicator with Authorization Indicator

• About the Warning for Unknown Authorization Indicator

• Using OCI Describe for Column Security

Example of Obtaining the Return Code

The following return codes are used to find the column authorizations:

• ORA-24530: column value is unauthorized to the user

• ORA-24531: column value authorization is unknown

• ORA-24536: column authorization unknown

If the unknown value authorization indicator (ORA-24531) is returned for any column, the OCI function status will be OCI_SUCCESS_WITHINFO and the error ORA-24536 will be returned in the error handle as warning. To suppress the warning, the application can set attribute, OCI_ATTR_NO_COLUMN_AUTH_WARNING to TRUE in the statement handle before fetching:

no_warning = TRUE;
OCIAttrSet(stmthp, OCI_HTYPE_STMT, (void *)&no_warning, 0,
           OCI_ATTR_NO_COLUMN_AUTH_WARNING, errhp);

The default boolean value of OCI_ATTR_NO_COLUMN_AUTH_WARNING is FALSE.

Example: Retrieving Return Codes from OCI for a Column Authorization shows OCI code that retrieves the return codes.

Example: Retrieving Return Codes from OCI for a Column Authorization

OCIDefineByPos(stmthp, &dfnhp, errhp,  1, (void *)data_bufp,  (sb4)data_bufl,
               data_typ, (void *)&data_ind, (ub2 *)&data_rlen,
               (ub2 *)&data_rcode, (ub4)OCI_DEFAULT);
status = OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
if (data_rcode == 24530)
    printf("column value not authorized, indicator=%d\n", data_ind);
else if (data_rcode == 24531)
         printf("column value authorization unknown, indicator=%d\n", data_ind);
else  {
       printf("column value authorized, indicator=%d\n", data_ind);
       /* process column data */
       ...
};

About Using the Return Code and Indicator with Authorization Indicator

To access tables with column security, you should access the return code at least when the column is bound or defined. If the return code is not accessed, the application needs to know if the column value is authorized with other means so that it can correctly interpret the indicator and the value.

You should also provide the indicator for the bind or define if column security is enabled. If the indicator is not provided and the column value is not authorized or unknown, Oracle Database returns error ORA-1405.

If column value authorization is unknown, the authorization indicator (for ORA-24531) will take precedence over the regular return codes that may otherwise be returned to the user. For example, column null fetch (ORA-1405) and column truncation (ORA-1406) may occur at the same time when a non-null column value is returned along with unknown authorization indicator. In that case, the application gets ORA-24531 as the return code for this column, instead of getting ORA-1405 or ORA-1406. Hence the application should not rely on column return code ORA-1405 or ORA-1406 to find the exact column that is null fetched or truncated.

Authorization Indicator Behavior (By Default) and Authorization Indicator Behavior (By Default) - OCI_ATTR_NO_AUTH_WARNING=TRUE summarizes the behavior of the authorization indicator, return code, indicator, and return status.

About the Warning for Unknown Authorization Indicator

If the unknown authorization indicator (ORA-24531) is returned for any column, an OCI warning is returned to the application, that is, the OCI function status will be OCI_SUCCESS_WITH_INFO, instead of OCI_SUCCESS. At the same time, ORA-24536 will be set in the error handle returned to the application.You must check this warning, examine the SQL being executed, and take appropriate action. The error ORA-24536 takes precedence over the error that is returned when the column is authorized or column security is not enabled.

If a column value is unauthorized or authorized, the OCI function status code will not be changed.

By default the column authorization warning is turned on for unknown authorizations. The application should be designed to handle the error. If the application is prepared for column security and wants to ignore any unknown authorization indicator, the OCI warning can be turned off by setting the OCI attribute, OCI_ATTR_NO_COLUMN_AUTH_WARNING to TRUE in the OCI statement handle before the column value is fetched.

Authorization Indicator Behavior (By Default) describes the default authorization behavior for OCI return indicators.

Authorization Indicator Behavior (By Default)

Column Authorization	Column Value	IND ProvidedRC Provided	IND Not ProvidedRC Provided	IND ProvidedRC Not Provided	IND Not ProvidedRC Not Provided
Unauthorized	Any	OCI_SUCCESS Error = 0 IND = -1 RC = 24530	OCI_SUCCESS Error = 1405 IND = N/A RC = 24530	OCI_SUCCESS Error = 0 IND = -1 RC = N/A	OCI_SUCCESS Error = 1405 IND =-N/A RC = N/A
Unknown	Null	SUCCESS_WITH_INFO Error = 24536 (0) IND = -1 RC = 24531 (0)	SUCCESS_WITH_INFO Error = 24536 (1405) IND = N/A RC = 24531 (1405)	SUCCESS_WITH_INFO Error = 24536 (0) IND = -1 RC = N/A	SUCCESS_WITH_INFO Error = 24536 (1405) IND = N/A RC = N/A
Unknown	Not Null and Not Truncated	SUCCESS_WITH_INFO Error = 24536 (0) IND = 0 RC = 24531 (0)	SUCCESS_WITH_INFO Error = 24536 (0) IND = N/A RC = 24531 (0)	SUCCESS_WITH_INFO Error = 24536 (0) IND = 0 RC = N/A	SUCCESS_WITH_INFO Error = 24536 (0) IND = N/A RC = N/A
Unknown	Not Null and Truncated	SUCCESS_WITH_INFO Error = 24536 (24345) IND = data_len RC = 24531 (1406)	SUCCESS_WITH_INFO Error = 24536 (24345) IND = N/A RC = 24531 (1406)	SUCCESS_WITH_INFO Error = 24536 (1406) IND = data_len RC = N/A	SUCCESS_WITH_INFO Error = 24536 (1406) IND = N/A RC = N/A

See Also: Oracle Call Interface Programmer’s Guide Table 2-4 shows the default fetch behavior without column security

Authorization Indicator Behavior (By Default) - OCI_ATTR_NO_AUTH_WARNING=TRUE describes the default behavior when the OCI_ATTR_NO_AUTH_WARNING parameter is set to TRUE.

Authorization Indicator Behavior (By Default) - OCI_ATTR_NO_AUTH_WARNING=TRUE

Column Authorization	Column Value	IND ProvidedRC Provided	IND Not ProvidedRC Provided	IND ProvidedRC Not Provided	IND Not ProvidedRC Not Provided
Unknown	Null	Error = 0 IND = -1 RC = 24531 (0)	Error = 1405 IND = N/A RC = 24531 (1405)	Error = 0 IND = -1 RC = N/A)	Error = 1405 IND = N/A RC = N/A
Unknown	Not Null and Not Truncated	Error = 0 IND = 0 RC = 24531 (0)	Error = 0 IND = N/A RC = 24531 (0)	Error = 0 IND = 0 RC = N/A	Error = 0 IND = N/A RC = N/A
Unknown	Not Null and Truncated	SUCCESS_WITH_INFO Error = 24345 IND = data_len RC = 24531 (1406)	SUCCESS_WITH_INFO Error = 24345 IND = N/A RC = 24531 (1406)	Error = 1406 IND = data_len RC = N/A)	Error = 1406 IND = N/A RC = N/A

Using OCI Describe for Column Security

The OCIDescribeAny() function enables an explicit describe of schema objects. Applications sometimes need to know if a column is protected by a column constraint before fetching data. You can use this information to guide the application to process the data and indicators. This is especially useful to applications that handle dynamic SQL. The attribute OCI_ATTR_XDS_POLICY_STATUS for the OCI parameter handle is of data type ub4 and has the following possible values:

• OCI_XDS_POLICY_NONE: No XDS policy for the column or the policy is not enabled

• OCI_XDS_POLICY_ENABLED: policy is enabled for the column

• OCI_XDS_POLICY_UNKNOWN: policy unknown

If the column status is OCI_XDS_POLICY_NONE, then the column values will always be “authorized.” If the column status is OCI_XDS_POLICY_ENABLED, then the column values will be either “authorized” or “unauthorized.” If the column status is OCI_XDS_POLICY_UNKNOWN, the column value authorization will always be “unknown.”

Example: Using the OCIDescribeAny Function to Enable an Explicit Describe shows how to use the OCIDescribeAny() function to perform an explicit describe on a set of schema objects.

See Also: Oracle Call Interface Programmer’s Guide

Example: Using the OCIDescribeAny Function to Enable an Explicit Describe

void desc_explicit()
{
  const char *table = "col_sec_tab";
  ub4 pos;
  ub2 numcol;
  OCIParam *paramh;
  OCIParam *collst;
  OCIParam *col;
  ub4 colnamelen, colseclen;
  ub1 colname[20];
  ub1 *colnm;
  ub4 colsec;
  ub4 tablen = strlen((char *)table);

  checkerr(errhp, OCIDescribeAny(svchp, errhp, (dvoid *)table, tablen,
                                 OCI_OTYPE_NAME, 0, OCI_PTYPE_TABLE, deschp));

  checkerr(errhp, OCIAttrGet(deschp, OCI_HTYPE_DESCRIBE, &paramh, 0,
                             OCI_ATTR_PARAM, errhp));

  checkerr(errhp, OCIAttrGet(paramh, OCI_DTYPE_PARAM, &numcol, 0,
                             OCI_ATTR_NUM_COLS, errhp));


  checkerr(errhp, OCIAttrGet(paramh, OCI_DTYPE_PARAM, &collst, 0,
                             OCI_ATTR_LIST_COLUMNS, errhp));

  printf("Number of columns = %d\n\n", numcol);

  printf(" Column No   Column Name   Column Security\n");
  printf(" ---------   -----------   ---------------\n\n");


  for (pos = 1; (ub4) pos <= numcol; pos++)
  {
    checkerr(errhp, OCIParamGet (collst, OCI_DTYPE_PARAM, errhp,
                                 (dvoid **)&col, pos));

    checkerr(errhp, OCIAttrGet ((dvoid *)col, (ub4) OCI_DTYPE_PARAM,
                                (dvoid **)&colnm, (ub4 *) &colnamelen,
                                (ub4) OCI_ATTR_NAME, errhp));

    memset (colname, ' ', 20);
    strncpy((char *)colname, (char *)colnm, colnamelen);
    colname[10] = '\0';

    checkerr(errhp, OCIAttrGet ((dvoid *)col, (ub4) OCI_DTYPE_PARAM,
                                (dvoid **)&colsec, (ub4 *) &colseclen,
                                (ub4) OCI_ATTR_XDS_POLICY_STATUS, errhp));

    printf("    %d         %s     %s\n", pos, colname,
                           ((colsec == OCI_XDS_POLICY_ENABLED) ? "ENABLED" :
                           ((colsec == OCI_XDS_POLICY_NONE)   ?  "NONE"    :
                           ((colsec == OCI_XDS_POLICY_UNKNOWN) ? "UNKNOWN" :
                                                                 "ERROR"))));
  }

  return;
}