21UserManagement

User Management

Outline

Basic security guidelines
Securing passwords
Adding/removing user accounts
Setting accounts resource limits
Locking/unlocking accounts

The first and the very important security guideline is the following:

Do not ever give anyone, except: root user, access to the relational tables in a database: my s q l !

A database: my s q l, contains the data dictionary relational tables with metadata.

Corruption of information included in the data dictionary tables is a catastrophic event for a database.

More security guidelines:

Use GRANT and REVOKE statements, to control access to a database and do not grant more privileges than necessary.

Try at a command line prompt a statement: my s q l dash u root. If it works all right, then it means, that you must set a password for a user: root.

Use SHOW GRANTS statement, to check which accounts have access to what data objects. Then, use REVOKE statement to remove the privileges, that are not necessary.

Basic security guidelines

Do not ever give anyone (except root account) access to a user table in a mysql database !
Use GRANT and REVOKE statements to control access to MySQL and do not grant more privileges than it is necessary
Try
If it works all right then you must set a password for a user root !
Use SHOW GRANTS statement to check which accounts have access to what data resources
Then, use the REVOKE statement to revoke the privileges, that are not necessary

The other security guidelines are the following:

Do not store cleartext passwords in your database.

It means, that all passwords must be encrypted.

To encrypt the passwords, use hashing functions like sha2, sha1, md5 or some other one way hashing function and store the hash value.

Do not choose the passwords from the dictionaries, because special programs exist to break the passwords.

Invest in a firewall, to protect you from at least 50% of all types of exploits in any software.

The applications, that access MySQL database, should not trust any data entered by the users.

Such applications, should be written using the proper defensive programming techniques.

Do not transmit plain (un-encrypted) data over the Internet, because such information is accessible to everyone, who has the time and ability to intercept it and use it for their own purposes.

Instead, use an encrypted protocol such as SSL or SSH.

Basic security guidelines

Do not store the cleartext passwords in your database
Instead, use hashing functions like sha2(), sha1(), md5() or some other one-way hashing function and store the hash values
Do not choose passwords from the dictionaries because the special programs exist to break the passwords
Invest in a firewall to protect yourself from at least 50% of all types of exploits in any software
Applications, that access MySQL should not trust any data entered by the users, and it should be written using the proper defensive programming techniques
Do not transmit plain (unencrypted) data over the Internet because such information is accessible to everyone who has the time and ability to intercept it and use it for their own purposes
Instead, use an encrypted protocols such as SSL or SSH

User Management

Securing passwords

Using -pyour_password or --password=your_password option on the command line like
is convenient way to provide a paswword but ... it is extremely insecure !
Using the -p or --password option on the command line with no password value like
is less convenient and it is more secure
Use the mysql_config_editor utility to store authentication credentials in an encrypted login path file named .mylogin.cnf.
The file can be read later by MySQL client programs

Securing passwords

Store your password in a file with system variables in a section [client]
To keep the password safe, the file should not be accessible to anyone but yourself
Store your password in the MYSQL_PWD environment variable
This method of specifying your MySQL password must be considered extremely insecure and should not be used.

User Management

Outline

Basic security guidelines
Securing passwords
Adding/removing user accounts
Setting accounts resource limits
Locking/unlocking accounts

Adding/removing user accounts

Creating a new user
Altering a user

Listing the users

SELECT USER FROM mysql.user;

+-----------+
| USER      |
+-----------+
| jamesb    |
| csit115   |
| mysql.sys |
| root      |
+-----------+

To drop a user account we use: DROP USER statement.

A name of a user account or simply speaking: user name, is up to 32 characters long.

A user account may have a password.

It is strongly recommended, to give a password to a user account.

Instead of a password, a user account may have the authentication plugin, that implements an external authentication methods.

User name and passwords are stored in a relational table: user, located at: my s q l database.

The passwords, stored in a relational table: my s q l dot user, are encrypted using a plugin specific algorithm.

