User Accounts and Privileges - Learning MySQL and MariaDB - page 312

then
continue
fi
`$mysql_connect --execute "GRANT SELECT ON birdwatchers.$item \
TO
'lena_stankoska'@'lena_stankoska_home'"`
done
exit
This simple shell script gets a list of tables using the SHOW TABLES statement. The
script then goes through the list to execute a GRANT statement for each table name in the
results, but skipping the three sensitive tables.
At this point, Lena can do plenty from her office and check on things from her home. If
she needs to do more than this, it will probably be because she is performing an adminis-
trative task like making a backup or importing large amounts of data. When she does
those tasks, she'll use one of the three administrative user accounts we created for her.
Let's give those three accounts the necessary privileges so that Lena can perform the tasks
required ofher.
Restricting to specific columns
To give a user account accessonly to specific columns, issue a GRANT statement listing
all of the columns permitted for the table within parentheses, in a comma-separated list
after the privilege for which they apply. This will make more sense when you see an ex-
ample. If you're granting many privileges, this can be an excessively long SQL statement.
In the previous section, as a security precaution, we didn't give Lena access to the hu-
mans table in the birdwatchers database from home. Suppose we changed our mind
about that. Suppose we want her to have access to most of the humans table when she
works at home, but not to the contact information of our clients (e.g., email addresses).
Looking at the columns in the humans table, we decide she needs access to the hu-
man_id column to be able to join to other tables, and the formal_title ,
name_first , and name_last columns, as well as membership_type . The other
columns either contain sensitive information or are unnecessary for her duties.
Based on the list of columns we want to permit Lena to access from home, let's enter the
following:
GRANT SELECT ( human_id , formal_title , name_first ,
name_last , membership_type )