The SAP HANA Academy’s Denys van Kempen provides a detailed overview of the form-based editor used to create and edit repository roles. This is a new security feature of SAP HANA Service Support Package 90.
Watch Denys’ tutorial video on this new security feature in SAP HANA SPS09.
(0:16 – 1:27) Issues with Granting Privileges
Denys is using a Windows machine and has started SAP HANA Studio. Denys has connected to the SAP HANA server as a system user. Using the system user is fine for this demo but it is not ideal for production. As detailed in the security guide one should only use system to create users and roles with the required privileges, as well as be used to connect with users and to disable the system.
Denys has created the role of security administrator. By granting the system privileges role and user admin users and roles can be created. In this example user Bill has been granted this role. Denys connects as Bill and thus no longer need to be concerned with the system user.
Bill has created user Benny. By clicking on Benny, Bill can directly grant system privileges, object privileges, analytical privileges, and many others. However, this would be not be a very good idea for the company as the moment Benny changed his job function or left then Bill would have to grant these same individual privileges to another user all over again.
(1:27 – 2:20) Interlude to Security Guide
From section 8.7 on Roles in the security guide Denys reads “A role is a collection of privileges…that can be granted directly to users. However, roles are the standard mechanism of granting privileges as they allow you to implement complex, reusable authorization concepts that can be modeled on business roles.” Simply put you can create a role once and then grant that newly created role to users. If the user no longer needs it then the role is revoked.
Roles can be complex as they can contain a number of system privileges such as backup, admin and restart HANA. Object privileges can be granted on tables, views, and stored procedures. Analytic privileges are for information models. Also roles can contain other roles so they can quickly become very complex.
(2:20 – 4:11) Why Have a Role as a Development Object?
In SAP HANA Studio the changes that are made on Benny’s user screen are translated to SQL in the background and executed on the SAP HANA server directly – this is called runtime. Catalog roles are created and just like any object they belong to the user that creates them.
Here an issue can arise. What happens if our security administrator, Bill, decides to leave the company? Bill created his role and if we deleted the user Bill then we lose all of his created objects and delete all of the roles he has created. Also, Bill can only grant the privileges he owns. On a developing system this may be fine. However, on a production system would we really want Bill to have access to all privileges and tables simply because he needs to create a role that contains those privileges? Most likely the answer is no.
So we let Bill design a role on the development system, save it to a SQL script and have someone else insert the script on the production system. Who will this someone else be? Certainly not the SYSTEM user.
Also, let’s assume that a new version of the application our company is developing is released. Then how can we test that the privileges are correctly assigned to the different roles? We have no proper transportation mechanism to go from development to create to production. What we really need is a role that is a development object.
So we design the roles needed, save it to a repository and then bundle it with the tables, views, and procedures. Then we activate it on the production system via a technical user, a repository owner. We can connect to the database as the repository owner so this way it stays secure. We will have conversion and separation of duties.
(4:04 – 4:45) Overview of Lifecycle
Denys now walks through the below lifecycle.
The application developer uses SAP HANA Studio or the Web IDE to create roles. The role is part of a package that sits in the repository so we can have multiple developers working on this. When the package is activated the runtime objects are created. In this examples roles, tables and views. Then we can export and import our package to the production repository and activate it there. The security administrator can then grant the role and runtime objects to the necessary user.
(4:45 – 5:25) Developer Scenario to Create Roles
Now Denys is connected as a developer, Maggie, who is using the developer perspective. After left clicking on her package, Denys selects new and then other. Under SAP HANA and Database Development Denys selects Role. He clicks finish on the wizard to generate the template.
Now we need to code the privileges. Denys then shows the example below for the system privilege.
So enter this in the SQL console, click execute, and then save it to the repository.
(5:25 – 6:50) Creating Roles on SAP HANA Web IDE
Denys mentions that the ideal situation would be if the same graphical editor exists for runtime objects. This is possible now with SAP HANA SPS 09. However, it can only be employed in the SAP HANA Web IDE at the moment.
After visiting this site in a web browser as a user with the required privileges to work on the Web IDE, we can click on security and enter our login credentials. The great advantages of this web-based workbench is that we don’t need to install anything and that it runs anywhere, even on a tablet.
The security on the SAP HANA Web IDE provides us with the same interface to work with users and roles in runtime. So Denys can select a new role, give it a name, and then grant the system privileges using the same clicks he would have done in SAP HANA Studio.
Now Denys navigates to the editor, selects a package and elects to create a new role. After naming the role we get the exact same view. Under system privileges we can add the objects we want without having to code anything.
All security concepts are documented the SAP HANA security guide.
Denys’ video tutorial highlights how to use the new form-based editor in the SAP HANA Web-based Development Workbench for SAP HANA Studio.
View over 75 tutorial videos on the new features of SAP HANA SPS 09.
SAP HANA Academy – over 750 free tutorial technical videos on using SAP HANA.