Skip to Content

Hana Stored Procedure

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

Add comment
10|10000 characters needed characters exceeded

  • 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 ??

  • Lars Breddemann Dilip Deenadayalan

    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.

  • 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

  • Get RSS Feed

2 Answers

  • Feb 21 at 08:39 AM

    execute below in sql and then execute the procedure.

    GRANT SELECT ON SCHEMA schema_name to _SYS_REPOT WITH GRANT OPTON;

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Feb 20 at 05:29 PM
    -1

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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