Learning MySQL and MariaDB (2015)

Part V. Administration and Beyond

In this final part, we will cover some administrative activities of MySQL and MariaDB. These are activities that are not necessarily related to the development of databases, but are still related to the management of data. Some of these are routine activities and some are only occasionally performed. And we’ll cover some aspects that go beyond MySQL and MariaDB.

First, in Chapter 13, we’ll cover the management of user accounts and their privileges. We covered this briefly at the start of this book, but in this chapter we will discuss it in more depth. We’ll go through how to be more precise about which privileges are given to each user and for which databases and tables.

In Chapter 14, we’ll discuss how to make backups of databases. This is a very important administrative duty. Related to that, we will cover the less routine administrative activity of restoring a backup. When this is needed, it’s usually critical and urgent. You’re always encouraged to complete the exercises at the end of each chapter, but because this chapter covers such an important topic, the exercises here are particularly essential.

Chapter 15 explains the administrative task of importing large amounts of data. The bulk importing of data from another database or from another format (e.g., from a spreadsheet or a text file containing comma-separated values) may not be something you will do often. However, knowing how to do it when needed can be very useful and save you plenty of time and frustration.

The book concludes with Chapter 16, which briefly covers a few APIs. These include examples for connecting and querying MySQL and MariaDB with PHP and a few other programming languages. Almost all databases are interfaced with an API, as it allows for greater control and security−and doesn’t require users to know anything about using a database.

Chapter 13. User Accounts and Privileges

We’ve addressed user accounts and privileges a few times up until this point, but in this chapter we’re going to thoroughly discuss this crucial topic. Given the importance of security in any data-related activity, some readers might feel that this topic should have been covered thoroughly at the beginning of the book, and there’s some logic to support that approach. But it’s much more interesting to work with databases first before spending a lot of time on the less exciting administrative tasks such as user privileges and security. Plus, it’s easier to understand the importance of user privileges, and to think about the various ways to set privileges, after you have a firm understanding of tables and other elements of a database. You’re now ready to consider user accounts and related topics, and will have a better appreciation of what’s covered here than you would have if we had explored this subject earlier in the book.

We’ll start by looking at the basics of creating a user account and granting privileges. Then we’ll go through the details of restricting access and granting privileges for various database components. Once you understand these ways to restrict access, we’ll look at what privileges to give some common administrative user accounts. We’ll then look at how to revoke privileges and delete user accounts, as well as how to change passwords and rename user accounts.

User Account Basics

In this book, I have used the term user account several times instead of just user. This was done to distinguish a person from the combination of a username and the location or host from which the user may access the MySQL or MariaDB server.

For instance, the root user has full access to all databases and all privileges, but only when connecting from the localhost. The root user is not allowed to access the server through a remote host, such as through the Internet. That would be a major security vulnerability. At a minimum, access and privileges are based on the combination of the user and its host, which is called the user account.

As the root user, you can create a user account with the CREATE USER statement. Here’s an example using this SQL statement to create a user account for a woman named Lena Stankoska:

CREATE USER 'lena_stankoska';

In this example, we’re just creating the user account without giving it any privileges. To see the privileges a user account has, use the SHOW GRANTS statement like this:

SHOW GRANTS FOR 'lena_stankoska';

+--------------------------------------------+

| Grants for lena_stankoska@% |

+--------------------------------------------+

| GRANT USAGE ON *.* TO 'lena_stankoska'@'%' |

+--------------------------------------------+

Notice that these results are in the form of an SQL statement. Instead of using the CREATE USER statement, you can enter a GRANT statement exactly as shown in the results. Let’s pull apart the results here, but a bit in reverse order.

The user is lena_stankoska and the host is the wildcard, %. The wildcard was used because we didn’t specify a host when we created the user. Any privileges that will be granted to this user account will be permitted from any host. This is not a good idea. You should always specify a host. For our examples, to start, we’ll use localhost. We’ll look at setting the host in the next section.

The *.* part in the results says that usage is granted for all databases and tables — the part before the period refers to databases, and the part after the period refers to tables. In order to limit usage to a specific database or table, you would have to change that part to database.table. We’ll look at that in a bit.

Once you create a user account, you would generally then give it privileges. If you want to give an existing user account all privileges to be able to use all SQL statements from the localhost, you would execute the GRANT statement like this:

GRANT ALL ON rookery.*

TO 'lena_stankoska'@'localhost';

SHOW GRANTS FOR 'lena_stankoska'@'localhost';

+---------------------------------------------------------------------+

| Grants for lena_stankoska@localhost |

+---------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'lena_stankoska'@'localhost' |

| GRANT ALL PRIVILEGES ON `rookery`.* TO 'lena_stankoska'@'localhost' |

+---------------------------------------------------------------------+

Notice that the results of the SHOW GRANTS statement for the lena_stankoska@localhost user account now shows two rows: one similar to the result shown previously, but with the host as localhost, and the new SQL statement we executed. This user account now has all of the privileges allowed on the rookery database, except the ability to give privileges to others. We’ll cover that one and the many privileges that may be given to a user account later in this chapter.

Because we didn’t specify a password for this user account, it can be accessed without a password. That makes this user account a high security risk: it can allow anyone who gets on to the server to do almost anything to the database, and it doesn’t require a password. Because we created it only to see how granting and showing privileges works, let’s remove it. We’ll create this user account again later.

User accounts are removed through the DROP USER statement. However, removing the user accounts for Lena isn’t as straightforward as you might think. When we executed the CREATE USER statement and didn’t specify a host, we created one user account — one with the wildcard for the host. When we executed the GRANT statement to give privileges to the same user, but with the host of localhost, a second user account was created. To understand this better, let’s look at what is stored in the user table in the mysql database. That’s where this user account information is stored. Execute the following SQL statement from your server:

SELECT User, Host

FROM mysql.user

WHERE User LIKE 'lena_stankoska';

+----------------+-----------+

| User | Host |

+----------------+-----------+

| lena_stankoska | % |

| lena_stankoska | localhost |

+----------------+-----------+

As you can see here, there are two user accounts, although we sought to create only one. If you had not understood before the distinction between a user and a user account, I hope you do now.

WARNING

Although you may be able to access the user account privileges directly in the mysql database, you should never use that method to make changes to user account data. Although the examples so far have been simple, there are situations in which user permissions will affect several tables in the mysql database. If you attempt to insert, update, or delete a user account in the user table using theINSERT, UPDATE, or DELETE statements instead of the appropriate user account statements described in this chapter, you may not make the changes the way you want and may orphan entries in other tables.

To eliminate both of the user accounts that we created for Lena, we will have to execute the DROP USER statement twice, like this:

DROP USER 'lena_stankoska'@'localhost';

DROP USER 'lena_stankoska'@'%';

This eliminates both user accounts for Lena. We’ll create more user accounts for her in the next sections. In doing so, though, we will look more closely at how to restrict access of user accounts, rather than give her all privileges and access from anywhere and without a password.

Restricting the Access of User Accounts

As a database administrator, you may give users full access to databases from anywhere, or you can limit them based on various aspects of the connection and the database. Put simply, you can restrict user access and privileges based on the username and host, the database components (e.g., tables) the user account may access, and the SQL statements and functions that may be used on those database components. We’ll address these restrictions in this section.

Username and Host

When you create user accounts, consider both who needs access and from where. First, let’s define who. This can represent a person or a group of people. You can give an individual a username — which might be related to their actual name, such as lena_stankoska for Lena Stankoska — or define a username to a group of people, such as sales_dept for the Sales Department. You could also create a user account based on a function or use. In that case, one person might have several user accounts.

If Lena Stankoska is a database administrator of the rookery and birdwatchers databases, she might have multiple usernames, perhaps all from the localhost, for example, lena_stankoska, for personal use; admin_backup, for when she makes backups; admin_restore, for when she restores backups; and admin_import, if she regularly imports large amounts of data.

Let’s first create the personal accounts for Lena Stankoska. We’ll create the administrative accounts later. For her personal username, lena_stankoska, let’s give her two user accounts: one from localhost and another from a remote location. We’ll give her more privileges when she’s logged into the localhost, but less when she accesses the server remotely — from her home if she has a static IP address. Let’s create for her lena_stankoska@localhost and lena_stankoska@lena_stankoska_home.

The hostname for a user account can be a name that a DNS can translate to an IP address or it can be an actual IP address. The DNS could be the server’s external DNS, which translates Internet domain names to an IP address. Or you can use the bind system and put the name in the server’s hosts file (e.g., /etc/hosts on a Linux system). If you do that, you’ll have to restart MySQL for it to take effect.

Let’s create these two personal user accounts for Lena. Enter the following SQL statements on your server:

CREATE USER 'lena_stankoska'@'localhost'

IDENTIFIED BY 'her_password_123';

GRANT USAGE ON *.* TO 'lena_stankoska'@'lena_stankoska_home'

IDENTIFIED BY 'her_password_123';

These examples used the CREATE USER and the GRANT statements to create the user accounts. If you enter GRANT and specify a username that doesn’t exist, it automatically creates the user — and remember that each combination of user and hostname is a unique user account. However, it’s recommended that you start with CREATE USER to create the user account and then grant privileges. We added the IDENTIFIED BY clauses in each of these SQL statements to set the passwords for each user account.

Let’s see how one of Lena’s user accounts looks at this point. Enter the following on your server:

SHOW GRANTS FOR 'lena_stankoska'@'localhost' \G

*************************** 1. row ***************************

Grants for admin_backup@localhost:

GRANT USAGE ON *.* TO 'lena_stankoska'@'localhost'

IDENTIFIED BY PASSWORD ' *B1A8D5415ACE5AB4BBAC120EC1D17766B8EFF1A1'

Notice that the password is encrypted in the results. There isn’t a way within MySQL to retrieve the password in plain text, to decrypt it. Also notice that the encrypted password is preceded by the PASSWORD keyword. If you don’t want to enter someone’s password with clear text as we did in the earlier commands, you could encrypt the password on a different computer with the PASSWORD() function and then copy the results to the server using the GRANT statement. You would do that like this:

SELECT PASSWORD('her_password_123');

+-------------------------------------------+

| PASSWORD('its_password_123') |

+-------------------------------------------+

| *B1A8D5415ACE5AB4BBAC120EC1D17766B8EFF1A1 |

+-------------------------------------------+

The encrypted text is identical to the one in the results of the earlier SHOW GRANTS statement. If your server is logging all transactions, you may want to encrypt passwords on your personal computer by this method and use the results for entering the passwords on your server so no one else will know the password for a user account. Starting with MySQL version 5.6, any SQL statement that contains the reserved word PASSWORD will not be logged.

At this point, Lena can log into the server with any one of these user accounts — one allows her to do so only from home, and the other four only when logging in from the server. But she can’t access any database, other than the default ones (i.e., test and information_schema) and not always those. This allows her to do anything she wants in the test database, including creating tables and selecting, updating, and deleting data. She can’t access or even see the other databases, and she can’t create another database. She is greatly limited with these user accounts. Let’s proceed to the next section to learn more about what a user account may access and then give Lena access to more than the test database.

SQL Privileges

Lena needs more than access to the databases to be able to perform her duties. We have to grant her the privileges to execute various tasks, such as reading and writing data on the rookery and birdwatchers databases. At this point, we need to give the lena_stankoska@localhost user accountthe SELECT, INSERT, and UPDATE privileges for both of our databases. To give a user account multiple privileges, list the privileges in a comma-separated list. Enter this on the server:

GRANT SELECT, INSERT, UPDATE ON rookery.*

TO 'lena_stankoska'@'localhost';

GRANT SELECT, INSERT, UPDATE ON birdwatchers.*

TO 'lena_stankoska'@'localhost';

SHOW GRANTS FOR 'lena_stankoska'@localhost \G

*************************** 1. row ***************************

Grants for lena_stankoska@localhost:

GRANT USAGE ON *.*

TO 'lena_stankoska'@'localhost'

*************************** 2. row ***************************

Grants for lena_stankoska@localhost:

GRANT SELECT, INSERT, UPDATE ON `birdwatchers`.*

TO 'lena_stankoska'@'localhost'

*************************** 3. row ***************************

Grants for lena_stankoska@localhost:

GRANT SELECT, INSERT, UPDATE ON `rookery`.*

TO 'lena_stankoska'@'localhost'

Some privileges cover more than one SQL statement. For a list of privileges, see Table 13-1.

Although we gave lena_stankoska@localhost enough privileges to manipulate data on our two databases, we didn’t give it the ability to delete data. To add privileges to a user account, you don’t have to list again all of the privileges it already has. Just execute the GRANT statement with the new privileges and the system will add them to the user account’s privileges list. Do that like so:

GRANT DELETE ON rookery.*

TO 'lena_stankoska'@'localhost';

GRANT DELETE ON birdwatchers.*

TO 'lena_stankoska'@'localhost';

SHOW GRANTS FOR 'lena_stankoska'@localhost \G

*************************** 1. row ***************************

Grants for lena_stankoska@localhost:

GRANT USAGE ON *.*

TO 'lena_stankoska'@'localhost'

*************************** 2. row ***************************

Grants for lena_stankoska@localhost:

GRANT SELECT, INSERT, UPDATE, DELETE ON `birdwatchers`.*

TO 'lena_stankoska'@'localhost'

*************************** 3. row ***************************

Grants for lena_stankoska@localhost:

GRANT SELECT, INSERT, UPDATE, DELETE ON `rookery`.*

TO 'lena_stankoska'@'localhost'

Now Lena can manipulate data in all of the basic ways on our two databases, but only from the localhost. She still can’t do anything from home. We’ll give her privileges from home later.

Table 13-1. Privileges for GRANT and REVOKE statements

Database Components and Privileges

Now we’ll turn to the parts of the database a user account can access. A user account can be given access to all of the databases on a server, or limited to specific databases, specific tables, and even specific columns. Let’s first see how to limit user accounts to specific databases, and then how to limit user accounts to tables and columns.

NOTE

We’ve given Lena more restrictions when she’s at home than when she’s at work. Of course, if she really wants access to more information at home, she can first log into the server at the operating system level using ssh and then log into MySQL from there using her lena_stankoska@localhost user account. This may be fine, because we can more easily control security at the operating system level, and we’re assuring that sensitive data isn’t being passed unencrypted through the Internet by adding extra restrictions to the home account. But on the operating system level, if you want, you can restrict use of ssh to prevent Lena from getting around security.

Restricting to specific databases

In order to limit the lena_stankoska@lena_stankoska_home user account to the rookery database, we would have to do something like this:

GRANT USAGE ON rookery.*

TO 'lena_stankoska'@'lena_stankoska_home'

IDENTIFIED BY 'her_password_123';

SHOW GRANTS FOR 'lena_stankoska'@'lena_stankoska_home' \G

*************************** 1. row ***************************

Grants for lena_stankoska@lena_stankoska_home:

GRANT USAGE ON *.* TO 'lena_stankoska'@'lena_stankoska_home'

IDENTIFIED BY PASSWORD '*B1A8D5415ACE5AB4BBAC120EC1D17766B8EFF1A1'

Here we’re limiting this user account’s access on the server to the rookery database. However, we can see from the results of the SHOW GRANTS statement that she still has global usage. If she were to access the server from her home to get a list of databases, this is what she’d see:

mysql --user lena_stankoska --password='her_password_123' \

--host rookery.eu --execute='SHOW DATABASES'

+--------------------+

| Database |

+--------------------+

| information_schema |

| test |

+--------------------+

She still can’t see the rookery database. This is because she can’t do anything on that database. She can’t even execute a SHOW TABLES statement or a SELECT statement for that database. To do that, we need to give her privileges other than hollow access to the rookery database. Let’s start by giving her the SELECT privilege for the rookery database. We’ll do that by executing the following:

GRANT SELECT ON rookery.*

TO 'lena_stankoska'@'lena_stankoska_home';

SHOW GRANTS FOR 'lena_stankoska'@'lena_stankoska_home';

+---------------------------------------------------------------------------+

| Grants for lena_stankoska@lena_stankoska_home |

+---------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'lena_stankoska'@'lena_stankoska_home' |

| IDENTIFIED BY PASSWORD '...' |

| GRANT SELECT ON `rookery`.* TO 'lena_stankoska'@'lena_stankoska_home' |

+---------------------------------------------------------------------------+

You can’t specify just the database name in the GRANT statement; you have to specify a table too. That’s why we added .* to refer to all tables in the rookery database.

In the results, notice that there is still the row granting global usage for this user account. Following that is an entry related to the rookery database. To make the results fit on the page here, I replaced the password with an ellipsis. Lena can now access the rookery database from her home, although she can only select data. Here’s what she sees from her home when she executes SHOW DATABASES and a SELECT statement to get a list of Avocet birds from the command line:

mysql --user lena_stankoska --password='her_password_123' --host rookery.eu \

--execute="SHOW DATABASES; \

SELECT common_name AS 'Avocets'

FROM rookery.birds \

WHERE common_name LIKE '%Avocet%';"

+--------------------+

| Database |

+--------------------+

| information_schema |

| rookery |

| test |

+--------------------+

+---------------------+

