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)
|
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)
|