Advanced Security
DBA 2: Administering MySQL Lesson 4 Column Security
Welco me back! In the previo us lesso n, we learned ho w to add users to the database, and ho w to specify table-level security fo r tho se users.
Many databases (MySQL included) also allo w administrato rs to specify security fo r individual co lumns in database tables. This can be very useful, depending o n yo ur applicatio n.
In o ur sakila database, we have a table called staff to sto re info rmatio n abo ut emplo yees. Suppo se the o wner needs to sto re payro ll info rmatio n like salary and so cial security number. This is very private info rmatio n that sho uld o nly be viewed by the o wner. Managers and clerks sho uld have no access to that info rmatio n. Ho wever, managers and clerks sho uld be allo wed to see emplo yee info rmatio n such as address o r pho ne number.
We can use MySQL's built-in co lumn-level security to limit access to sensitive co lumns. Befo re we can limit access, we'll need to add co lumns that will sto re salary and so cial security info rmatio n to the staff table.
Make sure MySQL is running and lo g in as ro o t. Type the fo llo wing at the MySQL pro mpt: mysql> use sakila;
Database changed
mysql> alter table staff add salary decimal (10,2);
Query OK, 2 rows affected (0.09 sec) Records: 2 Duplicates: 0 Warnings: 0
mysql> alter table staff add ss_number varchar(9);
Query OK, 2 rows affected (0.03 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql>
No w that we have the new co lumns, let's limit access fo r the manager user. The syntax fo r the grant statement is nearly the same as the statement yo u saw in the previo us lesso n, but this time we'll specify the individual co lumns that a manager is allo wed to select.
Type the fo llo wing at the MySQL pro mpt:
mysql> revoke all on sakila.staff from 'manager'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> grant select(staff_id, first_name, last_name, address_id, picture, email, store_ id, active, username, last_update) on sakila.staff to 'manager'@'localhost';
Query OK, 0 rows affected (0.01 sec) mysql>
OBSERVE:
revoke all on sakila.staff from 'manager'@'localhost';
grant select(staff_id, first_name, last_name, address_id, picture, email, store_id, act ive, username, last_update) on sakila.staff to 'manager'@'localhost';
We re m o ve d all pe rm issio ns (just to make sure we start fro m an empty set o f permissio ns), then adde d back de sire d co lum n pe rm issio ns.
Lo o ks go o d! Let's try o ut o ur new security settings. Lo g o ut o f MySQL, and lo g back in as m anage r. Once back in, we'll co nnect to the sakila database and try to view the structure o f the st af f table.
Type the fo llo wing at the MySQL pro mpt: mysql> use sakila; explain staff;
Database changed
+---+---+---+---+---+--- +
| Field | Type | Null | Key | Default | Extra |
+---+---+---+---+---+--- +
| staff_id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| first_name | varchar(45) | NO | | NULL | |
| last_name | varchar(45) | NO | | NULL | |
| address_id | smallint(5) unsigned | NO | MUL | NULL | |
| picture | blob | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
| store_id | tinyint(3) unsigned | NO | MUL | NULL | |
| active | tinyint(1) | NO | | 1 | |
| username | varchar(16) | NO | | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | |
+---+---+---+---+---+--- +
10 rows in set (0.00 sec) mysql>
MySQL do esn't even sho w us that the co lumns passwo rd, ss_num be r and salary are part o f the staff table. But can we query tho se co lumns? As a manager, we sho uld no t be able to . Try it!
Type the fo llo wing at the MySQL pro mpt:
mysql> select password, salary, ss_number from staff;
ERROR 1143 (42000): SELECT command denied to user 'manager'@'localhost' for column 'pas sword' in table 'staff'
mysql>
MySQL is do ing its jo b; it do esn't let us return the passwo rd, salary o r ss_number co lumns.
passwo rd is the first co lumn in the query that manager canno t SELECT, so it's the co lumn specified in the erro r. Can we still see the o ther co lumns? A simple query will tell us.
Type the fo llo wing at the MySQL pro mpt:
mysql> select first_name, last_name from staff;
+---+---+ | first_name | last_name | +---+---+ | Mike | Hillyer | | Jon | Stephens | +---+---+ 2 rows in set (0.00 sec)
Lo o ks great!
Row Security
In the last sectio n we successfully limited access to three co lumns in the staff table. Do ing so prevents o rdinary users fro m accessing secure info rmatio n.
What if yo u need to restrict access to certain ro ws in yo ur table? Co nsider the cust o m e r table in o ur sakila database. Each custo mer is tied to a specific sto re thro ugh st o re _id. Let's say the o wner creates a new po licy stating that managers and clerks can o nly deal with custo mers within their o wn sto re. Ho w will yo u enfo rce this po licy?
In so me databases this type o f restrictio n can o nly be do ne at the applicatio n level. This is po tentially insecure, since different applicatio ns might implement access rules in different ways. Also , the user co uld still try to query the database directly.
MySQL do esn't have any specific functio nality to implement ro w-level security, unlike larger databases such as Oracle. There are two co mmo n ways to implement ro w-level security witho ut specific database assistance—by using vie ws and st o re d pro ce dure s. Instead o f granting users access to tables, users are o nly allo wed access to a view o r sto red pro cedure.
Suppo se dave wants to access the custo mer list. He is no t allo wed access to the Cust o m e r List table; instead he must query the Cust o m e r List Lim it e d view. This view co ntains a whe re clause to limit the ro ws returned to the current user, dave .
In o rder to limit the ro ws a specific user can view, we will need to kno w the username o f the perso n who is co nnected to the database. In MySQL this is determined by the use r() functio n.
Make sure yo u're co nnected to MySQL as ro o t. Type the fo llo wing at the MySQL pro mpt: mysql> select user();
+---+ | user() | +---+ | root@localhost | +---+ 1 row in set (0.00 sec) mysql>
MySQL happily respo nds with yo ur user info rmatio n.
Recall in the last lesso n that we granted dave access to the database, but we didn't add his data to the appro priate places within the applicatio n. Usually this is do ne by a manager, using the applicatio n, but we'll just use SQL since this co urse do esn't use an applicatio n.
To add him to the applicatio n we'll need to add an address fo r 'dave' and add him to the staff table. Let's do this no w, assigning dave to sto re_id = 1. Make sure yo u're co nnected to MySQL as ro o t .
Type the fo llo wing at the MySQL pro mpt:
mysql> insert into address (address, district, city_id, phone)
-> values ('123 4th Street', 'Alberta', 300, '8885551212');
Query OK, 1 row affected (0.02 sec)
mysql> insert into staff (first_name, last_name, address_id, email, store_id, active, u sername)
-> values ('Dave','Smith', LAST_INSERT_ID(), '[email protected]', 1, 1, 'dave');
Query OK, 1 row affected (0.00 sec) mysql>
Run a quick query to make sure Dave was entered into the system. Type the fo llo wing at the MySQL pro mpt:
mysql> select * from staff_list;
+----+---+---+---+---+---+--- ---+---+
| ID | name | address | zip code | phone | city | coun try | SID |
+----+---+---+---+---+---+--- ---+---+
| 1 | Mike Hillyer | 23 Workhaven Lane | | 14033335568 | Lethbridge | Cana da | 1 |
| 2 | Jon Stephens | 1411 Lillydale Drive | | 6172235589 | Woodridge | Aust ralia | 2 |
| 3 | Dave Smith | 123 4th Street | | 8885551212 | Lethbridge | Cana da | 1 |
+----+---+---+---+---+---+--- ---+---+
3 rows in set (0.00 sec)
No w we can fo cus o n granting dave access to the custo mers that share his sto re_id. We'll do this by creating a new view cust o m e r_list _lim it e d, which will be based o n the existing view called cust o m e r_list .
What if we do n't kno w the select statement that is the basis o f the cust o m e r_list view? Fo rtunately MySQL keeps the definitio n fo r us, in its special INFORMAT ION_SCHEMA database. We can query that to get the view definitio n.
Note
Fo r mo re info rmatio n o n INFORMAT ION_SCHEMA, refer back to the lesso n o n "Info rmatio n Abo ut theDatabase" in DBA 1, o r visit the MySQL web site.Type the fo llo wing at the MySQL pro mpt:
mysql> select view_definition from information_schema.views where table_name='customer_ list' \G
*************************** 1. row ***************************
view_definition: /* ALGORITHM=UNDEFINED */ select `cu`.`customer_id` AS `ID`,concat(`cu `.`first_name`,_utf8' ',`cu`.`last_name`) AS `name`,`a`.`address` AS `address`,`a`.`pos tal_code` AS `zip code`,`a`.`phone` AS `phone`,`sakila`.`city`.`city` AS `city`,`sakila `.`country`.`country` AS `country`,if(`cu`.`active`,_utf8'active',_utf8'') AS `notes`,` cu`.`store_id` AS `SID` from (((`sakila`.`customer` `cu` join `sakila`.`address` `a` on ((`cu`.`address_id` = `a`.`address_id`))) join `sakila`.`city` on((`a`.`city_id` = `sak ila`.`city`.`city_id`))) join `sakila`.`country` on((`sakila`.`city`.`country_id` = `sa kila`.`country`.`country_id`)))
1 row in set (0.01 sec
INTERACTIVE SESSION: mysql> select
-> cu.customer_id AS ID,
-> concat(cu.first_name,_utf8' ',cu.last_name) AS name,
-> a.address AS address,
-> a.postal_code AS 'zip code',
-> a.phone AS phone, -> sakila.city.city AS city, -> sakila.country.country AS country, -> if(cu.active,_utf8'active',_utf8'') AS notes, -> cu.store_id AS SID -> from -> sakila.customer cu
-> join sakila.address a on (cu.address_id = a.address_id)
-> join sakila.city on (a.city_id = sakila.city.city_id)
-> join sakila.country on (sakila.city.country_id = sakila.country.country_id)
+---+---+---+---+---- ---+---+---+--- -+---+
| ID | name | address | zip code | pho ne | city | country | notes | SID |
+---+---+---+---+---- ---+---+---+--- -+---+
| 218 | VERA MCCOY | 1168 Najafabad Parkway | 40301 | 886 649065861 | Kabul | Afghanistan | active | 1 |
| 441 | MARIO CHEATHAM | 1924 Shimonoseki Drive | 52625 | 406 784385440 | Batna | Algeria | active | 1 |
| 69 | JUDY GRAY | 1031 Daugavpils Parkway | 59025 | 107 137400143 | Bchar | Algeria | active | 2 |
... lines omitted
| 7 | MARIA MILLER | 900 Santiago de Compostela Parkway | 93896 | 716 571220373 | Kragujevac | Yugoslavia | active | 1 |
| 553 | MAX PITT | 1917 Kumbakonam Parkway | 11892 | 698 182547686 | Novi Sad | Yugoslavia | active | 1 |
| 438 | BARRY LOVELACE | 1836 Korla Parkway | 55405 | 689 681677428 | Kitwe | Zambia | active | 1 |
+---+---+---+---+---- ---+---+---+--- -+---+
599 rows in set (0.06 sec) mysql>
To make this query (and eventually the view) handle ro w-level security, we'll have to add a jo in to the st af f table, as well as a WHERE clause to limit the current use r(). Since the result returned fro m the use r() functio n is slightly different fro m what we need (we o nly want the username, no t the "@lo calho st" part), we'll also have to do a bit o f manipulatio n.
Remo ving the "@lo calho st" part fro m use r() isn't difficult—we can use the SUBSTRING_INDEX functio n.
SUBSTRING_INDEX takes three arguments—a string, a delimiter, and a count. SUBSTRING_INDEX returns the text fro m the string befo re count o ccurrences o f the delimiter. Fo r mo re info rmatio n, check o ut the MySQL web site. Try it o ut! Make sure yo u're lo gged in as ro o t and type the fo llo wing query.
Type the fo llo wing at the MySQL pro mpt:
mysql> select substring_index(user(), '@', 1);
+---+ | substring_index(user(), '@', 1) | +---+ | root | +---+ 1 row in set (0.01 sec)
mysql>
Let's rewrite o ur select query. Try it while lo gged in as ro o t . Type the fo llo wing at the MySQL pro mpt:
mysql> select
-> cu.customer_id AS ID,
-> concat(cu.first_name,_utf8' ',cu.last_name) AS name,
-> a.address AS address,
-> a.postal_code AS 'zip code',
-> a.phone AS phone, -> sakila.city.city AS city, -> sakila.country.country AS country, -> if(cu.active,_utf8'active',_utf8'') AS notes -> from -> sakila.customer cu
-> join sakila.address a on (cu.address_id = a.address_id)
-> join sakila.city on (a.city_id = sakila.city.city_id)
-> join sakila.country on (sakila.city.country_id = sakila.country.country_id)
-> join sakila.staff s on (cu.store_id = s.store_id)
-> WHERE s.username = substring_index(user(), '@', 1);
Empty set (0.01 sec) mysql>
OBSERVE:
join sakila.staff s on (cu.store_id = s.store_id) WHERE s.username = substring_index(user(), '@', 1);
We added a jo in to the sakila.st af f table o n st o re _id, and add a WHERE clause to limit the st af f table to the current username—subst ring_inde x(use r(), '@ ', 1).
Why did we get an empty set? This is actually expected, because o ur st af f table do esn't have a ro w with username o f ro o t .
Lo g o ut and then reco nnect to MySQL as dave Try the query again, making sure yo u are using the sakila database. This time yo ur results will be much different:
INTERACTIVE SESSION: mysql> select
-> cu.customer_id AS ID,
-> concat(cu.first_name,_utf8' ',cu.last_name) AS name,
-> a.address AS address,
-> a.postal_code AS 'zip code',
-> a.phone AS phone, -> sakila.city.city AS city, -> sakila.country.country AS country, -> if(cu.active,_utf8'active',_utf8'') AS notes -> from -> sakila.customer cu
-> join sakila.address a on (cu.address_id = a.address_id)
-> join sakila.city on (a.city_id = sakila.city.city_id)
-> join sakila.country on (sakila.city.country_id = sakila.country.country_id)
-> join sakila.staff s on (cu.store_id = s.store_id)
-> WHERE s.username = substring_index(user(), '@', 1);
+---+---+---+---+---- ---+---+---+---+ | ID | name | address | zip code | phon e | city | country | notes | +---+---+---+---+--- ---+---+---+---+ | 1 | MARY SMITH | 1913 Hanoi Way | 35200 | 2830 3384290 | Sasebo | Japan | active | | 2 | PATRICIA JOHNSON | 1121 Loja Avenue | 17886 | 8386 35286649 | San Bernardino | United States | active | | 3 | LINDA WILLIAMS | 692 Joliet Street | 83579 | 4484 77190408 | Athenai | Greece | active | | 5 | ELIZABETH BROWN | 53 Idfu Parkway | 42399 | 1065 5648674 | Nantou | Taiwan | active |
... (lines omitted)
| 594 | EDUARDO HIATT | 1837 Kaduna Parkway | 82580 | 6408 43562301 | Jining | China | active | | 595 | TERRENCE GUNDERSON | 844 Bucuresti Place | 36603 | 9359 52366111 | Jinzhou | China | active | | 596 | ENRIQUE FORSYTHE | 1101 Bucuresti Boulevard | 97661 | 1995 14580428 | Patras | Greece | active | | 597 | FREDDIE DUGGAN | 1103 Quilmes Boulevard | 52137 | 6440 21380889 | Sullana | Peru | active | | 598 | WADE DELVALLE | 1331 Usak Boulevard | 61960 | 1453 08717464 | Lausanne | Switzerland | active | +---+---+---+---+--- ---+---+---+---+ 326 rows in set (0.03 sec)
mysql>
Sure eno ugh, the custo mer list is no w being limited by the current user's sto re. This means we are ready to implement the view. Lo g back into MySQL as ro o t —o therwise yo u wo n't have the permissio n to create a view!
Type the fo llo wing at the MySQL pro mpt:
mysql> CREATE VIEW customer_list_limited
-> AS
-> select
-> cu.customer_id AS ID,
-> concat(cu.first_name,_utf8' ',cu.last_name) AS name,
-> a.address AS address,
-> a.postal_code AS 'zip code',
-> a.phone AS phone, -> sakila.city.city AS city, -> sakila.country.country AS country, -> if(cu.active,_utf8'active',_utf8'') AS notes -> from -> sakila.customer cu
-> join sakila.address a on (cu.address_id = a.address_id)
-> join sakila.city on (a.city_id = sakila.city.city_id)
-> join sakila.country on (sakila.city.country_id = sakila.country.country_id)
-> join sakila.staff s on (cu.store_id = s.store_id)
-> WHERE s.username = substring_index(user(), '@', 1);
Query OK, 0 rows affected (0.03 sec) mysql>
Yo u'll also need to grant access to the appro priate peo ple fo r yo ur new view, so let's do that no w. Type the fo llo wing at the MySQL pro mpt:
mysql> grant select on customer_list_limited to 'manager'@'localhost';
Query OK, 0 rows affected (0.03 sec)
mysql> grant select on customer_list_limited to 'dave'@'localhost';
Query OK, 0 rows affected (0.03 sec) mysql>
Type the fo llo wing at the MySQL pro mpt:
mysql> select * from customer_list_limited;
+---+---+---+---+--- ---+---+---+---+ | ID | name | address | zip code | phon e | city | country | notes | +---+---+---+---+--- ---+---+---+---+ | 1 | MARY SMITH | 1913 Hanoi Way | 35200 | 2830 3384290 | Sasebo | Japan | active | | 2 | PATRICIA JOHNSON | 1121 Loja Avenue | 17886 | 8386 35286649 | San Bernardino | United States | active | | 3 | LINDA WILLIAMS | 692 Joliet Street | 83579 | 4484 77190408 | Athenai | Greece | active | | 5 | ELIZABETH BROWN | 53 Idfu Parkway | 42399 | 1065 5648674 | Nantou | Taiwan | active |
... (lines omitted)
| 594 | EDUARDO HIATT | 1837 Kaduna Parkway | 82580 | 6408 43562301 | Jining | China | active | | 595 | TERRENCE GUNDERSON | 844 Bucuresti Place | 36603 | 9359 52366111 | Jinzhou | China | active | | 596 | ENRIQUE FORSYTHE | 1101 Bucuresti Boulevard | 97661 | 1995 14580428 | Patras | Greece | active | | 597 | FREDDIE DUGGAN | 1103 Quilmes Boulevard | 52137 | 6440 21380889 | Sullana | Peru | active | | 598 | WADE DELVALLE | 1331 Usak Boulevard | 61960 | 1453 08717464 | Lausanne | Switzerland | active | +---+---+---+---+--- ---+---+---+---+ 326 rows in set (0.03 sec)
mysql>
There yo u have it—yo u've limited access to ro ws o f data based o n the current user. At this po int yo u'd pro bably replace the view cust o m e r_list with the new view cust o m e r_list _lim it e d, o r at least remo ve access to the o ld view cust o m e r_list fo r dave.
No rmally views and sto red pro cedures are available o nly to the user who created them. A user with sufficient grant privileges (such as tho se held by the ro o t acco unt) can allo w o ther users to use views and sto red pro cedures that tho se o ther users did no t create. In fact, we did just that when we granted the select privilege o n the
custo mer_list_limited view to the users 'manager'@'lo calho st' and 'dave'@'lo calho st'. (While we use the select privilege in the grant statement fo r views, the co rrespo nding privilege fo r sto red pro cedures is executed within the pro cedure.)
The creato r o f a view o r sto red pro cedure can include an o ptio nal SQL SECURITY characteristics clause in the create view o r create pro cedure statement. The default SQL SECURITY value is DEFINER. With SQL SECURITY DEFINER, a user selects fro m a view o r calls a pro cedure with the privileges o f the user who created it. Using SQL SECURITY DEFINER, a DBA can allo w users who do no t have specific table privileges to select fro m a view o r call a pro cedure that accesses tho se tables.
Pro grammers may also specifically define the SQL SECURITY value to be INVOKER. With the mo re restrictive SQL SECURITY INVOKER, a user selects fro m a view o r calls a pro cedure using their o wn privileges. If a view o r pro cedure accesses a particular table, the user wo uld require the appro priate privileges fo r the view o r pro cedure, as well as the underlying table.
Fo r mo re info rmatio n o n sto red pro gram and view security visit the MySQL web site.
In the last two lesso ns we've co vered many aspects o f database security, fro m granting and revo king user permissio ns to limiting access to co lumns and ro ws o f data. In the next lesso n we'll shift o ur fo cus to the tables themselves, and learn ho w to keep o ur database perfo rming well thro ugh pro per index management. See yo u there!
Copyright © 1998-2013 O'Reilly Media, Inc.
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License. See http://creativecommons.org/licenses/by-sa/3.0/legalcode for more information.