We are using the sa account to connect to and run our SQL statements in our JDBC code. The sa account is the administrator account in H2.

If an attacker somehow hijacks one of our database statements via an SQL injection flaw then they can do anything to the database that an administrator could (which is everything). Sometimes we run multiple databases out of a single database server instance --- this is what was happening with your Oracle databases in COMP101 --- you each had your own schema, but the entire class was using a single instance of Oracle. Running your connections using an administrator account puts every single one of those databases at risk.

In order to run each operation with the lowest level of privileges as possible we will need to create multiple user accounts in the database. The number of accounts that we need depends of the different types of operations that we need. We have identified the following four operation types that we need in the shopping system:

• account -- Customer account creation and sign in. This requires insert and select access to the customer table.

• browse -- Customer browsing the products. This requires select access to the product table.

• sale -- Customer creating a sale. This requires insert access to the sale and sale item tables, and select and update access to the product table. You need to have select access in order to update or delete, because the DBMS needs to find the affected rows before it can modify them. Inserting does not need select access because you are not accessing existing data. This varies a bit from DBMS to DBMS, so make sure you read the documentation.

• manage -- Product catalogue administration (via the Swing user interface). This requires select, insert, update and delete access to the product table. If you used a merge statement for the edit bonus task, this is covered by both the insert and update rights --- there isn't a separate right for merge.

It is good practice to create roles for each different type of operation to separate the rights from the users. The appropriate table rights are assigned to the roles. We can then create users and add them to the appropriate roles.

The following shows you how to do this for the first operation type (account):

-- create the role
create role if not exists account_role;

-- grant table rights to to the role
grant select, insert on customer to account_role;

-- create the user
create user if not exists account_user password 'somepassword';

-- add the user to the role
grant account_role to account_user;

1. Add this code to the bottom of your schema SQL file (just above the SET ALLOW_LITERALS NONE; line). It is part of your database definition so belongs in the schema file.

   Adapt to suit your table name. Don't worry too much about the password yet --- you can easily change it to something more secure later.

2. Copy the code into the H2 console and run it.

3. Disconnect from the database and reconnect using the account_user user. Can your query the customer table? What about the product table?

4. Using connection pools with roles is a bit clunky because JDBC connection pools are per-user. We are going to have to create several connection pools, so we are going to need to have several JdbiDaoFactory classes --- one for each role.

   Make a copy of the JdbiDaoFactory and name the copy AccountJdbiDaoFactory.

5. Change the DB_USERNAME field in the new class to account_user.

6. Change the DB_PASSWORD field to somepassword.

7. In your Server class, change the code that creates the CustomerDAO to use the new AcountJdbiDaoFactory to create the DAO instance.

8. Restart and test your system. You should still be able to perform all of the operations, but now the account creation and sign in operations are using an account that has much less privileges.

Repeat the same process for the other operations that were identified. Test as you go. You will need to reconnect to the database as the sa user before you will be allowed to modify the database.

The sale operation will need a separate grant statement for each of the tables that it needs to assign rights for.

The web service should be using the browse role for accessing products.

The product catalogue system should be using the manage role.

Once you are finished, add a @Deprecated annotation to the class header of the original JdbiDaoFactory since this class is still using the sa account and we should not be using that any more. The annotation will cause the compiler and IDE to warn us if we try to use this class and indicate that this class should probably be removed in the near future.