Issues with Dynamic Analytic Privilege using procedures

[Former Member]

Hi All,

I have created an catalog/Repo procedure which gives Dynamic Output to Analytic Privileges. Applied this AP to a Role and that Role is assigned to a User.

when i login as that User and do a data preview on that Analytic View or Calculation view, i get below error.

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

Requirement:

Need to restrict an user from AMER region from accessing other region data. created an catalog/Repo procedure as shown below,

CREATE PROCEDURE "MySchema"."USER_SECURITY" (

OUT VALTAB " MySchema"."AUTH_INFO_FILTER" )

LANGUAGE SQLSCRIPT

SQL SECURITY DEFINER

READS SQL DATA AS

BEGIN

VALTAB = SELECT "NT_Login"

FROM " MySchema "."USER_DETAIL"

WHERE "NT_Login" =  SESSION_USER;

END;

This will dynamically capture “SESSION_USER” and compare it with NT_LOGIN in USER_DETAIL table which is joined to Dimension and Fact table in Analytical View.

Based on that User will get to see data for his specific region.executed below statement after creating this procedure;

Grant execute on "MySchema"."USER_SECURITY" to "_SYS_REPO" with grant option

Analytic Privilege:

“Reference Model” - is my Analytical Privilege

“Associated Attribute Restriction” – Attribute view of USER_DETAIL

“Assign Restrictions” – Catalog or Repo procedure / Value – USER_SECURITY (Procedure which I created)

This AP is applied to a AMER_ROLE and AMER_ROLE is assigned to AMER_USER.

Privileges Applied to Role(AMER_ROLE):

System Privileges:

CATALOG READ

Object Privilege:

_SYS_BI - SELECT/EXECUTE

_SYS_BIC - SELECT/EXECUTE

_SYS_REPO - SELECT

GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE(_SYS_REPO)

GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT(_SYS_REPO)

also gave Select access to the Schema and it’s all under lying tables in content.

Analytic Privileges:

AP which i created

Package Privileges:

Package which i created for that users domain

Privileges Applied to User(AMER_USER):

Granted Roles:

PUBLIC

AMER_ROLE

Not sure if the issue is with Procedure or Privileges, any suggestion / help in fixing this issue will be greatly Appreciated.

Fyi…later we will connect Calculation view to BO universe, users will use BOBJ Webi to view the report using SSO.

Regards,

AV

[justin_molenaur2 · Accepted solution]

Reading this again, I am a little confused. You say "created an catalog/Repo procedure", did you create a catalog OR a repository procedure? From the looks of your code this is a catalog procedure, you should be creating in the repository. For repositoryprocedures, my recommendation above is valid.

Regards,

Justin

[Former Member · Accepted solution]

Justin, Thanks for your time.

Data to Analytic View are sourced from the tables in "MySchema" and couple of other Schema.Have executed below command for other 2 schema as well.

GRANT SELECT  ON SCHEMA "MySchema" TO "SYSTEM" WITH GRANT OPTION;

I did try removing Dynamic AP and apply "_SYS_BI_CP_ALL"", User was able to view all 3 regions data.

However, when i removed "_SYS_BI_CP_ALL"and applied Dynamic AP to User. he gets user is not authorized error.

Yes above code is for Catalog Procedure, Since catalog procedure didn't work, i  tried creating Repo proc for that Dynamic AP. Even that gave same error.

Could you please let me know if i need to grant any Privileges to Dynamic AP itself and below Dynamic AP is valid.

Analytic Privilege:

“Reference Model” - "My Analytical Privilege"

“Associated Attribute Restriction” – Attribute view of USER_DETAIL

“Assign Restrictions” – Catalog procedure / Value  (i selected the procedure name(USER_SECURITY) which gives Output value (NT_Login) by executing the Catalog Procedure)

Regards,

Av

[justin_molenaur2 · Accepted solution]

You show you granted select to SYSTEM, when it should be SYS_REPO. That is the actual user who is calling your repository procedure.

if you can read the AV without the AP, you narrowed down the issue to this. Try above out.

