​ ​Project​ ​3-A simple role-based access control scheme

​ ​Project​ ​3
 Project​ ​Description
In this project, you will implement a simple role-based access control scheme for a slightly modified version of the database you used in Project 1 and Project 2. The project has three goals: ● Using​ ​JDBC​ ​to​ ​query/update​ ​your​ ​database​ ​from​ ​within​ ​a​ ​Java​ ​program ● Building​ ​a​ ​simple​ ​role-based​ ​access​ ​control​ ​scheme ● Building a simple data encryption mechanism to hide data from database users with insufficient​ ​access​ ​privileges
Preliminaries
Role Based ​ ​ Access ​ ​ Control ​ ​ (RBAC) Role-based access control (RBAC) is a popular data protection mechanism, where the operations a user can perform on specific data are determined by the user’s data access roles. In this scheme, data access rights are granted to groups having specific roles instead of individual users. Users can have multiple roles and are entitled to all privileges of their assigned roles. In this project, we use a simple RBAC mechanism, where regular (non-admin) users of the database are assigned roles by the administrator, who also assigns specific permissions regarding the use of the database tables to each role. For simplicity, the only operations regular users of the database can perform on the data are INSERT and SELECT. Owner of a data item can restrict access to the item by making it available in plaintext only to users with a specific role, and hiding some fields of the data from other users using encryption. A user can see all fields of a table if she has the privilege for the SELECT operation on that table, but may see encrypted​ ​versions​ ​of​ ​some​ ​of​ ​the​ ​attribute​ ​values​ ​based​ ​on​ ​her​ ​role​ ​permissions​ ​for​ ​that​ ​table. Data ​ ​ Encryption ​ ​ Using ​ ​ Autokey ​ ​ Cipher Autokey Cipher ​encryption algorithm accepts a plaintext and a key to generate a ciphertext. Using the ciphertext and the key, the Autokey Cipher ​decryption algorithm can recover the original plaintext. To summarize, Autokey Cipher will have the following methods to implement its​ ​encryption​ ​and​ ​decryption​ ​algorithms: ● AKEncrypt(String​ ​plainText,​ ​String​ ​key)​ ​→​ ​String​ ​cipherText ● AKDecrypt(String​ ​cipherText,​ ​String​ ​key)​ ​→​ ​String​ ​plainText

To illustrate how Autokey Cipher works, we can view the alphabet algebraically. The letters A-Z are viewed as numbers from 0-25, and additions are performed modulo 26. For example, let the plaintext be "DATABASES" and the key be "KEY", then the encryption algorithm will work as following: ● AKEncrypt(“DATABASES”,​ ​“KEY”) ○ Generate string k` by prepending the key to plainText, limited by the length of plainText. For example, "DATABASES" has length of 9, so k` would be "KEYDATABA",​ ​i.e.​ ​the​ ​plaintext​ ​is​ ​appended​ ​to​ ​"KEY"​ ​till​ ​we​ ​reach​ ​length​ ​9 ○ Transform​ ​the​ ​plainText​ ​and​ ​k`​ ​to​ ​numbers,​ ​where​ ​A​ ​=​ ​0,​ ​B​ ​=​ ​1​ ​...​ ​Z​ ​=​ ​25 ○ Add each corresponding two numbers in plaintext and k` modulo 26 to get cipherText​ ​code,​ ​and​ ​then​ ​translate​ ​back​ ​to​ ​alphabet​ ​to​ ​get​ ​cipherText. The​ ​following​ ​table​ ​shows​ ​the​ ​whole​ ​process:
pt
D A T A B A S E S
3 0 19 0 1 0 18 4 18
k` K E Y D A T A B A
10 4 24 3 0 19 0 1 0
ct
13 4 17 3 1 19 18 5 18
N E R D B T S F S
The decryption algorithm works similarly, except that it uses subtraction modulo 26 to reveal the plaintext, and you would figure out k` step by step while deciphering, as you only know a prefix​ ​from​ ​it,​ ​which​ ​is​ ​the​ ​key. For​ ​this​ ​project,​ ​you​ ​can​ ​assume: ● Non-alphabetic characters in plainText would be copied to cipherText as is, including numbers,​ ​special​ ​characters​ ​and​ ​white​ ​spaces ○ “DATA​ ​@​ ​BASES​ ​1”​ ​→​ ​“NERD​ ​@​ ​BTSFS​ ​1”​ ​with​ ​the​ ​key​ ​“KEY’ ● cipherText​ ​would​ ​be​ ​case-sensitive​ ​following​ ​the​ ​case​ ​of​ ​plainText ○ ​ ​“DataBases”​ ​→​ ​“NerdBtsfs”​ ​with​ ​the​ ​key​ ​“KEY” ● Keys​ ​contain​ ​only​ ​alphabetic​ ​characters​ ​from​ ​A​ ​to​ ​Z,​ ​and​ ​are​ ​case-insensitive ○ “KEY”,​ ​“key”​ ​and​ ​“Key”​ ​all​ ​function​ ​similarly For​ ​more​ ​details,​ ​see​ ​​https://en.wikipedia.org/wiki/Autokey_cipher

