createMaterializedSearchResult failed error SQL: insufficient privilege

[former_member196166]

I'm trying to figure out security in HANA, but get stuck. My aim is to first create a user that only has read access to an analytic view. What I did was the following:

1. Create a new user
2. Grant SELECT rights to the schema where the data resides
3. Create an analytical privilege on the content package and grant that to this user
4. Grant the package privilege REPO.READ to this user.

I cannot get this to work. The user is not authorized to view data from the analytic view. The error:

sql processing error: createMaterializedSearchResult failed error SQL: insufficient privilege: Not authorized

This suggests to me that a temporary result must be stored somewhere and the user is not authorized to do so. What privileges does a read-only user need?

Paul Laman

Netherlands

[Former Member]

Paul,

Create a Package called package as ADMIN and then execute the following script:

-- Create REPO_READ_ROLE Role

CREATE ROLE REPO_READ_ROLE;

-- Assign GRANTED ROLES to ROLE
GRANT EXECUTE on REPOSITORY_REST to REPO_READ_ROLE;

-- Assign SQL/Object Privileges

GRANT SELECT ON SCHEMA _SYS_BI to REPO_READ_ROLE;

GRANT SELECT ON SCHEMA _SYS_BIC to REPO_READ_ROLE;

GRANT SELECT ON SCHEMA _SYS_REPO to REPO_READ_ROLE;

-- Assign Package Privileges

GRANT REPO.READ on "package" to REPO_READ_ROLE;

-- Create REPO_READ_ONLY Users
CREATE USER REPO01 PASSWORD Hana1234;

--Grant role to REPO_READ_ONLY Users
GRANT REPO_READ_ROLE TO REPO01;

[justin_molenaur2]

Reading through all the responses - these are very helpful. However, it looks like no one has mentioned that system user _SYS_REPO must have SELECT access that is -grantable- on the schema(s) of the data you are exposing in the analytic view. Otherwise, what you have shown looks like it will work.

Have gotten burnt on this one before, the user that actually is executing the model is _SYS_REPO, and he needs to be able to pass the SELECT privilege onto the user who is invoking (ie you) the model (grantable).

The following are bare minimum privileges for a user attempting reading the models.

Granted Roles: PUBLIC, comes by default

Object Privileges: SELECT/EXECUTE on _SYS_BIC and _SYS_BI

Analytic Privileges: _SYS_BI_CP_ALL unless further row level restriction is required. I would try to make this work before adding more authorizations.

This authorization is required for _SYS_REPO

Object Privileges: SELECT on <SCHEMA>, checkbox grantable

Package privileges and REPOSITORY_REST are actually not required, unless the user needs to browse within HANA Studio.

Regards,

Justin

[Former Member]

Open the SQL Editor as an ADMIN user or with specific privileges and issue the following for the specific "user":

GRANT EXECUTE on REPOSITORY_REST to user;

Using a script to create a general role does not automatically assign PUBLIC to the user when a user script is ran to assign the general role to the user unless you add the above line in the script of the general role.

[Former Member]

Hi,

Have you tried to run the following?

grant select on schema <SCHEMA> to _SYS_REPO with grant option;

grant select on schema <SCHEMA> to <USER>;

[rama_shankar3]

Please try Michaels syntax by

logging in  as SYSTEM and grant permissions to your user. It should work.

Hope this helps!

Regards,

Rama

[jones_joseph]

Thanks Mike. this solves my error "user is not authorized(2950) in Hana".

[Former Member]

Discussed a similar issue (http://scn.sap.com/thread/3198430) few days ago and the basic authorisation required for viewing analytical view data as I found out was:

Granted Role - PUBLIC

SQL Privilege - Execute & Select access on _SYS_BI and _SYS_BIC. Also Execute on Stored procedue REPOSITORY_REST

Analytical Privilege - _SYS_BI_CP_ALL (but you can use your own analytical privilege if you dont want to give access to all)

System privilege - Catalog Read

Package Privilege - Root (and REPO.READ on it) - again you could give your own package name is you dont want to give access to all packages.

[Former Member]

Give SELECT access to schema _SYS_BIC

[former_member196166]

Forgot to mention that one. User has ALL privileges on _SYS_BIC...

It also has EXECUTE on REPOSITORY_TEST. Read that somewhere, but don't know whether that's needed.

[Former Member]

How about read on _SYS_BI ?