SECURITY

• Database security


  Protection of sensitive information, maintaining availability
• If database information is compromised, it can have severe consequences including financial loss, reputational damage, and legal issues
• It is not enough to just capture data in a database, it is also important to store it in a way that is conducive to security
• Database security overview


  • Availability
  • Authenticity
  • Integrity
  • Confidentiality
• Availability


  Data needs to be available at all necessary times to appropriate users, ability to track access
• Authenticity


  Ensure data has been edited by authorized source, confirm users accessing the system, verify report requests and outbound data
• Integrity


  Verify external data formatting and metadata, ensure input data accuracy and compliance with workflow rules, report on all data changes and authors
• Confidentiality


  Ensure confidential data is only available to correct people, secure database from external and internal breaches, provide reporting on data access
• Security models


  • Considerations: who can access the database (DB administrators vs individuals), what data they can access (varies across applications, departments, individuals)
  • Need authentication (confirming identity) and authorisation (being allowed access)
• Authentication


  The process of confirming a user attempting to log in is authorized to do so
• Authentication examples


  • "We know you are Bob because you have entered Bob's password"
  • "We know you're REMOTE_PROCESS_X because you connect from IP address Z"
  • "We know you're a PUBLIC user because you haven't entered any password"
  • "We know you're MobileAPP Y because you have used the correct credentials"
• Authorisation


  A user is accorded the rights to perform activities they have been authorized to do
• Authorisation examples


  • Bob can save files here
  • Jane can read files here but not write them
  • Adam can SELECT from this table
  • Sarah can SELECT and INSERT on any table in this database
  • WEBAPP X can SELECT from this table and INSERT into that table
• Security = Authentication + Authorisation
• Maintaining data integrity involves creating users and granting them permissions to control access and limit their ability to read, change, add or delete data
• Local host account


  Extra feature not present on the remote server
• Creating a new user


  CREATE USER user_name @localhost IDENTIFIED BY 'password'
• Granting privileges


  GRANT Privilege ON database.table_name TO user_name@localhost IDENTIFIED BY password
• Checking privileges


  1. SHOW GRANTS

  2. SHOW GRANTS FOR 'user'@'localhost'

  
• My PC localhost root user account has all privileges granted to all tables of all databases
• The EEECS web hosting account user typically has the GRANT USAGE ON *.* privilege, which means they can't create new databases or view other users' databases
• App security


  The app connects to the database as a user and performs queries as that user, so access control is provided by the app rather than executing specific SQL statements
• App-level security is crucial as it allows only authorised users to access specific data, a feature not directly provided by a database's GRANT system
• Specifying row-level access requires app-level enforcement, typically through queries with conditions