Scenario: ExampleBANK using row and column access control - Database tables

This scenario focuses on two tables in the ExampleBANK database: the CUSTOMER table and the INTERNAL_INFO table.

The INTERNAL_INFO table stores information about employees who work for ExampleBANK. This scenario considers the following columns within the INTERNAL_INFO table:

HOME_BRANCH: The employee home branch ID.
EMP_ID: The employee ID.

The CUSTOMER table stores individual client information:

ACCOUNT: The client account number.
NAME: The client name.
INCOME: The client income.
BRANCH: The client branch ID.

The following example SQL statements create the customer, and INTERNAL_INFO tables. Authority is granted on the tables and data is inserted:

--Client table storing information regarding client information 
CREATE TABLE RCACTSPM.CUSTOMER (
   ACCOUNT VARCHAR(19), 
   NAME VARCHAR(20), 
   INCOME INTEGER, 
   BRANCH CHAR(1)
);

--Internal_info table which stores employee information

CREATE TABLE RCACTSPM.INTERNAL_INFO (
   HOME_BRANCH CHAR(1), 
   EMP_ID VARCHAR(10));

--Grant authority

GRANT SELECT ON RCACTSPM.CUSTOMER TO USER NEWTON, USER ZURBIE, USER PLATO;

--Insert data

INSERT INTO RCACTSPM.CUSTOMER VALUES ('1111-2222-3333-4444', 'Alice', 22000, 'A');
INSERT INTO RCACTSPM.CUSTOMER VALUES ('2222-3333-4444-5555', 'Bob', 71000, 'A');
INSERT INTO RCACTSPM.CUSTOMER VALUES ('3333-4444-5555-6666', 'Carl', 123000, 'B');
INSERT INTO RCACTSPM.CUSTOMER VALUES ('4444-5555-6666-7777', 'David', 172000, 'C');

INSERT INTO RCACTSPM.INTERNAL_INFO VALUES ('A', 'NEWTON');
INSERT INTO RCACTSPM.INTERNAL_INFO VALUES ('B', 'ZURBIE');
INSERT INTO RCACTSPM.INTERNAL_INFO VALUES ('C', 'PLATO');