Collecting the list of objects in HANA using definer's rights

[shyam_uthaman]

Hi All,

I was searching for a way to collect list of objects created with definer's rights.

The list should also show the user responsible for the object. Surely, there must be system tables which I can join to get this information.

I've looked through some and couldn't find the right one yet.

If someone already knows this, can you please help me out with this information?

Thanks,

Shyam

[pfefferf]

For what are you searching? For procedures with definer right execution? And what is your "responsible user", the schema owner to which the object belongs or the user who activate the object?

Regards,

Florian

[shyam_uthaman]

Hi Florian,

Thanks for the response.

I am looking for any object that was set to definer's rights by a user. I guess that would include procedures, hdbprocedures, table functions and scripted views.The last activated user should be enough to know for me.

Regards,

Shyam

[pfefferf]

To my knowledge no dedicated system views/columns exist which have the information about the DEFINER privilege execution. So one option is to determine the objects based on their definition.

For repository objects in XS classic you can get the information with following selects from table _SYS_REPO.ACTIVE_OBJECT. In column "ACTIVATED_BY" you find the user who activated the object.

-- repository - procedures
select * from _sys_repo.active_object
where object_suffix = 'hdbprocedure'
  and cdata like '%SQL SECURITY DEFINER%'; -- consider only procedures running with definer privileges


-- repository - functions
select * from _sys_repo.active_object
where object_suffix = 'hdbtablefunction'
  and cdata not like '%SQL SECURITY INVOKER%' -- consider only functions running with definer privileges; no SQL SECURITY information -> default DEFINER


-- repository - scripted calculation views
select * from _sys_repo.active_object
where object_suffix = 'calculationview'
  and cdata like '%calculationScenarioType="SCRIPT_BASED"%' -- script based calc view
  and not cdata like '%runWithInvokerPrivileges="true"%';   -- exclude views running with invoker privileges

For catalog objects system views PROCEDURES and FUNCTIONS can be used. There is no creator information available for the "catalog only" objects. For that the audit log functionality must be used. Please consider that the statements return also the runtime objects for the repository procedures and functions.

-- catalog - procedures (contains also runtime objects created for respository procedures)
select * from procedures
where definition like '%SQL SECURITY DEFINER%';


-- catalog - functions (constains also runtime objects created for repositry functions)
select * from functions
where definition like '%SQL SECURITY DEFINER%'   -- consider only functions running with definer privileges;
   or (         definition like '%RETURNS TABLE%'    -- consider table functions running with definer privileges; no SQL SECURITY information -> default DEFINER
        and not definition like '%SQL SECURITY INVOKER%'
      );

Regards,

Florian