docs/content/v2024.2/secure/authorization/column-level-security.md
Column level security in YugabyteDB is used to restrict the users to view a particular column or set of columns in a table. The simplest way to achieve column-level security in YugabyteDB is to create a view that includes only the columns that the user needs to have access to.
The steps below show how to enable column-level security using CREATE VIEW command.
Open the YSQL shell (ysqlsh), specifying the yugabyte user and prompting for the password.
./ysqlsh -U yugabyte -W
When prompted for the password, enter the yugabyte password. You should be able to log in and see a response similar to the following:
ysqlsh (11.2-YB-{{<yb-version version="v2024.2">}}-b0)
Type "help" for help.
yugabyte=#
Create an employee table and insert a few sample rows.
create table employees ( empno int, ename text,
address text, salary int, account_number text );
insert into employees values (1, 'joe', '56 grove st', 20000, 'AC-22001' );
insert into employees values (2, 'mike', '129 81 st', 80000, 'AC-48901' );
insert into employees values (3, 'julia', '1 finite loop', 40000, 'AC-77051');
select * from employees;
empno | ename | address | salary | account_number
-------+-------+---------------+--------+----------------
1 | Joe | 56 grove st | 20000 | AC-22001
2 | Mike | 129 81 st | 80000 | AC-48901
3 | Julia | 1 finite loop | 40000 | AC-77051
(3 rows)
ybadmin userCreate a user ybadmin and provide all privileges on the table to ybadmin user.
\c yugabyte yugabyte;
create user ybadmin;
GRANT ALL PRIVILEGES ON employees TO ybadmin;
Connect to the database with the ybadmin user.
\c yugabyte ybadmin;
User ybadmin has access to view all the contents of the table.
select current_user;
current_user
--------------
ybadmin
(1 row)
select * from employees;
empno | ename | address | salary | account_number
-------+-------+---------------+--------+----------------
1 | joe | 56 grove st | 20000 | AC-22001
3 | julia | 1 finite loop | 40000 | AC-77051
2 | mike | 129 81 st | 80000 | AC-48901
(3 rows)
CREATE VIEWAdmin user ybadmin has permissions to view all the contents on employees table. To prevent admin users from viewing sensitive information like salary and account_number, the CREATE VIEW statement can be used to secure these columns.
\c yugabyte yugabyte;
REVOKE SELECT ON employees FROM ybadmin;
CREATE VIEW emp_info as select empno, ename, address from employees;
GRANT SELECT on emp_info TO ybadmin;
Verify the permissions of the ybadmin user on the employee table.
\c yugabyte ybadmin;
select current_user;
current_user
--------------
ybadmin
(1 row)
Because permission is revoked for ybadmin, this user will not be able to query employees table.
select * from employees;
ERROR: permission denied for table employees
Because ybadmin was granted select permission on emp_info table, ybadmin user will be able to query emp_info table.
select * from emp_info;
empno | ename | address
-------+-------+---------------
1 | joe | 56 grove st
3 | julia | 1 finite loop
2 | mike | 129 81 st
(3 rows)
GRANTInstead of creating views, YugabyteDB supports column level permissions, where users can be provided access to select columns in a table using GRANT command.
Considering the preceding example, instead of creating a new view, ybadmin user can be provided with permissions to view all columns except salary and account_number, as follows:
\c yugabyte yugabyte;
grant select (empno, ename, address) on employees to ybadmin;
User ybadmin will now be able to access the columns to which permissions were granted.
\c yugabyte ybadmin;
select empno, ename, address from employees;
empno | ename | address
-------+-------+---------------
1 | joe | 56 grove st
3 | julia | 1 finite loop
2 | mike | 129 81 st
(3 rows)
ybadmin will still be denied if user tries to access other columns.
select empno, ename, address, salary from employees;
ERROR: permission denied for table employees