Hana Stored Procedure

[Former Member]

HI All ,

IT will be nice if some one pin point me the issue , i am learning to write a stored procedure in hana and i have some privilege issue and not sure what is that ?

Code :

DECLARE CURSOR c_Tables FOR select a.schema_name as schema_name, a.object_name as table_name, map(t.auto_merge_on,'TRUE',1,0) as merge_on from ownership a, users b, tables t where b.user_name = a.owner_name and a.schema_name = :p_schema_name and t.schema_name = a.schema_name and t.table_name = a.object_name and b.user_name like 'P0%' and a.object_type = 'TABLE' ;

FOR cur_row as c_Tables DO

table_name := cur_row.table_name;

schema_name := cur_row.schema_name;

merge_on := cur_row.merge_on;

-- Rename table to temp table

exec ( 'RENAME TABLE ' || schema_name || '.' || table_name || ' TO ' || schema_name || '.' || table_name || '_TMP');

-- Recreate table and populate with data exec ( 'CREATE COLUMN TABLE ' || schema_name || '.' || table_name || ' LIKE ' || schema_name || '.' || table_name || '_TMP WITH DATA');

-- CHECK if Merge is needed

IF :merge_on = 1 THEN

exec ( 'MERGE DELTA OF ' || :schema_name || '.' || :table_name);

ELSE

select current_date from dummy;

END IF;

-- Drop temp table

exec ( 'DROP TABLE ' || schema_name || '.' || table_name || '_TMP');

END FOR;

END;

When i run store procedure without rename ,create column table,merge and drop table statement it is running fine without issue and when i run all the rename ,create column table,merge and drop table statement individually in sql console it runs fine without any issues but when i run that as a procedure like i get below privilege issue

Could not execute 'CALL "CHANGE_OBJECTOWNERSHIP" ('TEST1')' in 34 ms 730 µs . SAP DBTech JDBC: [258]: insufficient privilege: "CHANGE_OBJECTOWNERSHIP": line 88 col 5 (at pos 3230): [258] (range 3) insufficient privilege exception: insufficient privilege: Not authorized

Thanks

[lbreddemann]

The error message tells you that your user is not authorized to execute the command in line 88 - you may want to check what that is in your code.

Additional comments:

• You can use CREATE TABLE LIKE to copy the structure and the data of a table, including indexes and constraints. CTAS doesn't do that.
• Dropping and recreating objects removes all references (e.g. granted privileges, view definitions, etc.) - Some of those references cannot be simply recreated via recompiling.
• HANA 2 offers a command to change the ownership of a DB object.

[Former Member]

Hi lars,

Spot on i am trying to change the ownership of the table but unfortunately we are on hana 1 with sps 12 and not sure about the when we will migrate to 2.0 so we need this asap.

Line 88 tells :

exec ( 'RENAME TABLE ' || schema_name || '.' || table_name || ' TO ' || schema_name || '.' || table_name || '_TMP');

As mentioned earlier i am able to run this from sql console but when i run the same through stored procedure i face

insufficient privilege: Not authorized

And also as you see i am using create table like not CTAS, one more question will create table like also copy

granted privileges, view definitions to the new table ??

[lbreddemann]

No, it won't. It creates a new independent object.

Another option to change the ownership of objects is to export them and import them again under the user that should become the new owner.

[Former Member]

Thanks Lars , that becomes tricky now as we want developer to do this that why we choose stored procedure as option to change ownership and we cant grant them export and import privileges .

Thanks

[Former Member]

execute below in sql and then execute the procedure.

GRANT SELECT ON SCHEMA schema_name to _SYS_REPOT WITH GRANT OPTON;

[Former Member]

Hi Umar,

Thanks the authorization team rejected this access but we are able to fix that by grating select access on tables for _sys_repo user and not sure why _sys_repo need to have access as the tables are in dw schema and procedure is in _sys_bic schema

[Former Member]

Hi Dilip,

_sys_repo is system user that needs SELECT authorization whenever you activate object.

[Former Member]

Thanks Umar

[Former Member]

Hi All,

2 more question on the same procedure of changing ownership , is their somewhere i can get the create syntax for sequence and synonym like what we get in definition column for views and procedure ??

I have some table name with upper case some with lower case and somewith upper and lowercase like

TEST.FINANCE

TEST.Record

TEST.dummy

because of this my rename is not working , it says table not found , can some one guide how can i handle this ??

Many thanks

[SergioG_TX]

if you are able to run these statements in the sql console but not from the stored procedure.. most likely, the dynamic sql is not allowed.

since you are in the learning process, let me recommend one more debugging step when you do not have dynamic sql - if you can find your stored procedure inside the schema, procedures folder, you can right click on it and select show authorizations, to display what you may be missing (from a view/table/schema)....

hope you can solve your dynamic sql issue

[Former Member]

Thanks Sergio ,

I see this, for all the red lines but not sure what this means

Thanks

[SergioG_TX]

red lines means you do not have the necessary access for that object ... in your case that schema

[Former Member]

Thanks Sergio that was helpful.

2 more question on the same procedure of changing ownership , is their somewhere i can get the create syntax for sequence and synonym like what we get in definition column for views and procedure ??

I have some table name with upper case some with lower case and some with upper and lowercase like

TEST.FINANCE

TEST.Record

TEST.dummy

because of this my rename is not working , it says table not found , can some one guide how can i handle this ??

exec ( 'RENAME TABLE ' || schema_name || '.' || table_name || ' TO ' || schema_name || '.' || table_name || '_TMP9');

The above code is working but i need to add double quotes infront of table_name for lower case table to been handled , if you correct the below syntax that will be great (double quotes which is in bold and underlined )

exec ( 'RENAME TABLE ' || schema_name || '.' " || table_name || " ' TO ' || schema_name || '.' || table_name || '_TMP9');

TIA