SQL - Restricted view for transient (stateless) user

Question

I’ve been looking for a solution to this for a few days now, but found nothing concrete. I’m not a DBA, so forgive me if this is a trivial question. The short of it is: I need to create a restricted view, which filters rows by user, without maintaining a unique user DB connection.

---

I’m building a stateless webapp using JWT token authentication (so I can store some kind of DB session id in the token, if that will help). The user is allowed to hit any server, and switch servers randomly (via automated load balancing). Since the web servers do not maintain any session state (the browser uploads the token on every request), I cannot maintain a unique DB connection for a given user. I am also going through JPA/Hibernate, so that will probably also limit what I’m capable of doing; although if necessary, Hibernate does allow native SQL.

I need to add an additional layer of data security in the DB, for row-level access restriction by user account. I was planning to add a column to all my tables containing a UUID accountID. Then, any record created by a user, has their accountID stored in that row, alongside their data. Thus, all their rows in all tables would be marked by their accountID. Then create a restricted view which filters by this accountID column, and then all users would only ever be able to see rows they, themselves created. (Eventually, I will also be having 2+ different applications (each with multiple servers) hitting the same tables through different variants of these restricted views; not sure if that matters).

---

Is it possible to create a restricted view which takes in a parameter? And, can that be done with JPA/Hibernate?

I found this question: https://stackoverflow.com/questions/2281890/can-i-create-view-with-parameter-in-mysql, which uses what appears to be a hack to get a view to take in a parameter, but I’m worried about concurrent accesses to the view, and parameter overlap. I.e. if multiple users hit the same view at the same time, each passing their own parameters, isn’t there a danger of those overwriting each other and bleeding through?

And this one: View with parameter IN, which uses a Stored Proc to generate a dynamic SQL query.

I also saw this one: https://stackoverflow.com/questions/4498364/create-parameterized-view-in-sql-server-2008, which talks about using “an inline table-valued function”, but I don’t quite understand the difference between his two solution examples, and it’s speaking specifically about SQL Server and I’m not sure if MySQL supports that, or how I would even begin to access that with JPA.

---

It is not sufficient to simply add a where clause to all my queries, since this would produce a very fragile restriction. If any of my queries forget to add this restriction, they would be able to see everything. However, by forcing a restricted view, it forced the DB to always check that column. If any query forgets to add that parameter, the DB should either explode & throw an error, or treat that parameter as null & use null for the column checks. Since the accountID column in all tables does not allow NULL values, this would result in a safe failure of simply returning an empty result with zero matches.

I looked into using Stored Procedures, but if that is the only solution, that would mean that everything in my app would be going through stored procedures (selects, updates, deletes, etc. on all tables) which would drastically increase the amount of code necessary to write. At least that’s how it appears to me, but I’m no expert on stored procs (I’ve never used them).

Right now, I’m committed to MySql Cluster (NDB), but if absolutely necessary, I could look into switching to another vendor in order to accomplish this security measure.

Example tables

Users are grouped together into account groups. In the below example, there are 4 users divided into 2 accountGroups (2 & 2). Each user should be able to see all records created by any user in their account group, and NOT be able to see any records created by users in other account groups. In the views, I've hard-coded an accountGroupId of 101, but that's the parameter I want to pass in (101, 202, etc.). All users share DB connections; there is nothing user-specific about the DB connections, because the servers are stateless (i.e. no session).

What I need to be able to do, is query a restricted view using a generic, shared DB connection, and pass in the accountGroupId to filter on.

CREATE TABLE user (
  id int(11) NOT NULL,
  username varchar(50) NOT NULL,
  passwordHash varchar(150) NOT NULL,
  accountGroupId int(11) NOT NULL
);

INSERT INTO user (id, username, passwordHash, accountGroupId) VALUES
(1, 'John',   '<A_HASHED_PASSWORD>', 101),
(2, 'George', '<A_HASHED_PASSWORD>', 101),
(3, 'Sarah',  '<A_HASHED_PASSWORD>', 202),
(4, 'Mary',   '<A_HASHED_PASSWORD>', 202);

-- --------------------------------------------------------

CREATE TABLE privateNotes (
  id int(11) NOT NULL,
  userId int(11) NOT NULL,
  text varchar(500) NOT NULL,
  accountGroupId int(11) NOT NULL
);

INSERT INTO privateNotes (id, userId, text, accountGroupId) VALUES
(30, 1, '[John] A Secret Message',         101),
(31, 1, '[John] An Embarassing Message',   101),
(32, 2, '[George] A Secret Message',       101),
(33, 2, '[George] An Embarassing Message', 101),
(34, 3, '[Sarah] A Secret Message',        202),
(35, 3, '[Sarah] An Embarassing Message',  202),
(36, 4, '[Mary] A Secret Message',         202),
(37, 4, '[Mary] An Embarassing Message',   202);

-- --------------------------------------------------------
-- Views

-- My Users
CREATE VIEW myUsers AS
SELECT
  user.id AS id,
  user.username AS username,
  user.passwordHash AS passwordHash
FROM
  user
WHERE
  user.accountGroupId = 101; /* 101, 202, etc. */

