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 webservers 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: https://dba.stackexchange.com/questions/107342/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 vender in order to accomplish this security measure.
Thanks for your help,
-Yurelle
**Update:**
Sorry, yea, I meant MySQL Cluster NDB. Added clarification.
----------
**Here are some 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.
--
-- Database: stackoverflowexamples
--
-- --------------------------------------------------------
--
-- Table structure for table user
--
CREATE TABLE user (
id int(11) NOT NULL,
username varchar(50) NOT NULL,
passwordHash varchar(150) NOT NULL,
accountGroupId int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Data for table user
--
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);
-- --------------------------------------------------------
--
-- Table structure for table privateNotes
--
CREATE TABLE privateNotes (
id int(11) NOT NULL,
userId int(11) NOT NULL,
text varchar(500) NOT NULL,
accountGroupId int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Data for table privateNotes
--
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);
-- --------------------------------------------------------
--
-- Table structure for table privatePurchases
--
CREATE TABLE privatePurchases (
id int(11) NOT NULL,
userId int(11) NOT NULL,
itemName varchar(150) NOT NULL,
cost int(11) NOT NULL,
accountGroupId int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Data for table privatePurchases
--
INSERT INTO privatePurchases (id, userId, itemName, cost, accountGroupId) VALUES
(20, 1, '[John] Something Secret', 123, 101),
(21, 1, '[John] Something Embarrassing', 321, 101),
(22, 2, '[George] Something Secret', 123, 101),
(23, 2, '[George] Something Embarrassing', 321, 101),
(24, 3, '[Sarah] Something Secret', 123, 202),
(25, 3, '[Sarah] Something Embarrassing', 321, 202),
(26, 4, '[Mary] Something Secret', 123, 202),
(27, 4, '[Mary] Something Embarrassing', 321, 202);
-- --------------------------------------------------------
--
-- Structure for view myPrivateNotes
--
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW myPrivateNotes AS
SELECT
privateNotes.id AS id,
privateNotes.userId AS userId,
privateNotes.text AS text,
privateNotes.accountGroupId AS accountGroupId
FROM
privateNotes
WHERE
(
privateNotes.accountGroupId = 101 /* @accountGroupId */
);
-- --------------------------------------------------------
--
-- Structure for view myPrivatePurchases
--
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW myPrivatePurchases AS
SELECT
privatePurchases.id AS id,
privatePurchases.userId AS userId,
privatePurchases.itemName AS itemName,
privatePurchases.cost AS cost,
privatePurchases.accountGroupId AS accountGroupId
FROM
privatePurchases
WHERE
(
privatePurchases.accountGroupId = 101 /* @accountGroupId */
);
-- --------------------------------------------------------
--
-- Structure for view myUsers
--
CREATE ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW myUsers AS
SELECT
user.id AS id,
user.username AS username,
user.passwordHash AS passwordHash,
user.accountGroupId AS accountGroupId
FROM
user
WHERE
(
user.accountGroupId = 101 /* @accountGroupId */
);
--
-- Indexes for table privatenotes
--
ALTER TABLE privatenotes
ADD PRIMARY KEY (id),
ADD UNIQUE KEY id (id);
--
-- Indexes for table privatepurchases
--
ALTER TABLE privatepurchases
ADD PRIMARY KEY (id),
ADD UNIQUE KEY id (id);
--
-- Indexes for table user
--
ALTER TABLE user
ADD PRIMARY KEY (id),
ADD UNIQUE KEY id (id),
ADD UNIQUE KEY username (username);