Example: Using Views For Row-Level Security

This question made me realize just how lucky I've been for several years, not having to deal with any Security Schemas Gone Wild!

Security is one of those areas of life that knows no physical bounds. People can eat only so much food, travel to only one place at a time, and are generally limited in their activities when those activities are based on real-world resources.

(even multitasking nutbars have their limits)

Security is an entirely different matter, especially computer system security: There is no limit to the rules and regulations that can be imposed by a database schema gone crazy... or if there is a limit, it is far beyond the point where administrators, end users and developers have been driven 'round the bend.

Examples? You want examples? ...not from me, I don't want anyone to get ideas.

In fact, I'm have second thoughts about this article, but here goes...

---

Here's the question: How do I provide insert, update, delete and select access to all tables and columns, to all users, but limit each user to a specific subset of rows in each table?

In other words, how do I provide row-level security?

Let's start with a simple base table that contains a primary key, some data columns plus a special "access_code" column that will be used to implement row-level security:

CREATE TABLE base_table (
   pkey             INTEGER NOT NULL PRIMARY KEY,
   access_code      INTEGER NOT NULL,
   data1            INTEGER NOT NULL,
   data2            INTEGER NOT NULL,
   data3            INTEGER NOT NULL );

INSERT base_table VALUES ( 1, 101, 0, 0, 0 );
INSERT base_table VALUES ( 2, 101, 0, 0, 0 );
INSERT base_table VALUES ( 3, 102, 0, 0, 0 );
INSERT base_table VALUES ( 4, 102, 0, 0, 0 );
INSERT base_table VALUES ( 5, 103, 0, 0, 0 );
INSERT base_table VALUES ( 6, 103, 0, 0, 0 );
COMMIT;

Here's a CREATE VIEW statement for a view through which end users have access to base_table data on a row-by-row basis:

CREATE VIEW view_base_table
AS SELECT base_table.pkey,
          base_table.access_code,
          base_table.data1, 
          base_table.data2, 
          base_table.data3 
     FROM base_table
    WHERE base_table.access_code = @access_code 
WITH CHECK OPTION;

The CREATE VIEW ... SELECT on lines 1 through 7 sets up the basics: a view which looks exactly the same as the underlying base table. The "looks exactly the same" part can be important when it comes to updating base_table via the view... more on that later.

The WHERE clause on line 8 implements row-level access control: the end user can only see base_table rows that have a access_code value that matches what's in the @access_code variable.

(and where does @access_code come from? more on that later, too)

The WITH CHECK OPTION lets your end users INSERT, UPDATE and DELETE data via the view without affecting any base_table rows that a SELECT on the view wouldn't return in the first place; in other words, the WHERE clause is applied to the data that's being inserted, updated or deleted.

Creating an updatable view is a bit more difficult that a SELECT-only view. Most of the rules for updatable views are described in the Help, but not all the rules: A view can only be INSERT-able and DELETE-able if there is only one table in the FROM clause, no matter what the ansi_update_constraints option is set to. Of course, that's no problem here, the access control predicate is coded in a WHERE clause rather than an INNER JOIN ON clause.

(but it can become a problem, later on... that one-table-FROM-clause rule)

There is also a common-sense rule that an updatable view SELECT must contain all the columns required to code the INSERT, UPDATE or DELETE you actually want. For example, the view SELECT should probably include the underlying primary key columns for an INSERT, and for UPDATE and DELETE statements as well... unless you want to update/delete all the rows. That's why "looks exactly the same" as the base tables can make these views a lot easier to deal with.

---

Here's where the @access_code variable comes from, it's a connection-level variable that is created and assigned a value depending on the user id, whenever that user id connects to the database:

CREATE PROCEDURE DBA.connection_start() 
BEGIN
   CREATE VARIABLE @access_code INTEGER;
   SET @access_code = CASE USER_NAME()
                         WHEN 'user_a' THEN 101
                         WHEN 'user_b' THEN 102
                         WHEN 'user_c' THEN 102
                         ELSE 0
                      END CASE;
END; 

SET OPTION PUBLIC.login_procedure = 'DBA.connection_start';

Connection-level variables like @access_code are visible to all the SQL run on the connection, including every reference to view_base_table from anywhere in the application or any stored procedure or trigger... plus, each connection has its own copy of @access_code that is inaccessible (invisible, in fact) to SQL code running on other connections.

