In the following example a user named APP_USER should be able to read all of the columns from the EMPLOYEE table except for the column EMP_SSN which stored the employee’s social security number.
yugabyte=# CREATE TABLE employee (emp_id INT PRIMARY KEY, emp_name VARCHAR, emp_ssn INT);
CREATE TABLE
yugabyte=# INSERT INTO employee VALUES (1, 'Jim', '123456789');
INSERT 0 1
yugabyte=# INSERT INTO employee VALUES (2, 'Jane', '987654321');
INSERT 0 1
yugabyte=# INSERT INTO employee VALUES (3, 'Lucy', '121212121');
INSERT 0 1
yugabyte=# CREATE USER app_user WITH LOGIN;
CREATE ROLE
yugabyte=# GRANT SELECT ON employee TO app_user;
GRANT
yugabyte=# \! ysqlsh -U app_user -c "SELECT * FROM employee;"
emp_id | emp_name | emp_ssn
--------+----------+-----------
1 | Jim | 123456789
2 | Jane | 987654321
3 | Lucy | 121212121
(3 rows)
Having just the open ended GRANT SELECT statement, APP_USER is able to access the EMP_SSN column.
We’ll need to be more specific with the GRANT to get the results we want.
yugabyte=# REVOKE SELECT ON employee FROM app_user;
REVOKE
yugabyte=# GRANT SELECT (emp_id, emp_name) ON employee TO app_user;
GRANT
yugabyte=# \! ysqlsh -U app_user -c "SELECT * FROM employee ORDER BY emp_id;"
ERROR: permission denied for table employee
yugabyte=# \! ysqlsh -U app_user -c "SELECT emp_id, emp_name FROM employee ORDER BY emp_id;"
emp_id | emp_name
--------+----------
1 | Jim
2 | Jane
3 | Lucy
(3 rows)
Now when APP_USER attempts to read from the EMP_SSN column it receives an error.
Have Fun!
