Try a preview of AI-powered search below!

• YSQL

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.

Step 1. Create example table

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

Step 2. Create ybadmin user

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

Step 3. Verify permissions

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)

Step 4a. Restrict column access using CREATE VIEW

Admin 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 access privileges

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)

Step 4b. Restrict column access using GRANT

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

Verify access privileges

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