Example of encrypting the employee sample database

Abstract: A simple isql script which shows how might one might go about encrypting the employee sample database included with InterBase

isql employee.gdb -user SYSDBA -pass masterkey

ALTER DATABASE ADD ADMIN OPTION;

 

CREATE USER SYSDSO SET PASSWORD 'sysdso';

CREATE USER P_Penny SET PASSWORD 'PennyPassword';

 

COMMIT;

EXIT;

 

isql employee.gdb -user SYSDSO -pass sysdso

ALTER DATABASE SET SYSTEM ENCRYPTION PASSWORD 'Secret Password';

 

CREATE ENCRYPTION Db_Level_Key FOR DES;

CREATE ENCRYPTION Col_Level_Key FOR DES;

CREATE ENCRYPTION Backup_Key FOR DES PASSWORD 'Backup Password';

 

GRANT ENCRYPT ON ENCRYPTION Db_Level_Key TO SYSDBA;

GRANT ENCRYPT ON ENCRYPTION Col_Level_Key TO SYSDBA;

GRANT ENCRYPT ON ENCRYPTION Backup_Key TO SYSDBA;

 

COMMIT;

EXIT;

 

isql employee.gdb -user SYSDBA -pass masterkey

ALTER DATABASE ENCRYPT WITH Db_Level_Key;

 

ALTER TABLE COUNTRY ALTER COLUMN COUNTRY ENCRYPT WITH Col_Level_Key;

ALTER TABLE COUNTRY ALTER COLUMN CURRENCY ENCRYPT WITH Col_Level_Key;

 

ALTER TABLE CUSTOMER ALTER COLUMN CUST_NO ENCRYPT WITH Col_Level_Key;

ALTER TABLE CUSTOMER ALTER COLUMN CUSTOMER ENCRYPT WITH Col_Level_Key;

ALTER TABLE CUSTOMER ALTER COLUMN CONTACT_FIRST ENCRYPT WITH Col_Level_Key;

ALTER TABLE CUSTOMER ALTER COLUMN CONTACT_LAST ENCRYPT WITH Col_Level_Key;

ALTER TABLE CUSTOMER ALTER COLUMN PHONE_NO ENCRYPT WITH Col_Level_Key;

ALTER TABLE CUSTOMER ALTER COLUMN ADDRESS_LINE1 ENCRYPT WITH Col_Level_Key;

ALTER TABLE CUSTOMER ALTER COLUMN ADDRESS_LINE2 ENCRYPT WITH Col_Level_Key;

TABLE CUSTOMER ALTER COLUMN CITYALTER ENCRYPT WITH Col_Level_Key;

ALTER TABLE CUSTOMER ALTER COLUMN STATE_PROVINCE ENCRYPT WITH Col_Level_Key;

ALTER TABLE CUSTOMER ALTER COLUMN COUNTRY ENCRYPT WITH Col_Level_Key;

ALTER TABLE CUSTOMER ALTER COLUMN POSTAL_CODE ENCRYPT WITH Col_Level_Key;

ALTER TABLE CUSTOMER ALTER COLUMN ON_HOLD ENCRYPT WITH Col_Level_Key;

 

ALTER TABLE DEPARTMENT ALTER COLUMN DEPT_NO ENCRYPT WITH Col_Level_Key;

ALTER TABLE DEPARTMENT ALTER COLUMN DEPARTMENT ENCRYPT WITH Col_Level_Key;

ALTER TABLE DEPARTMENT ALTER COLUMN HEAD_DEPT ENCRYPT WITH Col_Level_Key;

ALTER TABLE DEPARTMENT ALTER COLUMN MNGR_NO ENCRYPT WITH Col_Level_Key;

ALTER TABLE DEPARTMENT ALTER COLUMN BUDGET ENCRYPT WITH Col_Level_Key;

ALTER TABLE DEPARTMENT ALTER COLUMN LOCATION ENCRYPT WITH Col_Level_Key;

ALTER TABLE DEPARTMENT ALTER COLUMN PHONE_NO ENCRYPT WITH Col_Level_Key;

 

ALTER TABLE EMPLOYEE ALTER COLUMN EMP_NO ENCRYPT WITH Col_Level_Key;

ALTER TABLE EMPLOYEE ALTER COLUMN FIRST_NAME ENCRYPT WITH Col_Level_Key;

ALTER TABLE EMPLOYEE ALTER COLUMN LAST_NAME ENCRYPT WITH Col_Level_Key;

ALTER TABLE EMPLOYEE ALTER COLUMN PHONE_EXT ENCRYPT WITH Col_Level_Key;

