on 05-03-2013 10:25 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
.
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
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
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
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
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
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;
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
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
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:
Kind Regards, yatea
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
80 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.