Blog
DB2utor

Categories

November 30, 2010

Fine Grained Access Control Strengthens Data Security

With the regulatory requirements that have been enacted in recent years, companies must spend more time and money ensuring that customer data is kept private. There's also a technological side to this issue. In the old days application code and program logic would limit program access--end users could only view certain types of data. However, data access is increasingly being controlled through dynamic tools that allow end users to build the SQL. Because of this, more data is being exposed to a greater number of users.

DB2 10 is addressing this problem through what is called fine-grained access control at the row and column level. Rather than coding access logic in the program, database programmers imbed the access control logic in the database. This ensures the data is protected regardless of the applications and tools that are used to access it.

The row level authorization is handled by the CREATE PERMISSION statement. The column level masking is handled by the CREATE MASK statement. The SQL Reference manual contains a few good samples that show how these statements function. Here's one such example:

"The tellers in a bank can only access customers from their branch. All tellers have secondary authorization ID TELLER. The customer service representatives are allowed to access all customers of the bank. All customer service representatives have secondary authorization ID CSR. A row permission is created for each group of personnel in the bank accordingly to the access rule defined by SECADM authority. After row access control is activated for table CUSTOMER, in the SELECT statement the search conditions of both row permissions are merged into the statement and they are combined with the logic OR operator to control the set of rows accessible by each group."

    CREATE PERMISSION TELLER_ROW_ACCESS ON CUSTOMER
        FOR ROWS WHERE VERIFY_GROUP_FOR_USER(SESSION_USER,’TELLER’) = 1
           AND
          BRANCH = (SELECT HOME_BRANCH FROM INTERNAL_INFO
                               WHERE EMP_ID = SESSION_USER)
       ENFORCED FOR ALL ACCESS
       ENABLE;

    COMMIT;

    CREATE PERMISSION CSR_ROW_ACCESS ON CUSTOMER
        FOR ROWS WHERE VERIFY_GROUP_FOR_USER(SESSION_USER,’CSR’) = 1
        ENFORCED FOR ALL ACCESS
        ENABLE;

    COMMIT;

    ALTER TABLE CUSTOMER
         ACTIVATE ROW ACCESS CONTROL;
    COMMIT;
    SELECT * FROM CUSTOMER;

The CREATE MASK statement is used to specify the value returned for the specified column. This statement poses many implications that can impact not only the result set but the validity of the SQL statement. I’ll review the implications of using CREATE MASK in a future post.

For now, here's an example from the SQL Reference manual:

"After column access control is activated for table EMPLOYEE, Paul from the payroll department can see the social security number of the employee whose employee number is 123456. Mary who is a manager can see the last four characters only of the social security number. Peter who is neither cannot see the social security number."

CREATE MASK SSN_MASK ON EMPLOYEE
    FOR COLUMN SSN RETURN
        CASE
                   WHEN (VERIFY_GROUP_FOR_USER(SSESSION_USER,’PAYROLL’) = 1)
                         THEN SSN
                   WHEN (VERIFY_GROUP_FOR_USER(SESSION_USER,’MGR’) = 1)
                         THEN ’XXX-XX-’ || SUBSTR(SSN,8,4)
                    ELSE NULL
       END
    ENABLE;

COMMIT;

ALTER TABLE EMPLOYEE
        ACTIVATE COLUMN ACCESS CONTROL;

COMMIT;

SELECT SSN FROM EMPLOYEE
WHERE EMPNO = 123456;

In the past this type of security had to be handled in your applications. Access was granted at the table level. Controlling what data end users could access was accomplished either by having developers add the row and column level security to the application or by the DBA building DB2 Views. But with fine grained access control, now only a user with SECADM authority can create a row permission or a column mask. This gives the security administration team far greater control over the data being accessed.