SAP HANA DBA Ninja vs. GEIGOKAI

[lbreddemann]

A common inquiry

Last week I got two very similar question from colleagues in the consulting teams:

"[...] We created a user (“john” with select / execute privileges on “SYS_BI & SYS_BIC” schema.

User can access data using bo tools and all looking good.

When the user logged into hana studio with his userid…

He was able to execute

“CREATE view “stest”  as select * from <sys_bic> table…..

Is there any option to block user from creating views using sql script as above.[...]"

and

"[...]When a user is created in SAP HANA there is a schema created for this user automatically, for which the user has all privileges.

One of our customers wants to prevent this, as they fear that users can create their own secret data collections.

How can that be prevented for users with SAP HANA Studio access?[...]"

GEIGOKAI - a fierce enemy

free picture linked from pixabay.com.

Bottom line of these requests is that we want to have a true no-privileges-included user account that we can later provide with just the privileges it should have.

Killing two birds with one stone I decided to put out this blog post, instead of adding to the Great-Email-Inbox-Graveyard-Of-Knowledge-And-Information (GEIGOKAI... sounds like a Japanese management principle, but in fact, is just a sort of organisational dysfunction of communication :sad: ).

So, here we go again

As of SAP HANA SPS 08 this is really easy to have.

The tool of choice here is called "restricted user".

Restricted users are explained in the documentation and we cover them of course in chapter 12 "User Management and Security" of the SAP HANA Adminstration book.

Instead of going over the theoretical parts again, let's just look at an example.

I have my application schema I028297 which contains a table SOMESALESDATA and I have an analytic view build using this data called "I028297/AV_2013".

Now, I want to have a read only user that cannot just create a table or view himself, but who can read the data from my table and the analytic view.

1. I need to create the user:

CREATE RESTRICTED USER justread PASSWORD Zer0writes;

Not much to say about this... no black magic required here.

2. I add the user to the SAP HANA Studio

Here I get the first "surprise".

After I changed the initial password (a favorite activity with all first time SAP HANA users :wink: ) I am confronted with this friendly message:

    "You are not authorized to execute this action; you do not have the required privileges".

So, what's going on here?

The reason for this is that the restricted users by default can only connect via HTTP/HTTPS.

Access via this channel can be controlled by the XS engine access rules.

But there is no default access to the database level for restricted users.

In order to allow users accessing the database level via ODBC or JDBC we need to explicitly grant built-in system roles RESTRICTED_USER_ODBC_ACCESS or RESTRICTED_USER_JDBC_ACCESS.

I want to stick with SAP HANA Studio access here (that's JDBC access), so I use the later:

GRANT restricted_user_jdbc_access TO readonly;

Now I can logon to the system, but I cannot really do much:

SELECT current_user FROM dummy;

works but that is nearly everything.

Other simple actions like

CREATE VIEW whoami AS (SELECT current_user FROM dummy);

fail with

    SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

Same with accessing the application data:

SELECT * FROM i028297.somesalesdata;

    SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

and

SELECT "TXNO", "TXDATE", sum("AMOOUNT") AS "AMOOUNT"

FROM "_SYS_BIC"."I028297/AV_2013" GROUP BY "TXNO", "TXDATE";

   SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

or (depending on which privileges are used for the analytic view)

    SAP DBTech JDBC: [2048]: column store error: search table error:  [2950] user is not authorized

3. Clearly, we need to grant the read privileges for the application table and view to our user.

So, let's do that quickly:

GRANT SELECT ON i028297.SOMESALESDATA TO justread;

GRANT SELECT ON "_SYS_BIC"."I028297/AV_2013" TO justread;

Now, the JUSTREAD user can access the application data but (s)he cannot create own tables or views:

SELECT * FROM i028297.somesalesdata;

works, but

CREATE VIEW my_salesdata AS (SELECT * FROM i028297.somesalesdata);

again results in

    SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

Same thing with the analytic view:

SELECT "TXNO", "TXDATE", sum("AMOOUNT") AS "AMOOUNT"

FROM "_SYS_BIC"."I028297/AV_2013" GROUP BY "TXNO", "TXDATE";

now works nicely, but something like

CREATE COLUMN TABLE sales_2013 AS

    (SELECT "TXNO", "TXDATE", sum("AMOOUNT") AS "AMOOUNT"

    FROM "_SYS_BIC"."I028297/AV_2013" GROUP BY "TXNO", "TXDATE");

does not work.

So, there we have it.

Important to remember is that it is not possible to change existing normal users to be restricted users or vice versa.

This decision has to be made when the user is created.

Restricted users still have their own schema listed in the SCHEMAS view, so don't get confused here.

Also, it is not good practice to directly assign privileges to single user accounts like I did it in this example.

Instead privileges should be assigned to roles and these roles in turn should be assigned to the users.

There you go, now you know.

Another pearl of wisdom saved from GEIGOKAI.

Cheers,

Lars