Return to Question

--
-- 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);

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;

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 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.

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:

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 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.

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

Right now, I’m committed to MySql cluster, but if absolutely necessary, I could look into switching to another vender in order to accomplish this security measure.

-Yurelle

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.

-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);