Community

Row and Column Access Control in dashDB

Share this post:

The IBM dashDB Enterprise plan introduces row and column access control (RCAC) as a solution to help you further secure your data. RCAC is also referred to as fine-grained access control or FGAC. RCAC gives you the ability to regulate data access at the row and column levels. You can use RCAC to ensure that your users have access to only the data that is required for their work.

RCAC uses row permissions and column masks. Row permissions and column masks complement the table privileges security model. The first task in applying the table privileges security model is to grant the user access to the table. If the user has access to the table, the user is next given permissions to access specific rows of the table. Column masks that determine whether the user sees the actual or masked value in the column are then applied to specific columns of the table.

For example, row permissions ensure that when a doctor queries the table of patient data, he/she can see only the rows of data that represent patients under his/her care. Other patients are nonexistent as far as the doctor is concerned. Similarly, a column mask defined on the Social Security Number (SSN) column of that same table of patient data ensures that the SSN column can only be viewed by privileged users. For non-privileged users, the SSN is set to NULL or masked according to the column mask definition.

Example of controlling access to sensitive data with RCAC

To more concretely illustrate the usage of RCAC, let’s use an example scenario that applies to a healthcare organization. Because the organization handles patient health and personal information, the healthcare organization must comply with government privacy and data protection regulations such as the Health Insurance Portability and Accountability Act (HIPAA). These privacy and data protection regulations ensure that any sensitive patient medical or personal information is shared, viewed, and modified only by authorities who are privileged to do so.

In order to meet the security and privacy requirements for this example scenario, we’ll implement the following security policies in a dashDB Enterprise plan database:

  • Database administrators can see all of the data.
  • Doctors can only access their patients’ data and they can see only the last 4 digits of a patient’s Social Security Number (SSN).
  • Patients, or patient representatives, can access only their own data and they can see only the last 4 digits of their Social Security Number (SSN).

Configure data access roles

The following example scenario instructions describe how you can use RCAC to ensure that the patient data stored in the dashDB database system is secure and only privileged users have access to the data:

  • Log in to the dashDB console as an Administrator.
  • Go to the Settings > Users and Privileges console page. Add new users BOB, LEE, and JANE, and assign them to the User role.
  • Go to the Run SQL console page. Create the user-defined user roles PATIENT, DOCTOR, and ADMIN. Assign the users BOB, LEE, and JANE to the user-defined user roles.
    CREATE ROLE PATIENT;
    CREATE ROLE DOCTOR;
    CREATE ROLE ADMIN;

    GRANT ROLE PATIENT TO USER BOB;
    GRANT ROLE DOCTOR TO USER LEE;
    GRANT ROLE ADMIN TO USER JANE;


    User Bob is a patient, assigned to the PATIENT role. User Lee is a doctor, assigned to the DOCTOR role. Jane is an administrator, assigned to the ADMIN role.
  • Create the SAMPLE.PATIENT table and grant privileges to the roles.
    CREATE TABLE SAMPLE.PATIENT (
    PATIENT_NAME VARCHAR(18),
    DOCTOR_NAME VARCHAR(18),
    SSN CHAR(11),
    PHARMACY VARCHAR(50)
    );

    GRANT SELECT ON TABLE SAMPLE.PATIENT TO ROLE PATIENT;
    GRANT SELECT, UPDATE ON TABLE SAMPLE.PATIENT TO ROLE DOCTOR;
    GRANT ALL ON TABLE SAMPLE.PATIENT TO ROLE ADMIN;


    The SAMPLE.PATIENT table will store patient personal and health information.
  • Create a row permission on the SAMPLE.PATIENT table and activate the row permission.
    CREATE PERMISSION ROW_ACCESS ON SAMPLE.PATIENT
    FOR ROWS WHERE(VERIFY_ROLE_FOR_USER(SESSION_USER,'PATIENT') = 1
    AND SAMPLE.PATIENT.PATIENT_NAME = SESSION_USER)
    OR (VERIFY_ROLE_FOR_USER(SESSION_USER,'DOCTOR') = 1 AND SAMPLE.PATIENT.DOCTOR_NAME = SESSION_USER)
    OR (VERIFY_ROLE_FOR_USER(SESSION_USER,'ADMIN') = 1)
    ENFORCED FOR ALL ACCESS ENABLE;

    ALTER TABLE SAMPLE.PATIENT ACTIVATE ROW ACCESS CONTROL;

  • Create a column mask on the SSN column and activate the column mask.
    CREATE MASK SSN_MASK ON SAMPLE.PATIENT FOR
    COLUMN SSN RETURN
    CASE WHEN
    VERIFY_ROLE_FOR_USER(SESSION_USER,'ADMIN') = 1
    THEN SSN
    ELSE CHAR('XXX-XX-' || SUBSTR(SSN,8,4)) END
    ENABLE;

    ALTER TABLE SAMPLE.PATIENT ACTIVATE COLUMN ACCESS CONTROL;

  • Log in to the dashDB console as user Jane and insert data into the SAMPLE.PATIENT table.
    INSERT INTO SAMPLE.PATIENT VALUES('BOB','LEE','123-45-6789','Hypertension');
    INSERT INTO SAMPLE.PATIENT VALUES('MAX','LEE','345-67-8910','Codeine');
    INSERT INTO SAMPLE.PATIENT VALUES('SAM','HO','678-90-1234','High Blood Pressure');
  • Run the following SELECT statement to confirm that 3 rows of data were inserted into the SAMPLE.PATIENT table:
    SELECT * FROM SAMPLE.PATIENT;

    PATIENT_NAME DOCTOR_NAME SSN PHARMACY
    ------------------ ------------------ ----------- --------------------
    BOB LEE 123-45-6789 Hypertension
    SAM HO 678-90-1234 High Blood Pressure
    MAX LEE 345-67-8910 Codeine

    3 record(s) selected.

