Mysql – SQL – Restricted view for transient (stateless) user

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;