Access Granted - Born Privileged
(Page 4 of 7 )
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 |
+------+------+---------+----------------+
Next: The Perfect Host >>
More MySQL Articles
More By icarus, (c) Melonfire