Connecting ​ ​ to ​ ​ your ​ ​ Oracle ​ ​ database For this project, you will use your Oracle account to host the database of the application. Your program should connect to this database using a database connection library (JDBC). To get started using JDBC, you can go through the tutorial at http://www.tutorialspoint.com/jdbc/jdbc-quick-guide.htm​. This tutorial should provide the sufficient​ ​knowledge​ ​about​ ​JDBC​ ​for​ ​this​ ​project’s​ ​purposes.
Note that when registering the database driver in your code, you should pass the argument “oracle.jdbc.OracleDriver” to the Class.forName() method, as you will be connecting to an Oracle database. Also, you will need to include the JDBC driver ojdbc8.jar (provided to you) in your​ ​classpath​ ​at​ ​runtime​ ​to​ ​be​ ​able​ ​to​ ​use​ ​the​ ​database​ ​connectivity​ ​function.
You should use the URL “jdbc:oracle:thin:@claros.cs.purdue.edu:1524:strep” with the getConnection method of the DriverManager class when connecting to the database and include your Oracle account username (without @csora) and password as the username and password​ ​parameter​ ​values. Database​ ​Schema The database in this project consists of the tables listed below. The scripts to create/drop the database​ ​are​ ​already​ ​provided​ ​to​ ​you. Admin ​ ​ tables These tables are for administrative tasks only and are not accessible by regular users: Users, Roles,​ ​UsersRoles,​ ​Privileges,​ ​and​ ​RolesPrivileges. User ​ ​ tables There are four tables accessible by regular users: Companies, Schools, Students, and Internships. The users will need to have the appropriate permissions to insert data into or view data from these tables. Note that each of these tables has the fields EncryptedColumn and OwnerRole, the functions of which will be explained below. These two columns should not be output​ ​when​ ​a​ ​SELECT​ ​command​ ​is​ ​issued​ ​by​ ​any​ ​user. Command​ ​Set Your program should accept input from a text file (with each command terminated with a newline character), process the input and produce as output the responses in another text file. Ultimately,​ ​your​ ​submission​ ​should​ ​be​ ​executed​ ​as​ ​follows: java​ ​-cp​ ​.:ojdbc8.jar​ ​Project3​ ​input.txt​ ​output.txt

