MacLochlainns Weblog

A question came up today about how to stripe a MySQL view, and this post shows you how. Along with the question, there was a complaint about why you can’t use session variables in a view definition. It’s important to note two things: there’s a workaround and there’s an outstanding request to add lift the feature limitation in Bug 18433.

A striped view lets authorized users see only part of a table, and is how Oracle Database 11g sets up Virtual Private Databases. Oracle provides both schema (or database) level access and fine-grained control access. Fine grained control involves setting a special session variable during a user’s login. This is typically done by checking the rights in an Access Control List (ACL) and using an Oracle built-in package.

You can do more or less the same thing in MySQL by using stored functions. One function would set the session variable and the other would fetch the value for comparison in a view.

Most developers who try this initially meet failure because they try to embed the session variable inside the view, like this trivial example with Hobbits (can’t resist the example with the first installment from Peter Jackson out later this year):

1
2

CREATE VIEW hobbit_v AS
SELECT * FROM hobbit WHERE hobbit_name = @sv_login_name;

The syntax is disallowed, as explained in the MySQL Reference 13.1.20 CREATE VIEW Syntax documentation. The attempt raises the following error message:

ERROR 1351 (HY000): VIEW's SELECT contains a variable or parameter

The fix is quite simple, you write a function that sets the ACL value for the session and another that queries the ACL session value. For the example, I’ve written the SET_LOGIN_NAME and a GET_LOGIN_NAME functions. (If you’re new to stored programs, you can find a 58 page chapter on writing them in my Oracle Database 11g & MySQL 5.6 Developer Handbook or you can use Guy Harrison’s MySQL Stored Procedure Programming.)

You would call the SET_LOGIN_NAME when you connect to the MySQL database as the first thing to implement this type of architecture. You would define the function like the following. (Please note that the example includes all setup statements from the command line and should enable you cutting and pasting it. ;-)):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- Change the delimiter to something other than a semicolon.
DELIMITER $$
 
-- Conditionally drop the function.
DROP FUNCTION IF EXISTS set_login_name$$
 
-- Create the function.
CREATE FUNCTION set_login_name(pv_login_name VARCHAR(20)) RETURNS INT UNSIGNED
BEGIN
 
  /* Declare a local variable to verify completion of the task. */
  DECLARE  lv_success_flag  INT UNSIGNED  DEFAULT FALSE;
 
  /* Check whether the input value is something other than a null value. */
  IF pv_login_name IS NOT NULL THEN
 
    /* Set the session variable and enable the success flag. */
    SET @sv_login_name := pv_login_name;
    SET lv_success_flag := TRUE;
 
  END IF;
 
  /* Return the success flag. */
  RETURN lv_success_flag;
END;
$$
 
-- Change the delimiter back to a semicolon.
DELIMITER ;

You can use a query to set and confirm action like this:

SELECT IF(set_login_name('Frodo')=TRUE,'Login Name Set','Login Name Not Set') AS "Login Name Status";

Or, you can use the actual number 1 in lieu of the TRUE, like this:

SELECT IF(set_login_name('Frodo')=1,'Login Name Set','Login Name Not Set') AS "Login Name Status";

Please check this older post on how MySQL manages logical constants and the realities of TRUE and FALSE constants. A more practical example in an API would be this, which returns zero when unset and one when set:

SELECT set_login_name('Frodo') AS "Login Name Status";

The getter function for this example, simply reads the current value of the MySQL session variable. Like the prior example, it’s ready to run too.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

-- Change the delimiter to something other than a semicolon.
DELIMITER $$
 
-- Conditionally drop the function.
DROP FUNCTION IF EXISTS get_login_name$$
 
-- Create the function.
CREATE FUNCTION get_login_name() RETURNS VARCHAR(20)
BEGIN
  /* Return the success flag. */
  RETURN @sv_login_name;
END;
$$
 
-- Change the delimiter back to a semicolon.
DELIMITER ;

Before you test it, lets create a HOBBIT table, seed it with data, and create a HOBBIT_V view. They’re bundled together in the following microscript:

-- Conditionally drop the table.
DROP TABLE IF EXISTS hobbit;
 
-- Create the table.
CREATE TABLE hobbit
( hobbit_id    INT UNSIGNED
, hobbit_name  VARCHAR(20));
 
-- Seed two rows.
INSERT INTO hobbit VALUES ( 1,'Bilbo'),( 1,'Frodo');
 
-- Conditionally drop the view.
DROP VIEW IF EXISTS hobbit_v;
 
-- Create the function-enabled view.
CREATE VIEW hobbit_v AS
SELECT * FROM hobbit WHERE hobbit_name = get_login_name();

A query to the table after setting the session variable will only return one row, the row with Frodo in the HOBBIT_NAME column. It also guarantees an unfiltered UPDATE statement against the view only updates the single row returned, like this:

UPDATE hobbit_v SET hobbit_id = 2;

In a real solution, there are more steps. For example, you’d want your tables in one database, views in another, and functions and procedures in a library database. However, I hope this helps seed some ideas for those interested in creating fine-grained virtual private databases in MySQL with user-authenticated application controls.