CDS T-SQL Endpoint pt 8 – Security

The security model is a key advantage of CDS over alternatives such as Sharepoint or SQL. Data is one of any organisation’s most precious assets, and the built-in ability to restrict access to records based on ownership, sharing, teams, hierarchy and even per-field makes this an incredibly powerful feature.

All these security options take effect at the lowest levels of the CDS API, so however you access the data the same rules are automatically applied. So long as you’ve got your security roles set up correctly there should be no difference between whether your users use your Dynamics 365 app, XrmToolBox or even start building their own Power Apps.

Of course, the T-SQL endpoint provides a new way to get at your data, so we need to be sure that the same security rules apply here too.

Authentication

We’ve already seen that any user needs to authenticate with the T-SQL endpoint with the same details they use for any existing app. It knows who is connected and should be able to apply the same security rules.

Security Roles

Roles are the starting point for CDS data security. They identify the basic set of records that the user can access based on who owns the record. These can be very complex, but for now T-SQL is read-only so we only need to worry about the read permissions.

I’ve created a few account records and assigned them to one user. When I log in as another user and run

SELECT name, accountnumber FROM account

as another user with only user-level read permission, it sees no records. So far so good.

If I now update the security role to give organisation-wide read permission and re-run the query, all the accounts are returned. No need to disconnect & reconnect, all the changes are applied immediately. Great!

Field Level Security

While security roles define which records the user can access, field level security defines which fields within those records they can access. By default every field is readable & writable, but you can lock down specific fields to be read-only or even unreadable to all but specific users or teams.

To test this out I applied field level security to the accountnumber field on the account record. As a system administrator I automatically get full access to any secured fields. My other user account however does not. This time when I run the same query I get:

So field level security rules are also applied as expected, and an unreadable field will be returned as NULL.

Teams

Rather than working with individual users, it can be more efficient to work with teams. This allows you to assign permissions to a whole set of users at once. To complicate matters there are two different types of teams – Owner Teams and Access Teams. I won’t go into the differences here, but I will give both of them a go.

Owner Teams

I’ve switched my security roles back to give my normal user access to only records it owns. That user now can’t see my account records any longer. Next I’ve created an owner team, given it the same security role and reassigned one of the accounts to the team.

Now when I run the same query again as my normal user I see that one record. Why? Because I am part of the team, the team owns the record and the team’s security role says it can read accounts it owns.

Access Teams

I’ve now reassigned all my accounts back to my system administrator account, so my normal user can’t see any again. I’ve created an access team and added my normal user to it.

When I share one of the accounts to the access team, my normal user can now see it.

Sharing

Rather than using teams to give extra users access to a record, you can also share the record directly with a specific user. This doesn’t scale well as it gets time consuming to do for large numbers of records, but works well for odd cases.

Again I removed the team that was giving access to the accounts to my normal user account and ensured it couldn’t see any records, then shared one of the records directly to that user. Again, that record appears in the results as expected.

Hierarchy Security

With hierarchy security, the privileges of a user can change depending on where they are in the organisational hierarchy. For my test I’ve enabled the Manager Hierarchy option, which gives a manager access to the same record as the users that report to them.

I’ve reset everything so my normal user does not have access to any account records. Next I’ve updated the Manager field of my system administrator user to be my normal user, and re-run my query. Immediately both users can now see all the records, as the normal user has inherited access from the system administrator.

Summary

So far as I can tell, the T-SQL endpoint honours all the security restrictions that are imposed by the rest of CDS, which should be a big tick for enterprises allowing this to be rolled out. This should enable Power BI reports to finally start replacing the older charts and dashboards that we’ve had to use to ensure the correct security filtering is applied.

Other Posts

This is part of a series of posts on the T-SQL endpoint, read more about it:

1. Connecting
2. First Thoughts
3. SqlClient
4. Performance
5. EntityFramework / ORM
6. Aggregates
7. Extensibility
8. Security