Sample input and output files are also provided for you. input.txt is the file containing the commands and output.txt is a file you are going to create. output.txt should include the output for each command separated by newlines, and ends with a newline after the “QUIT” command. For example, if we have three commands in input.txt before the "QUIT" command, output.txt should​ ​look​ ​like:
Notice that "Command 1 text" is the actual first command text you read from input.txt. Following​ ​any​ ​other​ ​format​ ​will​ ​incur​ ​high​ ​penalty​ ​in​ ​grading.
The program should implement the command set listed below. Notice that the reserved words are in boldface and case-sensitive. There will be a single space between each word and no spaces between values in an attribute value list (i.e. no spaces before and after parentheses or commas). Each attribute value will be put between single quotes. Note that the commands will always​ ​be​ ​input​ ​in​ ​the​ ​correct​ ​format,​ ​i.e.​ ​you​ ​do​ ​not​ ​need​ ​to​ ​check​ ​for​ ​syntax​ ​errors. ● LOGIN​​ ​username​ ​password Upon issuance of this command, you should check that the provided username and password pair matches the one in the Users table in the database. If not, you should print the error message “​Invalid login​”. If a match is found, you should update the current user to the one specified by username and print “​Login successful​”. Please follow the exact output statements; e.g. "Invalid login" with the same casing, as this will be counted in grading. Before you run your program for the first time, you should add a record to your Users table for the admin user (UserId=1, Username=’admin’, Password=’pass’). You should also put a record for the ADMIN role in the Roles table (RoleId=1, RoleName='ADMIN', EncryptionKey='AK'). You should have a record in your

UsersRoles table, for which the UserId is 1 and the RoleId is 1 (i.e., user admin has role ADMIN). A script with this data initialization is also provided with this handout. When checking whether the current user is the admin, you can either check that the username is admin or that the user has the ADMIN role in the UsersRoles table (either approach will work here). For simplicity, we will only have one admin user in this project. Note that if the login command is issued before a user quits the program, you should just switch the current user to the user specified in the login command. You can assume that if the login is unsuccessful, the user will keep trying to login until it succeeds. The very first​ ​command​ ​issued​ ​to​ ​your​ ​program​ ​will​ ​always​ ​be​ ​the​ ​login​ ​command. ● CREATE​ ​ROLE​ ​​roleName​ ​​encryptionKey If the current user is the admin when this command is issued, you should insert a new record in the Roles table with the values (roleName, encryptionKey) and print “​Role created successfully​” (you should generate the role Id dynamically). You do not need to check if this role already exists in the database. If the current user is not the admin, then print​ ​the​ ​error​ ​message​ ​“​Authorization​ ​failure​”. ● CREATE​ ​USER​​ ​username​ ​password If the current user is the admin when this command is issued, you should insert a new record in the Users table with the values (username, password) and print “​User created successfully​” (you should generate the user id dynamically). You do not need to check if this user already exists in the database. If the current user is not the admin, then print the​ ​error​ ​message​ ​“​Authorization​ ​failure​”. ● GRANT​ ​ROLE​​ ​username​ ​roleName If the current user is the admin when this command is issued, you should insert a new record in the UsersRoles table with ids corresponding to the parameters (username, roleName) and print “​Role assigned successfully​”. You do not need to check if this user exists in the Users table, roleName exists in the Roles table or this pair already exists in the UsersRoles table. If the current user is not the admin, print the error message "​Authorization​ ​failure​". ● GRANT​ ​PRIVILEGE​​ ​privName​ ​​TO​​ ​roleName​ ​​ON​​ ​tableName You should populate the Privileges table with two rows (already included in the initialization​ ​script): ○ Insert​ ​Privilege:​ ​Id​ ​=​ ​1,​ ​Name​ ​=​ ​INSERT ○ Select​ ​Privilege:​ ​Id​ ​=​ ​2,​ ​Name​ ​=​ ​SELECT
If the current user is the admin when this command is issued, you should insert a new record in the RolesPrivileges table with values corresponding to the parameters

