Understanding MariaDB Authentication

MariaDB has a sophisticated multiuser access-control system that can allow or prevent each user from having access to a particular database or individual tables. You can even restrict the types of operations the user is able to perform on a table.

Web Access

When MariaDB is used as the back end to a website, you will usually have only a single user account through which all the connections from the web scripts are performed. Your application will handle any additional user authentication that is required in the web browser.

Authenticating with MariaDB

When you connect to a MariaDB server, you provide a username and password to gain access. MariaDB authenticates you using not only these login details, but also the hostname or IP address you are connecting from.

localhost

Often you communicate with MariaDB only from localhostthat is, when you run the mysql client on the same machine as the MariaDB server, or you access a database from an application or web script that is running on the same machine.

Not only do you need to supply a valid username and password for the database you want to gain access to, but you also need to satisfy MariaDB that you are connecting from an allowed host for that user. If you try to connect with a username and password that are not valid for the host you are connecting from, access will be denied, even if the login information works from another host.

It is also possible for two users who have the same username but are connecting from two different hosts to use separate passwords and have separate privileges. Therefore, you should try to think of a MariaDB user as a username plus a host when dealing with user permissions, rather than just the username. In other words, consider the access requirements for chris@localhost or chris@192.168.1.100, not for user chris.

MariaDB Privileges

MariaDB includes a number of individual privileges that determine the type of actions that a user can perform. For example, a user with the SELECT privilege may query a table, but the same user also requires the INSERT, DELETE, and UPDATE privileges to perform data manipulation on the same table.

A privilege can be granted to a userremember, this means a user plus a hostat the table level. Therefore, two users with access to the same database can be given permission to use only the tables they require access to.

Table 18.1 lists the privileges that can be assigned. User permissions are set using the GRANT and REVOKE commands, which are discussed later in this chapter.

Table 18.1. Privileges in MariaDB

ALTER	Allows use of ALTER TABLE.
ALTER ROUTINE	Alters or drops stored routines.
CREATE	Allows use of CREATE TABLE.
CREATE ROUTINE	Creates stored routines.
CREATE TEMPORARY TABLE	Allows use of CREATE TEMPORARY TABLE.
CREATE USER	Allows use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.
CREATE VIEW	Allows use of CREATE VIEW.
DELETE	Allows use of DELETE.
DROP	Allows use of DROP TABLE.
EXECUTE	Allows the user to run stored routines.
FILE	Allows use of SELECT... INTO OUTFILE and LOAD DATA INFILE.
INDEX	Allows use of CREATE INDEX and DROP INDEX.
INSERT	Allows use of INSERT.
LOCK TABLES	Allows use of LOCK TABLES on tables for which the user also has SELECT privileges.
PROCESS	Allows use of SHOW FULL PROCESSLIST.
RELOAD	Allows use of FLUSH.
REPLICATION	Allows the user to ask where slave or master
CLIENT	servers are.
REPLICATION SLAVE	Needed for replication slaves.
SELECT	Allows use of SELECT.
SHOW DATABASES	Allows use of SHOW DATABASES.
SHOW VIEW	Allows use of SHOW CREATE VIEW.
SHUTDOWN	Allows use of mysqladmin shutdown.
SUPER	Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached.
UPDATE	Allows use of UPDATE.
USAGE	Allows connection without any specific privileges.

The MariaDB Privilege Tables

Whether access is granted to MariaDB is determined by data in the mysql database, which contains a number of tables that control user privileges.

Superuser

Access to the mysql database is usually available only to the MariaDB root user. Only a MariaDB superuser user should have access to the privilege tables.

Data from the user table is used for authentication. The Host, User, and Password columns contain the values used to validate a connection attempt. The Password column is encrypted, so you cannot view a user's actual password by querying this table.

The db database contains the default permissions that a user has for each database. The Db, User, and Host columns are followed by a series of privilege columns, such as Select_priv and Alter_priv. Those columns contain either a Y or an N value to indicate whether that privilege has been granted.

Individual table privileges are stored in the tables_priv table, using the Db, User, Host, and Table_name columns to identify the database table and user. The Table_priv column contains the name of the privilege granted. Whereas db contains one row per user and database, there is one row in tables_priv for each table-level privilege assigned.

Changing Privileges

Although it is possible to query and update the privilege tables directly, it is easier to use the commands described in the "User Management" section of this lesson to do so.

Furthermore, changes made to the privilege tables do not take effect until you issue the FLUSH PRIVILEGES command.