When a user connects to the server, there is an initial authentication step, at which the user provides a password, that have a hash value equal to a hashed password stored in a relational table: mysql.user table.

Adding/removing user accounts

Dropping a user
A user name is up to 32 characters long
A user account may have a password
The accounts instead of a password may have an authentication plugins, that implements the external authentication methods
The user names and passwords are stored in a relational table mysql.user
Passwords stored in mysql.user table are encrypted using a plugin-specific algorithm
When a user connects to the server then there is an initial authentication step when a user provides a password, that have a hash value equal to hashed password stored in mysql.user table

Adding/removing user accounts

After a successful connections a user can, depending on the sufficient privileges, set or change a password
When connecting a password is either provided in a command line or it is entered interactively during a login process

User Management

Outline

Basic security guidelines
Securing passwords
Adding/removing user accounts
Setting accounts resource limits
Locking/unlocking accounts

It is possible, to set the resource limits for the individual user accounts, on the utilisation of the following database server resources.

MAX QUERIES PER HOUR: is a parameter, that limits the total number of queries an account can issue per hour.

MAX UPDATES PER HOUR: is a parameter, that limits the total number of updates an account can issue per hour.

MAX CONNECTIONS PER HOUR: is a parameter, that limits the total number of times an account can connect to the server per hour.

MAX USER CONNECTIONS: is a parameter, that limits total number of simultaneous connections to the server from a user account.

The resource limits for a user account can be set either by: CREATE USER or by ALTER USER statement.

A few examples of setting the resource limits, are given in a bottom part of the present slide.

Setting account resource limits

It is possible to set the limits for individual accounts on use of the following server resources:
- total number of queries an account can issue per hour (MAX_QUERIES_PER_HOUR)
- total number of updates an account can issue per hour (MAX_UPDATES_PER_HOUR)
- total number of times an account can connect to the server per hour (MAX_CONNECTIONS_PER_HOUR)
- total number of simultaneous connections to the server by an account (MAX_USER_CONNECTIONS)

The server stores the resource limits assigned to a user account in: my s q l dot user table, in a row corresponding to the user account.

A database server counts the total number of times each account uses the resources.

If an account reaches its limit on number of connections within the last hour, the server rejects the further connections for the account until that hour is up.

Similarly, if the account reaches its limit on the number of queries or updates, the server rejects further queries or updates until the hour is up

In all such cases, the server issues appropriate error messages.

To reset the current counts to zero for all accounts, a database administrator can use: FLUSH USER RESOURCES statement.

The counts for an individual account, can be reset to zero by setting any of its limits again.

Setting account resource limits

The database server stores the resource limits of an account in a relational table mysql.user in a row corresponding to the account
A database server counts the number of times each account uses each resource
If an account reaches its limit on number of connections within the last hour, the database server rejects further connections for the account until, that hour is up
Similarly, if the account reaches its limit on the number of queries or updates, the server rejects the further queries or updates until the hour is up
In all such cases, the server issues appropriate error messages
To reset the current counts to zero for all accounts, a database administrator issues a FLUSH USER_RESOURCES statement

Setting account resource limits

The counts for an individual account can be reset to zero by setting any of its limits again
Per-hour counter resets do not affect MAX_USER_CONNECTIONS limit
All counts begin at zero when the server starts and the counts do not carry over through server restarts

User Management

Outline

Basic security guidelines
Securing passwords
Adding/removing user accounts
Setting accounts resource limits
Locking/unlocking accounts

Locking and unlocking accounts

An account can be locked immediately after its creation ( CREATE USER) or at any time after its creation ( ALTER USER)


CREATE USER jamesb IDENTIFIED BY 'mi6' ACCOUNT LOCK;


ALTER USER harryp ACCOUNT LOCK;


ALTER user harryp ACCOUNT UNLOCK;

If an account is locked then its state is recorded in an account_locked column of a relational table mysql.user

References

MySQL 8.0. Reference Manual, 6.2 Access Control and Account Management