Controlling Runtime Behaviour of Procedures

Context:

There are times when a particular procedure say X, needs to be executed only as part of a workflow or else it may cause unwanted behavior, particularly when the procedure deals with DDLs and DMLs, as the workflow might involve a chain of checkpoints with the output of one checkpoint leading into the next checkpoint as input. The schema structure is at stake. Such procedures need to have restricted access. A typical application will have a Roles and Privileges framework, which restricts/grants certain users from/to creating/dropping/executing DB artifacts (procedures, tables, views etc.). So with the users who do have the privilege to create/modify DB artifacts, its always possible to create multiple wrappers around the procedure X in our case, and execute the wrappers, or in another case, directly execute X, outside the workflow. So, for DB artifacts, I prefer to think of them having 2 groups of users, we might say, in our case. One is the server, the other the client. While the server has the privileges to create/modify/delete (collectively called serve) and execute the procedures, the client can only execute the DB artifact. Restricting the client from executing X outside the workflow, therefore, becomes simple. We just give EXECUTE access only on this schema containing X to the client(s). But this itself is not enough. Lets say the relevant user in question is UserA. UserA might still log on to the HANA studio and run the procedure from backend, outside the workflow as he has the EXECUTE rights as mentioned before. Lets say the name of the schema is RUNTIME_SCHEMA. Therefore UserA cannot do any DDL/DML changes to the any of the DB artifacts in RUNTIME_SCHEMA, unless doing them from within SQLScript calls on procedures in the schema RUNTIME_SCHEMA. The UserA has all the rights on his schema UserA. He can create procedures in his schema and fire DDLs/DMLs on the artifacts in RUNTIME_SCHEMA. But the procedures won’t execute as the user does not have the appropriate DML privileges outside the scope of the EXECUTE privilege on the RUMTIME_SCHEMA. Therefore, the user is completely locked out from making any changes to this artifact X. So, we are good with UserA. So is the case, with other client(s). But if we talk about the server user or the user group that created the procedure X in the first place, then he is the one user (group) who can modify the procedure as well. Lets assume, he (or the group) modifies it for legitimate reasons as part of the workflow. But this user group can also control the possible point(s) of entry into this procedure X if it wants to as part of the workflow strategy. It is also possible to limit the point of entry to none if the procedure is supposed to be a “non-wrappable” procedure (i.e. the procedure can’t have a parent), so to speak.

Use Case:

1.Providing Access Control to Procedures- To ensure that the called procedure is only executed if called from a specific caller or caller group and not from other procedures, typically when you want a single/limited point(s) of entry to the called procedure, which typically happens during creation of a service model implementing design patterns at DB level, particularly the factory methods, knowing the caller of the procedure can be handy. Furthermore, the called procedure might be a decision table traversing different paths based on the specific caller from the caller group. One might say, the called procedure can behave as a router. Alternatively, we may think of the caller as the object and the called procedure as the private method of the object that can therefore be executed only from within the caller and not from the outside world. This, as I see it, is a way to implement the access specifiers part of OOPs in a declarative/imperative hybrid such as SQL. Not that this can’t be done statically. A procedure X calling Y, can pass its name ‘X’ to Y, but procedure Z can as well pass ‘X’ to Y and forge X. This also involves a lot of code maintenance

Example Pseudocode

create procedure schema_name.Inner as begin
//find the caller
....
...
...
if caller is 'Outer1' then
     execute model1
elseif caller is 'Outer2' then
     execute model2
else //for other callers not authorized to run this procedure
     log a 'No authorization' message
     return
end if;
end;

2.Another use case that comes to my mind is logging the caller procedure. One might have multiple points of entry into the called procedure in the same workflow at different points in time. Logging the name of the caller along with other metadata such as the checkpoint or the timestamp, can help in troubleshooting, which otherwise would need scrutiny of all the possible callers one by one, which can be time consuming, unless we manually pass the procedure name as a parameter to the called procedure, which in my opinion should not be the way to go, as it involves a lot of updates to the code the moment the procedure name changes

Premise

