Jun 5, 2018 by Robert Gravelle

Part 2: Creating a New User

In Part 1, we learned how to secure the MySQL root account using the Navicat Premium User Management Tool. Today's blog will focus on setting a new user's account details, account limits, and SSL settings.

The General Tab

Clicking the New User button on the Objects toolbar opens an Untitled User tab. It, in turn, contains five tabs named General, Advanced, Server Privileges, Privileges, and SQL Preview. We covered the General tab in Part 1, but we'll quickly recap here. On the General tab, we need to provide:

The User Name.
The database Host.
The encryption Plugin. Choose "mysql_native_password" or "sha256_password" from the dropdown.
The Password.
The Expire Password Policy.

Setting the Password Policy

MySQL enables database administrators to expire account passwords manually, and to establish a policy for automatic password expiration using either the MySQL mysql_native_password or sha256_password built-in authentication plugin.

Navicat abstracts the usual MySQL mechanism for setting password expiration using the PASSWORD EXPIRE statement by providing several options via a dropdown list. They are:

DEFAULT: Sets the password expiration length to the database default. Prior to version 5.7.11, the Default Value was 360 days. Since version 5.7.11 onwards, the Default Value is 0 days, which effectively disables automatic password expiration.
IMMEDIATE: Expires an account password, thus forcing the user to update it.
INTERVAL: Specifies the number of days in which the current password expires.
NEVER: Allows the current password to remain active indefinitely. Useful for scripts and other automated processes.

Here's an Example:

The Advanced Tab

Here you'll find settings for account limits, and SSL.

Account Limits

MySQL permits limits for individual accounts on use of various server resources so that any one user may not monopolize resources. Limits include:

The number of queries an account can issue per hour.
The number of updates an account can issue per hour.
The number of times an account can connect to the server per hour.
The total number of database connections an account can make.

These equate to the Max queries per hour, Max updates per hour, Max connections per hour, and Max user connections Advanced tab fields. Each of these fields accept a value of zero (0) or a positive integer.

SSL Settings

In order to use encrypted connections, OpenSSL or yaSSL must be present in your system. Also, the MySQL server needs to be built with TLS support and be properly configured to use one of them. Note that the term SSL, refers to the old, now insecure, protocol preceding TLS, is still used in many of the variable names and options for compatibility reasons although MySQL only uses its more secure (TLS) successors.

The SSL Type dropdown field maps to the ssl_type column of the mysql.user table, which only accepts certain values: ANY, SPECIFIED, and X509 (as well as '' for NONE).

Moreover, the MySQL GRANT statement also accepts the ISSUER, SUBJECT, and CIPHER options. These can be combined together in any order, and if you use any of them REQUIRE X509 is implicit.

Here's a GRANT statement, followed by the equivalent Advanced tab in Navicat:

GRANT USAGE ON *.* TO 'bob_s'@'localhost'
    REQUIRE SUBJECT '/CN=www.mydom.com/O=My Dom, Inc./C=US/ST=Oregon/L=Portland'
    AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/emailAddress=This email address is being protected from spambots. You need JavaScript enabled to view it.'
    AND CIPHER 'SHA-DES-CBC3-EDH-RSA';