ALTER TABLE EMPLOYEE ALTER COLUMN HIRE_DATE ENCRYPT WITH Col_Level_Key;

ALTER TABLE EMPLOYEE ALTER COLUMN DEPT_NO ENCRYPT WITH Col_Level_Key;

ALTER TABLE EMPLOYEE ALTER COLUMN JOB_CODE ENCRYPT WITH Col_Level_Key;

ALTER TABLE EMPLOYEE ALTER COLUMN JOB_GRADE ENCRYPT WITH Col_Level_Key;

ALTER TABLE EMPLOYEE ALTER COLUMN JOB_COUNTRY ENCRYPT WITH Col_Level_Key;

ALTER TABLE EMPLOYEE ALTER COLUMN SALARY ENCRYPT WITH Col_Level_Key;

ALTER TABLE EMPLOYEE ALTER COLUMN FULL_NAME ENCRYPT WITH Col_Level_Key;

 

ALTER TABLE JOB ALTER COLUMN JOB_CODE ENCRYPT WITH Col_Level_Key;

ALTER TABLE JOB ALTER COLUMN JOB_GRADE ENCRYPT WITH Col_Level_Key;

ALTER TABLE JOB ALTER COLUMN JOB_COUNTRY ENCRYPT WITH Col_Level_Key;

ALTER TABLE JOB ALTER COLUMN JOB_TITLE ENCRYPT WITH Col_Level_Key;

ALTER TABLE JOB ALTER COLUMN MIN_SALARY ENCRYPT WITH Col_Level_Key;

ALTER TABLE JOB ALTER COLUMN MAX_SALARY ENCRYPT WITH Col_Level_Key;

ALTER TABLE JOB ALTER COLUMN JOB_REQUIREMENT ENCRYPT WITH Col_Level_Key;

ALTER TABLE JOB ALTER COLUMN LANGUAGE_REQ ENCRYPT WITH Col_Level_Key;

 

ALTER TABLE PROJECT ALTER COLUMN PROJ_ID ENCRYPT WITH Col_Level_Key;

ALTER TABLE PROJECT ALTER COLUMN PROJ_NAME ENCRYPT WITH Col_Level_Key;

ALTER TABLE PROJECT ALTER COLUMN PROJ_DESC ENCRYPT WITH Col_Level_Key;

ALTER TABLE PROJECT ALTER COLUMN TEAM_LEADER ENCRYPT WITH Col_Level_Key;

ALTER TABLE PROJECT ALTER COLUMN PRODUCT ENCRYPT WITH Col_Level_Key;

 

ALTER TABLE PROJ_DEPT_BUDGET ALTER COLUMN FISCAL_YEAR ENCRYPT WITH Col_Level_Key;

ALTER TABLE PROJ_DEPT_BUDGET ALTER COLUMN PROJ_ID ENCRYPT WITH Col_Level_Key;

ALTER TABLE PROJ_DEPT_BUDGET ALTER COLUMN DEPT_NO ENCRYPT WITH Col_Level_Key;

ALTER TABLE PROJ_DEPT_BUDGET ALTER COLUMN QUART_HEAD_CNT ENCRYPT WITH Col_Level_Key;

ALTER TABLE PROJ_DEPT_BUDGET ALTER COLUMN PROJECTED_BUDGET ENCRYPT WITH Col_Level_Key;

 

ALTER TABLE SALARY_HISTORY ALTER COLUMN EMP_NO ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALARY_HISTORY ALTER COLUMN CHANGE_DATE ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALARY_HISTORY ALTER COLUMN UPDATER_ID ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALARY_HISTORY ALTER COLUMN OLD_SALARY ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALARY_HISTORY ALTER COLUMN PERCENT_CHANGE ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALARY_HISTORY ALTER COLUMN NEW_SALARY ENCRYPT WITH Col_Level_Key;

 

ALTER TABLE SALES ALTER COLUMN PO_NUMBER ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALES ALTER COLUMN CUST_NO ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALES ALTER COLUMN SALES_REP ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALES ALTER COLUMN ORDER_STATUS ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALES ALTER COLUMN ORDER_DATE ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALES ALTER COLUMN SHIP_DATE ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALES ALTER COLUMN DATE_NEEDED ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALES ALTER COLUMN PAID ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALES ALTER COLUMN QTY_ORDERED ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALES ALTER COLUMN TOTAL_VALUE ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALES ALTER COLUMN DISCOUNT ENCRYPT WITH Col_Level_Key;

ALTER TABLE SALES ALTER COLUMN ITEM_TYPE ENCRYPT WITH Col_Level_Key;

 

COMMIT;

EXIT;