Q&A
| Q1: | Are GRANT and REVOKE the only ways of managing user privileges? |
| A1: | No, there are other ways, and prior to MySQL 3.22.11, these commands weren't even implemented.
You can modify user privileges by editing the grant tables directly. For example, you would create a new user in the user table like this: INSERT INTO user SET Host="localhost",
User="someuser",
Password=PASSWORD("somepass"),
Select_priv="Y"
Insert_priv="Y"
For database-specific privileges (which you ought normally to be doing), you would also insert an entry into db, and there may well be entries into host, tables_priv, and even columns_priv. Although there's no technical difference in the outcome, these commands can be lengthy and prone to minor errors. GRANT and REVOKE make life a little easier. |
| Q2: | The privilege system seems complex. I just want to set up a user for simple learning purposes. |
| A2: | It's a good idea to learn the privilege system at some point. But for simple purposes (such as a system that's not going to be used in production), you can make the following simplifications:
|
| Q3: | I'm typing this but get an error:
mysql> SHOW GRANTS FOR user@%;
ERROR 1064: You have an error in your SQL syntax near '%' at line 1
What am I doing wrong? |
| A3: | The % wildcard counts as a special character, so you must enclose any strings containing it in single or double quotes. Thus you should do the following:
mysql> SHOW GRANTS FOR user@"%";
|
| Q4: | If I type this:
mysql> SHOW GRANTS FOR payroll@"%";
will it show me all grants for the payroll user, with all of that user's possible host locations? |
| A4: | No. It shows only entries for the user payroll@"%", not for the user payroll@"%.company.com", which is a different entry in the user grant table. Because they have different username@hostname combinations, MySQL treats them as different accounts.
To find all grants for that user (no matter what host), you would have to query the tables directly, for example: mysql> SELECT * FROM mysql.user WHERE User='payroll';
|
| Q5: | There's a lot about setting up secure connections on MySQL, but not much about how to make the client secure. Where can I learn more? |
| A5: | In today's lesson, we concentrated on how to set up SSL and, briefly, X509 on a MySQL server. You saw how our simple client written in C (from Day 13) can be modified to make a secure connection. Study Day 13, “Using the C API,” for more information on this program. If you have a prior knowledge of C, that lesson and today's lesson should now enable you to build database clients with secure connections.
Many good books about encryption and SSL in general are available, and a quick search on the Internet will uncover many more resources. It's beyond the scope of this book to create more sophisticated clients (using X509, certificates, and so on). Study your development environment (for example, Perl, Visual Basic, or whatever) to learn how to implement secure connections in them. |