As you may have learned, release 2108 has made it possible to access CDS view entities in an ABAP system from almost any application (including third-party). All you need to do is to expose the relevant view entities through a service and set SQL over ODBC as the communication protocol. That’s it. In very simplified terms: ABAP is henceforth also a database. If you didn’t know this yet, I recommend the following sites as a preliminary reading:

• SCN Blog Post: Consuming CDS View Entities Using ODBC-Based Client Tools
• SAP Help Portal: Accessing ABAP-Managed Data from External ODBC-Based Clients

The sites listed above explain why we implemented this initially absurd-sounding idea. You will also find installation instructions, setup hints, and a rough overview of the architecture. I, on the other hand, start right away in medias res and skip all of that.

Table of contents

• Motivation and objectives
  • Why is a new version needed?
  • What is the new strategy?
• Prerequisites
• Outline
• Let’s prepare the demo scenario
  • Create the database tables
  • Insert an abstraction layer
• Stay tuned

Motivation and objectives

If you have already worked with our SQL service (aka ODBC driver for ABAP), you may have noticed that the credo so far has been “all or nothing”: Either a user had access to all data of the exposed CDS view entities or they didn’t have access and accordingly couldn’t see a single record. Simple as that. We call this “privileged access”, which is actually intended for the integration of various systems rather than for the end-user. This is because, with human end users, data protection becomes relevant. To close this gap, our latest cloud release 2202 enables role-based service selection and record-level constraints. Wait, what does this mean?

Well, imagine there was a global company ACME SE that manufactures everything. Its warehouses are well stocked and spread across the globe. Jane Bloggs, the chief warehouse manager, follows a chaotic storage policy. The following is a highly simplified excerpt from the inventory summary, as of March 04, 2022.

As a result of a humanitarian disaster, some worrying orders have been received in recent weeks. The two business analysts Jan Kowalski from Poland and Minta Kata from Hungary would like to analyze these in detail. Following the Principle of Least Privilege (PoLP), we expect them to only have access to orders whose billing address corresponds to the country in which they reside. Too bad we store all our orders together in exactly the same database table…

The given situation confronts us with two challenges at once: First, we need to ensure that each employee can only access the database tables required for their job. This means a warehouse manager cannot inspect orders and business analysts cannot gain insight into the inventory. Second, we need to hide individual records within these tables from certain users. Specifically, this means that when Jan accesses the orders, he should see the following entries:

Kata should not get to see these entries under any circumstances. Instead, with the same query, she should obtain another subset which remains hidden from Jan. More specifically, while Jan can see all orders with billing address Poland, Kata should see all orders with billing address Hungary.

Why is a new version needed?

The tech enthusiasts among you might argue: “Can’t I just append a WHERE clause to the SQL query?” Yes and no. It’s a hacky workaround that will fix the issue for the given situation, but we want to ensure proper handling for any possible query. For this, you would need a full-fledged parser and your own user management. Both are incredibly hard to maintain and what’s more: Proper access would be limited to your own application, while the server would continue to return sensitive data if requested. Once users decide to get rid of your application and query the server directly, the problem is back. Serious access control looks different.

To cut a long story short: Save your time and please don’t ever do the user management yourself!

What is the new strategy?

The discussed workaround and any other mechanism that could be implemented on the client-side is ultimately doomed to fail, as it would be the consumer who restricts access, not the provider. It may not sound like it, but that’s pretty good news for you. The bulk of work is on ABAP’s side. You just need to declare which users should be authorized for which data. The ABAP server is then responsible for implementing exactly that. In this tutorial, I will show you step by step how to configure this professionally. So put your feet up and always leave data protection to the server…

Prerequisites

The tutorial is designed as a hands-on exercise. Accordingly, you achieve the greatest learning effect if you become active yourself and reproduce the individual steps in your own ABAP environment. For this, you need a development user in an SAP BTP ABAP Environment (aka “Steampunk”) system with release 2202 or higher.

To be able to create development objects in that cloud system, you need the ABAP Development Tools or ADT for short. This is an integrated development environment (IDE) based on Eclipse. If you don’t have it installed yet, you can download it from https://tools.hana.ondemand.com.

Also, make sure that ADT has a connection to your cloud system. This is the case when a corresponding project appears in ADT’s project explorer. If you are not familiar with ADT and need help creating a new ABAP cloud project, please see the guide in the SAP Help Portal.

Last but not least, you need the “ODBC driver for ABAP” to connect an ODBC-compliant application of your choice to the ABAP server. Please make sure that you have installed at least version 1.0.3 of the driver. Update the driver if necessary. Always up-to-date installation instructions can be found here.

Outline

