Create local temporary table in procedure

[#1]

Hi folks,

I have a procedure (I wrote in the procedure editor in Project Explorer).  In the procedure I'm trying to create a local temporary column table however when I attempt to activate the procedure I get error;

feature not supported: DDL is not supported in the READ ONLY procedure

In the past when I created a procedure via old method (via CONTENT/NEW PROCEDURE) I had the option of choosing READ or READ/WRITE.  I'm not seeing this option in the new procedure editor.  Also I noticed a second tab next to SQLScript tab called LOCAL TABLE TYPES.  I can't seem to place my create local temporary table there either.

I've read in other posts that this is possible... what do I need to do in order to be able to create and update this temp table?

Thanks,

-Patrick

[patrickbachmann · Accepted solution]

Guys I think I finally found the solution to this problem due to some good karma.   This morning I was helping somebody on SCN with a temporal join and created an analytic view using tables in my personal schema.  To my surprise the view failed to 'activate' with 'unauthorized' error.  Since I was using tables in my personal schema i looked at privileges for _SYS_REPO and it indeed already has SELECT privileges against my schema.  So I started looking at _SYS_REPO's privileges against other schemas and noticed another schema (that works) had select but with also 'grantable to others'.  On a whim I added 'grantable to others' on select against my schema for _SYS_REPO and retried my procedure and it worked!

Now I'm curious if anybody knows why _SYS_REPO needs 'grantable to others' and also I'm assuming this means all temp tables are created in the invokers personal schema??  Albeit temporarily.

Thanks all!

-Patrick

[lbreddemann · Accepted solution]

HI Patrick,

_SYS_REPO requires GRANTABLE TO OTHERS (or WITH GRANT OPTION) because it needs to  grant back the permissions to the actual user activating the view.

What surprises me is that this had been the case since the first version of HANA modeller; which fooled me into thinking that this surely wouldn't be the cause for this problem

Anyway, good to hear that it now works for you (even with the *column* store temp table?

- Lars

[patrickbachmann · Accepted solution]

Yeah and I know what you're thinking... how come I didn't notice this problem sooner... but alas all of the 'play' or sandbox type stuff in my personal schema was mostly utilizing real tables in our default schema. 

[justin_molenaur2 · Accepted solution]

Hey Patrick, I have hit this a number of times and left scratching my head - so I have learned to look for this right away if I am using tables in new schemas or my personal schema.

Anything executed out of the repository is executed using the SYS_REPO user and not your own ID. Therefore, SYS_REPO must be able to "pass on" the privileges that he is assigned. This is achieved by making the privileges 'grantable' as you show.

In the case of analytic views, SELECT must be grantable on whatever schemas are involved. In the case of read/write procedures, the INSERT/DELETE/UPDATE must be grantable on whatever schemas are involved.

I can't comment on the local temporary table as I have not used it quite yet.

Regards,

Justin