cancel
Showing results for 
Search instead for 
Did you mean: 

Need help with creating a new user/analytic privilege

BenedictV
Active Contributor
0 Kudos

Hi,

I was trying to create an analytic privilege for a new user I created on the 'hanacloud' '00' system. The analytic privilege is over a calculation view restricting certain shop IDs.

I followed Anooj's recomendation in this thread http://scn.sap.com/thread/3212315 to set the authorizations.

But the problem is, if I add the _SYS_BI_CP_ALL along with the privilege that I created then all values in the calculation view are displayed. If I remove it and leave only the AP that I created then I am getting the following error during data preview.

"ERROR EXEXUTING THE QUERY

Cannot iterate over result set rows: SAP DBTech JDBC: [2048]: column store error: search table error:  [2950] user is not authorized"

Thanks,

Benedict

Accepted Solutions (1)

Accepted Solutions (1)

rindia
Active Contributor
0 Kudos

Dear Benedict,

Please refer to SAP note 1809199 which describes the solution for your problem.

For your convenience I am pasting the solution below:

Solution

1. In HANA Studio, via the system panel open Trace Configuration tab

2. If you are performing analysis on a system with high amount of user traffic it is recommend to perform a User-Specific trace, otherwise choose Global Database trace

3. Type in "Authorization" in the blank text field above and check mark "Show all components" below

4. Select Trace Level "Info" for component Global/Indexserver: Authorization

5. Select "Finish"

6. Execute the steps to reproduce the error message

7. When you have observed the error message restore tracing to default

8. Go to "Diagnostic" tab in system panel and open latest the index server trace file

9. In the text editor search for "Current situation" to find relevant log statements

You will see a list of AP ObjectId's. These are the analytical privileges that are required to view your particular view/object. The user that is executing the query needs no more than one of the listed analytical privileges.

UserId(12345) is missing analytic privileges in order to access _SYS_BIC:<package>/<object_name>/...

Current situation:

AP ObjectId(13,2,oid=3): Not granted.

AP ObjectId(13,2,oid=123456) Not granted.

To look up the names of these analytical privileges in your system execute the following query:

SELECT * FROM OWNERSHIP WHERE OBJECT_OID IN (<ID#1>, <ID#2>, <etc>);

Important note: In most systems one of the ObjectId's will always include _SYS_BI_CP_ALL as a possible missing analytical privilege. This analytical privilege grants a user global access privileges and must not be used for modelling purposes or in production systems.

To view what analytical privileges are assigned to a specific user account execute below query:

SELECT * FROM EFFECTIVE_PRIVILEGES WHERE USER_NAME = '<username>' AND OBJECT_TYPE = 'ANALYTICALPRIVILEGE';

If the user is granted multiple analytical privileges, the definition of individual analytical privileges can be examined using the following query:

SELECT * FROM STRUCTURED_PRIVILEGES WHERE STRUCTURED_PRIVILEGE_NAME LIKE'%<analytical_privilege_name>%';

In the definition of the analytical privileges, pay attention to two restrictions with the restriction types CUBERESTRICTION and DIMENSIONRESTRICTION: Only if a view is included in the one of the cube restrictions and at least one of its attribute is employed by one of the dimension restrictions, access to the view is granted by this analytical privilege.

A user can only see privileges granted to himself in the system views EFFECTIVE_PRIVILEGES and STRUCTURED_PRIVILEGES.

This is sufficient to find out own missing analytical privileges.

Regards

Raj

BenedictV
Active Contributor
0 Kudos

Hi Raj,

Thanks for the huge effort you have put in to answer my question. Also helped me learn the technicalities regarding APs.

I see that I am missing _SYS_BI_CP_ALL. Which brings us back to my initial question. If I include this AP then all values are displayed if not I am getting an error.

I will do some more reading on this and try again.

Thanks,

Benedict

FabioV
Explorer
0 Kudos

Hi Benedict,

I've exactly the same issue you have: did you finally succed and find a soultion?

Any help would be very appreciated.

Regards

Fabio

BenedictV
Active Contributor
0 Kudos

No Fabio, I didnt find a solution then. Cloudshare was in SP4 Rev45 or less when I did this. I think now the it is SP5. I will give it a try once again.

Goodluck to you ,

Benedict

FabioV
Explorer
0 Kudos

Hi Benedict,

I solved, it was just my fault since I didn't define the Analytical Privilege correctly.

Investingating this issue I realized thta at least one attribute restriction must exists for at least one attribute of every view that must be authorized.

In my case I had the same attribute in Analytical views and in Calculation views but the tecnical name is different: in Analytical view the tecnical name is <package name>/<attribute view name>$<attribute name> while in Calculation Views it is simply <attribute name> (this is well clearly stated also in the HANA dev guide and can be checked querying the STRUCTURED_PRIVILEGES).

This required me to define two different attribute restrictions in the Analytical Privilege and then my AP worked fine and the _SYS_BI_CP_ALL has not been granted to the user.

Thanks for your quick answer.

Fabio

.

BenedictV
Active Contributor
0 Kudos

A pity, I dont have the object anymore to check this . Anyway thanks for the info Fabio.

Regards,

Benedict

justin_molenaur2
Contributor
0 Kudos

Was running into the same problem on my end. Through this series of posts I was able to resolve.

It's not super intuitive that you need to add at least one dimension from each view to fully allow access a calc view. One would think that just adding the view would provide an ALL (*) access unless a restriction was assigned.

Regards,

Justin

Former Member
0 Kudos

Hi Justin,

I'm running in this issue, wherein i created teh following objects:

a) With my user, I created a Table with UserID and Cost Center Mapping and inserted a record for User 'U2'