| Avocets |

+---------------------+

| Pied Avocet |

| Red-necked Avocet |

| Andean Avocet |

| American Avocet |

| Mountain Avocetbill |

+---------------------+

Restricting to specific tables

At this point, Lena has sufficient access to the two databases when at her office. However, although she can select data on the rookery database from home, she can’t access the birdwatchers databases from home. Let’s give her the SELECT privilege for that database, but only for certain tables.

If we want to give Lena access only to the bird_sightings table in the birdwatchers database from home, we would enter the following:

GRANT SELECT ON birdwatchers.bird_sightings

TO 'lena_stankoska'@'lena_stankoska_home';

SHOW GRANTS FOR 'lena_stankoska'@'lena_stankoska_home';

+---------------------------------------------------------------------------+

| Grants for lena_stankoska@lena_stankoska_home |

+---------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'lena_stankoska'@'lena_stankoska_home' |

| IDENTIFIED BY PASSWORD '...' |

| GRANT SELECT ON `rookery`.* TO 'lena_stankoska'@'lena_stankoska_home' |

| GRANT SELECT ON `birdwatchers`.`bird_sightings` |

| TO 'lena_stankoska'@'lena_stankoska_home' |

+---------------------------------------------------------------------------+

Now Lena can see only that one table in the birdwatchers database. Here is what happens if she executes the following from her home computer:

mysql --user lena_stankoska --password='her_password_123' --host rookery.eu \

--execute="SHOW TABLES FROM birdwatchers;"

+------------------------+

| Tables_in_birdwatchers |

+------------------------+

| bird_sightings |

+------------------------+

To give her access to more tables in the birdwatchers database, we could execute a GRANT statement for each table. That can be tedious with a database that has many tables, to give her access to many of them but not all. But there’s no simple way around it. I have requested while writing this chapter that a feature be added to MariaDB to specify multiple tables in a single GRANT statement. So maybe one day there will be an easy way to do it with MariaDB. For now, you can either manually enter the GRANT statement many times, or you can create a short script to do it.

For example, suppose that we want to give Lena access to all of the tables in the birdwatchers database, except ones with personal and sensitive information. The tables to exclude would be the humans table and the two tables containing information about children, the birder_families andbirding_events_children tables. Here’s how such a shell script might look:

#!/bin/sh

mysql_connect="mysql --user root -pmy_pwd"

results=`$mysql_connect --skip-column-names \

--execute 'SHOW TABLES FROM birdwatchers;'`

items=$(echo $results | tr " " "\n")

for item in $items

do

if [ $item = 'humans' ] ||

[ $item = 'birder_families' ] ||

[ $item = 'birding_events_children' ]

then

continue

fi

`$mysql_connect --execute "GRANT SELECT ON birdwatchers.$item \

TO 'lena_stankoska'@'lena_stankoska_home'"`

done

exit

This simple shell script gets a list of tables using the SHOW TABLES statement. The script then goes through the list to execute a GRANT statement for each table name in the results, but skipping the three sensitive tables.

At this point, Lena can do plenty from her office and check on things from her home. If she needs to do more than this, it will probably be because she is performing an administrative task like making a backup or importing large amounts of data. When she does those tasks, she’ll use one of the three administrative user accounts we created for her. Let’s give those three accounts the necessary privileges so that Lena can perform the tasks required of her.

Restricting to specific columns

To give a user account access only to specific columns, issue a GRANT statement listing all of the columns permitted for the table within parentheses, in a comma-separated list after the privilege for which they apply. This will make more sense when you see an example. If you’re granting many privileges, this can be an excessively long SQL statement.

In the previous section, as a security precaution, we didn’t give Lena access to the humans table in the birdwatchers database from home. Suppose we changed our mind about that. Suppose we want her to have access to most of the humans table when she works at home, but not to the contact information of our clients (e.g., email addresses). Looking at the columns in the humans table, we decide she needs access to the human_id column to be able to join to other tables, and the formal_title, name_first, and name_last columns, as well as membership_type. The other columns either contain sensitive information or are unnecessary for her duties.

Based on the list of columns we want to permit Lena to access from home, let’s enter the following:

GRANT SELECT (human_id, formal_title, name_first,

name_last, membership_type)

ON birdwatchers.humans

TO 'lena_stankoska'@'lena_stankoska_home';

Now Lena can access the humans table from home to get the names of members, as well as the type of membership each has.

Administrative User Accounts

Earlier, I mentioned that we need to create three administrative accounts for Lena to use in performing her duties as a database administrator from the localhost: admin_backup, admin_restore, and admin_import. These are common administrative user accounts that you may need to create and use. You’ll use them in examples and exercises in Chapter 14 (which covers backing up and restoring), and Chapter 15 (importing data). In this section, we’ll create these administrative user accounts and look at the privileges needed for them, as well as another one for granting privileges to other user accounts.

User Account for Making Backups

The admin_backup user account will be used with the mysqldump utility to make back-ups of the rookery and birdwatchers databases. This is covered in Chapter 14. Just a few privileges are needed to accomplish these tasks:

§ At a minimum, it will need the SELECT privilege to read our two databases. You should limit an administrative account to the databases it needs to backup. In particular, you should not let it have SELECT privileges for the mysql database, because that contains user passwords.

§ To lock the tables when making a backup, the LOCK TABLES privilege is required.

§ If a database contains views and triggers, which we didn’t cover in this book, the user account will need the SHOW VIEW and TRIGGER privileges, respectively.

Based on those considerations, let’s create the admin_backup@localhost user account and give it the SELECT and LOCK TABLES privileges, but only for the rookery and birdwatchers databases. Do that by executing the following SQL statement:

CREATE USER 'admin_backup'@'localhost'

IDENTIFIED BY 'its_password_123';

GRANT SELECT, LOCK TABLES

ON rookery.*

TO 'admin_backup'@'localhost';

GRANT SELECT, LOCK TABLES

ON birdwatchers.*

TO 'admin_backup'@'localhost';

This allows Lena to use this admin_restore account to make backups of our databases. We created another account for restoring data, so let’s give that account the privileges it needs.

User Account for Restoring Backups

Although you could create one administrative user account for both making backups and restoring them, you might want to use separate user accounts for those tasks. The main reason is that the task of making backups is usually one handled by scripts that run automatically. But the task of restoring data is generally run manually and can overwrite or destroy data on a live server. You might not want the user account with those privileges to be the same one for which you use in a script containing its password. For our examples in this chapter, let’s give theadmin_restore@localhost user account the privileges needed for restoring data to our databases:

§ At a minimum, a user account for restoring a dump file needs the INSERT privilege to insert data into tables.

§ It should also have the LOCK TABLES privilege to lock the tables while inserting data.

§ It will need the CREATE privilege to create tables and INDEX to create indexes.

§ Because a dump file can include SQL statements to alter tables to set the collation, the ALTER privilege may be needed.

§ Depending on the method Lena uses to restore tables, she might also want to restore them to temporary tables. For that, she will need the CREATE TEMPORARY TABLES privilege. Temporary tables are dropped when the client connection is closed.

§ If a database has views and triggers, the CREATE VIEW and TRIGGER privileges are required.

For our database usage, we won’t need CREATE VIEW or TRIGGER, but we will need the other privileges. Create the admin_restore@localhost user account and give it the necessary privileges by entering the following on your server:

CREATE USER 'admin_restore'@'localhost'

IDENTIFIED BY 'different_pwd_456';

GRANT INSERT, LOCK TABLES, CREATE,

CREATE TEMPORARY TABLES, INDEX, ALTER

ON rookery.*

TO 'admin_restore'@'localhost';

GRANT INSERT, LOCK TABLES, CREATE,

CREATE TEMPORARY TABLES, INDEX, ALTER

ON birdwatchers.*

TO 'admin_restore'@'localhost';

With those privileges, Lena should have what she needs to restore any of the data in the rookery and birdwatchers databases.

User Account for Bulk Importing

The last administrative user we need to create for Lena is admin_import. She’ll use this user account to import large data text files into our databases. This is covered in Chapter 15. For this method of importing data, she’ll use the LOAD DATA INFILE statement. That requires just the FILEprivilege.

WARNING

The FILE privilege is a security risk because it has the ability to read data from any file on the server to which MySQL has rights. This is why it is especially important that this privilege be given only to a user account designated for importing files. The password for that user account should be given only to someone who is trusted. You can restrict the directory from which files may be loaded with the secure_file_priv variable. That will minimize the security risk to the filesystem. You can also revoke this privilege when it’s not in use and grant it again when needed to minimize risk to the databases.

The FILE privilege cannot be given for specific databases or components. It’s a global privilege. If we give it to the admin_import@localhost user account, it can import data into any database — and it can export data from any database, including the mysql database. So be careful who gets this privilege and never allow it with a remote host. Still, create admin_import@localhost and give it this privilege by entering the following on the server:

CREATE USER 'admin_import'@'localhost'

IDENTIFIED BY 'another_pwd_789';

GRANT FILE ON *.*

TO 'admin_import'@'localhost';

We have created all of Lena’s administrative user accounts and set each one with the necessary privileges (no more and no less) for her to perform her duties related to our databases. Let’s create one more administrative user account, though, that may be of use to you.

User Account to Grant Privileges

Another user account that you might need is one for creating other users. You could use root for that, but to continue the policy of using limited administrative user accounts for separate functions, we should create a separate user account for user and privilege maintenance. Besides, this task might be given to someone who we don’t want to have complete control over our database system.

To create a user account with the ability to create other user accounts and grant those other user accounts privileges, the GRANT statement has to include the GRANT OPTION clause. This clause allows the user to grant the same privileges it has to other users — but only the precise privileges granted in this GRANT statement. If we limit the privileges in the GRANT statement to our two databases, the user account cannot grant privileges to other databases. For instance, execute the following on your server to create this user account and give it the GRANT OPTION for our two databases:

GRANT ALL PRIVILEGES ON rookery.*

TO 'admin_granter'@'localhost'

IDENTIFIED BY 'avocet_123'

WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON birdwatchers.*

TO 'admin_granter'@'localhost'

IDENTIFIED BY 'avocet_123'

WITH GRANT OPTION;

This creates the admin_granter@localhost user account, which has the privilege of granting privileges on the rookery and birdwatchers databases to other user accounts.

This user account’s privileges are still fairly limited if we want it to be used to manage other user accounts. Suppose we want this user account to create and drop user accounts for our databases. To do that, we need to grant the CREATE USER privilege globally to admin_granter@localhost. So that this user account can execute the SHOW GRANTS statement, it will also need the SELECT privilege on the mysql database. This is another security risk, so be careful who gets this privilege. Enter these two SQL statements to give this user account these two additional privileges:

GRANT CREATE USER ON *.*

TO 'admin_granter'@'localhost';

GRANT SELECT ON mysql.*

TO 'admin_granter'@'localhost';

Now the admin_granter@localhost user account has the privileges to perform its tasks of managing user accounts on our databases. Let’s test it by entering the first line in the following example from the command line to log into MySQL, then the following SQL statements from within themysql client:

mysql --user admin_granter --password=avocet_123

SELECT CURRENT_USER() AS 'User Account';

+-------------------------+

| User Account |

+-------------------------+

| admin_granter@localhost |

+-------------------------+

CREATE USER 'bird_tester'@'localhost';

GRANT SELECT ON birdwatchers.*

TO 'bird_tester'@'localhost';

SHOW GRANTS FOR 'bird_tester'@'localhost';

