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