Problem scheduling XS job under different user

[patrickbachmann]

Hi folks,

In enterprise HANA I typically write procedures that run under a different user's credentials such as SYSTEM or JOB_ADMIN for example.  Recently I've been starting to do development on a BW HANA server and I can't seem to accomplish the same thing.  I've created this ultra simple test procedure;

PROCEDURE "BACHMANN"."MYPACKAGE::TESTPROC" ( )

LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  READS SQL DATA AS

BEGIN

select * from "BACHMANN"."MYTABLE";

END;

When I run this procedure as myself (user BACHMANN) it executes successfully.

When I change schema at the top of the procedure from BACHMANN to another user like SYSTEM or JOB_ADMIN (as I would normally do in our EHANA environment) it fails with 'not authorized' error.  I've given the other user(s) full access with grantable to my schema.  I've given them access to MYPACKAGE.  I've given them every XS* role known to mankind and still it doesn't work.  What might I be missing?  The only roles I'm seeing in my enterprise HANA users that I do not see available in BWHANA is these;

sap.hba.explorer.roles::Business

sap.hba.explorer.roles::Developer

Could it be related to these?  Other than that I'm completely stumped.

-Patrick

[vivekbhoj · Accepted solution]

Hi Patric,

I think you don't need the below roles or any xs roles for this:

sap.hba.explorer.roles::Business

sap.hba.explorer.roles::Developer

Are you trying to create the new procedure using your user or using SYSTEM/ JOB_ADMIN user?

If you are trying to create the procedure using your own user then do you have CREATE ANY privilege access and other accesses in SYSTEM/JOB_ADMIN schema?

Have you tried creating the same procedure using SYSTEM user with SYSTEM schema?

Regards,

Vivek

[patrickbachmann · Accepted solution]

Hi Vivek,

I'm creating the procedure with my own credentials, testing it, then change the schema to JOB_ADMIN or SYSTEM so that it runs under their credentials.  I don't have access to sign on as SYSTEM in BWHANA but I will try to create directly with JOB_ADMIN user and see if I get similar issue this afternoon.

Thanks,

-Patrick

[patrickbachmann · Accepted solution]

Ok thanks Vivek,

Your suggestion of testing creating the proc with the other user (ie: JOB_ADMIN) worked.  I was then able to discover the issue was that when I created the procedure with my user BACHMANN I had CREATE ANY with Grantable = Yes against JOB_ADMIN schema.  I had no other options checked.  When i checked off all the options for the schema it began working. 

-Patrick