No authorization to execute PAL script

[Former Member]

Hi all,

I am new to PAL and I am trying to run this simple example

create type PAL_T_TS_DATA as Table (CALENDAR_ID INT, SALES_AMOUNT  DOUBLE);

create type PAL_T_TS_PARAMS as table (NAME VARCHAR(60), INTARGS INTEGER, DOUBLEARGS DOUBLE, STRINGARGS VARCHAR(100));

create type PAL_T_TS_RESULTS AS Table (CALENDAR_ID INT, SALES_AMOUNT DOUBLE);

create column table PAL_TS_SIGNATURE (ID INTEGER, VARCHAR(100), DIRECTION VARCHAR(100));

insert into PAL_TS_SIGNATURE VALUES (1, "PAL_T_TS_DATA", "in");

insert into PAL_TS_SIGNATURE VALUES (2, "PAL_T_TS_PARAMS", "in");

insert into PAL_TS_SIGNATURE VALUES (3, "PAL_T_TS_RESULTS", "out");

CALL SYSTEM.AFL_WRAPPER_GENERATOR ("PAL_TS_S", "AFLPAL", "SINGLESMOOTH", PAL_TS_SIGNATURE);

but I am getting this error:

SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

it is clear that the problem is that i don't have sufficient privileges, the PAL documentation says

that I have to have the following role in order to execute pal scripts:

AFL__SYS_AFL_AFLPAL_EXECUTE

Does anyone know if being granted this role is sufficient to solve the problem?

EDIT:

I contacted my HANA sys admin and I am already granted the CMS_TESTER role which includes the AFL__SYS_AFL_AFLPAL_EXECUTE role

so the problem is not in granting this role. Does anyone know what could be the cause of this problem?

Thanks & Regards

Mohamed Ali

[former_member193591]

One of the reason why this could happen is when we try to execute the algorithm through SYSTEM user or schema; create a user and assign

Grant the required access and try to execute the algorithm through a user:

CREATE SCHEMA "SCHEMA";

GRANT CREATE ANY, ALTER, DROP, EXECUTE, SELECT, INSERT, UPDATE, DELETE, INDEX ON SCHEMA "SCHEMA" TO USER1;

GRANT SELECT ON SCHEMA USER1 TO SYSTEM with GRANT OPTION;

GRANT SELECT ON SCHEMA "SCHEMA" TO SYSTEM with GRANT OPTION;

GRANT SELECT ON SCHEMA USER1 TO _SYS_REPO with GRANT OPTION;

GRANT SELECT ON SCHEMA "SCHEMA" TO _SYS_REPO with GRANT OPTION;

GRANT CONTENT_ADMIN TO USER1;

create the wrapper generator & eraser .... and assign access to the user

GRANT EXECUTE ON system.afl_wrapper_generator to USER1

GRANT EXECUTE ON system.afl_wrapper_eraser to USER1

AFL__SYS_AFL_AFLPAL_EXECUTE Role has to  be assigned to the user ... user has to have execute access for the schema under which we are generating tables;

Schema should belong to USER1, who should also be granted the privilege of executing SYSTEM.afl_wrapper_generator and SYSTEM.afl_wrapper_eraser; and●

USER1 has been assigned the AFL__SYS_AFL_AFLPAL_EXECUTE role.

[henrique_pinto]

Hi Mohamed,

I'd suggest to explicit the schema in which you're trying to create your objects, so that you're sure you're trying to create them under a schema you have read/write authorization for.

Also, which line threw the error?

Could you try to execute line by line and then check which one failed?

Best regards,

Henrique.

[Former Member]

indeed as Henrique stated, this is probably because of missing permissions in the schema you use.

Did you follow the R integration guide for HANA?  As there is more than just assigning the role you mentioned.

[Former Member]

Hi Henrique,

Thanks for responding,

I am following the PAL developer guide so I am using the _SYS_AFL schema

for the error, even trying to create a type throws the error, so if I run this :

SET SCHEMA _SYS_AFL;

create type PAL_T_TS_DATA as Table (CALENDAR_ID INT, SALES_AMOUNT  DOUBLE);

i get this error:

Could not execute 'create type PAL_T_TS_DATA as Table (CALENDAR_ID INT, SALES_AMOUNT DOUBLE)' in 167 ms 83 µs .

SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

Thanks & Regards

Mohamed Ali

[Former Member]

Does your user has select and execute permissions on the _SYS_AFL schema?

The whole permission setup in HANA can be quite confusing.

What you need is:

- GRANT EXECUTE ON system.afl_wrapper_generator to USER1 (logged on with SYSTEM user)

- GRANT SELECT ON user1.<signature table> to SYSTEM (logged on with your user)

Can you try that?

[henrique_pinto]

Hi Mohamed,

I'm not sure you're supposed to create custom objects in that schema.

It's a system schema to hold the AFL related objects that are released by SAP.

That's probably the reason you're getting an authorization error.

Each user in HANA has its own schema.

I'd suggest that you use your user's schema for creating these objects.

Best regards,

Henrique.

[Former Member]

Hi Henrique,

Thanks again for responding. As I said, I am a newBie to PAL and it's

my first example that I am trying to run. And, yes I discovered what you said the hard way, I shouldn't be creating the PAL tables in the _SYS_AFL Schema, but i was following the examples giving in the developer guide, a bit misguided.

Thanks & Regards

Mohamed Ali

[former_member300783]

Hi Mohamed

Thanks for your interesting to PAL.

I would like to suggest you to use the latest PAL guide via

http://help.sap.com/hana/hana_dev_pal_en.pdf