As already mentioned in the motivation, we have two different, albeit similar, tasks to accomplish. To keep a clear separation, I decided to split the tutorial into a series of blog posts instead of writing one thick tome. So far, I have the following structure in mind:

• Access Control for ABAP’s SQL Service (2/3) – Role-Based Service Selection [not yet available] We define (business) user roles, each of which grants access to an individual set of views/services.
• Access Control for ABAP’s SQL Service (3/3) – Record Level Constraints [not yet available] Additionally restricts access to those records of a table/view that meet predefined criteria.

In this article, we are just setting the stage for the two upcoming posts. We will create the database tables, populate them with sample values, and build CDS view entities on top of the tables. It has nothing to do with the SQL service in particular yet. So if you already have your own view entities that you want to use instead, you can safely jump to the last chapter stay tuned. You won’t miss anything, I promise.

All others are welcome to copy my code snippets. For the experts, I have added additional “shortcuts” at the beginning of each chapter. There you will find condensed information about what is done in the chapter. This is followed by detailed instructions so that newbies should also be able to follow along. I hope this will allow me to accommodate all experience levels. Let me know in the comments if I’ve succeeded.

Let’s prepare the demo scenario

I try to follow the existing base scenario of our documentation on the SAP Help Portal as far as possible. Unfortunately, minor adjustments are necessary to fulfill the purpose of this tutorial. Feel free to reuse and extend the various artifacts if they are still available in your development system. You can also name the objects differently and add some extra stuff to make the scenario more realistic and sophisticated.

If you use other object names, please remember them well. We will have to edit some code listings again in the upcoming posts. Of course, I could tacitly make all the settings now, but I prefer to mention things when they become relevant. In addition, my posts should enable you to apply the presented actions to your individual project. That’s why I outsourced the creation of the demo scenario. If I were to set up premises right at the beginning that could only be justified and understood later, anyone starting with a later post would fall flat on their face. So bear with me, we will approach the ultimate situation step by step.

Create the database tables

Shortcut: Use the code listings 1+2 from below to create a total of five database tables. Then populate the tables with sample values using the ABAP class in listing 3.

The sample tables shown above cannot possibly be database tables. That would result in far too many anomalies. I won’t do a full normalization here, but for a roughly realistic example and satisfactory demo, it should be no less than the five tables ZCOUNTRIES, ZPRODUCTS, ZINVENTORY, ZORDERS, and ZORDER_ITEMS.

To create a database table, you should make sure you have selected the correct project (system, client, user) in ADT’s Project Explorer and press Ctrl + N to open the “New Item Wizard”. Type “database table” in the dialog’s search field and hit Enter. If you use a custom key binding and can’t remember specific shortcuts, you can find a complete list in the menu under Window > Preferences > General > Keys.

Figure 1: creation process for a new database table

You will now be prompted to specify the table name and a description. I simply use ZCOUNTRIES for the name and “persists countries as defined by international law” for the description. If you use another name, you should remember it well. You will need it again later in the tutorial. In the code editor that opens after selecting a transport request, you should already see a generic structure with annotations similar to those in Listing 1. Now complete your table definition as shown below.

@EndUserText.label : 'persists countries as defined by international law'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zcountries {
  key code : abap.char(2) not null;
  name     : abap.char(10);
}

Listing 1: minimal table definition for countries

Save and activate your table definition. That’s it. You now need to repeat these steps four times for the remaining database tables. As already mentioned, the tables are far from complete, but we are not going to build yet another ERP here. Do not hesitate to extend the model. For example, you might consider providing a real billing address instead of just the country. Also, it would be useful to have actual customers who place the orders. Do whatever you want, but please keep at least the tables and their attributes from Listing 2. Some of them will become important later.

@EndUserText.label : 'persists goods in the company''s warehouse'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zinventory {
  key product_id   : abap.int4    not null;
  key storage_area : abap.char(7) not null;
  amount           : abap.int4    not null;
  country_code     : abap.char(2);
}
@EndUserText.label : 'persists the individual items of an order'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zorder_items {
  key order_id   : abap.char(10) not null;
  key order_line : abap.int4     not null;
  product_id     : abap.int4     not null;
  amount         : abap.int4     not null;
}
@EndUserText.label : 'persists the general header data of an order'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zorders {
  key id          : abap.char(10) not null;
  order_date      : abap.datn;
  billing_address : abap.char(2);
}
@EndUserText.label : 'persists all products and their properties'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #RESTRICTED
define table zproducts {
  key id : abap.int4      not null;
  name   : abap.char(100) not null;
}

Listing 2: further table definitions, please split after closing braces

Now we should populate the newly created or modified tables with our sample data from the motivation. For this reason, I have written the following ABAP class. Again, press Ctrl + N to open the “New Item Wizard”. Type “class” in the dialog’s search field and hit Enter. Specify the class name and its description. Press Enter, select a transport request, hit Enter one more time and paste the code below into the editor. If you have chosen other names for your class or your database tables, please remember to replace them after copying.

