Accounts Used to Setup and Administrate Specify Databases

Documentation Configuration & Installation
1

Root (“Specify IT User”) Account

  1. The root account is required within MySQL or MariaDB and must exist for every SQL installation. It has full access to all SQL commands and databases within a SQL data manager installation.
  2. In Specify documentation we have called the SQL root-level account, the “Specify IT User” account.
  3. In Sp7, the root account is located in the db container. However, for all database CRUD operations, we use the master user. We need the root account to perform the initial schema setup, to update the schema, and to create the Specify ‘Master’ account.
  4. We always need the root user as it is a prerequisite for MySQL or MariaDB installation and setup. The SQL root user is typically set up by the person who sets up a Specify database for the first time. This is usually done in the container if it is dockerized.
  5. If not dockerized, the person setting up MySQL on their system needs to configure the root user.
  6. Best practice has advocated that the actual ‘root’ account in a SQL installation be reserved for use only by the systems administrator. And that similar root-level privileges (or slightly less) accounts be set up for uses like Specify installation and setup needs. That way a single SQL ‘root’ user account’s credentials are not shared among multiple people where it can be inadvertently changed by one party, locking everyone else out.
  7. Specify may or may not choose to do this, for smaller sites, they often just use the actual SQL ‘root’ account for setup (Docker provides enough compartmentalization and security), for larger installations with multiple collections, creating a second SQL root- or sudo-root level account for Specify is more likely.

Master Account

This MySQL/MariaDB user is granted elevated permissions for the database. The Master account has SELECT, INSERT, UPDATE, DELETE and TABLE LOCK privileges on data tables in your Specify database only and has no other MySQL privileges. Specify uses only one Master username and password for all users, but encrypts the Master information with the User information, creating an encrypted string. This allows the Master information to become unique for each user.

  1. The Master user is created in the SQL installation and is granted specific permissions. We give it permission to modify databases. The root user is required to create the Master user. Initially, we connect as root and then create the Master user. The master user has full database CRUD access.
  2. We do not connect to Sp7 with the root account because it is only used for setup, which we don’t do in Sp7.
  3. There is one root account shared among different databases within a SQL installation, and there can be different Master accounts defined in SQL for each Specify database. The master account can have permissions to one or multiple Specify databases.
  4. We use the master account, instead of letting Specify user accounts to write to the database because it provides better and simpler security. In that way, we do not require code to create a new user account in SQL every time a user account is created in Specify. All database CRUD permissions are defined in the application layer with high-resolution, role-based, control in Specify 7.
  5. You can automatically create a master user by deploying with Docker, or if Docker is not used, it can be created using the Specify DB Wizard.

Specify User (Admin)

  1. The Admin account is created within Specify, not at the SQL level, by the Master user during setup and is the first user created. It has permissions to access to all aspects of Sp7 including that Specify database by default.
  2. The Admin account cannot directly connect to the database. When Sp7 needs to update an object or perform any other action, it sends the request to the master account, which then executes the action in MySQL.
  3. We can directly connect to a Specify database through MySQL Workbench using the SQL/Specify master account.
  4. The admin account needs to be created using the Wizard. In version 7, we will need a user interface (UI) to create the admin account. Currently, it simply involves creating a new user and granting it all permissions. Since we require a user to access Sp7, we need to create the admin account first. We should have access to the user creation page without needing to be connected as a user initially.
  5. All other Specify user accounts are created either by the admin account or by existing Specify accounts with permission in the Specify security system to do so.

Notes:

  1. Having both master and root accounts in SQL may seem redundant if there is only one database, but it adds an extra layer of security. It is good practice not to use a SQL root-level account for daily access to a resource on a server.
  2. We might look at the security implications of using a single ‘Master’ account for each database. One might imagine a finer level of control at the database level, like maybe having a SQL read-only account for Specify guest users or for API accesses.