Forum Discussion
Post ProdigyColumn-level security allows to select all table columns yet
I must admit the documentation is a bit confusing on this topic imo:
"If you'd like to set up a user's granular permissions before allowing them to connect to the warehouse, permissions can first be set up within SQL. Then, they can be given access by assigning them to a Workspace role or granting item permissions." https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-granular-permissions
This seems to imply that sql granular permissions can be used to modify (limit) the T-SQL permission of users with workspace roles. Is that correct? I have had the assumption that workspace roles override sql granular permissions. But that quote from the docs seems to contradict that.
Anyway, I guess the workspace users (except viewer) will be able to access the data using Spark, regardless of SQL permissions.
Also, restricting T-SQL permissions wouldn't restrict the other permissions that belong to workspace roles: https://learn.microsoft.com/en-us/fabric/get-started/roles-workspaces#-workspace-roles
These docs are probably also highly relevant: https://learn.microsoft.com/en-us/fabric/data-warehouse/workspace-roles#workspace-roles
-------
Here is a part of the docs which seems to insist on giving workspace access. I think this is confusing and I believe this is a bug in the docs:
"CREATE USER cannot be explicitly executed currently. When GRANT or DENY is executed, the user is created automatically. The user will not be able to connect until sufficient workspace level rights are given." https://learn.microsoft.com/en-us/fabric/data-warehouse/sql-granular-permissions#limitations
--------
The two main points that confuse me, are:
- Is it possible to restrict SQL permissions, e.g. DENY, RLS or CLS, for workspace users (viewer, contributor, member and admin)?
- Is it possible to restrict SQL permissions, e.g. DENY, RLS or CLS, for users with item level ReadData permission?
I'm hoping the docs can become clearer on this.
------
"In my opinion, it would be better to start with access at the Warehouse level and then GRANT on the relevant ojects/columns." AndyDDC
I think this approach makes very good sense!
I think this is achieved by sharing the warehouse with a user (a user who doesn't have any workspace role), and leaving all Additional Permissions unchecked.
"If no additional permissions are selected - The shared recipient by default receives "Read" permission, which only allows the recipient to connect to the SQL analytics endpoint, the equivalent of CONNECT permissions in SQL Server. The shared recipient won't be able to query any table or view or execute any function or stored procedure unless they're provided access to objects within the Warehouse using T-SQL GRANT statement." https://learn.microsoft.com/en-us/fabric/data-warehouse/share-warehouse-manage-permissions#fabric-security-roles
Start at zero, and add T-SQL permissions as required by using T-SQL GRANT statements.
Hi pmscorca ,
Thanks for the reply from AndyDDC .
Overrides can occur between permissions, so you need to make sure that the user doesn't have more advanced permissions that could override column-level security. For example, if a user has SELECT permission on an entire table, this may allow them to see all columns.
Try testing the permissions with a different user account to see if the problem is specific to that user or a generalized issue.
See the documentation below for more information on database column level permissions:
How to Implement Row and Column Level Security in SQL Server (netwrix.com)
If you have any other questions please feel free to contact me.
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
pmscorcaHi, thanks for your replies.
I've executed some proofs with the user account of my colleague and so I'd like to start from this point.
My colleague has the admin role for the workspace and moreover he created the warehouse.
For the warehouse no roles was defined (like e.g. db_owner, db_datareader and so on).
In a such situation if I want to hide a specific column successfully I should use the DENY statement:
DENY SELECT ON def.mytable ( [column2] -- this is the column to hide ) TO [[email protected]];So my colleague cannot select the specific column. With a REVOKE SELECT ON ... FROM statement it is possible to go back.
I've solved using a DENY statement and not a GRANT one.
Therefore with an user account having high permissions it occurs using a GRANT statement to remove a such granular permission (at a column level).
On the other hand, with an user account having minimum permissions it occurs using a GRANT statement to add a such granular permission: in this case, the CLS documentation doesn't specify the initial permissions for the user account to try the GRANT statement successfully; could the user access to the workspace and with which role (admin, member, contributor, viewer)? or should the user not to access the workspace but at the warehouse (item level security) or the table (object level security)?
However, the documentation about the CLS should improve.
Thanks
- AndyDDC
Most Valuable Professional
In my opinion, it would be better to start with access at the Warehouse level and then GRANT on the relevant ojects/columns.
If you assign at the workspace level, then any new objects are going to be automatically accessable from the user account unless you DENY.
- pmscorca
Therefore starting with an item level security against the warehouse without assigning any roles to the specific user account at workspace level: it could be a good idea, but not documented respect to the column level security subject.
