Chapter 5 ■ MySQL priMer
another consideration is connectivity. as with the raspberry pi, if you connect a database to your
network and the network is in turn connected to the internet, it may be possible for other users on your
network or the internet to gain access to the database. Don’t make it easy for them—change your root
user password, and create users for your applications.The second command allows access to databases. There are many privileges that you can give a user.
The example shows the most likely set that you would want to give a user of a sensor network database: read
(SELECT), add data (INSERT), and change data (UPDATE). See the online reference manual for more about
security and account access privileges.
The command also specifies a database and objects to which to grant the privilege. Thus, it is possible
to give a user read (SELECT) privileges to some tables and write (INSERT, UPDATE) privileges to other tables.
This example gives the user access to all objects (tables, views, and so on) in the room_temp database.
As mentioned, you can combine these two commands into a single command. You are likely to see this
form more often in the literature. The following shows the combined syntax. In this case, all you need to do
is add the IDENTIFIED BY clause to the GRANT statement. Cool!
GRANT SELECT, INSERT, UPDATE ON room_temp.* TO 'sensor1'@'%' IDENTIFIED BY 'secret';
Common MySQL Commands and Concepts
Learning and mastering a database system requires training, experience, and a good deal of perseverance.
Chief among the knowledge needed to become proficient is how to use the common SQL commands
and concepts. This section completes the primer on MySQL by introducing the most common MySQL
commands and concepts.
■Note rather than regurgitate the reference manual,^9 this section introduces the commands and concepts
at a high level. if you decide to use any of the commands or concepts, please refer to the online reference
manual for additional details, complete command syntax, and additional examples.
MySQL Commands
This section reviews the most common SQL and MySQL-specific commands that you will need to know
to get the most out of your IOT database. While you have already seen some of these in action, this section
provides additional information to help you use them.
■Note Case sensitivity of user-supplied variables (for example, last_name versus Last_Name) is not consistent
across platforms. For example, case-sensitivity behavior is different on Windows than it is on Mac OS X.
MySQL adheres to the platform’s case-sensitivity policy. Check the online reference manual for your platform to
see how case sensitivity affects user-supplied variables.
(^9) Now, that’s one trick the professional regurgitator might not be able to do. See https://en.wikipedia.org/wiki/
Stevie_Starr.