For more information see the CREATE VARIABLE statement and the login_procedure option.

---

Here are the GRANT statements that set up three different users with access to the view:

• The GRANT CONNECT and GRANT GROUP statements on lines 1 and 2 create a user group called "usergroup"... this is optional, but it does make administration easier when you have a lot of tables and a lot of users.
  

• The GRANT GROUP and GRANT MEMBERSHIP on lines 4 and 5 provide name visibility on all the objects created by DBA to all the members of usergoup. Name visibility simply means you can refer to table t instead of having to code DBA.t; it doesn't actually give you the right to do anything useful like INSERT or SELECT, that's done separately. And being a member of the group called DBA doesn't confer any special powers: the DBA privilege is not inherited through group membership (whew!)
  

• The GRANT EXECUTE on statement 7 lets everyone execute the connection_start procedure when they log in.
  

• The GRANT statement on line 9 sets up each member of usergroup with all the necessary privileges on the view. This statement is the reason usergroup exists: Only one such GRANT is required for each view, not one GRANT for each view for each user... on a large system that brings the number of GRANT statements down to the hundreds from the thousands.
  

• The three pairs of GRANT statements on lines 11 through 18 are all that is required to set up three users with (1) the ability to connect, (2) name visibility to all the stuff DBA owns and (3) INSERT, UPDATE, DELETE and SELECT privileges on the view.
  

GRANT CONNECT TO usergroup;
GRANT GROUP TO usergroup;

GRANT GROUP TO DBA;
GRANT MEMBERSHIP IN GROUP DBA TO usergroup;

GRANT EXECUTE ON connection_start TO usergroup;

GRANT INSERT, UPDATE, DELETE, SELECT ON view_base_table TO usergroup;

GRANT CONNECT TO user_a IDENTIFIED BY SQL;
GRANT MEMBERSHIP IN GROUP usergroup TO user_a;

GRANT CONNECT TO user_b IDENTIFIED BY SQL;
GRANT MEMBERSHIP IN GROUP usergroup TO user_b;

GRANT CONNECT TO user_c IDENTIFIED BY SQL;
GRANT MEMBERSHIP IN GROUP usergroup TO user_c;

Here's what works...

Here are some INSERT, UPDATE, DELETE and SELECT statements that work OK for user_a:

• the two INSERTs work OK because they both set the access_code to 101,
  

• the UPDATE works OK because the access_code for row 7 is 101,
  

• the DELETE works OK because the access_code for row 8 is 101, and
  

• the SELECT returns only the rows that have access_code = 101.
  

-- user_a
INSERT view_base_table VALUES ( 7, @access_code, 0, 0, 0 );
INSERT view_base_table VALUES ( 8, @access_code, 0, 0, 0 );
UPDATE view_base_table SET data1 = 555 WHERE pkey = 7; 
DELETE view_base_table WHERE pkey = 8;    
COMMIT;
SELECT * FROM view_base_table ORDER BY pkey;           

pkey,access_code,data1,data2,data3
1,101,0,0,0
2,101,0,0,0
7,101,555,0,0

Here's the same test for user_b; similar results, different rows:

-- user_b
INSERT view_base_table VALUES ( 9,  @access_code, 0, 0, 0 );
INSERT view_base_table VALUES ( 10, @access_code, 0, 0, 0 );
UPDATE view_base_table SET data1 = 666 WHERE pkey = 9; 
DELETE view_base_table WHERE pkey = 10;    
COMMIT;
SELECT * FROM view_base_table ORDER BY pkey;           

pkey,access_code,data1,data2,data3
3,102,0,0,0
4,102,0,0,0
9,102,666,0,0

Here's the same test for user_c...

-- user_c
INSERT view_base_table VALUES ( 11, @access_code, 0, 0, 0 );
INSERT view_base_table VALUES ( 12, @access_code, 0, 0, 0 );
UPDATE view_base_table SET data1 = 777 WHERE pkey = 11; 
DELETE view_base_table WHERE pkey = 12;    
COMMIT;
SELECT * FROM view_base_table ORDER BY pkey;           

...but this time, the SELECT displays something different; what is it?



Here's a clue, the INSERT, UPDATE and DELETE statements all work, but the SELECT result set is still different...



Here's the answer: the SELECT displays 4 rows instead of three because both user_b and user_a have @access_code set to 102!