-- My Private Notes
CREATE VIEW myPrivateNotes AS
SELECT
  privateNotes.id AS id,
  privateNotes.userId AS userId,
  privateNotes.text AS text
FROM
  privateNotes
WHERE
  privateNotes.accountGroupId = 101; /* 101, 202, etc. */

-- --------------------------------------------------------
-- ------- --
--  TESTS  --
-- ------- --

-- View Select Test
select * from myUsers;
select * from myPrivateNotes;

Answer 1

I am not sure that MySQL can handle this in an easy way. At least, it's not part of the standard implementation of MySQL nor MariaDB. You can check Protect Your Data: Row-level Security in MariaDB 10.0 for a solution involving stored procedures. I guess it can be applied to not only MariaDB, but also to MySQL.

---

If you have the option, you can check PostgreSQL and how you can define Row Level Policies. These policies align very well with your needs; and might provide a much better match.

You can see on this part documentation one use-case which matches yours:

To allow all users to access their own row in a users table, a simple policy can be used:

CREATE POLICY user_policy ON users
    USING (user_name = current_user);

(user_name would be equivalent to your accountID).

Although this is not a full answer, I hope it helps.

Answer 2

Ok, so, thanks to @joanolo I’ve got a pretty good solution.

1. Create a session variable which holds the accountGroupId. Spring @Transactional & Hibernate isolate DB sessions on a per request/thread basis (in Spring requests & threads are 1-to-1; i.e. 1 isolated thread per request), so there’s no risk of concurrent parameter bleed-through.

2. Create DB stored functions which take in a value and store it in the session variable. Although, if you wanted to, you could just run an update query SET @mySessionVar = 101; instead of creating a set() function, but you MUST have a get function. MySQL won't let you put a session variable in a view definition.

3. Create restricted views which compare against the session var in the where clause. Since this variable is an account-wide single value (& won’t be changing from select to select within the same user), this works fine. Each request/user will have their own DB session & their own instance of the session var; all completely isolated from each other. They just call the set() function to initialize their session var before running their queries / inserts, and everything works perfectly.

Now, speaking of inserts: this is where it gets sticky. There is an easy solution (very simple triggers), the problem is DB support. There is a bug in MySQL v5.6 (& in MariaDB) where they were checking NULL constraints before running triggers. This prevented using triggers to populate these fields. However, this bug was fixed in MySQL v5.7 (& MariaDB v10.1.21 & v10.2.4). If you use MySQL v5.7+ (MariaDB v10.1.21+ / v10.2.4+), or any other DB which complies with the SQL standard, and runs triggers before checking NULL constraints, you’ll be fine.

Here’s a simple code example implementing the fix, and I also made a SQL Fiddle:

CREATE TABLE privateNotes (
  id int(11) NOT NULL,
  userId int(11) NOT NULL,
  text varchar(500) NOT NULL,
  groupId int(11) NOT NULL
);

INSERT INTO privateNotes (id, userId, text, groupId) VALUES
(30, 1, '[John] A Secret Message',         101),
(32, 2, '[George] A Secret Message',       101),
(34, 3, '[Sarah] A Secret Message',        202),
(36, 4, '[Mary] A Secret Message',         202);

-- --------------------------------------------------------

-- Get
CREATE FUNCTION getCurrentGroupId()
    RETURNS int(11)
    NO SQL
    BEGIN
      RETURN @currentGroupId;
    END //

-- Set
CREATE FUNCTION setCurrentGroupId(groupId_in int(11))
    RETURNS int(11)
    NO SQL
    BEGIN
      SET @currentGroupId = groupId_in;
      RETURN @currentGroupId;
    END //

-- --------------------------------------------------------

CREATE VIEW myPrivateNotes AS
SELECT
  privateNotes.id AS id,
  privateNotes.userId AS userId,
  privateNotes.text AS text
FROM
  privateNotes
WHERE
  privateNotes.groupId = getCurrentGroupId(); /* 101, 202, etc. */

-- --------------------------------------------------------
-- Trigger

CREATE TRIGGER before_insert_privateNotes
    BEFORE INSERT ON privateNotes
    FOR EACH ROW BEGIN
        IF NEW.groupId IS NULL THEN
            SET NEW.groupId = getCurrentGroupId();
        END IF;
    END //

-- --------------------------------------------------------
-- ------- --
--  TESTS  -- To see resultSets, run selects in the SQL box over there ->
-- ------- -- Can't run inserts over there ->

-- View Insert Test
SELECT setCurrentGroupId(999);
//
-- Bug in MySQL fixed in v5.7 & in MariaDB v10.1.21 / v10.2.4
-- Bug causes -> Error: Field 'groupId' doesn't have a default value
INSERT INTO privateNotes (id, userId, text) VALUES (50, 51, 'a cool message');
//

-- View Select Tests
-- 
-- Copy the full chunk bellow to the Sql box over there ->
-- Don't forget to change the delimeter to: [//]
SELECT setCurrentGroupId(NULL);
//
SELECT * from myPrivateNotes; -- Returns Zero Results [Correct]
//

SELECT setCurrentGroupId(101);
//
SELECT * from myPrivateNotes; -- Returns Valid Results [Correct]
//