+---------------------------------------------------------------+

| Grants for bird_tester@localhost |

+---------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'bird_tester'@'localhost' |

| GRANT SELECT ON `birdwatchers`.* TO 'bird_tester'@'localhost' |

+---------------------------------------------------------------+

DROP USER 'bird_tester'@'localhost';

That worked well. We logged in with the admin_granter@localhost user account and used the CURRENT_USER() to confirm the user account. Then we created a user with the SELECT privilege on the birdwatchers database. We were able to execute SHOW GRANTS to verify this and then successfullyissued DROP USER to delete the user account. We can give this user account to someone on our staff whose responsibility will be to manage user accounts for our databases.

Revoking Privileges

So far in this chapter we have been giving privileges to user accounts. But there may also be times when you want to revoke a privilege that you gave to a user account. Maybe you gave a privilege by mistake, or you’ve changed your mind about which tables you want the user account to have access, or changed your policy about which tables you want to protect.

The REVOKE statement revokes all or certain privileges that were granted to a user account. There are two forms of syntax to do this: one to revoke all privileges and another for specific privileges. Let’s look at examples for both syntaxes.

Suppose we have a user, Michael Stone, who is taking a leave of absence for a few months, and there is no chance he will access the database while he’s gone. We could delete his user account, but instead we decide to revoke his user account privileges. We’ll add them back when he returns. To do this, we would enter something like this:

REVOKE ALL PRIVILEGES

ON rookery.*

FROM 'michael_stone'@'localhost';

REVOKE ALL PRIVILEGES

ON birdwatchers.*

FROM 'michael_stone'@'localhost';

The syntax is similar to the GRANT statement that grants all privileges. The main difference is that instead of an ON clause, there’s a FROM to revoke privileges from a user account. Although Michael may have had privileges for only certain tables in the two databases, this removes them all. We don’t have to remove the specific privileges with multiple SQL statements for each table. To give privileges again to the user account, though, we may have to use the GRANT statement many times as we would for a new user account.

The second syntax can be used to revoke only some privileges. The specific privileges have to be given in a comma-separated list after the keyword REVOKE. The privileges for REVOKE are the same as for GRANT (see Table 13-1). You can specify one table per REVOKE statement, or revoke privileges on all tables of a database by putting an asterisk in as the table name. To revoke privileges for specific columns, list them within parentheses in a comma-separated list — the same as with the GRANT statement. Let’s look at an example of this second syntax.

To keep security tight, suppose we have a policy of removing any privileges not needed by user accounts. When we granted privileges to the admin_restore@localhost user account, we included the ALTER privilege. Suppose we have found that ALTER is never needed. We can revoke it like so:

REVOKE ALTER

ON rookery.*

FROM 'admin_restore'@'localhost';

REVOKE ALTER

ON birdwatchers.*

FROM 'admin_restore'@'localhost';

Deleting a User Account

The DROP USER statement deletes a user account. Let’s look at an example of how this is done. Suppose Michael Stone tells us that he won’t return from his leave of absence because he has found a new job. We would execute the following to delete his user account:

DROP USER 'michael_stone'@'localhost';

TIP

If you use an older version of MySQL (i.e., before 5.0.2), you must first revoke all privileges before you drop the user account. This requires executing REVOKE ALL ON *.* FROM 'user'@'host' and then DROP USER 'user'@'host'.

Some users, like Lena, may have more than one personal user account. So we should check to see whether there are any other accounts associated with Michael Stone. Unfortunately, there isn’t a SHOW USERS statement. Instead, we’ll have to check the user table in the mysql database like this:

SELECT User, Host

FROM mysql.user

WHERE User LIKE '%michael%'

OR User LIKE '%stone%';

+---------------------+-------------+

| User | Host |

+---------------------+-------------+

| mstone | mstone_home |

| michael_zabbalaoui | localhost |

+---------------------+-------------+

It seems that Michael Stone has another user account related to his home IP address. After confirming that it’s his user account, we’ll drop it like so:

DROP USER 'mstone'@'mstone_home';

When you drop a user account, if the user account is logged in and has active sessions running, it won’t stop the sessions. The active sessions will continue for the user account until the user exits or they’ve been idle so long that they end. However, you can shut down a user’s activities sooner. First, you will need to get the process identifier for the session. You can do this be executing the following:

SHOW PROCESSLIST;

...

*************************** 4. row ***************************

Id: 11482

User: mstone

Host: mstone_home

db: NULL

Command: Query

Time: 78

State: init

Info: SELECT * FROM `birds`

Progress: 0.000

These are trimmed results, but we can see that mstone@mstone_home has an active connection even though we’ve dropped this user account. We’re concerned that he’s selecting data from our databases from his home, even though he no longer works for us and isn’t intending on returning. We can kill this process by executing the following:

KILL 11482;

Notice that we used the process identification number from the results of the SHOW PROCESSLIST statement. The SHOW PROCESSLIST statement requires the PROCESS privilege, and the KILL statement requires the user account to have the SUPER privilege to execute it. Now that that session has been killed and his user accounts have been dropped, he can no longer access our databases. For good measure, we should remove his account from our server at the operating system level, a topic beyond the scope of this book.

Changing Passwords and Names

For better security, it’s a good idea to change the passwords for user accounts regularly, especially for accounts with administrative privileges. How to change passwords is covered in the next subsection. A user may ask, or you may want to rename a user account. This isn’t done as often, although it could be another security precaution. However, when you change a name or a password, you should be mindful of whether the user account name and password are incorporated into any scripts, in particular ones that run automatically to make backups of the databases. You’ll have to change them in those scripts, as well.

Setting a User Account Password

In the examples throughout this chapter, we have created user accounts without passwords or given them passwords when creating the user accounts. You will occasionally need to change the password for a user account, and actually should do so regularly for good security. To do this, use theSET PASSWORD statement with the PASSWORD() function to encrypt the password given.

