This information about Computer Security is for advanced IT users. MySQL security setup is the responsibility of your practice or IT, although we recommend contacting Royal dental Software 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 your 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 your routers and do not allow incoming traffic for that port. You can open this port as a Firewall exception rule on your servers but NOT on your routers. In simple terms, this means do not open the database to the entire internet, even if you think you are verifying the source IP address: it is not sufficient as IP addresses can be spoofed.

MySQL user names and passwords are a secondary level of security. To access an Royal dental Software database, you must use a MySQL user and can require a password.

Small offices (users on small closed networks): It is up to you to decide whether or not to set MySQL passwords. We recommend that you do. If someone has access to the data on your 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 installations, the Trial Installer will prompt for the creation of a MySQL username and password.

Enter the information as desired. If you attempt to leave the password blank you will presented with a warning and required to confirm the choice.

Adding MySQL Password Later

If you are using an older installation, or you have clicked Do this later and would like to start using a MySQL password, see MySQL User Manager. For versions 19.3 and below, follow the steps below:

1. In the MySQL command line client, or in User Query in Royal dental Software 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 will have 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 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. You must change the passwords 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.

Change the User and Password on the Choose Database Window

After making password changes in MySQL, the Royal dental Software workstations will no longer have access to the database.

1. Close Royal dental Software on each workstation.
2. Run Royal dental Software as an admin. It will ask for the user and password on the Choose Database window.
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 you enter into the MySQL Configuration window on each computer is stored as obfuscated text in the FreeDentalConfig.xml.

If you plan on hosting multiple customers on the same database server, see Multi-tenant Hosting for isolation information.

You may need to work from particular devices or IP address ranges for multi-tenant installations or if you have complex networks where some segments should not have access. Royal dental Software 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. Royal dental Software works fine as long as the specified MySQL user has the correct (full) permission set.

Middle Tier Users

There are two MySQL users that may be specified: a regular user and a user with lower privileges.

• Regular user: Must have all permissions for all tables starting with "royaldental" (if that is the name of your database). Must be able to do everything from adding tables to making a backup of the database. It is during the backup process that the user must also have access to a database that might be called "royaldentalbackup_07_14_2006", and the user must also have permission to create tables, etc in this other database. Either give this user privileges for all databases or use a wildcard character to give privileges for similar databases.
• UserLow: A user with much lower privileges. No SQL statements are allowed to be passed to the middle tier service except report queries. UserLow is always used for these report queries. This allows prevention of injection attacks at the database level. UserLow should have very limited privileges, just SELECT and CREATE TEMPORARY TABLES, and only for the current database. They will still be able to use queries to select any data, but not to alter it.

When users connect from the client computers, they will never see or have access to the users that you set up in this file. They will be connecting using an Royal dental Software username and password. See Choose Database for an example.

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 royaldental database. Be aware of the difference between user@% and user@localhost. Double check the privileges and then try again.