Create Encrypted Stored Procedure on SAP HANA Database
With SAP HANA 2.0 database developers can create encrypted stored procedure using SQLScript on a HANA database easily. To encrypt stored procedure on SAP HANA Studio prevents source codes of SQL stored procedure to be visible by all developers after all. If you use open connection information to other database systems like database user name and passwords, then encrypting stored procedure is meaningful. Otherwise, if SQL programmer prefers to encrypt all procedures created on a HANA database, in future to maintain and troubleshooting these stored procedures will not be so easy.
In order to encrypt a stored procedure or a user-defined function on HANA database, during creation of the procedure "WITH ENCRYPTION" clause is added as seen in following SQLScript code block.
create procedure City_Read (
CityId int
)
language SQLScript
sql security invoker
reads sql data with encryption
as
begin
select * from city where cityid = :CityId;
end;
SQL programmers can add "With Encryption" clause into their stored procedure definitions to encrypt SQLScript codes of their procedures.
If a HANA database developer wants to see the SQLScript codes of an encrypted stored procedure, and double clicks on the stored procedure name on SAP HANA Studio, only definition section will be displayed as follows:
Even if you are the SQL programmer who has encrypted this procedure, you will not be able to see the SQL code within the body of the HANA stored procedure.
As I note before in this tutorial, when you encrypt a procedure or a SQL function when you manage to hide its SQL codes, you lose maintainability and optimization options that might be vital in future. Developers can't debug stored procedures or SQL functions which are encrypted by using SQLScript Debugger or won't be able to use PlanViz and SQL traces for those encrypted SQL artifacts.
So SAP HANA database developers should keep source codes of stored procedures or SQL functions before encrypting them. If a SQL programmers requires updating SQL codes of the encrypted procedure, following ALTER PROCEDURE command will work.
alter procedure City_Read (
CityId int
)
language SQLScript
sql security invoker
reads sql data with encryption
as
begin
select city, citycode from city where cityid = :CityId;
end;
To convert an encrypted stored procedure into a normal procedure whose content is human-readable, executing ALTER PROCEDURE by removing the "with encryption" clause will work.
alter procedure City_Read (
CityId int
)
language SQLScript
sql security invoker
reads sql data
as
begin
select * from city where cityid = :CityId;
end;
Unfortunately right now, I don't know how to decrypt encrypted stored procedures on SAP HANA database.
But it is possible to decrypt an encrypted stored procedure using third party tools or by executing decrypting stored procedures which is not the case for encrypted procedures/function on HANA databases.