TIP

As of version 5.6, you can force a user to change their password by expiring it. For this, you would use the ALTER USER statement with the PASSWORD EXPIRE clause like this:

ALTER USER 'admin_granter'@'localhost' PASSWORD EXPIRE;

The next time the user tries to log in or execute an SQL statement, he will receive an error message instructing him to change his password. He’ll have to use the SET PASSWORD statement to do that, before any other SQL statements can be executed.

Let’s change the password for the admin_granter@localhost user account:

SET PASSWORD FOR 'admin_granter'@'localhost' = PASSWORD('some_pwd_123');

That’s not a very good password. Let’s change the password to something more complicated, such as P1ed_Avoce7-79873. For an extra security measure, we’ll use our personal computer to encrypt that password before logging onto the server to set it in MySQL. From a local computer, we’ll execute the following from the command line, assuming MySQL is running on it:

mysql -p --skip-column-names --silent \

--execute="SELECT PASSWORD('P1ed_Avoce7-79873')"

*D47F09D44BA0456F55A2F14DBD22C04821BCC07B

The result returned by the statement is the encrypted password. We’ll copy that, log into the server, and use it to change the password for admin_granter@localhost, like so:

SET PASSWORD FOR 'admin_granter'@'localhost' =

'*D47F09D44BA0456F55A2F14DBD22C04821BCC07B';

This will immediately update the privileges cache for the new password. Try that on your server and then see whether you can log in with the P1ed_Avoce7-79873 password.

TIP

If you forget the root password, there’s an easy way to reset it. First, create a simple text file with this text, each SQL statement on one line:

UPDATE mysql.user SET Password=PASSWORD('new_pwd') WHERE User='root';

FLUSH PRIVILEGES;

Name this file something like rt-reset.sql and put it in a protected directory. Then start MySQL from the command line using the --init-file option like so:

mysqld_safe --init-file=/root/rt-reset.sql &

Once it’s started, log into MySQL to confirm the password has changed. You can change it again, if you want. Then delete the rt-reset.sql file, and if you want, restart MySQL without the --init-file option.

Renaming a User Account

A username can be changed with the RENAME USER statement. This SQL statement can change the username and the host for the user account. The user account that you use to rename another user account needs to have the CREATE USER privilege, as well as the UPDATE privilege for the mysqldatabase.

In order to see how the RENAME USER statement works, let’s rename the lena_stankoska@lena_stankoska_home user account to lena@stankoskahouse.com, assuming she is the owner of that domain and will access our databases from it. Do that by entering the following:

RENAME USER 'lena_stankoska'@'lena_stankoska_home'

TO 'lena'@'stankoskahouse.com';

When you do this, all of the privileges related to lena_stankoska@lena_stankoska_home will be changed for the new username and host. Let’s check that by executing the following:

SHOW GRANTS FOR 'lena'@'stankoskahouse.com';

+------------------------------------------------------------------------------+

| Grants for lena@stankoskahouse.com |

+------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'lena'@'...' IDENTIFIED BY PASSWORD '...' |

| GRANT SELECT ON `rookery`.* TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`eastern_birders_spottings` TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`membership_prospects` TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`survey_answers` TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`surveys` TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`survey_questions` TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`eastern_birders` TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`prospects` TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`prize_winners` TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`possible_duplicate_email` TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`birdwatcher_prospects_import` TO 'lena'@'...'|

| GRANT SELECT (membership_type, human_id, name_last, formal_title, name_first)|

| ON `birdwatchers`.`humans` TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`bird_identification_tests` TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`birdwatcher_prospects` TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`bird_sightings` TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`birding_events` TO 'lena'@'...' |

| GRANT SELECT ON `birdwatchers`.`random_numbers` TO 'lena'@'...' |

+------------------------------------------------------------------------------+

This user account has many entries in the grants tables. This is because we gave it some privileges based on the tables and one based on columns, in addition to privileges at the database level. What’s important here is that all of these privileges have been changed for the user account when we renamed it and changed the host for it.

User Roles

Creating multiple user accounts for one person is a bit tiresome. Imagine if you were the administrator for an organization with many users similar to Lena Stankoska. You would have to create a few user accounts for each of them. If a user needed certain privileges for a short period of time, perhaps covering for someone on vacation, you would have to grant them extra privileges and later revoke the privileges. It can be plenty of work to manage user accounts like these, leading eventually to sloppy security policies (e.g., granting too many privileges) and ineffective controls (e.g., poor monitoring of user accounts). There’s a better way to do this.

An alternative method, called user roles, was introduced in version 10.0.5 of MariaDB. It’s not available in MySQL. User roles allow you to a create a higher-level concept, a role, and grant it to specific user accounts. The user accounts would have their normal privileges for daily use, but when they need to perform an unusual task requiring special privileges, they can temporarily assume the role you’ve created for them. When they’re done, they can unassume the role. It’s very convenient. Let’s look at an example of how you would do this.

Earlier, we created for Lena a user account called admin_import with the FILE privilege for her to be able to execute the LOAD DATA INFILE statement. She’ll use this to import data from text files into our databases. This SQL statement and the process involved is covered in Chapter 15. Suppose there are two other users — Max Mether and Ulf Sandberg — who occasionally need to do this task. Rather than create extra user accounts for Max and Ulf, in addition to Lena, we could give Max and Ulf the password for admin_import. But that would be an unprofessional security method. Instead, we’ll use the CREATE ROLE statement to create a role that we’ll name, admin_import_role and then grant that role to Max and Ulf.

Enter the following if you have MariaDB installed on your server:

CREATE ROLE 'admin_import_role';

GRANT FILE ON *.*

TO 'admin_import_role'@localhost;

