Granting and restricting access in MySQL

• Viewing the current privileges
• Allowing MySQL access from other systems

   Start MySQL as the MySQl root user. Change to the mysql database, 
   which is also called the grant tables. The privileges are stored in a 
   MySQL database, whose name is mysql.

$ mysql -uroot -p 
Enter password: *******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 3.21.26-gamma-log

Type 'help' for help.

mysql> use mysql
Database changed
mysql> show TABLES;
+-----------------+
| Tables in mysql |
+-----------------+
| db              |
| func            |
| host            |
| user            |
+-----------------+
4 rows in set (0.01 sec)

mysql> 

   
The db table
   The following shows that the test database (containing the albums table)
   and any database whose name begins with test_ can be accessed and
   modified by any user. The % wildcard under host matches any host system.
   The blank user matches any user.


mysql> select * from db;
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+
| Host | Db      | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+
| %    | test    |      | Y           | Y           | Y           | Y           | Y           | Y         |
| %    | test\_% |      | Y           | Y           | Y           | Y           | Y           | Y         |
+------+---------+------+-------------+-------------+-------------+-------------+-------------+-----------+
2 rows in set (0.02 sec)

mysql> 


   
The user table
   The following shows that root on localhost (otherwise known as
   dtr.dtrbus.COM) requires a password and can do anything to any
   database. 
   
 
   The Y attribute in the user table over-rides any N attribute in the db or host tables. 
   A N attribute in the user table can be over-ridden by a Y attribute in the db or host tables.
   
   In general, only root should have Y privileges set in this user table
   since they are global. All other users should get their privileges
   from the db and hosts table.


mysql> select * from user;
+----------------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+
| Host           | User | Password         | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
+----------------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+
| localhost      | root | 5336eb751494bdb1 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         |
| localhost      |      |                  | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         |
| dtr.dtrbus.COM | root | 5336eb751494bdb1 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         |
| dtr.dtrbus.COM |      |                  | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         |
+----------------+------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+
4 rows in set (0.03 sec)

mysql> describe user;
+---------------+---------------+------+-----+---------+-------+
| Field         | Type          | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| Host          | char(60)      |      | PRI |         |       |
| User          | char(16)      |      | PRI |         |       |
| Password      | char(16)      |      |     |         |       |
| Select_priv   | enum('N','Y') |      |     | N       |       |
| Insert_priv   | enum('N','Y') |      |     | N       |       |
| Update_priv   | enum('N','Y') |      |     | N       |       |
| Delete_priv   | enum('N','Y') |      |     | N       |       |
| Create_priv   | enum('N','Y') |      |     | N       |       |
| Drop_priv     | enum('N','Y') |      |     | N       |       |
| Reload_priv   | enum('N','Y') |      |     | N       |       |
| Shutdown_priv | enum('N','Y') |      |     | N       |       |
| Process_priv  | enum('N','Y') |      |     | N       |       |
| File_priv     | enum('N','Y') |      |     | N       |       |
+---------------+---------------+------+-----+---------+-------+
13 rows in set (0.03 sec)

mysql>

   To allow MySQL access from other systems, we need to add an
   entry to the user table as described above. Note that we only
   need to supply allowed host, user name, and password since the
   remaining fields will be N (no) if we don't specify them.
   The following adds a user entry for
      hostname: %      (a wild card for any host)
      username: winpc
      password: winpass

   Notice the reload to make the change take effect.


mysql> insert into user (host,user,password)
    -> values ('%','winpc',password('winpass'));
Query OK, 1 row affected (0.19 sec)

mysql>
mysql> select * from user;
+----------------+-------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+
| Host           | User  | Password         | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv |
+----------------+-------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+
| localhost      | root  | 5336eb751494bdb1 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         |
| localhost      |       |                  | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         |
| dtr.dtrbus.COM | root  | 5336eb751494bdb1 | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         |
| dtr.dtrbus.COM |       |                  | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         |
| %              | winpc | 111aefe358501204 | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         |
+----------------+-------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+
5 rows in set (0.01 sec)

mysql> quit
Bye
$ mysqladmin -uroot -p reload
Enter password: *******
$