权限管理 - Role based Access Control

本章我们将在Datashare上实现基于角色的访问控制

我们将在bi-redshift-cluster上创建两个角色，然后创建不同用户并绑定角色，根据角色授予的权限，实现不同的权限控制

打开bi-redshift-cluster的SQL页面，执行以下语句创建两个ROLE：

CREATE ROLE bi_users;
CREATE ROLE data_analysts;

image-20230820194225701

创建两个用户，分别将上面两个role绑定上去：

CREATE USER dan PASSWORD 'Redshift123' ;
GRANT ROLE data_analysts to dan;

CREATE USER sam PASSWORD 'Redshift123';
GRANT ROLE bi_users to sam;

为每个表创建view，这些view要授予给data_analysts访问：

CREATE VIEW supplier_view AS SELECT * FROM tpc.public.supplier WITH NO SCHEMA BINDING;
CREATE VIEW lineitem_view AS SELECT * FROM tpc.public.lineitem WITH NO SCHEMA BINDING;
CREATE VIEW nation_view AS SELECT * FROM tpc.public.nation WITH NO SCHEMA BINDING;
CREATE VIEW orders_view AS SELECT * FROM tpc.public.orders WITH NO SCHEMA BINDING;
CREATE VIEW part_view AS SELECT * FROM tpc.public.part WITH NO SCHEMA BINDING;
CREATE VIEW partsupp_view AS SELECT * FROM tpc.public.partsupp WITH NO SCHEMA BINDING;
CREATE VIEW region_view AS SELECT * FROM tpc.public.region WITH NO SCHEMA BINDING;

创建supplier_shipmode_agg视图，这个视图要授予给bi_users访问：

CREATE VIEW supplier_shipmode_agg as
select l_suppkey, l_shipmode, datepart(year, L_SHIPDATE) l_shipyear,
SUM(L_QUANTITY) TOTAL_QTY,
SUM(L_DISCOUNT) TOTAL_DISCOUNT,
SUM(L_TAX) TOTAL_TAX,
SUM(L_EXTENDEDPRICE) TOTAL_EXTENDEDPRICE
from tpc.public.lineitem
group by 1,2,3
WITH NO SCHEMA BINDING;

把下面视图授予给data_analysts访问：

GRANT SELECT ON 
lineitem_view, 
nation_view,
orders_view,
part_view,
partsupp_view,
region_view,
supplier_view
TO ROLE data_analysts;

把supplier_shipmode_agg授予给bi_users访问：

GRANT SELECT ON supplier_shipmode_agg
TO ROLE bi_users;

权限测试

dan用户属于data_analysts角色，我们先使用这个用户登录。右键bi-redshift-cluster，编辑连接：

image-20230820194450247

image-20230820194512134

Dan用户能访问除supplier_shipmode_agg外的视图：

Select * from nation_view limit 100;

image-20230820194537857

但是当访问supplier_shipmode_agg视图的时候就会报权限错误：

Select * from supplier_shipmode_agg limit 100;

image-20230820194614409

同理，使用sam用户登录到集群，sam用户可访问supplier_shipmode_agg视图，但他没有访问其他视图的权限：

Select * from supplier_shipmode_agg limit 100;

image-20230820194735446

Select * from nation_view limit 100;

image-20230820194906194