Using Wildcards

You can grant privileges to usernames that match wildcard criteria by using the % and _ wildcard characters in the username.

You might actually want to create a user that can access the database from any remote host using the same username and password without needing to create a separate username for each possible host. Using % as the host part of the username accomplishes this, as shown in the following example:

MariaDB> CREATE USER 'chris@%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)

MariaDB> GRANT SELECT, INSERT
 -> ON mysql10.products
 -> TO 'chris@%';
Query OK, 0 rows affected (0.00 sec)

Wildcard Usernames

The username in a CREATE USER, GRANT, or REVOKE command must be enclosed in quotes if it contains a wildcard character. Otherwise, MariaDB gives an error.


If you want to grant access only to a user from a particular range of IP addresses, you could use a statement like the following:

MariaDB> CREATE USER 'localuser@192.168.1.1_'
 -> IDENTIFIED BY 'localpass';
Query OK, 0 rows affected (0.00 sec)

In this case, the user would be authenticated only if connecting from an IP address in the range 192.168.1.10 to 192.168.1.19.

Viewing a User's Privileges

You can see what privileges have been granted to a user with the SHOW GRANTS command. The output from the command is a list of GRANT statements that can be used to re-create the user with the same set of privileges.

The following example shows the privileges for chris@localhost that were set up throughout this lesson.

MariaDB> SHOW GRANTS FOR chris@localhost;
+-------------------------------------------------------------+
| Grants for chris@localhost___ |
+-------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'chris'@'localhost' IDENTIFIED |
| BY PASSWORD '*D8DECEC305209EEFEC43008E1D420E1AA06B19E0' |
| GRANT SELECT ON `mysql10`.`products` TO 'chris'@'localhost' |
--------------------------------------------------------------+
2 rows in set (0.04 sec)

Notice that this output does not contain each GRANT statement issued for the user, and does not contain any REVOKE statements. After revoking INSERT, the user has only the SELECT privilege.

Deleting a User

To completely remove a user, in MariaDB 5.0.2 and above you can use the DROP USER command, as shown:

MariaDB> DROP USER chris@localhost;
Query OK, 0 rows affected (0.00 sec)

In earlier versions of MariaDB, you must revoke the user's privileges first and then manually delete the appropriate record from the mysql.user table. The following example shows the steps required to reproduce the previous DROP USER command:

MariaDB> REVOKE ALL PRIVILEGES
 -> ON *.*
 -> FROM chris@localhost;
Query OK, 0 rows affected (0.01 sec)

MariaDB> DELETE FROM mysql.user
 -> WHERE User = 'chris'
 -> AND Host = 'localhost';
Query OK, 1 row affected (0.00 sec)

MariaDB> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

Dropping Users

To use the DROP USER command, you must have the CREATE USER privilege if you are not a MariaDB superuser. To delete the user using the manual method, however, you must have the DELETE privilege for the mysql.user table.


Previous Page Next Page