Lab 4: API access

NYC has a roster of Health Inspectors that carry out restaurant inspections. In this Lab you will create an application to track Health Inspectors in the nyc_data schema via a RESTful API. Health Inspectors should log in to the application by providing a valid username and password. The application should then call web API methods to create, read, update, or delete Health Inspectors as described below. The API methods should not take action unless the user is properly authenticated (e.g., has provided a valid username and password).

Note: this lab is designed to increase your experience with raw SQL. Do not use an Object Relationship Mapper (ORM) like SQL Alchemy.

Exercises

Write code for both the server side as well as the client side as follows:

Server side [7 points]

  1. [1 point] Create an Employees table in your nyc_data schema to track the Health Inspectors, you pick the attributes, but at a minimum this table should have attributes for: EmployeeID and name, and whether the Inspector has admin privileges. You'll also want to store each Inspector's username and hashed password. Insert the first employee who is an admin (you might need to write a short Python script to get the required data). Do not create an API route that creates the first user (you have SQL skills and such a route is silly and dangerous). Also research how to create attributes to automatically timestamp when a row is first inserted (call this attribute CreatedAt), and automatically timestamps whenever a row is updated (call this attribute UpdatedAt). Do not use a trigger for these two attributes, find a way to carry out these fields in the CREATE TABLE command.

    Also create a new database user that your API will use to access the database. Give this user the minimal needed permissions necessary to execute the API methods.

    Give the SQL to create this table and user in one .sql file.

  2. [1 point] Do NOT to store the Inspector's passwords in plain text! Instead hash and salt them (pepper if you'd like). I recommend using bcrypt. That package can generate a salt for you. Then you can combine the salt with the plain text password to get a hash using the bcrypt.hashpw(password,salt) function. Store the hashed password in your Employees table (bcrypt's hash will include the salt). Use bcrypt.checkpw(pwdToCheck,storedHash) to determine if a plain-text password pwdToCheck matches a stored hash in your table. This function returns true if pwdToCheck matches the stored hash, false otherwise.

  3. [5 points] Use Flask to create RESTful methods to login then create, read, update, and delete Inspectors from the table using the proper HTTP verbs. To access the database, use the database user's credentials that you created on the server side in step 1 above. You may use app.txt (rename as .py) from class as a scaffold. There are many, many tutorials on the web about creating web APIs if you get stuck. Use JWT to authenticate users after they log in. Allow only authenticated users to access the API following these rules:
    ROUTE ADMIN       NON-ADMIN FUNCTION
    POST /api/login ✓ ✓ Login, passing username and password
    GET /api/employees ✓ ✗ View all employees
    GET /api/employees/me ✓ ✓ View own profile
    POST /api/employees ✓ ✗ Create new employee, passing new employee information
    PUT /api/employees ✓ ✗ Update any employee, passing fields to update
    PUT /api/employees/me ✓ ✓ Update own profile, passing fields to update (non-admin cannot change their admin status)
    DELETE /api/employees/:id   ✓ ✗ Delete an employee with EmployeeID = :id

    I recommend testing this functionality using Postman.

Client side [3 points]

  1. Create a client-side application program that allows users to create, read, update, or delete Health Inspectors in your Employees table (provided they have rights as described above) by calling your RESTful API methods. Write your client-side code in Python. You may use call_api.txt (rename as .py) from class as a scaffold for this portion of the exercise.

  2. Your solution need not be aesthetically beautiful, but it should be functional:
    • You can use Python's GUI tools such as TkInter, or
    • Your solution could be purely command line driven (e.g., press 1 to log in, press 2 to create a new Inspector, ...).

  3. Provide Python code that tests your API considering logged in vs. not logged in, and admin vs. non-admin users (e.g. "Press 8 to test all functionality" or similar).

Submission Instructions

Submit the following in a zip file:

Remember, if you work with a partner, each partner should submit the same solution and should note the names of each partner in the comments section on Canvas. Remember to include the header as described in the submission instructions for Lab 1.