MySQL security works by limiting both the users who have access to a database ''and'' what they are allowed to do once that have access. This requires careful consideration of issues such as who is allowed to read from or write to particular database tables and which users have permission to delete tables or use other MySQL features.
+
MySQL security works by limiting both the users who have access to a database ''and'' what they are allowed to do once they have access. This requires careful consideration of issues such as who is allowed to read from or write to particular database tables and which users have permission to delete tables or use other MySQL features.
−
== Getting Information About Users ==
+
== Getting Information about Users ==
−
The first step securing a database is to find out which users already have access. This information is stored, not suprisingly, in a MySQL database called ''mysql''.
+
The first step securing a database is to find out which users already have access. This information is stored, not surprisingly, in a MySQL database called ''mysql''.
The mysql database contains a table called ''user'' which in turn contains a number of columns including the user login name and the users various privileges and connection rights. To obtain a list of users run the following command:
The mysql database contains a table called ''user'' which in turn contains a number of columns including the user login name and the users various privileges and connection rights. To obtain a list of users run the following command:
As we can see, the password is not stored in plain text in the user table and has instead been encrypted by MySQL so that it cannot be obtained simply by performing a SELECT query on the table.
As we can see, the password is not stored in plain text in the user table and has instead been encrypted by MySQL so that it cannot be obtained simply by performing a SELECT query on the table.
−
You may have noted that we specified that johnB could only connect from 'localhost', in other words the same system on which the MySQL server is running. This means that if johnB tries to connect to the MySQL server from a client running on a remote system, the connection will fail. In order to create an account which can connect from a particular host, simply specify the host name or IP address in place of the ''localhost'' in the above example. Alternatively, to allow a user to connect to the MySQL server from any remote host, simply use the '%' character in place of the host name:
+
You may have noted that we specified that johnB could only connect from 'localhost', in other words the same system on which the MySQL server is running. This means that if johnB tries to connect to the MySQL server from a client running on a remote system, the connection will fail. In order to create an account that can connect from a particular host, simply specify the host name or IP address in place of the ''localhost'' in the above example. Alternatively, to allow a user to connect to the MySQL server from any remote host, simply use the '%' character in place of the host name:
<pre>
<pre>
The statement 'USAGE ON *.*' indicates that the user has no privileges on any database or table. Simply put, the user cannot do anything once logged into the database server.
The statement 'USAGE ON *.*' indicates that the user has no privileges on any database or table. Simply put, the user cannot do anything once logged into the database server.
