Selecting a Database
Of course, simply connecting to the MySQL server isn't going to accomplish much. Chances are you will want to select a database to work with. This is accomplished in one of two ways:
- Including the name of the database along with the mysql connection command. For example, the command used to both connect to the MySQL server and select the mysql database is: %>mysql -u root -p mysql
This might be misleading for some readers, as it seems as if the intent is to input mysql as the password. This is not correct. Take a moment to review the syntax as described in the mysql --help output, and it will be apparent that –u root –p actually comprise the [options] component of the syntax, and mysql comprises the [database] component.
- Once connected, select the database using the use command, as follows: mysql>use mydatabase
Once executed, all queries not explicitly specifying a database name will be directed towards the hypothetical mydatabase database.
mysqladmin
The mysqladmin program is used to administrate various aspects of the MySQL database server. Using it, the administrator can perform tasks such as: create and delete databases, shutdown the database server, update the privilege tables, and view running MySQL processes. The general syntax is:
%>mysqladmin [options] command(s)
Where [options] can be one or a series of options used in conjunction with the mysqladmin program, and [database] is the name of the database to use. Since it is assumed to be the reader's first time using the MySQL monitor, take a moment to review all offered options by executing the following command:
%>mysqladmin --help
This produces a long list of options that can be used in conjunction with the mysqladmin program. As a demonstration of how these options are used, let's use mysqladmin to create a new database named widgets, which will be used throughout the remainder of this article to demonstrate various other useful MySQL functions. A new database is created as follows:
%>mysqladmin -u root -p create widgets Enter Password:
Upon execution, mysqladmin will create the database and return to the shell prompt. Typically, the next step is to secure the new database by modifying the privilege tables. Details regarding how this is accomplished is the subject of the next section.
Further Reading
Securing a Database
Security should be the first thought that comes to a MySQL administrator's mind after creating a new database. Privilege as was discussed in the section, "The Privilege Tables" , securing a database is accomplished through modifications made to the tables found in the mysql database. In this section, the reader will learn how to secure the newly created widgets database. Before doing so, a brief summary of exactly how the privilege tables are modified is in order.
There are two methods used to modify the privilege tables. The first is through the use of typical SQL statements such as INSERT, UPDATE and DELETE. However, this method has largely been deprecated through the introduction of the second method, which involves the use of the special commands GRANT and REVOKE. Therefore, only this second method will be discussed in this section.
The Grant Command
The GRANT function is used both to create new users, and to assign privileges to users. Its syntax is:
mysql>GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {tbl_name | * | *.* | db_name.*} TO user_name [IDENTIFIED BY 'password'] [, user_name [IDENTIFIED BY 'password'] ...] [WITH GRANT OPTION]
An understanding of how GRANT works is best gained through examples. In the first example, GRANT is used to add a new user to the database. This user will be used to access the widgets database:
mysql>GRANT usage ON *.* TO widgetAdmin@localhost ->IDENTIFIED BY 'ilovewidgets';
This will create a new user named widgetAdmin , capable of connecting to the MySQL database server via the host localhost using the password ilovewidgets . Keep in mind that this only grants connection privileges. It will not allow the user to do anything with the MySQL server! Go ahead and switch to the mysql database and execute the following query:
mysql>SELECT * FROM user;
Notice that the row containing the widgetAdmin user has N values for all of the privileges. This is good, since the user table contains a user's global privilege settings. To clarify this, if a Y value is set for any user's privilege in the user table, that user can apply that privilege to any MySQL database. Therefore, it is almost always best to set all privileges to N within this table.
So how then are user privileges assigned for a particular database? This is easily done just like the usage privilege was set in the previous example. For example, assume that the administrator wanted to grant user widgetAdmin with SELECT, INSERT, UPDATE and DELETE privileges for the widget database. This is accomplished using the following GRANT command:
mysql>GRANT SELECT, INSERT, UPDATE, DELETE ->ON widgets.* TO widgetAdmin@localhost;
Upon execution, the user widgetAdmin can immediately begin using these privileges.
The privileges introduced thus far are not the only ones available to the administrator. Table 1-1 provides a listing of all available privileges.
Table 1-1: Privileges available for use within GRANT and REVOKE commands
| ALL PRIVILEGES |
FILE |
RELOAD |
| ALTER |
INDEX |
SELECT |
| CREATE |
INSERT |
SHUTDOWN |
| DELETE |
PROCESS |
UPDATE |
| DROP |
REFERENCES |
USAGE |
To view the new privilege table updates, execute the following query: mysql>SELECT * FROM db;
Notice that a row has been added to the db table for user widgetAdmin , with Y values assigned to the SELECT, INSERT, UPDATE and DELETE fields.
Incidentally, it is also possible to bypass the usage query, instead both creating the new user and assigning user privileges simply by executing a variation of the previous query:
mysql>GRANT SELECT, INSERT, UPDATE, DELETE ->ON widgets.* TO widgetAdmin@localhost ->IDENTIFIED BY 'ilovewidgets';
Assuming that the user widgetAdmin did not yet exist when this query is executed, both the user and db tables will be updated with the necessary rows.
Of course, the administrator can revoke previously granted privileges. Exactly how this is accomplished in discussed in the following section.
The Revoke Command
The REVOKE command is used to rescind privileges previously granted to a user. Its syntax is: REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...] ON {tbl_name | * | *.* | db_name.*} FROM user_name [, user_name ...]
As is the case with the GRANT command, perhaps the best way to really understand how it operates is to experiment with several examples. Assume that the administrator wants to repeal the DELETE privilege from the user widgetAdmin . This is accomplished using the following command:
mysql>REVOKE DELETE ON widgets.* ->FROM widgetAdmin@localhost;
Refer to Table 1-1 for a complete listing of privilege types which can be used within the REVOKE command.
One point to keep in mind is that while REVOKE can remove all privileges (including connection privileges) from a user, it does not explicitly remove that user from the privilege tables. To illustrate this, consider the following command:
mysql>REVOKE ALL PRIVILEGES ON widgets.* ->FROM widgetAdmin@localhost;
While this would result in all privileges being revoked from the user widgetAdmin , it would not delete the relevant rows from the privilege tables! If completely removing the user from the database is the intention, the rows would have to be removed using the delete command, as follows:
mysql>DELETE FROM user WHERE user = 'widgetAdmin'; Query OK, 1 row affected (0.00 sec) mysql>flush privileges;
This will effectively deny that user from connecting to the MySQL server. Note that rows from the user table will have to be explicitly removed using DELETE should the administrator wish to entirely remove the user from the privilege tables.
Further Reading
Next page...
|