We already updated the guide's sample script which request user should NOT use _SYS_AFL schema.

More question, please let me know.

Thanks again.

[former_member300783]

and also at the same time, have you grant the EXECUTE privilege of system.afl_wrapper_generator to the user you are connecting? You could refer to the PAL reference guide Chapter 2.4 Step 1.

Thanks a lot.

[Former Member]

Yes, I am granted the CMS_TESTER role which includes the afl_wrapper_generator execute role.

Thanks

[former_member300783]

Hi Mohamed

Please kindly create CSN ticket to our component BC-DB-HDB-AFL-PAL with your detail instance information and attach your script.

Thanks a lot.

Jimmy

[Former Member]

hi, thanks for responding,

but i am not using R in this case.

[Former Member]

Hi all, this problem was resolved by:

     - executing the script on my own user schema.

     - Some PAL tutorials are using the _SYS_AFL schema which is incorrect or at least not recommended. 

     - also you will get an error if you execute the script with the same procedure name twice => you need to change the procedure name or create the AFL_WRAPPER_ERASER procedure to erase the procedure before recreating it.

     - you will also get an error that states the existence of duplicated column names in the type table created by the AFL_WRAPPER_GENERATOR, you can solve this issue by granting select permission on the signature table that you have created to the SYSTEM user

for example: GRANT SELECT ON "you_schema"."your signature table" to SYSTEM;

These some of the issues I came across while working with PAL. in short follow the examples in the PAL guide.

Thanks & regards.

Mohamed Ali

[Former Member]

Greetings,

I am trying out SAP PAL on SAP HANA developer version on Amazon cloud. I got an error message that is same as the discussion here.

I login as "SYSTEM". I created a schema PAL under Catelog. I can run

CALL SYSTEM.AFL_WRAPPER_GENERATOR and Eraser. I can also upload data into the PAL schema.

But, in the final step to run "CALL _SYS_AFL.PAL_Proc" to begin association rule process, I got the following error:

Could not execute 'CALL _SYS_AFL.PAL_Proc(V_AP_DATA, AP_PARAMS, AP_RULES, AP_PMML) WITH OVERVIEW
SAP DBTech JDBC: [258]: insufficient privilege: Not authorized

I checked "SYSTEM" user's setting, there are only "AFLPM_CREATOR_ERASER_EXECUTE", "CONTENT_ADMIN", "MODELING", "PUBLIC".

So, I try to add "AFL__SYS_AFL_AFLPAL_EXECUTE" to SYSTEM using HANA Studio GUI. But, there is an "X" beside it and it is red after I added via GUI.

I also tried "Grant AFL__SYS_AFL_AFLPAL_EXECUTE to SYSTEM; " But, I got another message

SAP DBTech JDBC: [7]: feature not supported: grantor and grantee are identical

Can anyone help me here? I am just one step to complete my first PAL example.

Thanks.

Scott,

[former_member300783]

Hi Scott

It is NOT suggested to call PAL function via SYSTEM user, Please kindly create normal DB user and follow the PAL manual guide to grant authorization and then try to run.

Thanks a lot.

Jimmy

[Former Member]

Jimmy,

Thanks for your reply.

I tried to create another user and login using this new user. When I try to run

CALL _SYS_AFL.PAL_Proc(V_AP_DATA, AP_PARAMS, AP_RULES, AP_PMML) WITH OVERVIEW

I got another error message:

"Could not execute 'CALL _SYS_AFL.TitanicProc (V_AP_DATA, AP_PARAMS, AP_RULES, AP_PMML) WITH OVERVIEW' in 81 ms 966 µs.
SAP DBTech JDBC: [2048]: column store error: search table error:  [34091] No ScriptServer available. See SAPNote 1650957 for further information."

But, I have followed HANA video and used "SYSTEM" user to change the ScriptServer setting under "Configuration.Daemon.ini.scriptserver.instances" to 1. I also double checked this settng.

So, would you please advise what should I solve this problem?

Also, I know that it is not recommended to call PAL function via SYSTEM user. But, just as a quick test, why do I get error message like "SAP DBTech JDBC: [258]: insufficient privilege: Not authorized" when I try to "CALL _SYS_AFL.PAL_Proc(......)"? How can I enable the privilege so I can do a quick test?

Thanks in advance for your help.

Scott,

[Former Member]

I actually figured it out. To ensure PAL run properly, I have to use "m2.2xlarge" on AWS which costs about $1/Hr. Earlier, I tried to save money so I used "m2.xlarge". It will have "ScriptServer" error. Once i switch to "m2.2xlarge", I can run PAL SQL.

But, I have another question.When I clicked on "Catalog" for both SYSTEM and general users, it shows a "clock" with "Pending..." message. It just keep pending there forever. Is there a solution? (I know I can right click and use "Find Table" to show data. But, it is inconvenient (with some other problems).

Anyone experience this? Why? and any solution?

Thanks.

Scott,

[former_member300783]

Hi Scott

Thanks a lot for your information.

I don't think you could enable the privilege to run PAL as SYSTEM user, since it is NOT allowed by HANA by Security reason.

For your second question, Sorry I don't have such experience, it might be releated to the network reason?

Thanks a lot.

JImmy

[Former Member]

Hello Chih Lai,

I am in a very similar situation. I have configured the SCRIPTSERVER to active. MY scriptserver is running. NOw I get an error "No scriptserver available".

I have AWS HANA instance. So, could you please tell me  where did you find this ""m2.xlarge" ?

I need to check it too since I feel I am running the same situation as you described 😮

Thanks

Sangamesh