MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

PHP for MySQL Striped View

with 5 comments

Back in May I explained how to create MySQL striped views with session variables. A couple folks wanted to know how to implement them through PHP programs. The trick is sharing the same connection between a call to the function before a query against the striped view.

I’ve updated the MySQL example beyond the Hobbit analogy from the prior post. It now uses the following database components:

  • An APPLICATION_USER table
  • A striped AUTHORIZED_USER view
  • A FUNCTION_QUERY view to optimize function calls
  • A SET_LOGIN function
  • A GET_LOGIN_ID function
  • A GET_USER_ID function

The definition of the APPLICATION_USER table is:

CREATE TABLE application_user
( user_id  int(10) unsigned PRIMARY KEY AUTO_INCREMENT
, user_name  varchar(20) NOT NULL
, user_role  varchar(20) NOT NULL
, user_group_id  int(10) unsigned NOT NULL
, user_type  int(10) unsigned NOT NULL
, first_name  varchar(20)
, middle_name  varchar(20)
, last_name  varchar(20)
, created_by  int(10) unsigned NOT NULL
, creation_date  datetime NOT NULL
, last_updated_by  int(10) unsigned NOT NULL
, last_update_date  datetime NOT NULL
, CONSTRAINT natural_key UNIQUE (user_name)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

You should note that the natural key is a user-defined user name (mind you in reality it is often set by the application administrator). This guarantees that the authorize_cursor in the set_login function below always returns only one row.

The following seeds five rows in the APPLICATION_USER table:

INSERT INTO application_user VALUES
 ( null, 'potterhj', 'System Admin', 2, 1, 'Harry', 'James', 'Potter', 1, NOW(), 1, NOW())
,( null, 'weasilyr', 'Guest', 1, 1, 'Ronald', null, 'Weasily', 1, NOW(), 1, NOW())
,( null, 'longbottomn', 'Guest', 1, 1, 'Neville', null, 'Longbottom', 1, NOW(), 1, NOW())
,( null, 'holmess', 'DBA', 3, 1, 'Sherlock', null, 'Holmes', 1, NOW(), 1, NOW())
,( null, 'watsonj', 'DBA', 3, 1, 'John', 'H', 'Watson', 1, NOW(), 1, NOW());

Before creating the striped view, you should create the functions that set the session variables and query them. The set_login function sets two session variables when successful, which requires a user name that matches a valid value in the user_name column of the application_user table. The function returns an integer of 1 on success and 0 on failure. The set_login function code follows.

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
CREATE FUNCTION set_login(pv_login_name VARCHAR(20)) RETURNS INT UNSIGNED
BEGIN
 
  /* Declare a local variable to verify completion of the task:
  || ==========================================================
  ||   a. Default value is zero, which means false.
  ||   b. Non-default value is one, which means true.
  || ==========================================================
  */
  DECLARE  lv_success_flag  INT UNSIGNED  DEFAULT 0;
 
  /* Declare local variables to hold the return values from the cursor. */
  DECLARE  lv_login_id  INT UNSIGNED;
  DECLARE  lv_group_id  INT UNSIGNED;
 
  /* Declare a condition variable for zero rows fetched, selected, or processed. */
  DECLARE  no_rows_fetched  CONDITION FOR 1329;
 
  /* Declare a cursor to return an authorized user id. */
  DECLARE authorize_cursor CURSOR FOR
    SELECT   a.user_id
    ,        a.user_group_id
    FROM     application_user a
    WHERE    a.user_name = pv_login_name;
 
  /* Declare a handler for the cursor when it fails to return a row. */   
  DECLARE EXIT HANDLER FOR no_rows_fetched
    BEGIN
      /* The return statement when the function is aborted through an error. */
      RETURN lv_success_flag;
    END;
 
  /* Check whether the input value is something other than a null value. */
  IF pv_login_name IS NOT NULL THEN
 
    OPEN  authorize_cursor;
    FETCH authorize_cursor INTO lv_login_id, lv_group_id;
    CLOSE authorize_cursor;
 
    /* Set the success flag. */
    SET @sv_login_id := lv_login_id;
    SET @sv_group_id := lv_group_id;
 
    /* Check whether the session variables are set. */
    IF NOT ISNULL(@sv_login_id) AND @sv_login_id > 0 AND
       NOT ISNULL(@sv_group_id) AND @sv_group_id > 0 THEN
      SET lv_success_flag := 1;
    END IF;
 
  END IF;
 
  /* Return the success flag. */
  RETURN lv_success_flag;
END;
$$

The following GET_LOGIN_ID function returns the value from the @sv_login_id variable.

1
2
3
4
5
6
CREATE FUNCTION get_login_id() RETURNS INT UNSIGNED
BEGIN
  /* Return the success flag. */
  RETURN @sv_login_id;
END;
$$

The following GET_GROUP_ID function returns the value from the @sv_group_id variable.

1
2
3
4
5
6
CREATE FUNCTION get_group_id() RETURNS INT UNSIGNED
BEGIN
  /* Return the success flag. */
  RETURN @sv_group_id;
END;
$$

Lastly, you create the MySQL striped AUTHORIZED_USER like this one. It looks ineffective because it includes four function calls to the get_group_id() and one to the get_login_id().

CREATE VIEW authorized_user AS
SELECT   au.user_id
,        au.user_name
,        au.user_role
,        CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name
FROM     application_user au
WHERE   (au.user_group_id = 1
AND      au.user_group_id = get_group_id()
AND      au.user_id = get_login_id())
OR       get_group_id() = 2
OR      (get_group_id() > 2
AND      au.user_group_id = get_group_id());

The prior view’s query lets you see the logic for the three types of access. You can eliminate the multiple function calls by using an inline view, like the following in a SQL statement:

CREATE VIEW authorized_user AS
SELECT   au.user_id
,        au.user_name
,        au.user_role
,        CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name
FROM     application_user au CROSS JOIN
        (SELECT   get_login_id() AS login_id
         ,        get_group_id() AS group_id) fq
WHERE   (au.user_group_id = 1
AND      au.user_group_id = fq.group_id
AND      au.user_id = fq.login_id)
OR       fq.group_id = 2
OR      (fq.group_id > 2
AND      au.user_group_id = fq.group_id);

Unfortunately, the preceding query raises the following exception if you attempt to put it in a view:

ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause

MySQL raises the error because a SELECT statement can’t contain a subquery in the FROM clause, according to the Create View MySQL Reference material.

The solution to the limitation of the CREATE VIEW syntax requires that you breakup the SQL statement into queries, and put them into separate views. The following example shows the function_query view holding the function calls and the authorized_user view cross joining the function_query view.

CREATE VIEW function_query AS
SELECT   get_login_id() AS login_id
,        get_group_id() AS group_id;
 
CREATE VIEW authorized_user AS
SELECT   au.user_id
,        au.user_name
,        au.user_role
,        CONCAT(au.last_name,", ",au.first_name," ",IFNULL(au.middle_name,"")) AS full_name
FROM     application_user au CROSS JOIN function_query fq
WHERE   (au.user_group_id = 1
AND      au.user_group_id = fq.group_id
AND      au.user_id = fq.login_id)
OR       fq.group_id = 2
OR      (fq.group_id > 2
AND      au.user_group_id = fq.group_id);

The following PHP program calls the SET_LOGIN function before querying the AUTHORIZED_USER view. It uses a $_GET global parameter to simplify testing the concept but you should always run parameters through the $_POST global parameter. The $_GET and $_REQUEST global parameters are security risks.

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
<html>
<header>
<title>Static Query Object Sample</title>
<style type="text/css">
  /* HTML element styles. */
  table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
  th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
  td {border:solid 1px gray;}
 
  /* Class tag element styles. */
  .ID {min-width:50px;text-align:right;}
  .Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
 
  // Process the input parameter, which should be through a $_POST argument.
  (isset($_GET['user_name'])) ? $input = $_GET['user_name'] : $input = '';
 
  // Assign credentials to connection.
  $mysqli = new mysqli("localhost", "student", "student", "studentdb");
 
  // Check for connection error and print message.
  if ($mysqli->connect_errno) {
    print $mysqli->connect_error."<br />";
    print "Connection not established ...<br />";
  }
  else {
 
    // Declare a dynamic function call.
    $query = "SELECT set_login(?)";
 
    // Attempt preparing statement.
    if (!$stmt = $mysqli->prepare($query)) {
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }     
    else {
 
      // Bind variable to SQL statement and execute it.
      $stmt->bind_param("s", $input);
      $stmt->execute();
      $stmt->close(); 
    }
 
    // Declare a static query.
    $query = "SELECT au.user_id, au.user_name, au.user_role, au.full_name FROM authorized_user au" ;
 
    // Loop through a result set until completed.  
    do {
 
      // Attempt query and exit with failure before processing.
      if (!$stmt = $mysqli->query($query)) {
 
        // Print failure to resolve query message.
        print $mysqli->error."<br />";
        print "Failed to resolve query ...<br />";
      }     
      else {
 
        // Print the opening HTML table tag.
        print '<table><tr><th class="ID">ID</th><th class="Label">User Name</th><th class="Label">User Role</th><th class="Label">Full Name</th></tr>';
 
        // Fetch a row for processing.
        while( $row = $stmt->fetch_row() ) {
 
          // Print the opening HTML row tag.
          print "<tr>";
 
          // Loop through the row's columns.
          for ($i = 0;$i < $mysqli->field_count;$i++) {
            // Handle column one differently.
            if ($i == 0)
              print '<td class="ID">'.$row[$i]."</td>";
            else
              print '<td class="Label">'.$row[$i]."</td>";
          }
          // Print the closing HTML row tag.
          print "</tr>"; 
        }
      }
    } while( $mysqli->next_result());
 
  // Print the closing HTML table tag.
  print "</table>"; 
 
  // Release connection resource.
  $mysqli->close(); }
?>
</script>
</body>
</html>

You can call this through a browser with the following type of URL. You have two possible striped values, and they are any user’s unique user name.

http://localhost/stripedquery1.php?user_name=potterhj

It returns one row when the user isn’t in a privileged group, all rows when the user is the root privileged group and all rows for a privilege group when not in the root privileged group. Naturally, you can extend this level of individual and group membership.

You can test this in the web page or directly in MySQL. The MySQL test doesn’t require image files and thereby loads faster, which is why I’ve opted to show it to you that way.

  1. Test for the root privilege group:
SELECT set_login('potterhj');

You get the full five rows:

+---------+-------------+--------------+----------------------+
| user_id | user_name   | user_role    | full_name            |
+---------+-------------+--------------+----------------------+
|       1 | potterhj    | System Admin | Potter, Harry James  |
|       2 | weasilyr    | Guest        | Weasily, Ronald      |
|       3 | longbottomn | Guest        | Longbottom, Neville  |
|       4 | holmess     | DBA          | Sherlock, Holmes     |
|       5 | watsonj     | DBA          | John, Watson H       |
+---------+-------------+--------------+----------------------+
  1. Test for an individual in a non-privileged group:
SELECT set_login('weasilyr');

You get the single user’s row:

+---------+-----------+-----------+------------------+
| user_id | user_name | user_role | full_name        |
+---------+-----------+-----------+------------------+
|       2 | weasilyr  | Guest     | Weasily, Ronald  |
+---------+-----------+-----------+------------------+
  1. Test for a non-root privileged group:
SELECT set_login('holmess');

You get the two rows that belong to the non-root privileged group:

+---------+-----------+-----------+-------------------+
| user_id | user_name | user_role | full_name         |
+---------+-----------+-----------+-------------------+
|       4 | holmess   | DBA       | Holmes, Sherlock  |
|       5 | watsonj   | DBA       | Watson, John H    |
+---------+-----------+-----------+-------------------+

Naturally, it’s more effective to put these components into a function library. The trick to making this work in a session is to share the connection. The object approach to the MySQL Improved (mysqli) object holds the connection, and that’s whats passed in the following example files.

This is an optimistic setter function. It forks (starts) the MySQL SQL/PSM set_login function but it doesn’t wait see if it ran successfully. That’s the nature of optimistic programming solutions, and the principal problem with them.

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
function set_login($mysqli, $user_name) {
 
  // Define return string.
  $return = false;
 
  // Declare a dynamic function call.
  $query = "SELECT set_login(?)";
 
  // Attempt preparing statement.
  if (!$stmt = $mysqli->prepare($query)) {
 
    // Print failure to resolve query message.
    print $mysqli->error."<br />";
    print "Failed to resolve query ...<br />";
  }     
  else {
 
    // Bind variable to SQL statement and execute it.
    $stmt->bind_param("s", $user_name);
    $stmt->execute();
    $stmt->close();
    $return = true;
  }
 
  // Return the string.
  return $return;
}

An optimistic setter function fails to synchronize behaviors between the PHP and MySQL coding levels. It should be rewritten to fork the MySQL SQL/PSM set_login function and evaluate it’s successful or unsuccessful completion, which makes it a pessimistic function.

There are two ways to solve this problem. One can write a wrapper that accesses the get_login_id stored function to confirm the session variable is set, and the other handles the return value from the native set_login_id stored function. The former requires knowledge of the internal workings of the database model, while the latter does not. That means the first is more tightly coupled than the latter.

The following set_login PHP function is rewritten to be pessimistic but dependent on a supplemental call to another get_login PHP function, which calls the get_login_id stored function in the MySQL Server:

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
30
function set_login($mysqli, $user_name) {
 
  // Define return string.
  $return = false;
 
  // Declare a dynamic function call.
  $query = "SELECT set_login(?)";
 
  // Attempt preparing statement.
  if (!$stmt = $mysqli->prepare($query)) {
 
    // Print failure to resolve query message.
    print $mysqli->error."<br />";
    print "Failed to resolve query ...<br />";
  }     
  else {
 
    // Bind variable to SQL statement and execute it.
    $stmt->bind_param("s", $user_name);
    $stmt->execute();
    $stmt->close();
 
    // True only when query returns a row.
    if (get_login($mysqli)) {
      $return = true; }
  }
 
  // Return the string.
  return $return;
}

A more effective solution reads the return value from the set_login function, like this:

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
function set_login($mysqli, $user_name) {
 
  // Define return string.
  $return = false;
 
  // Declare a dynamic function call.
  $query = "SELECT set_login(?)";
 
  // Attempt preparing statement.
  if (!$stmt = $mysqli->prepare($query)) {
 
    // Print failure to resolve query message.
    print $mysqli->error."<br />";
    print "Failed to resolve query ...<br />";
  }     
  else {
 
    // Bind variable to SQL statement and execute it.
    $stmt->bind_param("s", $user_name);
 
    // Attempt query and exit with failure before processing.
    if (!$stmt->execute()) {
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }     
    else {
 
      // Fetch a row for processing.
      $result = $stmt->get_result();
      $row = $result->fetch_array(MYSQLI_NUM);
    }
 
    // Close the statement cursor.
    $stmt->close();
 
    // True only when query returns a row.
    if (!is_null($row[0]) && ($row[0] > 0)) {
      $return = true; }
  }
 
  // Return the string.
  return $return;
}

This is the getter function:

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
30
31
function get_login($mysqli) {
 
  // Define return string.
  $return = false;
 
  // Declare a dynamic function call.
  $query = "SELECT get_login_id()";
 
  // Attempt preparing statement.
  if (!$stmt = $mysqli->query($query)) {
 
    // Print failure to resolve query message.
    print $mysqli->error."<br />";
    print "Failed to resolve query ...<br />";
  }     
  else {
 
    // Fetch a SQL statement.
    $row = $stmt->fetch_row();
 
    // Close the statement.    
    $stmt->close();
 
    // True only when query returns a row.
    if (!is_null($row[0]) && ($row[0] > 0)) {
      $return = true; }
  }
 
  // Return the string.
  return $return;
}

The get_authorized_user PHP function gets and displays the table result from the authorized_user striped view:

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
function get_authorized_user($mysqli) {
 
  // Define return string.
  $out = '';
 
  // Declare a static query.
  $query = "SELECT au.user_id, au.user_name, au.user_role, au.full_name FROM authorized_user au" ;
 
  // Loop through a result set until completed.  
  do {
 
    // Attempt query and exit with failure before processing.
    if (!$stmt = $mysqli->query($query)) {
 
      // Print failure to resolve query message.
      print $mysqli->error."<br />";
      print "Failed to resolve query ...<br />";
    }     
    else {
 
      // Print the opening HTML table tag.
      $out .= '<table><tr><th class="ID">ID</th><th class="Label">User Name</th>'
      .       '<th class="Label">User Role</th><th class="Label">Full Name</th></tr>';
 
      // Fetch a row for processing.
      while( $row = $stmt->fetch_row() ) {
 
        // Print the opening HTML row tag.
        $out .= "<tr>";
 
        // Loop through the row's columns.
        for ($i = 0;$i < $mysqli->field_count;$i++) {
 
          // Handle column one differently.
          if ($i == 0)
            $out .= '<td class="ID">'.$row[$i]."</td>";
          else
            $out .= '<td class="Label">'.$row[$i]."</td>";
        }
 
        // Print the closing HTML row tag.
        $out .= "</tr>"; 
      }
    }
  } while( $mysqli->next_result());
 
  // Print the closing HTML table tag.
  $out .= "</table>"; 
 
  // Return an HTML table of the results.
  return $out;
}

This is the modified program using the functions:

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<style type="text/css">
  /* HTML element styles. */
  table {background:white;border-style:solid;border-width:3px;border-color:black;border-collapse:collapse;}
  th {text-align:center;font-style:bold;background:lightgray;border:solid 1px gray;}
  td {border:solid 1px gray;}
 
  /* Class tag element styles. */
  .ID {min-width:50px;text-align:right;}
  .Label {min-width:200px;text-align:left;}
</style>
</header>
<body>
<?php
 
  // Include the credentials file if omitted.
  include_once("striping.inc");
 
  // Process the input parameter, which should be through a $_POST argument.
  (isset($_GET['user_name'])) ? $input = $_GET['user_name'] : $input = '';
 
  // Assign credentials to connection.
  $mysqli = new mysqli("localhost", "student", "student", "studentdb");
 
  // Check for connection error and print message.
  if ($mysqli->connect_errno) {
    print $mysqli->connect_error."<br />";
    print "Connection not established ...<br />";
  }
  else {
 
    // After setting the session variable, get the striped view result. 
    if (set_login($mysqli,$input)) {
 
      // Print the authorized list.
      print get_authorized_user($mysqli);
 
    }
 
    // Release connection resource.
    $mysqli->close();
  }
?>
</script>
</body>
</html>

Hope this helps those implementing MySQL Striped tables.

Written by maclochlainn

July 16th, 2012 at 1:47 am

Posted in MySQL,PHP,Stored Procedures

Tagged with ,

5 Responses to 'PHP for MySQL Striped View'

Subscribe to comments with RSS or TrackBack to 'PHP for MySQL Striped View'.

  1. Hi Michael, this is a really cool idea of getting around the VIEW limitation on variable usage! Having a table “authorized_user” for the current user all the time comes in very handy, too. I’ve just set up a little playground to drag this idea even further and to improve it a bit:

    CREATE TABLE user (
      user_id int unsigned auto_increment key,
      user_name varchar(64) not null,
      user_group tinyint unsigned not null,
      key(user_name(16))
    );
     
    INSERT INTO user (user_name, user_group) values ('robert', 1), ('paul', 33), ('bryan', 24);
     
    -- improved set_login()
    CREATE FUNCTION set_login( name VARCHAR(20) )
      RETURNS INT UNSIGNED
    BEGIN
     
      IF @user_id IS NOT NULL THEN
        -- we're already auth'ed
        RETURN TRUE;
      END IF;
     
      SELECT user_id, user_group
      INTO @user_id, @user_group
      FROM user
      WHERE user_name = name
      LIMIT 1; -- please not the limit here
     
      IF @user_id IS NOT NULL THEN
        RETURN TRUE;
      END IF;
     
      RETURN FALSE;
     
    END$$
     
    -- getter
    CREATE FUNCTION get_user_id()
      RETURNS INT UNSIGNED
      RETURN @user_id;
     
    -- getter
    CREATE FUNCTION get_group_id()
      RETURNS INT UNSIGNED
      RETURN @user_id;
     
    -- Test
    DO set_login('robert');
    SELECT get_user_id(); -- Works!
     
    -- Workaround for another stupid view limitation
    CREATE VIEW user_id AS
      SELECT get_user_id() AS var_id;
     
    -- Improved view
    CREATE VIEW this_user AS
      SELECT A.*
      FROM user A
      JOIN user_id ON user_id=var_id  -- please note the join here, WHERE would slow down the app
      LIMIT 1; -- help the optimizer again

    As I have just started tinkering, I had another idea. I tried to reduce the initial call to set_login(), by merging it into the main query for the first call, but without success (kind of a race condition):

    SELECT A.*
    FROM this_user A
    JOIN (
      SELECT set_login('robert')
    ) B;

    Robert

    Robert Eisele

    17 Jul 12 at 10:10 am

  2. Awesome, and thanks!

    I also made some modifications to the example. They make it a more complete solution like the one I use in class.

    By the way, the idea came from trying to mimic Oracle’s user-level Virtual Private Database (VPD) methods. I wrote about that in PHP/Oracle database authentication article for Oracle’s Technical Network.

    maclochlainn

    17 Jul 12 at 12:30 pm

  3. I really like the idea of ​​a VPD in MySQL, especially if you build a user-centric application and have to send user-credentials over and over again. Well, that’s just the user ID from a session variable in most cases, but it improves the view of the application while developing (Even though at the cost of an additional JOIN). I’ve implemented something like the auth_user table with TEMPORARY tables, until now. But a view also reduces the copying and creating costs of such a table.

    I’ll read your article at Oracle’s Tech Network and try to abstract the idea for my “transparent query layer”, I’ve published a while before: http://www.xarg.org/2010/11/transparent-query-layer-for-mysql/

    Robert

    Robert Eisele

    18 Jul 12 at 1:52 am

  4. Updated the post with expanded code coverage that demonstrates how to integrate and leverage MySQL Stored Functions within PHP.

    maclochlainn

    7 Mar 13 at 12:37 am

  5. [...] speaking at UTOUG on PHP and MySQL Striped Views [...]

Leave a Reply