This topic describes how to manage permissions by row. This can allow you to enable specific users to only view specific data.
Project A has a table named table_order. This table contains information about the transaction orders of all merchants. Each merchant can view only their own transaction orders.
- Solution 1: Create an independent downstream table for each merchant in the table_order table and grant the permissions for the independent table to the corresponding merchant. In this solution, duplicate data may be stored. Therefore, when the table_order table is updated, its downstream tables must also be updated to ensure data consistency.
- Solution 2: Create an independent downstream view for each merchant in the table_order table and grant the permissions for the view to the corresponding merchant.The second solution is superior to the first solution in the regard that it does not incur duplicate data, therefore we recommend that you use the second solution.
- Create a view for each merchant in project A.
CREATE VIEW <viewname> as select * from table_order WHERE sellerid='xxxx';
- Create a package for each view in Project A and share the resources in this package
to grant the merchant the viewing permissions for these resources.
create package <packagename>; add table <viewname> to package <packagename>; allow project <Projectname_seller> to install package <packagename>;
- Allow each merchant to be able to use their view.
--All commands are run the project for the merchant. install package <ProjectA>.<packagename>; grant read on package <ProjectA>.<packagename> to user <username>;--The username is the account that requests to query a view in the project.
grant select,describe on table <viewname> to user <username>;