Create ABAP program to send SQL command to another HANA DB

[saumel]

Hello,

Is there any possibility to create an ABAP program for instance with a select screen where we can write a SQL command then execute this SQL command into x hana DB ?

Thank you for your help.

[RaymondGiuseppi]

Why not use the SQL Console for SAP HANA to execute queries against SAP HANA databases?

Have you talked to your security officer about SQL injection?

[saumel]

Hi Raymond ! It's a request from basis team in order to do this action in few hana DB with only 1 sql command. They already checked if we can do this with SQL console but is not possible. I mean, imagine you need to create one user in x hana DB you need to do this x times. This is the reason of my request.

[RaymondGiuseppi]

Basically, you want to create a wizard for Managing Database Users ?

[RaymondGiuseppi]

Basically, you want to create a cockpit/wizard for Managing Database Users ?

• Look at attachment of notes such as 1986645 - Allow only administration users to work on HANA database to get an idea of the SQL statements to be executed.
• Insure that SAP HANA Database Administration with SAP HANA Cockpit isn't the solution

[RemiKaimal]

Hi @Samuel 
The easier option is to use HANA SQL Console/HANA Studio, what is the need to write a ABAP report when there are tools to query. There is option to query huge volume of data too
Data lake often resort to Standard SAP tools for a query
Answering the question :
Check list
1) If your user has S_DBCON and S_TABU_DIS authorizations, then that means you have execution within SAP's Open SQL or native SQL framework.
2) DML operations like (INSERT, UPDATE, DELETE) should be blocked / handled inside your custom report

Here is the sample code for your question : 

REPORT zsql_exe.
TABLES: dd02l.

DATA: gv_sql TYPE string, " ------->>>>replace with your SQL statement
gt_results TYPE STANDARD TABLE OF dd02l, " Table to store results
gv_message TYPE string.

SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME TITLE TEXT-001.
PARAMETERS: p_sql TYPE string LOWER CASE OBLIGATORY.  ---->important
SELECTION-SCREEN: END OF BLOCK b1.

START-OF-SELECTION.

"-------------- avoid  harmful commands---- throw error otherwise
IF p_sql CS 'DELETE' OR p_sql CS 'UPDATE' OR p_sql CS 'INSERT'
   OR p_sql CS 'DROP' OR p_sql CS 'ALTER'.
gv_message = 'Only SELECT statements are allowed!'.
MESSAGE gv_message TYPE 'E'.
EXIT.
ENDIF.

" Store SQL Command
gv_sql = p_sql.

TRY.
EXEC SQL.
EXECUTE IMMEDIATE :gv_sql INTO TABLE :gt_results
ENDEXEC.

IF gt_results IS NOT INITIAL.
cl_demo_output=>display_data( gt_results ). " Display output
ELSE.
gv_message = 'No data found for the query'.
MESSAGE gv_message TYPE 'I'.
ENDIF.

"handle error
CATCH cx_sy_dynamic_osql_error INTO DATA(lo_error).
gv_message = lo_error->get_text( ).
MESSAGE gv_message TYPE 'E'.
ENDTRY.

If an exception is thrown that means you may not have access
1) check su53 results after running the above sample code
2) or else execute excecute function module: DB_CONNECTION_TEST.
with parameter DB connection = DEFAULT.
If it fails, direct SQL access may be blocked.

Hope this helps! Cheers

[saumel]

Hi Remi ! thank you so much for your reply I really appreciate this !

The request was to create a program to execute SQL commands to create or delete hana users not to execute sql commands in tables. Did you already do something similar ? The idea was to excute DBCO connection to a specific system when executing the abap program, then execute sql commande to insert or delete hana user then close DBCO connection. 

I never did something like this, this is why I posted this request.

I received the information that we cannot do this kind of action in mass so execute this kind of sql action in many systems one after one. If we want to do something like this with a console, we need to connect into each system then execute sql command and could be a waist of time.

Thank you so much for your help !

[RaymondGiuseppi]

I would add a OR p_sql NS 'SELECT'. to your checklist 😉