The first SQL statement creates the role. The next uses the GRANT statement to grant the FILE privilege that this role will need to import files into the databases. Now let’s grant this role to Max and Ulf — assuming they already have user accounts. We would enter this on the MariaDB server:

GRANT 'admin_import_role' TO 'max'@localhost;

GRANT 'admin_import_role' TO 'ulf'@localhost;

Now Max and Ulf can assume the role of admin_import_role when they need it. Max, for instance, would enter the following while he’s logged into MariaDB to do this:

SET ROLE 'admin_import_role';

LOAD DATA INFILE

...

SET ROLE NONE;

As you can see here, Max set his role to admin_import_role and then executed the LOAD DATA INFILE statement — I removed the details of that SQL statement and any others he might execute so that we can focus just on the user role. Then Max set his role to NONE to unassume the role.

NOTE

One drawback with roles is that they may be used only for the current session. This makes it difficult to use with an external utility such as mysqldump. If you run the mysql client from the command line to set the role for your user account and then exit mysql or open a different terminal to execute the mysqldump, the dump would be in a new client session and wouldn’t have the assumed role. So you wouldn’t have the privileges you need.

User roles work well and are much easier than creating many user accounts and setting passwords and privileges for each. They’re ideal for granting someone a role temporarily. They make the management of user accounts and privileges easier for you as an administrator. For users, they will need to enter only one username and password for all of their activities. They will need only to assume a role when necessary. Of course, you will have to rely on each user to assume the role only when necessary, and to reset the role to NONE afterward.

Summary

When you first start as a database administrator, you may have a tendency to create a minimal number of user accounts — you may even try to use only the root user account. However, you should learn not to use root and to instead use various user accounts. You should also learn to give each person at least one personal user account — try not to allow sharing of user accounts, if practical. Additionally, learn to give access only to databases and tables that are needed by each user account and only the privileges needed. This may be tedious, but it’s a good security practice — not just to protect sensitive data, but to protect data from being lost and schema being changed or deleted inadvertently.

There are several options related to user accounts and security that we did not discuss. Some options limit the number of connections at a time or per hour for a user account. There are several functions for encrypting and decrypting strings that may be used for passwords. You probably won’t need these often, especially not as a newcomer to MySQL and MariaDB. However, you can find more information on them in my book, MySQL in a Nutshell, or on the MySQL Resources site.

Exercises

Although you can easily refer back to this chapter for the syntax for using CREATE USER, GRANT, REVOKE, and DROP USER, you should try to learn them well without having to do so every time. The SHOW GRANTS statement can help you to remember the syntax. Still, if you know these SQL statements well, you will be more likely to tweak user account privileges. Otherwise, you might resort to using the same user accounts for everyone in your database department and giving each user account all privileges. The exercises here are therefore intended to make you more familiar and comfortable with these SQL statements. However, you will need to discipline yourself to always maintain good policies about managing user accounts and privileges.

1. Log onto your server and use the CREATE USER statement to create an administrative user account with the username admin_boss and the host localhost.
Then use the GRANT statement to give this account ALL privileges on the rookery and birdwatchers databases, and the SUPER privilege to be able to change server settings. Also give the account the GRANT OPTION rights, covered in User Account to Grant Privileges. You may have to use theGRANT statement more than once. Be sure to use the IDENTIFIED BY clause at least once to set the password for the user account.
When you’ve finished creating this user account, exit MySQL and try to log in again with the admin_boss user account to be sure the password was entered correctly. Try using this user account instead of root for now on.

2. While logged into the server as admin_boss, use the GRANT statement to create a user named sakari for the localhost. Assign the user account only the SELECT, INSERT, and UPDATE privileges on the rookery and birdwatchers databases. Be sure to give the user account a password. Do all of this in one GRANT statement. When you’re finished, exit MySQL.
Log into MySQL with the sakari@localhost user account you created. Execute the SHOW DATABASES statement to make sure you see only the two default databases and our two databases. Execute a SELECT to get a list of rows from the humans table in the birdwatchers database. Use theINSERT statement to insert one row with minimal data. Then use the UPDATE statement to change the data in at least one column for the row you added. You should be able to do all of this. If you can’t, log in as admin_boss and use SHOW GRANTS to see how the permissions look forsakari@localhost. Fix whatever is wrong or missing and test the user account again.
Now try to delete the row you added with DELETE, while logged in with the sakari@localhost user account — not admin_boss. You shouldn’t be able to do that with this user account.

3. While logged into the server as admin_boss, use the REVOKE statement to revoke the INSERT and UPDATE privileges from the sakari@localhost user account you created in the second exercise. When finished, exit MySQL.
Log into MySQL with the sakari@localhost user account. Try to use the INSERT statement to insert another row in the humans table. You shouldn’t be able to do this. If sakari still has the user privilege, log back into MySQL with admin_boss and determine what you did wrong when you executed the REVOKE statement and fix it. Then try again to insert a row using sakari.

4. Log into the server with admin_boss and change the password for the sakari@localhost user account (this was covered in Changing Passwords and Names). When finished, log out of MySQL.
Log in with sakari, using the new password. Then press the up arrow key on your keyboard a few times. Check whether you can you see the sakari@localhost password in one of the entries. If so, this means that other users may also be able to see the password. Exit MySQL when finished checking.
From the command line using the mysql client on your personal computer — preferably not on the server — execute the SET statement, using the PASSWORD() function to get an encrypted password for sakari@localhost. Set a different password. For an example of how to do this, refer toChanging Passwords and Names.
Log into the server with admin_boss and change the password for sakari@localhost using the encrypted password without the PASSWORD() function and plain text this time. Then log out and back in as sakari with the new password. Press the up arrow a few times to see that it shows the new password encrypted and not in plain text this time.

5. Log into the server with admin_boss and use the DROP USER statement to drop the sakari@localhost user account. Then log out and try logging in as sakari. You shouldn’t be able to do that.