The premise this works on is that recursion is not supported in HANA and that at a particular point in time a DB transaction consisting of a workflow having only one Connection ID can have a particular procedure being called from only one other procedure (or else it would become a cyclic dependency), which stays true till the called procedure is done getting executed, after which another procedure may call the same called procedure.

Implementation

create procedure INNER(in inp nvarchar(32),out status nvarchar(5000)) as
lv_var nvarchar(5000):='{ call "MYSCHEMA"."INNER" (?, ?) }';
lv_statement nvarchar(256):='';
begin
select statement_string into lv_statement from "SYS"."M_PREPARED_STATEMENTS" where statement_status='ACTIVE' and connection_id=current_connection and statement_id= (select parent_statement_id from "SYS"."M_PREPARED_STATEMENTS" where statement_status='ACTIVE' and cast(statement_string as nvarchar(5000))=:lv_var);
if lv_statement= 'call MYSCHEMA.OUTER' then
  status:=:lv_statement||' Success::Procedure called:'||ucase(substr_after(:lv_statement,'call '));
else
  status:=:lv_statement||' Failure::Procedure called:'||ucase(substr_after(:lv_statement,'call '));
end if;
end;
create procedure OUTER as
stat nvarchar(5000):='Hello';
begin
call MYSCHEMA.INNER('TEST',:stat);
select :stat from dummy;
end;
call MYSCHEMA.OUTER;

In the above sample code, the called procedure ‘INNER’ checks if the call is coming from the procedure ‘OUTER’.

Sample Use Case:

Here, I have created a utility tool of re-creating a DB artifact with the latest definition amidst a workflow as a small part of the workflow. Creating a DB artifact from within a procedure would involve checking if it already exists, dropping it if it does, and then creating it again with the latest definition. I want the re-creation of the DB artifact to be run only as a part of this workflow and not outside it, as far as the execution of the procedure is concerned. So, I create a verification utility tool called myschema.check_caller which takes the called procedure as the parameter and returns the caller service, looks up the artifact_definition table (that stores the string definitions of the artifacts each getting generated in the preceding steps of the individual workflow) for the definition and creates the artifact using dynamic SQL

--You might need to make changes to the procedure if you try this example in your system as the example skips the actual code and presents the pseudocode in some sections
--create lookup table that stores artifact definitions created during the workflow before the common service is called
create table myschema.artifact_definition (procedure_schema nvarchar(256),procedure_name nvarchar(256),service_name nvarchar(256), artifact_schema nvarchar(32),artifact_name nvarchar(32),artifact_Type nvarchar(32),artifact_definition nvarchar(5000));
insert into myschema.artifact_definition values('MYSCHEMA','RECREATE_ARTIFACT','Service1','MYSCHEMA','TEST_TAB','TABLE','create table MYSCHEMA.TEST_TAB like MYSCHEMA.STAG_TEST_TAB with data');
insert into myschema.artifact_definition values('MYSCHEMA','RECREATE_ARTIFACT','Service1','MYSCHEMA','TEST_PROC','PROCEDURE','create procedure MYSCHEMA.TEST_PROC as begin select col_a,col_b,col_c*2 as col_d from MYSCHEMA.TEST_TAB; end;');
insert into myschema.artifact_definition values('MYSCHEMA','RECREATE_ARTIFACT','Service2','MYSCHEMA','TEST_VIEW','VIEW','create view MYSCHEMA.TEST_VIEW as select col_a,col_b,col_c from MYSCHEMA.STAG_TEST_TAB');
insert into myschema.artifact_definition values('MYSCHEMA','RECREATE_ARTIFACT','Service2','MYSCHEMA','TEST_PROC','PROCEDURE','create procedure MYSCHEMA.TEST_PROC(out cnt int) as lv_cnt int:=0; begin select count(*) into lv_cnt from MYSCHEMA.TEST_TAB; cnt:=:lv_cnt; end;');
insert into myschema.artifact_definition values('MYSCHEMA','ALTER_ARTIFACT','Service3','MYSCHEMA','TEST_TAB','TABLE','alter table MYSCHEMA.TEST_TAB add(col_e nvarchar(32))');
insert into myschema.artifact_definition values('MYSCHEMA','ALTER_ARTIFACT','Service3','MYSCHEMA','AT_TEST_VIEW','ATTRIBUTE VIEW','<ATTRIBUTE VIEW XML>');
insert into myschema.artifact_definition values('MYSCHEMA','ALTER_ARTIFACT','Service3','MYSCHEMA','TEST_SEQ','SEQUENCE','ALTER SEQUENCE MYSCHEMA.TEST_SEQ RESTART WITH 2;');
--check caller
drop procedure MYSCHEMA.check_caller;
create procedure MYSCHEMA.check_caller(in source nvarchar(5000)
,out status nvarchar(5000)
) as
lv_statement nvarchar(256):='';
begin
select statement_string
into lv_statement
from "SYS"."M_PREPARED_STATEMENTS" where statement_status='ACTIVE' and connection_id=current_connection and statement_id= (select parent_statement_id from "SYS"."M_PREPARED_STATEMENTS" where statement_status='ACTIVE' and cast(statement_string as nvarchar(5000))=:source);
status:=ucase(substr_after(:lv_statement,'call '));
end;
--check if DB artifact exists
drop procedure MYSCHEMA.is_exist;
create procedure MYSCHEMA.is_exist(in artifact_schema nvarchar(32),in artifact_name nvarchar(32),in artifact_Type nvarchar(32)
,out is_exist int
) as
begin
if :artifact_type='USER' then
select count(*) into is_exist from "SYS"."ROLES" where role_name=:artifact_name;
elseif :artifact_type='ROLE' then
select count(*) into is_exist from "SYS"."USERS" where user_name=:artifact_name;
else
  select count(*) into is_exist from "SYS"."OBJECTS" where schema_name=:artifact_schema and object_name=:artifact_name and object_type = :artifact_Type;
