--source include/not_embedded.inc

--echo #
--echo # Column-level DENY privilege test
--echo #

CREATE DATABASE deny_col_db;
CREATE TABLE deny_col_db.sensitive_data (
  id INT,
  public_info VARCHAR(50),
  salary DECIMAL(10,2),
  ssn VARCHAR(11),
  address VARCHAR(100)
);

INSERT INTO deny_col_db.sensitive_data VALUES
  (1, 'John Doe', 75000.00, '123-45-6789', '123 Main St'),
  (2, 'Jane Smith', 85000.00, '987-65-4321', '456 Oak Ave');

CREATE USER col_user1@localhost;
CREATE USER col_user2@localhost;

--echo #
--echo # Basic column-level DENY
--echo #

GRANT SELECT ON deny_col_db.sensitive_data TO col_user1@localhost;
DENY SELECT (salary, ssn) ON deny_col_db.sensitive_data TO col_user1@localhost;

connect (con1, localhost, col_user1,,deny_col_db);
SELECT id, public_info FROM sensitive_data;
--error ER_COLUMNACCESS_DENIED_ERROR
SELECT salary FROM sensitive_data;
--error ER_COLUMNACCESS_DENIED_ERROR
SELECT ssn FROM sensitive_data;
--error ER_COLUMNACCESS_DENIED_ERROR
SELECT * FROM sensitive_data;
--error ER_COLUMNACCESS_DENIED_ERROR
SELECT id, salary FROM sensitive_data;
disconnect con1;
connection default;

REVOKE SELECT ON deny_col_db.sensitive_data FROM col_user1@localhost;
REVOKE DENY SELECT (salary, ssn) ON deny_col_db.sensitive_data FROM col_user1@localhost;

--echo #
--echo # Column DENY takes precedence over explicit column GRANT
--echo #

GRANT SELECT (salary,id,public_info) ON deny_col_db.sensitive_data TO col_user1@localhost;
DENY SELECT (salary) ON deny_col_db.sensitive_data TO col_user1@localhost;
connect (con3, localhost, col_user1,,deny_col_db);
SELECT id, public_info FROM sensitive_data;
--error ER_COLUMNACCESS_DENIED_ERROR
SELECT salary FROM sensitive_data;
disconnect con3;
connection default;
REVOKE SELECT (salary,id,public_info) ON deny_col_db.sensitive_data FROM col_user1@localhost;
REVOKE DENY SELECT (salary) ON deny_col_db.sensitive_data FROM col_user1@localhost;

--echo #
--echo # REVOKE DENY on a column
--echo #
GRANT SELECT (salary) ON deny_col_db.sensitive_data TO col_user1@localhost;
DENY SELECT (salary,ssn) ON deny_col_db.sensitive_data TO col_user1@localhost;
REVOKE DENY SELECT (salary) ON deny_col_db.sensitive_data FROM col_user1@localhost;

connect (con4, localhost, col_user1,,deny_col_db);
SELECT salary FROM sensitive_data;
--error ER_COLUMNACCESS_DENIED_ERROR
SELECT ssn FROM sensitive_data;
disconnect con4;
connection default;
REVOKE DENY SELECT (ssn) ON deny_col_db.sensitive_data FROM  col_user1@localhost;

--echo #
--echo # Multiple column DENYs
--echo #
GRANT SELECT ON deny_col_db.sensitive_data TO col_user2@localhost;
DENY SELECT (salary, ssn, address) ON deny_col_db.sensitive_data TO col_user2@localhost;

connect (con5, localhost, col_user2,,deny_col_db);
SELECT id, public_info FROM sensitive_data;
--error ER_COLUMNACCESS_DENIED_ERROR
SELECT salary FROM sensitive_data;
--error ER_COLUMNACCESS_DENIED_ERROR
SELECT address FROM sensitive_data;
--error ER_COLUMNACCESS_DENIED_ERROR
SELECT * FROM sensitive_data;
disconnect con5;
connection default;
REVOKE DENY SELECT (salary, ssn, address) ON deny_col_db.sensitive_data FROM col_user2@localhost;
REVOKE SELECT ON deny_col_db.sensitive_data FROM col_user2@localhost;

--echo #
--echo # Column DENY with INSERT/UPDATE operations
--echo #

CREATE TABLE deny_col_db.audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  action VARCHAR(50),
  modified_by VARCHAR(50),
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

GRANT SELECT, INSERT ON deny_col_db.audit_log TO col_user1@localhost;
DENY INSERT (modified_by) ON deny_col_db.audit_log TO col_user1@localhost;

connect (con8, localhost, col_user1,,deny_col_db);
INSERT INTO audit_log (action) VALUES ('test_action');
--error ER_COLUMNACCESS_DENIED_ERROR
INSERT INTO audit_log (action, modified_by) VALUES ('test', 'hacker');
--replace_column 4 TIMESTAMP
SELECT * FROM audit_log;
disconnect con8;
connection default;

--echo #
--echo # Column DENY with UPDATE
--echo #

GRANT UPDATE ON deny_col_db.sensitive_data TO col_user1@localhost;
DENY UPDATE (salary) ON deny_col_db.sensitive_data TO col_user1@localhost;
GRANT SELECT(id,public_info) on deny_col_db.sensitive_data TO col_user1@localhost;

connect (con9, localhost, col_user1,,deny_col_db);
UPDATE sensitive_data SET public_info = 'Updated' WHERE id = 1;
--error ER_COLUMNACCESS_DENIED_ERROR
UPDATE sensitive_data SET salary = 100000 WHERE id = 1;
--error ER_COLUMNACCESS_DENIED_ERROR
UPDATE sensitive_data SET public_info = 'X', salary = 90000 WHERE id = 2;
disconnect con9;
connection default;
REVOKE SELECT (id,public_info) ON deny_col_db.sensitive_data FROM col_user1@localhost;

--echo #
--echo # Table-level DENY SELECT: UPDATE without WHERE must succeed (no read needed)
--echo #

DENY SELECT ON deny_col_db.sensitive_data TO col_user1@localhost;

connect (con9b, localhost, col_user1,,deny_col_db);
--echo # UPDATE with WHERE reads id -> SELECT needed -> DENY fires
--error ER_COLUMNACCESS_DENIED_ERROR
UPDATE sensitive_data SET public_info = 'ps_test' WHERE id = 1;
--echo # UPDATE without WHERE: no SELECT needed, must succeed
UPDATE sensitive_data SET public_info = 'ps_test';
disconnect con9b;
connection default;

REVOKE DENY SELECT ON deny_col_db.sensitive_data FROM col_user1@localhost;
REVOKE UPDATE ON deny_col_db.sensitive_data FROM col_user1@localhost;

--echo #
--echo # Column DENY is case-insensitive
--echo #

GRANT SELECT ON deny_col_db.sensitive_data TO col_user1@localhost;
DENY SELECT (SaLaRy) ON deny_col_db.sensitive_data TO col_user1@localhost;

connect (con10, localhost, col_user1,,deny_col_db);
--error ER_COLUMNACCESS_DENIED_ERROR
SELECT salary FROM sensitive_data;
disconnect con10;
connection default;

REVOKE DENY SELECT (SaLaRy) ON deny_col_db.sensitive_data FROM col_user1@localhost;
REVOKE SELECT ON deny_col_db.sensitive_data FROM col_user1@localhost;

--echo #
--echo # Cleanup
--echo #

DROP USER col_user1@localhost;
DROP USER col_user2@localhost;
DROP DATABASE deny_col_db;

--echo #
--echo # End of column-level DENY tests
--echo #