Now, speaking of inserts: this is where it gets sticky. There is an easy solution (very simple triggers), the problem is DB support. I have another stack exchange question right now, trying to finalize the issue, but as it looks right now: there is an old 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 (& claimed to be fixed in MariaDB v10.1.6, but it doesn’t appear to be). If you use MySQL v5.7+, or any other DB which complies with the SQL standard, and runs triggers before checking NULL constraints, you’ll be fine.

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);
//
-- (Uncomment) 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]
//

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.

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]
//

1. Create a session variable which holds the accountGroupId. Spring @Transactional & Hibernate isolate DB sessions on a per thread basis, 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.

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.