Data Based Authorization Concept for Analytic Queries
CDS access controls are based on CDS roles that are defined in Data Control language (DCL), see ABAP CDS views(and SAP Annotations) for further information. When CDS entities that are assigned to a CDS role are accessed, additional access conditions are evaluated. In case of an Analytic Query (based on a CDS Cube with access controls)this means that during query runtime, the authorized values are added to the WHERE clause of the SQL statement with a logical AND(done by the Kernel). In general, access controls are only evaluated when there is a Open SQL (ABAP SQL) access on the entity the access control is defined for('grant select on' - see example below). Access controls defined for entities which are used in another entity are ignored, so, only the top level entity(accessed by SQL) is evaluated.
When we compare this with the concept of OLAP Authorizations, we notice the following:
- Authorized values are automatically applied as filter values. Hence, in case of missing authorizations, a user does not get an error message like 'no authorization...' but no or less data displayed in the query.
- In the area of OLAP authorizations often variables replaced by the authorized values of the user are used. This, of course, is not necessary when queries are based on transient CDS providers with Access Controls.
Important to know
- You can define data restrictions for the data categories CUBE and DIMENSION(with annotation AccessControl.authorizationCheck and the corresponding Access Controls)
- The annotation AccessControl.authorizationCheck is ignored for an analytic query. This also fits to the general concept of OLAP Authorizations where authorizations do not depend on the query.
- If a certain dimension is restricted on Cube level, this does not imply a corresponding restriction on the dimension itself. So, e.g. if you use the F4 help for a variable defined on such a dimension and the so called M-Mode is used(see F4 Modes), all master data values are displayed. If you want to avoid this, you need to define corresponding access controls for the dimension as well. See Examples I and II.
- If a certain dimension is authorization relevant this has no impact on the check of transactional data on the level of an corresponding CDS Cube. See Example III.
CDS view Zstpe_cds_V4 has the access control Authorization Check set to 'CHECK' meaning that it is 'authorization relevant'.
The access control ZSTPE_ACC_CONT_4 defines a role which refers to the authorization object S_DBCON: the auhtorized values for the field AircraftType are retrieved from the corresponding user authorizations for this auth. object (field DBA_DBSID)
Then we use the debug function "Show DCL authorizations for CDS" and "Display SAP Hana Query" to get more information:
The Hana Statement (open SQL) does no show any details about the authorized values, but of course the restriction of the query:
In contrast to the open SQL statement displayed in RSRT, the native SQL statement of an ST05 SQL trace show the complete WHERE clause:
Please note that the following two restrictions where added
- "MANDT" = '003' <- client restriction (added independent of the authorizations)
- "AIRCRAFTTYPE" LIKE 'Q%' <- auhorized values
So, it is correct that the query does not return data since the there is no intersection between the query filter and the authorized values of the user!
The function "Show DCL authorizations for CDS" in RSRT shows the client restrictions and the restriction coming from the access control (manual authorization test for CDS Views can also be done with trasnaction SACM)
In case the user has no authorization at all the following statement is generated (which obviously can never return values)
The following 'normal' BW query is defined on the CDS Cube from above. It uses a BW variable for the field Aircrafttype which is authorization relevant in this Cube.
When we use the F4 help for this variable(we execute the query in n RSRT), all values are displayed(F4 Mode M used), so we also get values displayed the user is actually not authorized to see on the cube level(transactional data). This effect is caused by the fact that the dimension Aircrafttype is not authorization relevant although it is on Cube level. Hence, in general, it makes sense to assign the same authorizations to the cube and the corresponding dimension.
If we choose the value A319-100 and run the query, no data is returned(since the user is not authorized to see this aircraft type):
In the ST05 SQL trace we can see that, in addition to the filter value A319-100 of the query, the system added the corresponding filter generated by the authorization(see Example I):
Example III (only Dimension authorization relevant)
We check a 'normal' BW query which is based on a CDS Cube which is not authorization relevant. However, the dimension Aircrafttype of this Cube is authorization relevant.
When we run the query in RSRT and use the F4 helpy(F4 Mode M used) for the variable, no values(apart from #) are displayed since the user has only authorization to see values with the pattern Q* - and such values do not exist.
SQL statement(ST05 trace) of F4 help:
If we leave the variable blank and run the query we can see all aircraft types available in the provider: