SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for SAP HANA and ABAP, HANA Database, SQLScript, SAP UI5, Screen Personas, Web Dynpro, Workflow

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;
Code

SQL programmers can add "With Encryption" clause into their stored procedure definitions to encrypt SQLScript codes of their procedures.

encrypt stored procedure on SAP HANA database using with encryption

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:

encrypted stored procedure codes are not visible

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;
Code

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;
Code

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.



SAP HANA and ABAP

Install SAP Free
CRM Companies List
Web Based CRM Software


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.