<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Db2 Row and Column Access Control
Row permissions and column masks are two new database concepts introduced in DB2 10 that address the shortcomings of traditional security methods. These new features act as a second layer of security that complements the table privileges security model available in prior releases of DB2.
One advantage of row permissions and column masks is that no database user is automatically exempted from them — not even users with the DATAACCESS authority. The ability to manage row permissions and column masks within a database is vested solely in a user with SECADM, thereby ensuring that users with DATAACCESS authority can no longer freely access all data in the database.
Another key advantage is that row permissions and column masks ensure that table data is protected regardless of how the SQL accesses the table, be it through an application, through ad hoc query tools, or through report generation tools.
Finally, and perhaps most significantly, row permissions and column masks require no application changes; RCAC is transparent to existing applications.
Step1:  We populate the database with the EMPLOYEE and DEPARTMENT tables so that we can run the various examples.
Step2:  Health Care Scenario
We will use a sample scenario based on the Health-care industry.
Data in a hospital needs to be provided to a number of individuals,including other organizations like insurance companies, clearing house etc. Each of these companies have their own employees, and there are government regulations in place dictating who can see what data or parts of patient data.
As a result security policies must be in place to ensure the rules are being met.
Across the scenario there will be different groups of individuals that access the database and there must be security in place to ensure each group or individual only sees the data they are allowed to see.
This SQL will create the base PATIENT table and populate it with information.
Step3:  Setting Permissions for Access
The following permissions need to be defined for accessing the PATIENT data
Step4:  We also need to create a SESSION_USER global variable that can be used to identify the "current" user. Normally you would just use the SESSION_USER variable in the rules, but since we don't want to use SECADM we need to fake the userid.
Step5:  The HC.VERIFY_ROLE_FOR_USER function will mimic what the VERIFY_ROLE_FOR_USER
function does by returning a value of 1 if the user has that particular ROLE.
Step6:  Create some ROLES for people in the Healthcare scenario.
Step7:  Now we can test to see if a user has a particular role. This first test checks to see the Dr. Lee is a PCP (Primary Care Provider).
Step8:  At this point in time we can set up some rules on what the various groups can see.
Step9:  The rules now need to be activated in order for them to be enforced.
Step10:  Updating a Patient Record
Dr. Lee needs to update the medication that his patient Sam is taking.
First we examine the original record in the table.
Step11:  Dr. Lee decides to give Sam some codeine for his pain. The update is successful and we can see the results.
Step12:  Update Failure
If a doctor is not assigned to a patient, they will not be able to update their
record. The rule is that if you can't read (select) the record then you can't
update it.
Step13:  The UPDATE completes, but no records are modified. To see all of the records, we need to change our userid to someone who can see all records (John in accounting). Note there is no way around this restriction - you must have the proper clearance to see the records.
Step14:  Selecting Rows from a Table
The rule with Row and Column access control is that if you can read (select) the data then you can potentially update it. Here is a select statement issued by Dr. Lee and you can see that only patients assigned to him are displayed.
Step15:  Changing the current user to Dr. James will change the results that are displayed.
Step16:  Changing the current user to one of the accounting, research, or fund raising users will result in all records being displayed.
Step17:  Patients are able to see only their row.
Step18:  A DBA (Fred) who is not part of any of these groups will not be able to see any of the records, even though they may performance maintenance on the table itself.
Step19:  Column Masks
Column access control is implemented in the form of a mask, or lack thereof, on the data.
Using our Health-care scenario as the base, we will implement column access control/rules in two forms
Step20:  The second mask will return the entire SIN number for the PATIENT, but only the last three digits of the SIN for all others.
Step21:  In order for the MASKS to be effective, they need to be enabled for the table.
Step22:  When someone from accounting now views the records, they will only see the last three digits of the SIN field but they will see all of the accounting data.
Step23:  When a researcher looks at the data, they will also only see the last three digits of the SIN field, but they will get a zero balance in the accounting field.
Step24:  Dr. Lee will only see his patients (ROW CONTROL) and will see the last three digits of the SIN field and zero for the account balance (COLUMN MASK).
Step25:  Finally, the patients will be able to see their own SIN field, but the account balance will show as zero (presumably so they don't get sick over the amount!).
Step26:  Catalog Views
The information about the ROW and COLUMN permissions can be found in the SYSCAT.CONTROLS table. One line is found in this table for each column permission and one for the row permission on a table.
<ASSISTANT_TASK:>
Python Code:
%run db2.ipynb
Explanation: Db2 Row and Column Access Control
Row permissions and column masks are two new database concepts introduced in DB2 10 that address the shortcomings of traditional security methods. These new features act as a second layer of security that complements the table privileges security model available in prior releases of DB2.
One advantage of row permissions and column masks is that no database user is automatically exempted from them — not even users with the DATAACCESS authority. The ability to manage row permissions and column masks within a database is vested solely in a user with SECADM, thereby ensuring that users with DATAACCESS authority can no longer freely access all data in the database.
Another key advantage is that row permissions and column masks ensure that table data is protected regardless of how the SQL accesses the table, be it through an application, through ad hoc query tools, or through report generation tools.
Finally, and perhaps most significantly, row permissions and column masks require no application changes; RCAC is transparent to existing applications.
End of explanation
%sql -sampledata
Explanation: We populate the database with the EMPLOYEE and DEPARTMENT tables so that we can run the various examples.
End of explanation
%%sql -q
DROP TABLE HC.PATIENTS;
CREATE TABLE HC.PATIENTS
  (
  SIN      VARCHAR(11),
  USERID   VARCHAR(8),
  NAME     VARCHAR(8),
  ADDRESS  VARCHAR(12),
  PHARMACY VARCHAR(12),
  ACCT_BALANCE DEC(9,2),
  PCP_ID   VARCHAR(8)
  );
INSERT INTO HC.PATIENTS VALUES
  ('123 551 234','MAX','Max','First St.','hypertension',89.7,'LEE'),
  ('123 589 812','MIKE','Mike','Long St.','diabetics',8.3,'JAMES'),
  ('123 119 856','SAM','Sam','Big St.','aspirin',12.5,'LEE'),
  ('123 191 454','DOUG','Doug','Good St.','influenza',7.68,'JAMES'),
  ('123 456 789','BOB','Bob','123 Some St.','hypertension',9,'LEE');
SELECT * FROM HC.PATIENTS;
Explanation: Health Care Scenario
We will use a sample scenario based on the Health-care industry.
Data in a hospital needs to be provided to a number of individuals,including other organizations like insurance companies, clearing house etc. Each of these companies have their own employees, and there are government regulations in place dictating who can see what data or parts of patient data.
As a result security policies must be in place to ensure the rules are being met.
Across the scenario there will be different groups of individuals that access the database and there must be security in place to ensure each group or individual only sees the data they are allowed to see.
This SQL will create the base PATIENT table and populate it with information.
End of explanation
%%sql
DROP TABLE HC.ROLES;
CREATE TABLE HC.ROLES
  (
  USERID VARCHAR(8),
  ROLE   VARCHAR(10)
  );
Explanation: Setting Permissions for Access
The following permissions need to be defined for accessing the PATIENT data:
Patients can only access their own data
Physicians Can only access their own patients' data
Membership officers, Accounting, and Drug researchers can access all data
All other access is denied
Normally SQL similar to the following is used to define who can access the data.
verify_role_for_user(SESSION_USER,'PATIENT') = 1
   AND patient.userid = SESSION_USER
Changing from one user to another requires a number of SECADM features and separate connections, so in order to simplify the demo we will use a ROLE table and a UDF to simulate this capability.
Create ROLE table
The ROLE table will simulate what ROLES does natively in DB2. The table contains two 
columns:
USERID - the name or ID of the user
ROLE   - what their ROLE is 
Users can have more than one ROLE in this table.
End of explanation
%sql CREATE OR REPLACE VARIABLE HC.SESSION_USER VARCHAR(8);
Explanation: We also need to create a SESSION_USER global variable that can be used to identify the "current" user. Normally you would just use the SESSION_USER variable in the rules, but since we don't want to use SECADM we need to fake the userid.
End of explanation
%%sql -d
CREATE OR REPLACE FUNCTION 
  HC.VERIFY_ROLE_FOR_USER(UID VARCHAR(8), IN_ROLE VARCHAR(10))
    SECURED NO EXTERNAL ACTION DETERMINISTIC
    RETURNS INT
    BEGIN ATOMIC
      RETURN
       SELECT COUNT(*) FROM HC.ROLES H
         WHERE H.USERID = UID AND H.ROLE = IN_ROLE;
    END@
Explanation: The HC.VERIFY_ROLE_FOR_USER function will mimic what the VERIFY_ROLE_FOR_USER
function does by returning a value of 1 if the user has that particular ROLE.
End of explanation
%%sql
INSERT INTO HC.ROLES
  VALUES
    ('LEE','PCP'),('JAMES','PCP'),
    ('MAX','PATIENT'),('MIKE','PATIENT'),('SAM','PATIENT'),
    ('DOUG','PATIENT'),('BOB','PATIENT'),
    ('JOHN','ACCOUNTING'),
    ('TOM','MEMBERSHIP'),
    ('JANE','RESEARCH'),
    ('FRED','DBA');
Explanation: Create some ROLES for people in the Healthcare scenario.
End of explanation
%%sql
SET HC.SESSION_USER = 'LEE';
VALUES
   HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'PCP');
Explanation: Now we can test to see if a user has a particular role. This first test checks to see the Dr. Lee is a PCP (Primary Care Provider).
End of explanation
%%sql
CREATE OR REPLACE PERMISSION HC.ROW_ACCESS ON HC.PATIENTS
  FOR ROWS WHERE
  (
     HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'PATIENT') = 1 AND
     HC.SESSION_USER = USERID
  )
  OR
  (   
     HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'PCP') = 1 AND
     HC.SESSION_USER = PCP_ID
  )
  OR
  (
     HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'MEMBERSHIP') = 1 OR
     HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'ACCOUNTING') = 1 OR
     HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'RESEARCH') = 1
  )
  ENFORCED FOR ALL ACCESS
  ENABLE;
Explanation: At this point in time we can set up some rules on what the various groups can see.
End of explanation
%sql ALTER TABLE HC.PATIENTS ACTIVATE ROW ACCESS CONTROL;
Explanation: The rules now need to be activated in order for them to be enforced.
End of explanation
%%sql 
SET HC.SESSION_USER = 'LEE';
SELECT * FROM HC.PATIENTS WHERE NAME = 'Sam';
Explanation: Updating a Patient Record
Dr. Lee needs to update the medication that his patient Sam is taking.
First we examine the original record in the table.
End of explanation
%%sql
UPDATE HC.PATIENTS SET PHARMACY = 'Codeine' WHERE NAME = 'Sam';
SELECT * FROM HC.PATIENTS WHERE NAME = 'Sam'
Explanation: Dr. Lee decides to give Sam some codeine for his pain. The update is successful and we can see the results.
End of explanation
%%sql
SET HC.SESSION_USER = 'LEE';
UPDATE HC.PATIENTS SET PHARMACY = 'Codeine' WHERE NAME = 'Doug';
Explanation: Update Failure
If a doctor is not assigned to a patient, they will not be able to update their
record. The rule is that if you can't read (select) the record then you can't
update it.
End of explanation
%%sql
SET HC.SESSION_USER = 'JOHN';
SELECT * FROM HC.PATIENTS WHERE NAME = 'Doug'
Explanation: The UPDATE completes, but no records are modified. To see all of the records, we need to change our userid to someone who can see all records (John in accounting). Note there is no way around this restriction - you must have the proper clearance to see the records.
End of explanation
%%sql
SET HC.SESSION_USER = 'LEE';
SELECT * FROM HC.PATIENTS;
Explanation: Selecting Rows from a Table
The rule with Row and Column access control is that if you can read (select) the data then you can potentially update it. Here is a select statement issued by Dr. Lee and you can see that only patients assigned to him are displayed.
End of explanation
%%sql
SET HC.SESSION_USER = 'JAMES';
SELECT * FROM HC.PATIENTS;
Explanation: Changing the current user to Dr. James will change the results that are displayed.
End of explanation
%%sql
SET HC.SESSION_USER = 'JOHN';
SELECT * FROM HC.PATIENTS;
Explanation: Changing the current user to one of the accounting, research, or fund raising users will result in all records being displayed.
End of explanation
%%sql
SET HC.SESSION_USER = 'BOB';
SELECT * FROM HC.PATIENTS;
Explanation: Patients are able to see only their row.
End of explanation
%%sql
SET HC.SESSION_USER = 'FRED';
SELECT * FROM HC.PATIENTS;
Explanation: A DBA (Fred) who is not part of any of these groups will not be able to see any of the records, even though they may performance maintenance on the table itself.
End of explanation
%%sql
CREATE OR REPLACE MASK HC.ACCT_BALANCE_MASK ON HC.PATIENTS FOR
  COLUMN ACCT_BALANCE RETURN
     CASE
        WHEN HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'ACCOUNTING') = 1
           THEN ACCT_BALANCE
        ELSE 0.00
     END
  ENABLE;