end if;
end;
--drop DB artifact
drop procedure MYSCHEMA.drop_artifact;
create procedure MYSCHEMA.drop_artifact(in artifact_schema nvarchar(32),in artifact_name nvarchar(32),in artifact_Type nvarchar(32)) as begin
exec 'drop '||artifact_type||' '||artifact_schema||'.'||artifact_name;
end;
--create DB artifact
drop procedure MYSCHEMA.GENERATE_ARTIFACT;
create procedure MYSCHEMA.GENERATE_ARTIFACT(
in artifact_schema nvarchar(32),
in artifact_name nvarchar(32),
in artifact_Type nvarchar(32)--TABLE/PROCEDURE/VIEW
)
as
result nvarchar(5000);
begin
call check_caller('{ call "MYSCHEMA"."GENERATE_ARTIFACT" (?, ?, ?) }',result);
--1. Lookup the myschema.artifact_definition table to get the artifact definition string based on the caller name, artifact name, schema, and type
--2. execute the artifact definition string using dynamic sql
end;
--wrapper: Master Procedure
drop procedure myschema.recreate_artifact;
create procedure myschema.recreate_artifact(
in artifact_schema nvarchar(32),
in artifact_name nvarchar(32),
in artifact_Type nvarchar(32),--TABLE/PROCEDURE/VIEW
) as
result nvarchar(5000);
begin
  call MYSCHEMA.is_exist(:artifact_schema,:artifact_name,:artifact_Type,result);
  if result != 0 then
  call MYSCHEMA.drop_artifact(:artifact_schema,:artifact_name,:artifact_Type);
  end if;
  call MYSCHEMA.GENERATE_ARTIFACT('MYSCHEMA','TEST_TAB','TABLE');--can only be excuted from recreate_artifact
end;
call MYSCHEMA.RECREATE_ARTIFACT('MYSCHEMA','TEST_TAB','TABLE');

Benefit

The benefit is that as long as the GENERATE_ARTIFACT procedure is executed, unmodified, the procedure, on the fly, determines the service to pick the definition of the artifact from, based on the caller, without any need for parameter passing.

Request your thoughts on the use of such a utility.

Thank You

Regards,

Sheel