Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- import java.io.*;
- import java.sql.*;
- import java.util.ArrayList;
- import java.util.Arrays;
- public class Main {
- static User user;
- public static void main(String[] args) {
- Connection conn = null;
- Statement stmt = null;
- user = null;
- if(args.length == 2) {
- try {
- Class.forName("oracle.jdbc.OracleDriver");
- String user = "chadda";
- String pass = "hRZ3I22c";
- conn = DriverManager.getConnection("jdbc:oracle:thin:@claros.cs.purdue.edu:1524:strep", user, pass);
- FileReader fr = new FileReader(new File(args[0]));
- BufferedReader br = new BufferedReader(fr);
- String line = null;
- while((line = br.readLine()) != null ) {
- String output = executeQuery(line, conn);
- System.out.println(output);
- //do fun stuff with the output
- }
- }
- catch (ClassNotFoundException e) {
- e.printStackTrace();
- System.out.println("oracle jdbc driver was not found");
- }
- catch (SQLException e) {
- e.printStackTrace();
- System.out.println("sql error");
- }
- catch(FileNotFoundException e) {
- e.printStackTrace();
- System.out.println("file not found");
- }
- catch(IOException e) {
- e.printStackTrace();
- System.out.println("IO EXCEPTION");
- }
- finally {
- try {
- conn.close();
- }
- catch(SQLException e) {
- System.out.println("error while closing");
- e.printStackTrace();
- }
- }
- System.out.println("Goodbye!");
- }
- else {
- System.out.println("invalid number of arguments");
- }
- }
- public static String executeQuery(String line, Connection conn) throws SQLException{
- //execute this query
- String[] words = line.split(" ");
- if(words[0].equalsIgnoreCase("LOGIN")) {
- if(words.length == 3) {
- //handle login case
- String username = words[1];
- String password = words[2];
- String query = "SELECT USERS.USERID FROM USERS WHERE USERS.USERNAME = '"+username+"' AND USERS.PASSWORD = '"+password+"'";
- Statement stmt = conn.createStatement();
- ResultSet rs = stmt.executeQuery(query);
- int size = 0;
- int userId = 0;
- while(rs.next()) {
- userId = rs.getInt("USERID");
- size++;
- }
- if(size == 1) {
- //given a new user check if this user is an admin
- query = "SELECT ROLENAME, USERID FROM (USERSROLES INNER JOIN ROLES ON USERSROLES.ROLEID = ROLES.ROLEID) WHERE USERID = '"+userId+"'";
- rs = stmt.executeQuery(query);
- boolean test = false;
- String role = "";
- while(rs.next()) {
- role = rs.getString("rolename");
- if(role.equalsIgnoreCase("admin")) {
- user = new User(username, password, userId, true);
- test = true;
- }
- }
- if(test == false)
- user = new User(username, password, userId, false);
- return "Login succesful";
- }
- else {
- return "Invalid Login";
- }
- }
- else {
- return "Invalid Login";
- }
- }
- else if(words[0].equalsIgnoreCase("CREATE")) {
- //len must be 4
- if(words.length == 4) {
- if(!isAdmin()) {
- return "Authorization failure";
- }
- if(words[1].equalsIgnoreCase("ROLE")) {
- //get role newRole id
- String roleName = words[2];
- String encryptionKey = words[3];
- String query = "SELECT ROLEID FROM ROLES ORDER BY ROLEID DESC";
- Statement stmt = conn.createStatement();
- ResultSet rs = stmt.executeQuery(query);
- int roleid = 0;
- while(rs.next()) {
- roleid = rs.getInt("ROLEID");
- break;
- }
- roleid++;
- query = "insert into Roles values('"+roleid+"', '"+roleName+"', '"+encryptionKey+"')";
- stmt = conn.createStatement();
- stmt.executeQuery(query);
- return "Role created succesfully";
- }
- else if(words[1].equalsIgnoreCase("USER")) {
- String username = words[2];
- String password = words[3];
- String query = "SELECT USERID FROM USERS ORDER BY USERID DESC";
- Statement stmt = conn.createStatement();
- ResultSet rs = stmt.executeQuery(query);
- int userid = 0;
- while(rs.next()) {
- userid = rs.getInt("USERID");
- break;
- }
- userid++;
- query = "insert into Users values('"+userid+"', '"+username+"', '"+password+"')";
- stmt = conn.createStatement();
- stmt.executeQuery(query);
- return "User created succesfully";
- }
- else {
- return "Invalid arguments";
- }
- }
- else
- return "Invalid Number of Arguments";
- }
- else if(words[0].equalsIgnoreCase("GRANT")) {
- if(!isAdmin()) {
- return "Authorization failure";
- }
- if(words[1].equalsIgnoreCase("ROLE")) {
- if(words.length == 4) {
- String username = words[2];
- String roleName = words[3];
- //get user id and roleid from username and role name
- String query = "SELECT USERID FROM USERS WHERE USERNAME = '"+username+"'";
- Statement stmt = conn.createStatement();
- ResultSet rs = stmt.executeQuery(query);
- int userid = 0;
- while(rs.next()) {
- userid = rs.getInt("USERID");
- }
- query = "SELECT ROLEID FROM ROLES WHERE ROLENAME = '"+roleName+"'";
- stmt = conn.createStatement();
- rs = stmt.executeQuery(query);
- int roleid = 0;
- while(rs.next()) {
- roleid = rs.getInt("ROLEID");
- }
- //we now have useid and roll id combine and insert into table
- query = "insert into usersroles values('"+userid+"', '"+roleid+"')";
- stmt = conn.createStatement();
- stmt.executeQuery(query);
- return "Role assigned successfully";
- }
- else {
- return "Invalid arguments";
- }
- }
- if(words[1].equalsIgnoreCase("PRIVILEGE")) {
- if(words.length == 7) {
- if(!isAdmin()) {
- return "Authorization failure";
- }
- String privName = words[2];
- String roleName = words[4];
- String tableName = words[6];
- //find roleID
- String query = "SELECT ROLEID FROM ROLES WHERE ROLES.ROLENAME = '"+roleName+"'";
- int roleId = extractInt(conn, query, "ROLEID");
- //find privId
- query = "SELECT PRIVID FROM PRIVILEGES WHERE PRIVILEGES.PrivName = '"+privName+"'";
- int privId = extractInt(conn, query, "PRIVID");
- String[] values = {roleId+"", privId+"",tableName};
- insertInto(conn, "RolesPrivileges", values);
- return "Privilege granted successfully";
- }
- else {
- return "Invalid arguments";
- }
- }
- }
- else if(words[0].equalsIgnoreCase("REVOKE")) {
- if(!isAdmin()) {
- return "Authorization failure";
- }
- String privName = words[2];
- String roleName = words[4];
- String tableName = words[6];
- String query = "SELECT privid FROM Privileges where privName = '"+privName+"'";
- int privId = extractInt(conn, query, "privId");
- query = "select roleid from roles where rolename = '"+roleName+"'";
- int roleId = extractInt(conn, query, "roleId");
- //delete from database
- query = "Delete from RolesPrivileges where roleId = '"+roleId+"' and privId = '"+privId+"' And tableName = '"+tableName+"'";
- Statement stmt = conn.createStatement();
- stmt.executeQuery(query);
- stmt.close();
- return "Privilege revoked succesfully";
- }
- else if(words[0].equalsIgnoreCase("INSERT")) {
- //first check if if the current user has the role of insert privilege on the tablename
- String tablename = words[2];
- if(user != null) {
- //check if the user has insert privileges
- int userId = user.getId();
- String query = "SELECT privname FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM USERSROLES WHERE USERID = '"+userId+"') table1 INNER JOIN ROLESPRIVILEGES ON table1.ROLEID = ROLESPRIVILEGES.ROLEID) table2 INNER JOIN PRIVILEGES ON table2.privid = PRIVILEGES.privid)";
- ResultSet rs= extractResults(conn, query);
- boolean gucci = false;
- while(rs.next()) {
- String privilege = rs.getString("privname");
- if(privilege.equalsIgnoreCase("INSERT")) {
- gucci = true;
- break;
- }
- }
- if(gucci) {
- int beginIndex = 0;
- int outIndex = 0;
- for(int i = 0; i<line.length(); i++) {
- if(line.charAt(i) == '(')
- beginIndex = i;
- if(line.charAt(i) == ')')
- outIndex = i;
- }
- //assume that valuelist works with no brackets
- String valueList = line.substring(beginIndex+1, outIndex);
- String[] values = valueList.split(",");
- for(int i = 0; i<values.length; i++) {
- values[i] = values[i].trim();
- values[i] = values[i].substring(1, values[i].length()-1);
- }
- //get the roleid from roletable given rolename aka ownerrole
- String ownerRole = words[words.length-1];
- query = "SELECT ROLEID FROM ROLES WHERE RoleName = '"+ownerRole+"'";
- int roleid = extractInt(conn, query, "ROLEID");
- int colNo = Integer.parseInt(words[words.length - 2]);
- if(colNo > 0) {
- String word = values[colNo-1];
- query = "SELECT EncryptionKey FROM ROLES WHERE ROLEID = '" +roleid+"'";
- String encryptionKey = extractString(conn, query, "EncryptionKey");
- String encryptedWord = encrypt(word, encryptionKey);
- values[colNo-1] = encryptedWord;
- }
- String[] values2 = new String[values.length+2];
- for(int i = 0; i<values.length; i++) {
- values2[i] = values[i];
- }
- values2[values2.length-2] = colNo+"";
- values2[values2.length-1] = roleid+"";
- //now ship everything to the db
- insertInto(conn, tablename, values2);
- return "Row inserted successfully";
- }
- else {
- return "Authorization failure";
- }
- }
- else {
- return "Authorization failure";
- }
- }
- else if(words[0].equalsIgnoreCase("SELECT")) {
- if(words.length == 4) {
- if(user != null) {
- int userId = user.getId();
- String query = "SELECT privname FROM (SELECT * FROM (SELECT * FROM (SELECT * FROM USERSROLES WHERE USERID = '"+userId+"') table1 INNER JOIN ROLESPRIVILEGES ON table1.ROLEID = ROLESPRIVILEGES.ROLEID) table2 INNER JOIN PRIVILEGES ON table2.privid = PRIVILEGES.privid)";
- ResultSet rs= extractResults(conn, query);
- boolean gucci = false;
- while(rs.next()) {
- String privilege = rs.getString("privname");
- if(privilege.equalsIgnoreCase("SELECT")) {
- gucci = true;
- break;
- }
- }
- if(gucci) {
- String tableName = words[3];
- query = "SELECT * FROM "+tableName;
- rs = extractResults(conn, query);
- ResultSetMetaData meta = rs.getMetaData();
- int columns = meta.getColumnCount();
- String ans = "";
- for(int i = 0; i<columns-2; i++) {
- String col = meta.getColumnName(i+1);
- col = col.toUpperCase();
- if(i != columns-1)
- ans += col+", ";
- else
- ans += col;
- }
- ans += '\n';
- while(rs.next()) {
- ArrayList<String> strs = new ArrayList<>();
- String ownerRole = null;
- int encrpyCol = 0;
- for(int i = 0; i<columns; i++) {
- if(i == columns-1) {
- //owner role
- ownerRole = rs.getString(i);
- continue;
- }
- if(i == columns-2) {
- //encrpytionCol
- encrpyCol = Integer.parseInt(rs.getString(i+1));
- continue;
- }
- String feature = rs.getString(i+1);
- strs.add(feature);
- }
- if(ownerRole == null || encrpyCol == 0) {
- return "failed to extract ownerRole or encrptyCol from db";
- }
- // get the role id
- int roleId = Integer.parseInt(ownerRole);
- query = "SELECT * FROM ROLES WHERE ROLEID = '"+ownerRole+"'";
- String encrpytionKey = extractString(conn, query, "EncryptionKey");
- //check if the user has permission to read
- query = "SELECT * FROM USERSROLES WHERE USERID = '"+userId+"' AND ROLEID = '"+roleId+"' ";
- ResultSet rs2 = extractResults(conn, query);
- boolean hasPermission = false;
- while(rs2.next()) {
- hasPermission = true;
- break;
- }
- //get encryption key
- //if the user has permission on the tuple
- if(hasPermission) {
- //identify which coloumn should be decrpyted and decrpyt it
- String encrtpyed = strs.get(encrpyCol-1);
- String decrypted = decrypt(encrtpyed, encrpytionKey);
- strs.set(encrpyCol-1, decrypted);
- }
- for(int i = 0; i<strs.size(); i++) {
- if(i == strs.size()-1) {
- ans+= strs.get(i);
- }
- else {
- ans+= strs.get(i)+ ", ";
- }
- }
- ans+='\n';
- }
- return ans;
- }
- else {
- return "Authorization failure";
- }
- }
- else {
- return "Authorization failure";
- }
- }
- else {
- return "Invalid Arguments";
- }
- }
- return "";
- }
- public static boolean isAdmin() {
- if(user == null || user.isAdmin() == false) {
- return false;
- }
- return true;
- }
- //returns 0 if it doesnt exit
- public static int extractInt(Connection conn, String query, String feature) throws SQLException{
- ResultSet rs = extractResults(conn, query);
- int result = 0;
- while(rs.next()) {
- result = rs.getInt(feature);
- break;
- }
- rs.close();
- return result;
- }
- //returns nullif it doesnt exist
- public static String extractString(Connection conn, String query, String feature) throws SQLException{
- ResultSet rs = extractResults(conn, query);
- String result = null;
- while(rs.next()) {
- result = rs.getString(feature);
- break;
- }
- rs.close();
- return result;
- }
- public static ResultSet extractResults(Connection conn, String query) throws SQLException{
- Statement stmt = conn.createStatement();
- ResultSet rs = stmt.executeQuery(query);
- //stmt.close();
- return rs;
- }
- public static void insertInto(Connection conn, String tableName, String[] values) throws SQLException {
- String query = "insert into "+tableName+" values (";
- for(int i = 0; i<values.length; i++) {
- query+= "'"+values[i]+"'";
- if(i != values.length-1)
- query+=", ";
- }
- query+= ")";
- Statement stmt = conn.createStatement();
- stmt.executeQuery(query);
- return;
- }
- public static String encrypt(String ptOg, String key) {
- String k = key.toUpperCase();
- String pt = ptOg.toUpperCase();
- for (int i = 0; i < pt.length() ; i++) {
- if (k.length() < pt.length()) {
- if (Character.isLetter(pt.charAt(i))) {
- k += pt.charAt(i);
- }
- }
- else break;
- }
- int ptNum = 0, kNum = 0;
- int ctNum = 0;
- String ct = "";
- char x = 'A';
- for(int i = 0, j = 0; i < pt.length(); i++) {
- if (Character.isLetter(pt.charAt(i)) && Character.isLetter(k.charAt(j))) {
- ptNum = pt.charAt(i) - 'A';
- kNum = k.charAt(j) - 'A';
- ctNum = (ptNum +kNum) % 26;
- x = (char) ('A' + ctNum);
- ct += x;
- j++;
- }
- else {
- ct += pt.charAt(i);
- continue;
- }
- }
- char z = 'a';
- for (int i = 0; i < ct.length(); i++) {
- if (Character.isLowerCase(ptOg.charAt(i))) {
- z = Character.toLowerCase(ct.charAt(i));
- char[] temp = ct.toCharArray();
- temp[i] = z;
- ct = String.valueOf(temp);
- }
- }
- return ct;
- }
- public static String decrypt(String ctOg, String key) {
- String ct = ctOg.toUpperCase();
- String kprime = key;
- String pt = "";
- int ctNum = 0, kNum = 0;
- char x = 'A';
- int temp = 0;
- for (int i = 0, j = 0; i < ct.length(); i++) {
- if (Character.isLetter(ct.charAt(i)) && Character.isLetter(kprime.charAt(j))) {
- ctNum = ct.charAt(i) - 'A';
- kNum = kprime.charAt(j) - 'A';
- temp = (ctNum - kNum) % 26;
- if (temp < 0) {
- temp += 26;
- }
- System.out.println(ctNum + " " + kNum + " " + temp);
- x = (char) ('A' + temp);
- pt += x;
- kprime += x;
- j++;
- }
- else {
- pt += ct.charAt(i);
- continue;
- }
- }
- char z = 'a';
- for (int i = 0; i < pt.length(); i++) {
- if (Character.isLowerCase(ctOg.charAt(i))) {
- z = Character.toLowerCase(pt.charAt(i));
- char[] t = pt.toCharArray();
- t[i] = z;
- pt = String.valueOf(t);
- }
- }
- return pt;
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement