Row-Course Permission Control

YMatrix rank and column permission control can be performed using views. Restrict user access to certain rows and columns by creating views. Views can contain only rows and columns that users have access to, thus hiding other sensitive rows and columns.

In this section we will demonstrate row-level permission control and column-level permission control through a basic example.

1. Table creation

Create a test table and insert the test 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,'张三',5000,'mxzz1'),(2,'李四',6000,'mxzz2');

2. Row-level permission control

Create a test user

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

Assign permissions


grant select on table user_salary to mxzz1;
grant select on table user_salary to mxzz2;

- Data query using mxzz1 user

-- Query Test psql postgres -h 127.0.0.1 -U mxzz1

-- Return only the salary information of mxzz1 user select * from user_salary;

id | name | amount | users ---|------|---------------------------------------------------------------------------------------------------------------------- 1 | zhang| 5000 | mxzz1

- Data query using mxzz2 user

-- Query Test 2 psql postgres -h 127.0.0.1 -U mxzz2

-- Return only the salary information of mxzz2 user select * from user_salary;

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

## 3. Column-level permission control

- Create a test user

drop user lingdao; drop user commonuser; create user lingdao with password '123123'; create user commonuser with password '123123';

- Public permissions for recycling tables

revoke all ON salary from public;

- Authorize column permissions

grant select(id,name,users) on table salary to lingdao; grant select(id,name,amount,users) on table salary to commonuser;

- Use lingdao users to query data

psql postgres -h 127.0.0.1 -U lingdao

-- Full table query failed select * from salary; ERROR: penmission denied for table salary

-- The authorization column query was successful select id,name,users from salary;

id	name	users
1	zhang	mxzz1
2	Li	mxzz2

- Use commonuser users to query data

psql postgres -h 127.0.0.1 -U commonuser

-- The query of the full table was successful select * from salary ;

id | name | amount | users ---|------|---------------------------------------------------------------------------------------------------------------------- 1 | zhang| 5000 | mxzz1 2 | Li | 6000 | mxzz2