Column-level security allows to select all table columns yet

[pmscorca]

Hi,

on a my warehouse table I've executed this query, for the username of a my colleague:

GRANT SELECT ON def.mytable
(
[column1],
--[column2], this is the column to hide
[column3]
) TO [firstname.lastname@domain.com];

but when he runs SELECT * FROM def.mytable he continues to see all the table columns.

Are there any conditions in order to apply rightly the column-level security?

My colleague created the warehouse.

I've also tried to put my colleague as a workspace viewer, but unsuccessfully.

Many thanks

[Anonymous]

Hi @pmscorca ,

Thanks for the reply from frithjof_v / AndyDDC .

Is my follow-up just to ask if the problem has been solved?

If so, can you accept the correct answer as a solution or share your solution to help other members find it faster?

Thank you very much for your cooperation!

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!

[Anonymous]

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!

[pmscorca]

Hi, 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 [firstname.lastname@domain.com];

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]

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.

[AndyDDC]

Workspace roles override item level permissions. Not sure how much more I can add other than this permissions doc that talks about workspace and item level.

the workspace role matrix shows that as a Viewer you can read all data. If you need more granular, use item level and assign permissions to objects

https://learn.microsoft.com/en-us/fabric/security/permission-model

[frithjof_v · Accepted solution]

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-se...

Start at zero, and add T-SQL permissions as required by using T-SQL GRANT statements.

[pmscorca]

Hi, thanks for your reply.

I've solved the issue about the applying CLS to a warehouse in this way:

1. first, running a GRANT SELECT ON ... TO ... statement,
2. second, sharing the warehouse to the user as a item (so applying the item level security) with only read permission,

in agreement with this

For an higher role it occurs to run a DENY statement, for a lower role it occurs to run a GRANT statement.

Thanks

[AndyDDC]

Hi @pmscorca I think there are 2 problems here, if the same colleague also created the Warehouse then they are the owner of the Warehouse and it's possible RLS/CLS is going to be bypassed (need to verify).  That will also be the case if you assign workspace roles to that user too, it'll override the specific Warehouse permissions.  E.G give your colleague "viewer" role in the workspace, it'll override RLS/CLS.