class zpopulate_acme_database definition
  public
  final
  create public.

  public section.
    interfaces if_oo_adt_classrun.
  protected section.
  private section.
endclass.

class zpopulate_acme_database implementation.

  method if_oo_adt_classrun~main.
    delete from zcountries.
    insert zcountries from table @( value #(
      ( code = 'DE' name = 'Germany' )
      ( code = 'UA' name = 'Ukraine <3' )
      ( code = 'HU' name = 'Hungary' )
      ( code = 'PL' name = 'Poland' )
      ( code = 'US' name = 'USA' )
      ( code = 'TR' name = 'Turkey' )
    ) ).
    out->write( sy-dbcnt ).

    delete from zinventory.
    insert zinventory from table @( value #(
      ( product_id = 1 storage_area = 'HAW0056' amount = 509203 country_code = 'US' )
      ( product_id = 2 storage_area = 'IST0144' amount = 131071 country_code = 'TR' )
      ( product_id = 2 storage_area = 'ANK0042' amount = 271441 country_code = 'TR' )
      ( product_id = 3 storage_area = 'WAR0026' amount =   1722 country_code = 'PL' )
    ) ).
    out->write( sy-dbcnt ).

    delete from zorder_items.
    insert zorder_items from table @( value #(
      ( order_id = 'DE00161803' order_line = 1 product_id =  4 amount = 5000 )
      ( order_id = 'UA00271828' order_line = 1 product_id =  5 amount = 44130000 )
      ( order_id = 'HU00314159' order_line = 1 product_id =  6 amount = 63 )
      ( order_id = 'HU00314159' order_line = 2 product_id =  2 amount = 28000 )
      ( order_id = 'PL00466920' order_line = 1 product_id =  7 amount = 8 )
      ( order_id = 'PL00466920' order_line = 2 product_id =  8 amount = 50000 )
      ( order_id = 'UA00547722' order_line = 1 product_id =  9 amount = 15 )
      ( order_id = 'UA00547722' order_line = 2 product_id =  2 amount = 400000 )
      ( order_id = 'PL00628319' order_line = 1 product_id = 10 amount = 885303 )
      ( order_id = 'HU00707107' order_line = 1 product_id = 11 amount = 169053 )
    ) ).
    out->write( sy-dbcnt ).

    delete from zorders.
    insert zorders from table @( value #(
      ( id = 'DE00161803' order_date = '20220116' billing_address = 'DE' )
      ( id = 'UA00271828' order_date = '20220224' billing_address = 'UA' )
      ( id = 'HU00314159' order_date = '20220301' billing_address = 'HU' )
      ( id = 'PL00466920' order_date = '20220303' billing_address = 'PL' )
      ( id = 'UA00547722' order_date = '20220304' billing_address = 'UA' )
      ( id = 'PL00628319' order_date = '20220305' billing_address = 'PL' )
      ( id = 'HU00707107' order_date = '20220305' billing_address = 'HU' )
    ) ).
    out->write( sy-dbcnt ).

    delete from zproducts.
    insert zproducts from table @( value #(
      ( id =  1 name = 'Internet Terminal' )
      ( id =  2 name = 'Bread' )
      ( id =  3 name = 'Tent' )
      ( id =  4 name = 'Helmet' )
      ( id =  5 name = 'White Pigeon' )
      ( id =  6 name = 'Barrel' )
      ( id =  7 name = 'Generator' )
      ( id =  8 name = 'Surgical Mask' )
      ( id =  9 name = 'Geiger Counter' )
      ( id = 10 name = 'Camp Bed' )
      ( id = 11 name = 'Blanket' )
    ) ).
    out->write( sy-dbcnt ).
  endmethod.

endclass.

Listing 3: populating the demo database using an ABAP class

Execute the main method by pressing F9. After that, make sure that the tables have actually been populated. Open the table definitions for this purpose and either press F8 or call up the context menu with the right mouse button and select Open with > Data preview.

Please Note: I follow the Clean Code guidelines and do not use the Hungarian notation as is usually the case in ABAP. The community has found many good reasons for this and published them in the book “Clean ABAP”. If you haven’t heard of it, you should check it out.

Insert an abstraction layer

Shortcut: Create a total of three CDS view entities using Listings 4-6.

Our minimalist model is now complete, but even though you could already use the tables in ABAP coding, it is strongly discouraged these days. In contrast, the SQL service does not even allow access to database tables in principle. This is because direct use of them leads to a strong coupling, which makes it difficult to subsequently adapt the data model. Instead, it is recommended to use an abstraction layer so that the tables can be changed without breaking existing applications.