Explanation: Column Masks
Column access control is implemented in the form of a mask, or lack thereof, on the data.
Using our Health-care scenario as the base, we will implement column access control/rules in two forms:
We MASK the account balance column. Only the ACCOUNTING team can see the account balance in the table and all others see a balance of zero.
We MASK the SIN column (Social Insurance Number column). Only the PATIENT themselves can see the full Social Insurance number and all others see only the last three digits of the number.
This first MASK is on account balance and sets it to zero for everyone except for those people in accounting.
End of explanation
%%sql
CREATE OR REPLACE MASK HC.SIN_MASK ON HC.PATIENTS FOR
  COLUMN SIN RETURN
     CASE
        WHEN HC.VERIFY_ROLE_FOR_USER(HC.SESSION_USER,'PATIENT') = 1
          THEN SIN
        ELSE
         'XXX XXX ' || SUBSTR(SIN,9,3)
     END
  ENABLE;
Explanation: The second mask will return the entire SIN number for the PATIENT, but only the last three digits of the SIN for all others.
End of explanation
%sql ALTER TABLE HC.PATIENTS ACTIVATE COLUMN ACCESS CONTROL;
Explanation: In order for the MASKS to be effective, they need to be enabled for the table.
End of explanation
%%sql
SET HC.SESSION_USER = 'JOHN';
SELECT * FROM HC.PATIENTS;
Explanation: When someone from accounting now views the records, they will only see the last three digits of the SIN field but they will see all of the accounting data.
End of explanation
%%sql
SET HC.SESSION_USER = 'JANE';
SELECT * FROM HC.PATIENTS;
Explanation: When a researcher looks at the data, they will also only see the last three digits of the SIN field, but they will get a zero balance in the accounting field.
End of explanation
%%sql
SET HC.SESSION_USER = 'LEE';
SELECT * FROM HC.PATIENTS;
Explanation: Dr. Lee will only see his patients (ROW CONTROL) and will see the last three digits of the SIN field and zero for the account balance (COLUMN MASK).
End of explanation
%%sql
SET HC.SESSION_USER = 'BOB';
SELECT * FROM HC.PATIENTS;
Explanation: Finally, the patients will be able to see their own SIN field, but the account balance will show as zero (presumably so they don't get sick over the amount!).
End of explanation
%sql -a SELECT * FROM SYSCAT.CONTROLS;
Explanation: Catalog Views
The information about the ROW and COLUMN permissions can be found in the SYSCAT.CONTROLS table. One line is found in this table for each column permission and one for the row permission on a table.
End of explanation
<END_TASK>