Release History
Requirements

Introduction

Quick onboard

Deployment

Data Modeling

Connecting

Write data

Migration

Query

Operations and Maintenance

Performance Tuning

Troubleshooting

Reference Guide

SQL Reference

FAQ

Row and Column Privilege Control

YMatrix supports row-level and column-level privilege control using views. By creating views, you can restrict user access to specific rows and columns. A view can include only the rows and columns that a user is authorized to access, thereby hiding sensitive data.

This section demonstrates row-level and column-level privilege control through a basic example.

1. Table Creation

Create a test table and insert sample data.

drop table if exists salary cascade;
create table salary (
         id int
        ,name varchar(100)
        ,amount numeric
        ,users varchar
) distributed by (id);

insert into salary values(1,'Zhang',5000,'mxzz1'),(2,'Li',6000,'mxzz2');

2. Row-Level Privilege Control

  • Create test users
drop user mxzz1;
drop user mxzz2;
create user mxzz1 with password '123123';
create user mxzz2 with password '123123';
  • Create a view
create or replace view user_salary as 
select * from salary
where users = user::varchar(64);
  • Grant privileges
grant select on table user_salary to mxzz1;
grant select on table user_salary to mxzz2;
  • Query data as user mxzz1
-- Test query
psql postgres -h 127.0.0.1 -U mxzz1

-- Returns only salary information for mxzz1
select * from user_salary;

id | name  | amount | users 
---|-------|--------|-------
1  | Zhang |  5000  | mxzz1
  • Query data as user mxzz2
-- Test query 2
psql postgres -h 127.0.0.1 -U mxzz2

-- Returns only salary information for mxzz2
select * from user_salary;

id | name | amount | users 
---|------|--------|-------
2  | Li   |  6000  | mxzz2

3. Column-Level Privilege Control

  • Create test users
drop user lingdao;
drop user commonuser;
create user lingdao with password '123123';
create user commonuser with password '123123';
  • Revoke public privileges on the table
revoke all ON salary from public;
  • Grant column-level privileges
grant select(id,name,users) on table salary to lingdao;
grant select(id,name,amount,users) on table salary to commonuser;
  • Query data as user lingdao
psql postgres -h 127.0.0.1 -U lingdao

-- Full table query fails
select * from salary;
ERROR: permission denied for table salary

-- Query on granted columns succeeds
select id,name,users from salary;

id |  name | users 
---|-------|-------
1  | Zhang | mxzz1 
2  |  LI   | mxzz2
  • Query data as user commonuser
psql postgres -h 127.0.0.1 -U commonuser

-- Full table query succeeds
select * from salary;

id |  name | amount | users 
---|-------|--------|-------
1  | Zhang |  5000  | mxzz1 
2  |  Li   |  6000  | mxzz2
← Previous
Resource Group Use
Next →
GSSAPI