About Using JDBC to Retrieve Column Authorization Indicators

JDBC applications can access database tables that have data security policies enabled, and test columns for authorization indicators. You can use the JDBC APIs described in this section to check the security attributes and user authorization for a table column.

This section contains:

• About Checking Security Attributes for a Table Column

• About Checking User Authorization for a Table Column

• Example of Checking Security Attributes and User Authorization

About Checking Security Attributes for a Table Column

The getSecurityAttribute method of the oracle.jdbc.OracleResultSetMetaData interface enables you to check the data security policy attribute for a column. The security attribute has the following definition:

public static enum SecurityAttribute
  {
    NONE,
    ENABLED,
    UNKNOWN;
  }

SecurityAttribute can have the following values:

• NONE implies that no column data security policy is enabled for the column. This means that the column either does not have a policy applied to it, or the policy is not enabled.

• ENABLED implies that column data security policy is enabled for the column.

• UNKNOWN implies that the column data security policy for the column is unknown. This could happen, for example, if the column is a union of two columns but only one of the columns has data security attributes.

The getSecurityAttribute method has the following signature:

public SecurityAttribute getSecurityAttribute(int indexOfColumnInResultSet) throws SQLException;

The getSecurityAttribute method returns the SecurityAttribute value for the column.

See Also: Example: Check Security Attributes and User Authorization for an example of using the getSecurityAttribute method

About Checking User Authorization for a Table Column

The getAuthorizationIndicator method of the oracle.jdbc.OracleResultSet interface enables you to check the AuthorizationIndicator attribute for a column. The AuthorizationIndicator attribute has the following definition:

public static enum AuthorizationIndicator
  {
    NONE,
    UNAUTHORIZED,
    UNKNOWN;
  }

AuthorizationIndicator can have the following values:

• NONE implies that access to column data is authorized. The user might have explicit authorization or the column could be lacking security attributes.

• UNAUTHORIZED implies that access to column data is not authorized.

  When the column value is retrieved, the authorization indicator is evaluated based on the enabled column constraint policy for the column. If the user is not authorized to access the column value, a NULL value is returned to the application along with the authentication indicator, AuthorizationIndicator.UNAUTHORIZED.

  If there is a column expression involving the unauthorized base column, the evaluated value is returned to the application along with the AuthorizationIndicator.UNAUTHORIZED indicator. The application should examine the authorization indicator before interpreting the returned data.

• UNKNOWN implies that the authorization indicator cannot be determined.

  Sometimes, the server fails to determine the authorization indicator for a SELECT item due to functionality limitations or performance constrains. This can happen if the query involves a column expression, for example, and the server is unable to compute whether the top operator is supposed to be authorized. In such a scenario, the server returns the authorization indicator, AuthorizationIndicator.UNKNOWN to the application. The returned value can be NULL or not NULL depending on how the column expression operates on the underlying column value.

  If the application sees an UNKNOWN authorization indictor, it should determine whether or not the returned value should be accessed. If the query and its column expressions are designed to handle unauthorized NULL values from the underlying columns, then the application can use the returned value. Otherwise the application may have to take appropriate actions for the returned value.

The getAuthorizationIndicator method has the following forms:

/**
 * Accepts the column index number as an argument and retrieves the corresponding column security AuthorizationIndicator value
 */
public AuthorizationIndicator getAuthorizationIndicator(int columnIndex) throws SQLException;

/**
 * Accepts the column name as a string and retrieves the column security AuthorizationIndicator value
 */
public AuthorizationIndicator getAuthorizationIndicator(String columnName)throws SQLException;

Note:

• The preceding methods throw a SQLException if the index specified in the argument is invalid.

• If a column is masked, the JDBC user sees it as a NULL value. An exception is not thrown for this.

See Also: Example: Check Security Attributes and User Authorization for an example of using the getAuthorizationIndicator method

Example of Checking Security Attributes and User Authorization

Example: Check Security Attributes and User Authorization illustrates the use of the getSecurityAttribute and getAuthorization methods to check security attributes and user authorization. The program uses the sample EMP table to illustrate the procedure.

The EMP table is configured as follows:

Column No.	Column Title	Security Attribute
1	EMPNO	No security attribute
2	ENAME	Active security
3	JOB	No security attribute
4	MGR	Active security
5	HIREDATE	Unknown security attribute
6	SAL	Active security
7	COMM	No security attribute
6	DEPTNO	Active security

The program performs the following actions:

1. Selects rows from the EMP table

2. Uses the getSecurityAttribute method to extract the security setting for each column in the result set. It prints these as column headings

3. Uses the getAuthorizationIndicator method to check the user authorization for returned column values. The program prints these values and formats them as follows:

   An unauthorized value that is returned as NULL is represented by four asterisk characters (****).

Example: Check Security Attributes and User Authorization

PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM EMP");
  ResultSet rs = pstmt.executeQuery();
  OracleResultSetMetaData metaData =
   (OracleResultSetMetaData)rs.getMetaData();
  int nbOfColumns = metaData.getColumnCount();
  OracleResultSetMetaData.SecurityAttribute[] columnSecurity
    = new OracleResultSetMetaData.SecurityAttribute[nbOfColumns];
  // display which columns are protected:
  for(int i=0;i<nbOfColumns;i++)
  {
    columnSecurity[i] = metaData.getSecurityAttribute(i+1);
    System.out.print(columnSecurity[i]);
    System.out.print("\t");
  }
  System.out.println();
  System.out.println("---------------------------------------------");
  while(rs.next())
  {
    for(int colIndex=0;colIndex<nbOfColumns;colIndex++)
    {
      OracleResultSet.AuthorizationIndicator visibility
        = ((OracleResultSet)rs).getAuthorizationIndicator(colIndex+1);
      if(visibility == OracleResultSet.AuthorizationIndicator.UNAUTHORIZED)
        System.out.print("****");
      else
        System.out.print(rs.getString(colIndex+1));
      System.out.print("\t");
    }
    System.out.println("");
  }
  rs.close();
  pstmt.close();

The program generates the following output:

NONE   ENABLED   NONE       ENABLED   UNKNOWN      ENABLED   NONE   ENABLED
----------------------------------------------------------------------------------
7369   SMITH     CLERK      7902      1980-12-17   ****      null   20
7499   ALLEN     SALESMAN   7698      1981-02-20   ****      300    30
7521   WARD      SALESMAN   7698      1981-02-22   ****      500    30
7566   JONES     MANAGER    7839      1981-04-02   ****      null   20
7654   MARTIN    SALESMAN   7698      1981-09-28   ****      1400   30
7698   BLAKE     MANAGER    7839      1981-05-01   ****      null   30
7782   CLARK     MANAGER    7839      1981-06-09   ****      null   10
7788   SCOTT     ANALYST    7566      1987-04-19   ****      null   20
7839   KING      PRESIDENT  null      1981-11-17   ****      null   10
7844   TURNER    SALESMAN   7698      1981-09-08   ****      0      30
7876   ADAMS     CLERK      7788      1987-05-23   ****      null   20
7900   JAMES     CLERK      7698      1981-12-03   ****      null   30
7902   FORD      ANALYST    7566      1981-12-03   ****      null   20
7934   MILLER    CLERK      7782      1982-01-23   ****      null   10