Another thing to check - make sure that the data in your security table (that the SP reads from) matches the case of your HANA user (all caps) and the same for the column in the AV where the restriction is applied, ie they should all be uppercase or your select or restriction might give an incorrect result.

Regards,

Justin

[Former Member · Accepted solution]

Executed below statements,

GRANT SELECT ON SCHEMA "MySchema" to "_SYS_REPO" WITH GRANT OPTION;

GRANT EXECUTE ON SCHEMA "MySchema" to "_SYS_REPO" WITH GRANT OPTION;

GRANT SELECT  ON SCHEMA "MySchema" TO "SYSTEM" WITH GRANT OPTION;

GRANT EXECUTE  ON SCHEMA "MySchema" TO "SYSTEM" WITH GRANT OPTION;

Also under Package Privileges for that Role I have included the projects and enabled “REPO.READ” and “Grantable to others”

Ensured all column names and values are case sensitive compliant, still same error.

Could you please let me know if i need to grant any Privileges to Dynamic AP itself?

Regards,

Av

[justin_molenaur2 · Accepted solution]

No, there should be nothing related to AP directly as it's assigned to a role. What you have so far looks good, below is the very basic configuration present in a system that is using dynamic AP's.

Please do a couple things, I believe your issue is in the dynamic AP, so you need to narrow down where that is failing.

- Provide the SQL you are using to query the AV as the user "AMER_USER"

- Execute this SQL as modeler user (you) and provide the results

SELECT "NT_Login" FROM "MySchema"."USER_DETAIL" WHERE "NT_Login" = 'AMER_USER';

- Execute this SQL as modeler user (you) and provide the results

SELECT <CHOOSE_COLUMN_FIELDS> FROM <ANALYTIC_VIEW> WHERE "USER_DETAIL" IN (SELECT "NT_Login" FROM "MySchema"."USER_DETAIL" WHERE "NT_Login" = 'AMER_USER')

GROUP BY <CHOOSE_COLUMN_FIELDS>;

-Execute this SQL as end user (AMER_USER) and provide the results

CALL <Stored_Procedure>;

User

Granted Roles

PUBLIC

Object Privileges

SYS_BI - SELECT

SYS_BIC - SELECT

REPOSITORY_REST

Analytic Privileges

Defined AP for dynamic

SYS_REPO

Object Privileges
Schema with Analytic View Data - SELECT, grantable

Schema with Security Data - SELECT, grantable

Many others..

Analytic Privilege

Reference Model - Target Analytic View

Attribute Restriction on column; Repository Procedure; Operator "IN"; <AP path>

For additional details, OSS Note 1809199 provides detailed information on how to debug these types of authorization issues.

Regards,

Justin

[former_member182302 · Accepted solution]

Adding to the Justin points,

As i see in your procedure the below code:

VALTAB = SELECT"NT_Login"
FROM " MySchema "."USER_DETAIL"
WHERE "NT_Login" =  SESSION_USER;

Instead of using the table directly i.e.

" MySchema "."USER_DETAIL"

create a attribute view and use that view in the procedure and check if it works.

Regards,

Krishna Tangudu

[Former Member · Accepted solution]

Thanks Justin, It worked.

Attribute Restriction on column Operator was "EQUAL" instead of "IN".

Thanks a lot for you time and suggestion. Appreciate it.

[Former Member · Accepted solution]

Thanks Krishna, Justin suggested me few steps, i was able to fix it.

[justin_molenaur2]

SYS_REPO also needs grantable select access to read the table holding the allowed values. Looks like the error is that this user can't read the tables.

Try this

GRANT SELECT ON "MySchema" to "_SYS_REPO" with grant option;

Also - you don't mention anything about where the data supplied to the Analytic View is sourced from, is it also from "MySchema"? If not, SYS_REPO also needs grantable SELECT access there.

One thing to try is to remove the dynamic AP from the role and give FULL (all data) access to the Analytic View to that role. If it doesn't work at this point, then something fundamental is wrong like my second point above.

Regards,

Justin