b) Also i created a Structured Privilege as well.

c) I've given 'U2' EXECUTE and SELECT on SYS_BI and SYS_BIC and also granted this structured privilege created in step (b).

d) But now when I'm doing a select on an Analytic View as user 'U2', then i'm getting error as shown below:

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

Can you please let me know what might be the root cause of this issue?

Thanks & regards,

Jomy

justin_molenaur2
Contributor
0 Kudos

Can you elaborate on how you defined the analytic (structured) privilege you mentioned? What exactly are your filters and object selections? I assume you are trying to use dynamic restrictions with the use of a stored procedure?

What schema did you create the new table in? You need to ensure that user _SYS_REPO also has select access to this schema/table as well - I assume this has to be the case as you wouldn't be able to activate the analytic view with it.

Regards,

Justin

Former Member
0 Kudos

Hi Justin,

Yes, you are right. I'm trying to use dynamic restrictions with the use of stored procedure.

with the following code:

CREATE STRUCTURED PRIVILEGE '<?xml version="1.0" encoding="utf-8"?><analyticPrivilegeSchema version="1">

<analyticPrivilege name="Test/STRUCT_PRIV_AN_DYNAMIC_COSTCENTER">

<cubes>

      <cube name="_SYS_BIC:Test/ZAN_POC_COPA_CC" />

      <cube name="_SYS_BIC:Test/ZAN_POC_COPA_CC/olap" />

</cubes>

<validity> <anyTime/> </validity>

<activities> <activity activity="read" /> </activities>

<dimensionAttributes>

  <dimensionAttribute name=" Test/ZAN_POC_COPA_CC$COPA_KOSTL">

   <restrictions>

     <valueFilter operator="EQ"> <value value="1"></value></valueFilter>                

     <valueFilter operator="IN">

       <procedureCall schema="JOMY" procedure="ZPR_USER_AUTH_CC"/>                       

     </valueFilter> 

   </restrictions>

  </dimensionAttribute>

</dimensionAttributes>

</analyticPrivilege></analyticPrivilegeSchema>';

The table is in my schema i.e. JOMY and _SYS_REPO has select access to the Analytic View.

Now we've a end user i.e. 'U2" with no _SYS_BI_CP_ALL privilege but had execute and select on SYS_BIC and SYS_BI schema, but getting error"User is not authorized".

Let me know, if you need any further info.

Thanks & regards,

Jomy

justin_molenaur2
Contributor
0 Kudos

Ok, what I think is going on here is the following

- Your procedure to return Cost Center values from a table in schema "JOMY" is defined as a catalog object in the schema "JOMY" and my guess is that it is set up as SQL security invoker.

- The procedure is being executed by user "U2", who has no 'execute' privileges for schema "JOMY"

- The stored procedure fails/returns no values

- The user is not authorized to see any data

Can you post the create statement for your stored procedure and also attempt to execute the catalog stored procedure (CALL) as "U2" in the SQL console?

Regards,

Justin

Former Member
0 Kudos

Hi Justin,

- The Procedure is set up as 'Security Definer'

- I've given Execute privileges to User 'U2' for Schema 'JOMY'

- The Stored procedure returns with success as I tried to execute the catalog stored procedure as User 'U2' and it returns with the desired result.

procedure Code:

CREATE PROCEDURE "JOMY"."ZPR_USER_AUTH_CC" (OUT TAB_CC "JOMY"."TT_COST_CENTER")

LANGUAGE SQLSCRIPT SQL SECURITY DEFINER READS SQL DATA AS

 

   BEGIN

        TAB_CC = SELECT COST_CENTER FROM "JOMY"."CC_AUTH"

                    WHERE USER_NAME = SESSION_USER;

     END;

justin_molenaur2
Contributor
0 Kudos

Two things I would try

- Change the definition to security invoker, since "U2" already has auth it should work fine in this context.

- Make sure that the execute privilege is grantable on the definer's ID.

I had a similar problem, and switching from definer to invoker helped out. Additionally I would consider moving the procedure from catalog to content, so you only really have to ever worry about SYS_REPO having the correct authorizations.

Regards,

Justin

Former Member
0 Kudos

Sorry Justin,

I didn't get your point.

a) I changed the 'DEFINER' to 'INVOKER' in the procedure and now User 'U2' is not able to run the procedure.

I don't know how changing the procedure will make any difference,as I was able to run and have return values successfully,once I ran with User U2 earlier.

What does this mean :

Make sure that the execute privilege is grantable on the definer's ID.

Thanks & regards,

Jomy

Answers (1)

Answers (1)

YatseaLi
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi all,

Just would like to share my findings in HANA Rev 97.02.

Just apply privilege in the main view as "analytical privilege".

and leave the apply privilege field as blank for the others depending views(dimensions)

It is not required to add at least one attribute from depending views into the AP.

The minimal object privilege for the user:

  • _SYS_BI: Select (Without this you can’t open the data preview, but you can access the view with sql script without data preview.)
  • REPOSITORY_REST: Select (Allow the new user can view the package in HANA studio)
  • The main view: Select.

Kind Regards, yatea