Dabbling in Live Databases: MySQL - Page 6

This is the easy part. We're still within the mysql interactive client, so we'll import the raw data from here. You could alternatively use the mysqlimport utility as a standalone client, which has its own syntax. Simply:

Click here for code example.

Sometimes the server will report one or more warnings when importing raw data. Possible reasons include extra data in the raw data, compared to the table structure, or that the server felt some of the data wasn't a perfect fit for the field type defined. Warnings are not fatal problems, and in our case, the data imported just fine even if the server was a bit skeptical.

Just to be sure, we can run a little test: send a simple query from the mysql client.

mysql> select hours from invoices where invoice=99070101
    -> \g
+-------+
| hours |
+-------+
|  3.70 |
+-------+
1 row in set (0.05 sec)

Yee-ha! It's alive ... it's alive.

Protecting the Kingdom

Now, to prevent others from getting their grubby little hands on our precious data. Remember earlier we looked at the MySQL security model and we created a root password. Now that we have a database in the shed, we should create a MySQL account for querying it. Actually, we'll create two accounts with access to Clients -- one will be a simple read-only account, which our query scripts will use, and the other will be a more powerful account which someone could use to manage the database.

You are welcome, of course, to read the MySQL reference documentation on setting up user accounts and privileges. Be warned, though, that it may cause serious and longlasting mental damage. Here, we distill what you need to know to create some basic user accounts.

First, a read-only account named serf which can access the Clients database, and execute SQL Select statements against any of its tables. This account cannot access any other database supported by this MySQL server, nor can it modify or delete the Clients tables (nor add new tables). First, launch the mysql client as the root user.

/path/to/mysql/bin/mysql -u root -p
password: ***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 3.22.20

Type 'help' for help.

Now, we use the GRANT statement to construct the privilege list for the user serf, whose password will be "readonly", may have only SELECT privilege to tables within Clients, and who may connect from any remote host:

mysql> GRANT SELECT ON Clients.* TO serf@'%' 
       IDENTIFIED BY 'readonly'

The bit following the @ symbol defines which remote hosts serf can originate from. The % is a wildcard and thus means "any host". We can create more limited ranges, such as all remote hosts from the domain "safe.net", with the syntax:

mysql> GRANT SELECT ON Clients.* TO serf@'%.safe.net' 
       IDENTIFIED BY 'readonly'

Caveat: the user serf can connect to the MySQL server from a remote machine, but not the machine running the server itself, known as localhost. To allow access from localhost, we need to explicitly grant that in another GRANT statement:

mysql> GRANT SELECT ON Clients.* TO serf@localhost 
       IDENTIFIED BY 'readonly'

A more powerful account, such as lord, might be used to make changes to the Clients database.

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 
ON Clients.* TO lord@localhost IDENTIFIED BY 'fiefdom'

Should a user lord login from the localhost machine, s/he could have free reign over this database, but no other databases on the server (unless we issues additional GRANT statements).

Of course, you could create an even more powerful user who can modify all databases on this server:

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 
ON *.* TO king@'%' IDENTIFIED BY 'kingdom'

There are so many permutations you can easily see how fun it would be to spend all day creating user accounts. Alas, we must press onwards -- just keep in mind security issues as applicable to your data, and create access accounts accordingly.