pkey,access_code,data1,data2,data3
3,102,0,0,0
4,102,0,0,0
9,102,666,0,0
11,102,777,0,0

For the record here's what the underlying base_table looks like from the DBA's point of view:

-- DBA
SELECT * FROM base_table ORDER BY pkey;           

pkey,access_code,data1,data2,data3
1,101,0,0,0
2,101,0,0,0
3,102,0,0,0
4,102,0,0,0
5,103,0,0,0
6,103,0,0,0
7,101,555,0,0
9,102,666,0,0
11,102,777,0,0

Here's what doesn't work...

Here's what happens when an end user tries to use the base table: Permission denied.

-- user_a
SELECT * FROM base_table ORDER BY pkey;           

Permission denied: you do not have permission to select from "base_table"
SQLCODE=-121, ODBC 3 State="42000"

Here's what happens when an end user tries to insert a row with a different access code: SQLCODE -632.

-- user_a
INSERT view_base_table VALUES ( 13, 102, 0, 0, 0 );

WITH CHECK OPTION violated for insert/update on base table 'base_table'
SQLCODE=-632, ODBC 3 State="44000"

When a user tries to UPDATE a row with a different access code, it fails silently with SQLCODE 100:

-- user_b
UPDATE view_base_table SET data1 = 888 WHERE pkey = 1; 
SELECT SQLCODE;

sqlcode
100

Same thing when trying to DELETE someone else's row: silent failure, SQLCODE 100.

-- user_c
DELETE view_base_table WHERE pkey = 2;    
SELECT SQLCODE;

sqlcode
100

So what's all the fuss about?

The problem with row-level security is that CREATE VARIABLE is a gateway drug that only satisfies security cravings for a very short time... the next step is CREATE TABLE, followed by many-to-many relationships among multiple security tables implementing hierarchical access rules... by department, by job description, by time-of-day, by range-of-dollar-value... bwaaa ha ha ha!

Eventually, each single-table CREATE VIEW involves hidden joins to several other tables, and every five-way join among views becomes a performance nightmare involving twenty, thirty or more tables.

Why won't CREATE VARIABLE satisfy?

Because each user gets only one access code, thus limiting the relationship between users and rows, not to mention the different security relationships between one user and several tables.

But never mind that, consider this loophole: There's nothing stopping a user with an adhoc reporting tool from changing @access_code on the fly:

-- user_a
SET @access_code = 102;
SELECT * FROM view_base_table ORDER BY pkey;           

pkey,access_code,data1,data2,data3
3,102,0,0,0
4,102,0,0,0
9,102,666,0,0
11,102,777,0,0

So no, CREATE VARIABLE makes a nice demo but the security high doesn't last long at all.

Moving on, from crack cocaine to CREATE TABLE

Here's a workaround to close the CREATE VARIABLE loophole. It involves a single-row connection-level table to replace the CREATE VARIABLE, plus modifications to the CREATE VIEW and connection_start procedures:

CREATE GLOBAL TEMPORARY TABLE connection_settings (
   one_row_per_connection    INTEGER NOT NULL DEFAULT 1 CHECK ( one_row_per_connection = 1 ),
   access_code               INTEGER NOT NULL DEFAULT 0,
   PRIMARY KEY ( one_row_per_connection ) ) 
   NOT TRANSACTIONAL;

CREATE VIEW view_base_table
AS SELECT base_table.pkey,
          base_table.data1, 
          base_table.data2, 
          base_table.data3 
     FROM base_table
    WHERE EXISTS ( SELECT * 
                     FROM connection_settings
                    WHERE connection_settings.access_code = base_table.access_code )
WITH CHECK OPTION;

CREATE PROCEDURE DBA.connection_start() 
BEGIN
   INSERT connection_settings 
   VALUES ( DEFAULT, 
            CASE USER_NAME()
               WHEN 'user_a' THEN 101
               WHEN 'user_b' THEN 102
               WHEN 'user_c' THEN 102
            END CASE );
END; 

SET OPTION PUBLIC.login_procedure = 'DBA.connection_start';

The end user is now prevented from changing the access code assigned in the connection_start procedure because the user doesn't have permission to UPDATE the connection_settings table.

Thus it starts: a security table is introduced, and the view WHERE clause introduces an implicit join with a second table... just to close one loophole. Not to increase flexibility in setting up security rules, for that you need more columns, more rows, more tables, more joins...

---