How to GRANT the Analytic Privilege _SYS_BI_CP_ALL using SQL?

[Former Member]

I'm trying to define a user named LUMIRA that can be used as a least privileged user for accessing Analytic Views that I've created for use with Lumira.

So far, I did the following to define access to the various schemas

CREATE USER LUMIRA PASSWORD Hana1234;

GRANT SELECT ON SCHEMA "WIKIDATA" TO LUMIRA;

GRANT SELECT ON SCHEMA "_SYS_BI" TO LUMIRA;

GRANT SELECT ON SCHEMA "_SYS_BIC" TO LUMIRA;

GRANT EXECUTE ON "SYS"."REPOSITORY_REST" TO LUMIRA;

The problem is I can't figure out the syntax for granting the _SYS_BI_CP_ALL Analytic Privilege. I was able to do this in the UI as shown below.

This is what the privilege looks like in GRANTED_PRIVILEGES.

I'm using SPS5.

Thanks in advance,

Bill

[lbreddemann · Accepted solution]

Hi Bill

_SYS_BI_CP_ALL is an analytic privilege itself.

It can only be granted by calling the procedure GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE:

call GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE ('_SYS_BI_CP_ALL', 'LUMIRA');

and you're done.

- Lars

[Former Member · Accepted solution]

Thanks Lars - I guess I got lucky. More importantly, you confirmed it! Time to add it to my blog so I don't forget.

[lbreddemann · Accepted solution]

Great! That's the approach I really like: finding out stuff by really looking at what the system does (e.g. JDBC trace, Shared SQL cache, etc.) and documenting it for public access.

Really good!

BTW: the thing about the procedure meanwhile made it also into the documentation .

Cheers, Lars

[Former Member]

I figured it out by looking at the SQL Plan Cache after assigning the Analytic Privilege using the UI.  I saw a statement that looked like this:

call GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE(?,?);

Knowing that the ? are markers for parameters, I guessed that the command should look like this.

call GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE('_SYS_BI_CP_ALL','LUMIRA1');

Sure enough, it worked!

[lbreddemann]

Here you go. Found it yourself, just when I wrote my answer

I should wait longer next time

- Lars