Results of data access role configuration

With RCAC, people in different roles can have a different result set from the same SQL query. Bob, Lee, and Jane each connect to the database and try the following SQL query: SELECT * FROM SAMPLE.PATIENT;

Bob gets the following result set:

PATIENT_NAME       DOCTOR_NAME        SSN         PHARMACY
------------------ ------------------ ----------- ------------
BOB LEE XXX-XX-6789 Hypertension

1 record(s) selected.


Bob, being a patient, can see only his own data and only the last 4 digits of his SSN. Lee gets the following result set:
PATIENT_NAME       DOCTOR_NAME        SSN         PHARMACY
------------------ ------------------ ----------- -------------
BOB LEE XXX-XX-6789 Hypertension
MAX LEE XXX-XX-8910 Codeine

2 record(s) selected.


Lee, being a doctor, can see only his own patients’ data and only the last 4 digits of their SSNs. Jane gets the following result set:
PATIENT_NAME       DOCTOR_NAME        SSN         PHARMACY
------------------ ------------------ ----------- -------------------
BOB LEE 123-45-6789 Hypertension
SAM HO 678-90-1234 High Blood Pressure
MAX LEE 345-67-8910 Codeine

3 record(s) selected.


Jane, being an admin, can see all of the data in all of the columns.

Summary

You can see from this example that RCAC provides a powerful new functionality to secure sensitive data. Row permissions and column masks are based on SQL expressions that provide great flexibility. Row permissions and column masks are transparent to applications and provide data-centric security that is managed solely by database administrators. Row permissions and column masks can be applied to data in a dashDB data warehouse to meet the security and privacy requirements that arise from several areas of concern, which include regulatory compliance, multi-tenancy, database hosting, and data consolidation. RCAC is an important part of any database security strategy.

References

For more information about RCAC, see the IBM dashDB Knowledge Center.

More Community stories
May 7, 2019

We’ve Moved! The IBM Cloud Blog Has a New URL

In an effort better integrate the IBM Cloud Blog with the IBM Cloud web experience, we have migrated the blog to a new URL: www.ibm.com/cloud/blog.

Continue reading

April 19, 2019

Reach Out to the IBM Cloud Development Teams on Slack

Get the help you need fast—directly from the IBM Cloud Development Teams and other users on Slack.

Continue reading

April 11, 2019

Permanent Redirect to cloud.ibm.com from console.bluemix.net

Starting on April 27, 2019, we will be turning on permanent redirects from bluemix.net to cloud.ibm.com. All of the same functionality that existed on bluemix.net is still available in cloud.ibm.com.

Continue reading