• Choose a category
• All
• Classic-Fiction

Consider the following:

• Most users need to read and write data from tables, but few users will ever need to be able to create and drop tables.

• Some users might need to read tables but might not need to update them.

• You might want to allow users to add data but not delete data.

• Some users (managers or administrators) might need rights to manipulate user accounts, but most should not.

• You might want users to access data via stored procedures but never directly.

• You might want to restrict access to some functionality based on from where the user is logging in.

These are just examples, but they help demonstrate an important point. You need to provide users with the access they need and just the access they need. This is known as access control, and managing access control requires creating and managing user accounts.

Back in Chapter 3, “Working with MariaDB,” you learned that you need to log in to MariaDB to perform any operations. When first installed, MariaDB creates a user account named root that has complete and total control over the entire MariaDB server. You might have been using the root login throughout the chapters in this book, and that is fine when experimenting with MariaDB on nonlive servers. But in the real world you’d never use root on a day-to-day basis. Instead, you’d create a series of accounts, some for administration, some for users, some for developers, and so on.

* * *

Note: Preventing Innocent Mistakes

It is important to note that access control is not just intended to keep out users with malicious intent. More often than not, data nightmares are the result of an inadvertent mistake, a mistyped MariaDB statement, being in the wrong database, or some other user error. Access control helps avoid these situations by ensuring that users are unable to execute statements they should not be executing.

* * *

* * *

Caution: Don’t Use root

The root login should be considered sacred. Use it only when absolutely needed (perhaps if you cannot get in to other administrative accounts). root should never be used in day-to-day MariaDB operations.

* * *

Managing Users

MariaDB user accounts and information are stored in a MariaDB database named mysql. You usually do not need to access the mysql database and tables directly (as you will soon see), but sometimes you might. One of those times is when you want to obtain a list of all user accounts. To do that, use the following code:

Input

USE mysql;

SELECT user FROM user;

Output

+------+

| user |

+------+

| root |

+------+

Analysis

The mysql database contains a table named user that contains all user accounts. user contains a column named user that contains the user login name. A newly installed server might have a single user listed (as seen here); established servers will likely have far more.

* * *

Tip: Test Using Multiple Clients

The easiest way to test changes made to user accounts and rights is to open multiple database clients (multiple copies of the mysql command line utility, for example), one logged in with the administrative login and the others logged in as the users being tested.

* * *

Creating User Accounts

To create a new user account, use the CREATE USER statement, as seen here:

Input

CREATE USER ben IDENTIFIED BY 'p@$$w0rd';

Analysis

CREATE USER creates a new user account. A password need not be specified at user account creation time, but this example does specify a password using IDENTIFIED BY 'p@$$w0rd'.

If you were to list the user accounts again, you’d see the new account listed in the output.

* * *

Tip: Specifying a Hashed Password

The password specified by IDENTIFIED BY is plain text that MariaDB will encrypt before saving it in the user table. To specify the password as a hashed value, use IDENTIFIED BY PASSWORD instead.

* * *

* * *

Note: Using GRANT or INSERT

The GRANT statement (which we will get to shortly) can also create user accounts, but generally CREATE USER is the cleanest and simplest syntax. In addition, it is possible