HANA Security

[Former Member]

Hello Folks,

I have a scenario

1) We have two users, each user should not see the content of each users.

2) If _SYS.BIC is granted we can see definitions of all the views from all users

3) If _SYS.BIC revoked from the SQL Privilege for a particular user then that user is not able to activate any attribute or analytical views

4) I have also tried the sql command using the follow

REVOKE ALL PRIVILEGES ON "_SYS_BIC"."<schema_name>/<Object_name>" FROM USER

However it executes successfully but returns with 0 rows & techincally no change in my results.

Is there someone who has worked on the HANA security setup , yet ?

any help appreciated . Thanks

PS: I have already referred the Hana security pdf multiple times, yet no clues.

[tomas-krojzl]

Hello,

recently I wrote two blogs addressing exactly this issue:

but here is the answer in short:

1.) If you do not wish users to see each others work you need to create them different schemas and packages and grant them appropriate privileges (through the roles ideally)

2.) Ability to view the content can be defined by schema privileges or package privileges (user will see the schema or package but it will appear as empty)

3.) _SYS.BIC is needed if you want user to be able to perform activations - unfortunately there is no way around that I am aware of

4.) You cannot grant privilege on schema and revoke on object level - revoke is opposite of granting therefore you can revoke only something that was granted before

I would suggest to check the blogs - I was fighting exactly same issues and managed to solve...

Tomas

[Former Member]

Thank you Tomas, your blog is extensive & self explainatory.

Excellent work there.

I have some questions though :

1. While Assigning Analytical Privilege , i am not able to add the ZTables which I created in HANA itself. It shows me only the Tables imported & the views created. Any way to tackle this ?

2. I observed that when I activate the Analytical Privilege, the user assignment get revoked. & I have to manually reassign the Privilege to a User/Role. This seems to be a bug .

Have you encountered anything like this ?

Thanks

[tomas-krojzl]

Hello,

thanks for feedback...

1.) Analytic privileges are not addressing tables but models (Attribute Views, Analytic Views and Calculation Views) - you should not be able to add any tables (in the dialog there should be only tree with packages). Analytic privilege is always required if you want to make queries against models (unless you will grant corresponding system privilege which is more powerful).

2.) To be honest I did not observe this behavior (but I am not surprised because same is happening with procedures and other objects). In our system we did not use this security for purely practical point. I created dummy analytic privilege that is empty which is equal to "grant all" privilege (on all models in all packages). Reason is that I wanted to make life of developers more easy as they would need to always add every created model into the analytic privilege to be able to test it.

What is missing is possibility to grant analytical privilege against package itself. That would solve a lot.

What I would suggest it to use dedicated role - this way you need to make just one grant after adjusting privilege. In other words privilege adjustment would have two steps - adjustment and then grant to the corresponding role.

Tomas

[Former Member]

Thank you Tomas.

1. In my case, Different Divisions are using the same HANA box, & we need to clearly demark the access. Hence, i am using Analytical Privilege for each views. However, in 1 particular view i have added some ZTables, now without these Ztables added to the Priviilege , it gives me an error while data preview.

hence i was looking for a work around

2. I agree with this, & this is what i'm doing as well, creating new Role for each new Privilege.

Yes, possibility of assigning a Analytical Privilege for a Package or Schema, would certainly make my life much easier.

3. I have been playing around with a specific scenario. Each user should be reuse the Tables imported in a Package by any other user. Do you know what will be the minimum authorization needed to achieve this.

again , I dont want to give Grant all access , as i have other divisions also working on the same server, so want to keep access as limited as possible.

Thanks

[tomas-krojzl]

Hello,

1.) Again - tables cannot exist in package therefore you cannot add them to the analytic privilege.

In case that you have error in data preview for model you might need to grant select privilege for these Z-tables (or whole schema) to the user SYSREPO with admin option - you need to login as someone having ability to grant - owner.

GRANT SELECT ON SCHEMA DATA_BODS_TESTING TO SYSREPO WITH GRANT OPTION

For query on attribute view you need:

- analytic privilege for this view

- read privilege on corresponding package

- select privilege on used tables (or their schema)

- select privilege with admin option on used tables (or their schema) for SYSREPO user

For query on analytic view or calculation views you need:

- all privileges for used attribute views

- analytic privilege for this view

- read privilege on corresponding package

- select privilege on used tables (or their schema)

- select privilege with admin option on used tables (or their schema) for SYSREPO user

!!! BUT !!!

You can also access data by looking on view in schema SYSBIC / Column Views / <package>/<model name>.

To access data you need:

- analytic privilege for this view

- select privilege for schema SYSBIC (which is part of CREATE ANY privilege that you need to be able to activate models)

Please go through the lists to validate you got all parts correctly configured.

3.) Again - tables cannot exist in package they exist only in schema - can you please rephrase your question? Do you mean "imported views in package" or do you mean "provisioned tables in schema"

[Former Member]

1. Thank you Tomas , once again.

I will play around with these & let you know if it works for me.

3. yes, I meant " Table provisioning in Schema" , so all the user access a particular Package can view/use Tables imported in each other's schema.

[tomas-krojzl]

Hello,

first option is to grant select privilege on whole schema. (Note that there should not be any other privilege then select privilege granted to anyone for table that was provisioned.) But I guess that this is something you do not want.

Other option is to create several roles - one for each division and then for each provisioned table to grant select privilege on table level to these "division roles". You will need dedicated "data provisioning expert" (might be you) that will take care of this post activity.

Addition information that might be important for you is that SLT is replicating whole table (all clients). And on table level you cannot enforce row level security (this works only for models). If this is issue and modellers must not see data from all clients - then I am afraid you will need separate HANA system for each division. I am not aware about any "supported" possibility to replicate data from just one client or to replicate each client into separate table. (Unsupported approach can be messing with triggers but I would not recommend that.)

In such case you would need more sophisticated approach - for example having development HANA working with dummy data from development system and after models are done then you can transport them into production HANA where modellers would not have any direct access.

Tomas