Born Privileged - MySQL

Once the users, passwords and hosts are specified, it becomes necessary to specify the privileges each user has - which is where the other fourteen columns (or "privilege fields") come in. Here's what each of those fields represents:

Select_priv - execute a SELECT query

Insert_priv - execute an INSERT query

Update_priv - execute an UPDATE query

Delete_priv - execute a DELETE query

Create_priv - CREATE databases and tables

Drop_priv - DROP databases and tables

Reload_priv - Reload/refresh the mySQL server

Shutdown_priv - Shut down a running mySQL server

Process_priv - track activity on a mySQL server

File_priv - read and write files on the server

Grant_priv - GRANT other users privileges

Index_priv - Create, edit and delete indices

Alter_priv - execute an ALTER query

It is important to note at this point that the security privileges assigned to each user in the "user" table are globally valid - they apply to each and every database on the system. The following record

+-----------------+---------------+
| Field           | Value         |
+-----------------+---------------+
| Host            | apple.pie.com  |
| User            | joe       |
| Password        | gf64us       |
| Select_priv     | Y  |
| Insert_priv     | N  |
| Update_priv     | N  |
| Delete_priv     | Y  |
| Create_priv     | N  |
| Drop_priv       | N  |
| Reload_priv     | N  |
| Shutdown_priv   | N  |
| Process_priv    | N |
| File_priv       | N  |
| Grant_priv      | N  |
| References_priv | N  |
| Index_priv      | N  |
| Alter_priv      | N  |
+-----------------+---------------+

would imply that user "joe" has the ability to DELETE records from any table in any database on the server - not a Good Thing if Joe happens to be in a bad mood. It is for this reason that most administrators (and the mySQL manual) recommends leaving all privileges in this table to "N" (the default value) for every user, and using the "host" and "db" tables to assign more focused levels of access.

Similarly, the following record would create a super-user named "god", with complete access to all mySQL privileges.

+-----------------+---------------+
| Field           | Value         |
+-----------------+---------------+
| Host            | apple.pie.com  |
| User            | god       |
| Password        | hjgj4j34      |
| Select_priv     | Y  |
| Insert_priv     | Y  |
| Update_priv     | Y  |
| Delete_priv     | Y  |
| Create_priv     | Y  |
| Drop_priv       | Y  |
| Reload_priv     | Y  |
| Shutdown_priv   | Y  |
| Process_priv    | Y |
| File_priv       | Y  |
| Grant_priv      | Y  |
| References_priv | Y  |
| Index_priv      | Y  |
| Alter_priv      | Y  |
+-----------------+---------------+

It is instructive at this point to look at the default "user" table that ships with mySQL, in order to better understand the implications of running an out-of-the-box mySQL setup.

+-----------+------+----------+----------------+
| Host      | User | Password | all_privileges |
+-----------+------+----------+----------------+
| localhost | root |          | Y              |
| %         |      |          | N              |
+-----------+------+----------+----------------+

In other words, the user connecting as "root" from "localhost" has complete access, while any other user, connecting from any other host, would not be able to perform any actions at all.{mospagebreak title=The Belly Of The Beast} The "host" and "db" tables are used together - they control which databases are available to which users, and the operations possible on those databases. Take a look at the fields in a typical "db" table:

+-----------------+---------------+------+-----+---------+-------+
| Field           | Type          | Null | Key | Default | Extra |
+-----------------+---------------+------+-----+---------+-------+
| Host            | char(60)      |      | PRI |         |       |
| Db              | char(32)      |      | PRI |         |       |
| User            | char(16)      |      | PRI |         |       |
| 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       |       |
| Grant_priv      | enum('N','Y') |      |     | N       |       |
| References_priv | enum('N','Y') |      |     | N       |       |
| Index_priv      | enum('N','Y') |      |     | N       |       |
| Alter_priv      | enum('N','Y') |      |     | N       |       |
+-----------------+---------------+------+-----+---------+-------+

Again, the first three fields are scope fields, which link a specific user and host to one or more databases. The remaining fields are used to specify the type of operations the user can perform on the named database.

A record like this would imply that the user "bill", connecting from host "cranberry.domain.com", would be able to use database "darkbeast"

+-----------------------+------+-----------+----------------+
| Host                  | User | Db        | all_privileges |
+-----------------------+------+-----------+----------------+
| cranberry.domain.com  | bill | darkbeast | Y              |
+-----------------------+------+-----------+----------------+

while this would imply that any user, connecting from any host, would have complete access to the "test" database.

+------+------+---------+----------------+
| Host | User | Db      | all_privileges |
+------+------+---------+----------------+
| %    |      | test    | Y              |
+------+------+---------+----------------+

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus