MySQL Security

This information about Computer Security is for advanced IT users. MySQL security setup is the responsibility of a practice or its IT, although we recommend contacting Open Dental Support for assistance implementing any changes.

MySQL / MariaDB is the database server where all the data tables are stored.

MySQL is only as vulnerable as the local network security allows. It is not secure to expose the MySQL service to the internet (World Wide Web). Do not open the MySQL port (default is port 3306) on routers and do not allow incoming traffic for that port. The port can be opened as a Firewall exception rule on servers but NOT on routers. In simple terms, this means do not open the database to the entire internet, even if it is thought that the source IP address is verified: it is not sufficient as IP addresses can be spoofed.

MySQL user names and passwords are a secondary level of security. To access an Open Dental database, a MySQL user can require a password to be used.

Note:
  • Putting passwords on MySQL does not solve all security issues. Always ensure the network and server are protected. See Computer and Network Setup.
  • MySQL passwords are also not the same as password features built into Open Dental.
  • Open Dental specifically advises customers to avoid giving their database (MySQL/Maria) credentials to third party vendors. It is generally safe to give read-only access, but many Vendors will ask for full control, and it is dangerous. It also circumvents the user-level security built into Open Dental. See Integration Types for information on safer integration methods.


Small offices (users on small closed networks): It is up to the practice to decide whether or not to set MySQL passwords. We recommend that this is done. If someone has access to the data on the server then MySQL passwords do not provide additional protection. Therefore some users may choose to not set MySQL passwords.

Large enterprises should use MySQL users and passwords and create institutional safeguards against losing the password.

Set Up the User and Password in MySQL During Installation

For new MySQL installations, the Trial Installer prompts for the creation of a MySQL username and password.

Enter the information as desired. If the password is left blank, users receive a warning and must confirm their choice to proceed without a password.

Adding MySQL Password Later

If using an existing MySQL installation, or if Do this later was selected when installing, see MySQL User Manager to start using a MySQL password. For versions 19.3 and below, use the following steps:

Note: We highly recommend contacting our Support Team for assistance with this process as we have internal tools that make it easier.

  1. In the MySQL command line client, or in User Query in Open Dental from any workstation, run this query to get a list of current users, hosts, and passwords.

    SELECT USER, HOST, PASSWORD FROM mysql.user;


    A default installation has 5 users already set up.
    • 'root'@localhost

    • 'root'@localhostIP

    • 'root'@'::1'

    • 'root'@'%'

    • ''@'localhost'

  2. For each user/host combination, run a query to change the password (replace 'new_password' with the desired password). Run each query one at a time.

    Examples:

    SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('new_password');

    SET PASSWORD FOR 'root'@'::1' = PASSWORD('new_password');

    SET PASSWORD FOR 'root'@'%' = PASSWORD('new_password');

    SET PASSWORD FOR ''@'localhost' = PASSWORD('new_password');

    SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');


    IMPORTANT: root@% is a completely different than root@localhost. The password must be changed for both to secure the database from root on all computers.

    Consult the MySQL website for details about each user.

    By default, root@localhost has global access for MySQL and all privileges are enabled.
  3. Run the query in Step 1 again to confirm the passwords. Each user/host combination should show a hashed version of the password.
  4. Restart the server, or run the query FLUSH PRIVILEGES; for the changes to take effect.

Note: If workstations are logged into Open Dental when the password is set, they may receive an error. Close out of Open Dental and reopen the program. Follow the steps below to Change the User and Password from Choose Database.


Change the User and Password on the Choose Database Window

After making password changes in MySQL, the Open Dental workstations no longer have access to the database.

  1. Close Open Dental on each workstation.
  2. Run Open Dental as an admin. The Choose Database window prompts for a MySQL User name and Password.
  3. Verify the server and database.
  4. Verify the user (e.g., root).
  5. Enter the password just created (for default installations typically the root@localhost password).

More Details

The password entered into the MySQL Configuration window on each computer is stored as obfuscated text in the FreeDentalConfig.xml.

If the practice plans on hosting multiple customers on the same database server, see Host Multiple Databases from One Web Server for isolation information.

Practices may need to work from particular devices or IP address ranges for multi-tenant installations or if there are complex networks where some segments should not have access. Open Dental does not provide advice or direct support on setting up usernames for particular devices or network segments. For multi-tenant installations, consult an IT professional. For complex networks, consult an IT professional, or for even more security use the Middle Tier. Information about setting up usernames for specific devices or network segments is available at http://dev.mysql.com/doc/refman/5.5/en/account-names.html. Open Dental works fine as long as the specified MySQL user has the correct (full) permission set.

MariaDB supports encryption for databases using the InnoDB storage engine. See: MariaDB Data-at-Rest Encryption for more information.

Middle Tier Users

Two MySQL users may be specified: a regular user and a user with lower privileges.

When users connect from the client computers, they never see or have access to the users that are set up in this file. They connect using an Open Dental username and password. See Choose Database for an example.

To securely access the Middle Tier from outside the network (e.g., from home), additional steps are required. See: Middle Tier Security Certificate

Troubleshooting

If workstations have trouble connecting, it may be a Firewall issue. If you can't get past the Choose Database window, then it's a firewall issue. You may need to create an exception to allow Port 3306. See Open Port 3306. If you've turned off all firewalls on the server and are still stuck at the Choose Database window, see Troubleshooting.

Users and password: If you change passwords and it no longer works, carefully retrace your steps to make sure the new user has all privileges and access to the Open Dental database. Be aware of the difference between user@% and user@localhost. Double-check the privileges and try again.