Using INSERT on tables with row access control

You can use the INSERT statement on tables that are activated for row access control.

About this task

Suppose that you are responsible for managing patient memberships and you are associated with role MEMBERSHIP that is already created for table HOSPITAL.PATIENT. Table HOSPITAL.PATIENT is also activated for row access control, and Role MEMBERSHIP is allowed to access, create, and retrieve rows in the table.

Suppose that table HOSPITAL.PATIENT contains columns to record a patient's social security number (SSN), account authorization ID (USERID), name (NAME), address (ADDRESS), pharmacy (PHARMCY), account balance (ACCT_BALANCE), and doctor (PCD_ID). You want to add a new row for a new patient Bob.

Procedure

Begin general-use programming interface information.To add a row to a table that is enforced with row access control:

  1. Ensure that role MEMBERSHIP is allowed to access, insert, and update rows when row permission rules are set for table HOSPITAL.PATIENT by the SECADM authority. 
    CREATE PERMISSION NETHMO.ROW_ACCESS ON HOSPITAL.PATIENT
   FOR ROWS WHERE (VERIFY_TRUSTED_CONTEXT_ROLE_FOR_USER(SESSION_USER,
                      'MEMBERSHIP') = 1 )
      ENFORCED FOR ALL ACCESS
  	 ENABLE;

COMMIT;
  2. Issue the INSERT statement to insert a new row for patient Bob: 
    INSERT INTO HOSPITAL.PATIENT(SSN, USERID, NAME, ADDRESS)
     VALUES('123-45-6789','BobXYZ100',‘Bob',‘123 Some St.');

COMMIT;
  3. Verify that Bob was successfully added by issuing the following SELECT statement: 
    SELECT * FROM HOSPITAL.PATIENT WHERE SSN = ‘123-45-6789';

COMMIT;

    The following result is returned and shows that a new row for Bob was successfully added to the table.

    SSN          USERID     NAME  ADDRESS       PHARMACY  ACCT_BALANCE  PCP_ID
-----------  ---------  ----  ------------  --------  ------------  ------
123-45-6789  BobXYZ100  Bob   123 Some St.  ?         0.00          ?

DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
         SUCCESSFUL RETRIEVAL OF 1 ROW(S)
    End general-use programming interface information.