(roleName, tableName, privName) and print "​Privilege granted successfully​". You do not need to check whether this privilege already exists in the database. If the current user​ ​is​ ​not​ ​the​ ​admin,​ ​print​ ​the​ ​error​ ​message​ ​“​Authorization​ ​failure​”. ● REVOKE​ ​PRIVILEGE​​ ​privName​ ​​FROM​​ ​roleName​ ​​ON​​ ​tableName If the current user is the admin when this command is issued, you should delete the record in the RolesPrivileges table with values corresponding to the parameters (roleName, tableName, privName) and print "​Privilege revoked successfully​". You do not need to check whether this privilege already exists in the database. If the current user​ ​is​ ​not​ ​the​ ​admin,​ ​print​ ​the​ ​error​ ​message​ ​“​Authorization​ ​failure​”. ● INSERT​ ​INTO​​ ​tableName​ ​​VALUES​(valueList) ENCRYPT​​ ​columnNo​ ​ownerRole Upon issuance of this command, you should first check if any of the roles of the current user has the “INSERT” privilege on the table tableName. If not, you should print the error message “​Authorization failure​”. Otherwise, you should insert the values in valueList (which is a list of comma-separated strings enclosed in quotes) into the table tableName. While inserting the tuple, you should set the OwnerRole attribute value to the id corresponding to ownerRole and set the value of the EncryptedColumn attribute to columnNo. You do not need to check whether this record already exists in the database. Before insertion, you should check the value of columnNo: If it is greater than 0, then you need to encrypt the corresponding attribute value with the encryption key of the owner role (which is found in the ROLES table). columnNo specifies the order of the column to be encrypted in the table and will never be negative or greater than number of columns in the table. For example, if columnNo is 3 when inserting into the table Schools, the value to be inserted for the column Address should be encrypted. Your​ ​output​ ​should​ ​be​ ​"​Row​ ​inserted​ ​successfully​"​ ​if​ ​the​ ​user​ ​is​ ​authorized. ● SELECT​​ ​*​ ​​FROM​​ ​tableName Upon issuance of this command, you should first check if any of the roles of the current user has the “SELECT” privilege on the table tableName. If not, you should print the error message “​Authorization failure​”. Otherwise, you should print the names of the attributes in this table (in upper case) on a line by itself, each separated by a comma and all records in the table tableName, one record per line, with each attribute value separated by a comma (attribute values in the same order as the attribute names listed on the first line). The value printed for each attribute will depend on the owner of the data. If any of the current user’s roles is the owner role of a tuple, all attribute values for that tuple should be printed in plain text (i.e., encrypted attribute values should be decrypted before printing), otherwise attribute values should be printed as they are stored in the database. You can assume that there will be at least one record in the

table tableName when this command is issued. Normally, having the ADMIN role should not grant the admin user to see the encrypted fields of a table unless the admin has the role​ ​required​ ​to​ ​see​ ​these​ ​fields,​ ​but​ ​this​ ​case​ ​will​ ​not​ ​be​ ​tested. ● QUIT Your program should terminate upon issuance of this command (you should ignore any command​ ​that​ ​appears​ ​after​ ​the​ ​QUIT​ ​command​ ​in​ ​the​ ​input​ ​file). Submission​ ​Instructions The project should be implemented in Java and your main file should be named Project3.java. Before you submit your project, make sure to delete all data from your tables except for the data​ ​in​ ​the​ ​initialization​ ​script​ ​(not​ ​doing​ ​so​ ​could​ ​interfere​ ​with​ ​your​ ​test​ ​results). Please​ ​create​ ​a​ ​README​ ​file​ ​containing​ ​identifying​ ​information.​ ​For​ ​example: CS348​ ​-​-​ ​Project​ ​3 Author:​ ​AuthorName​ ​Login:​ ​author Email:​ ​​author@cs.purdue.edu You​ ​can​ ​also​ ​include​ ​here​ ​anything​ ​you​ ​might​ ​want​ ​us​ ​to​ ​know​ ​when​ ​grading​ ​your​ ​project. Your​ ​submission​ ​should​ ​be​ ​compiled​ ​using​ ​the​ ​following​ ​command: javac​ ​-cp​ ​.:ojdbc8.jar​ ​Project3.java and​ ​should​ ​be​ ​run​ ​using​ ​the​ ​following​ ​command: java​ ​-cp​ ​.:ojdbc8.jar​ ​Project3​ ​input.txt​ ​output.txt
Please submit on Blackboard a ZIP file named "username_project3.zip" where username is your Purdue career account username (i.e. username@purdue.edu). The ZIP file should contain the following: ● Project3.java:​ ​Your​ ​java​ ​main​ ​file ● Any​ ​other​ ​source​ ​code​ ​files​ ​you​ ​might​ ​be​ ​using ● README:​ ​your​ ​readme​ ​file