We create this abstraction layer with the help of CDS view entities. So please open the “New Item Wizard” again, type “data def” in the dialog’s search field to create a new data definition, and hit Enter. You will then be prompted to specify the view name, a description, and the referenced object. I called my view ZPRODUCTS_VIEW and the referenced object is, of course, the database table through which the view gets its data. In my case, that is ZPRODUCTS. Confirm your input with Enter, choose a transport request, and select the template for “View Entities”.

Please Note: As you can see in the screenshot below, there is a second template type for “Views”. This is not the same as “View Entities”! CDS DDIC-based views (without the entity suffix) are the predecessors of view entities and lag behind in many areas, both in terms of features and activation performance. Given an existing successor, that shouldn’t come as a surprise. Please favor CDS view entities over CDS DDIC-based views wherever possible. ODBC access only works with view entities anyway. Yet, it is only a template. So don’t go crazy if you accidentally select the wrong template. Just copy my code and everything should be fine.

Figure 2: template selection in the data definition wizard

If you followed my instructions, the wizard should have already created a complete and correct definition. That’s all we need. Nevertheless, I have pasted my definition in the following listing for comparison purposes. Make sure that it really says “define view entity” and not just “define view“. After activation, you may be confronted with the warning “no access control for entity ZPRODUCTS_VIEW“. We will take care of access control in the third post. For now, you can suppress the warning by specifying #NOT_REQUIRED instead of #CHECK in the annotation authorizationCheck. Further default values with the corresponding description are displayed by pressing Ctrl + Space simultaneously.

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'selects all products and their properties'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
  serviceQuality: #X,
  sizeCategory: #S,
  dataClass: #MIXED
}
define view entity ZPRODUCTS_VIEW
  as select from zproducts
{
  key id   as Id,
      name as Name
}

Listing 4: CDS view entity for the product range

In addition, view entities offer the advantage of modeling semantic dependencies. This is very convenient for us because normalization forced us to spread the attributes over several database tables. However, this technical requirement should not affect the way we see things. We still want a representation that is as close as possible to tables 1 and 2.

Let’s try this with a second view entity for database table ZINVENTORY. Repeat the steps you took for the first view entity, but this time name it ZINVENTORY_VIEW, for example. In addition to the generated code, we now add an (inner) join to the database tables ZCOUNTRIES and ZPRODUCTS. This gives us the possibility to incorporate individual attributes from these two join tables into the view by adding them to the comma-separated list inside the curly braces. To get the same result as in table 1, I just added the product and country name. Accordingly, my definition is as follows:

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'selects all storage bins including contents'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
  serviceQuality: #X,
  sizeCategory: #S,
  dataClass: #MIXED
}
define view entity ZINVENTORY_VIEW
  as select from zinventory as _inventory
  inner join zcountries     as _country on _country.code = _inventory.country_code
  inner join zproducts      as _product on _product.id   = _inventory.product_id
{
  key _inventory.storage_area as StorageArea,
  _product.name               as Product,
  _inventory.amount           as Amount,
  _country.name               as Country
}

Listing 5: CDS view entity for the inventory

Now we do the same one last time with the database table ZORDERS. It should have a join with the order items in addition to the countries and the products. As always, feel free to copy the code below.

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'selects all orders with the items they contain'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
  serviceQuality: #X,
  sizeCategory: #S,
  dataClass: #MIXED
}
define view entity ZORDERS_VIEW
  as select from zorders  as _order
  inner join zorder_items as _item    on _item.order_id = _order.id
  inner join zcountries   as _country on _country.code  = _order.billing_address
  inner join zproducts    as _product on _product.id    = _item.product_id
{
  key _order.id           as OrderId,
  key _item.order_line    as OrderLine,
  _product.name           as ProductName,
  _item.amount            as Amount,
  _order.order_date       as OrderDate,
  _country.name           as BillingAddress
}

Listing 6: CDS view entity for incoming orders

That’s it. You’ve done it. For the two remaining database tables, you don’t necessarily need to create any more view entities. I mean, we never intended them. They are merely the result of normalization, and thanks to the joins, the essential information is already included in the other view entities.

Your project structure should now look similar to mine:

Figure 3: final project structure, the basis of upcoming posts

Stay tuned

Lucky you, there’s nothing left to do. We now have all the basics in place. Thank you for hanging in there for so long. In the next post, we’ll give Jane Bloggs access to the inventory and our two business analysts access to the orders. In the meantime, you are welcome to try this with communication users. However, I will present another way that offers more options. For as you know, a third post (the most interesting one) is planned that will make use of these capabilities 😉

I would be exceptionally happy to receive your feedback – good as well as bad. I promise to consider your wishes in the upcoming posts.