The DBACOCKPIT of SAP NetWeaver AS ABAP and ABAP Platform provides the SQL Editor which can be used to execute SQL statements on connected databases.
The SQL Editor is a full featured SQL command line and supports all SQL statements as listed, for example, in the SAP HANA SQL Reference Guide for SAP HANA Platform. It was designed to cover the exact scope as the SQL Editor of HANA Studio.
Updates:
Since the SQL statements are executed on DB level as the connecting user, the privileges of this particular user are applied. For the ABAP system's primary database, it is the schema user SAPDAT, SAP<SID>, or SAPABAP<n>.
With this, the SQL Editor can be used, for example, to access all tables of the ABAP schema.
Please also note that on database level, there is no special handling of the client field (field 'MANDT'). This leads to data of any client can be accessed or modified, even if the SQL Editor is started, for example, in client 000.
Access to the SQL Editor is protected by the following authorization objects.
S_TCODE for the entry point to DBACOCKPIT. As there are several tcodes allowing to enter DBACOCKPIT and switching to the SQL Editor, you can find a summary below (without guarantee of being complete).
S_ADMI_FCD, S_RZL_ADM and S_DBCON which are required to start the DBACOCKPIT and to enter the SQL Editor.
Overview of involved authorization objects:
There are two authorization objects controlling what a user can actually do with the SQL Editor:
S_DBCON and S_TABU_SQL
At time of writing the documentation differentiates three use cases:
To map these use cases, a combination of S_DBCON and S_TABU_SQL is utilized.
Details about the combinations are outlined in https://help.sap.com/viewer/6b8fe8492ce14d24af5855c3d10701e3/202110.001/en-US/d4716e2d3f304b23846349....
As of my research, in reality there are four use cases:
While for 1. and 2. S_TABU_SQL must be additionally assigned to the user, for 3. and especially 4. this is not the case.
The description in the documentation does not make this clear. At time of writing there is stated:
"The SQL editor is enabled for all types of SQL statements.
You need to grant authorization for each individual table to be accessed. To specify tables and views to be accessed, use the authorization object S_TABU_SQL."
As of my observations this should read as
"The SQL editor is enabled for all types of SQL statements, except SELECT statements.
For SELECT, you need to grant authorization for each individual table to be accessed. To specify tables and views to be accessed, use the authorization object S_TABU_SQL."
In fact, S_TABU_SQL is not needed for INSERT, UPDATE, UPSERT, DELETE, ALTER, and all other manipulative SQL statements.
To run manipulative SQL statements, the user needs in total the following privileges in the ABAP system:
No involvement of S_TABU_SQL here!
I found the following roles and profiles delivered by SAP containing S_DBCON:ACTVT=36:
For each SQL statement, except SELECT, an entry is written in the Audit Log of DBACOCKPIT. All events of any commands executed in the SQL Editor are logged as 'Data manipulation' in column Object.
Technically the audit logs are stored in the tables DB6AUDITDT and DB6AUDITHD.
By default, the audit logs are deleted after 90 days. The retention period can be adjusted by adjusting the field retain = <number of retention days> of table DB6PM_CLN where the field sysid = '<sapsid>' and objecttype = 'U' and stattype = 'D'.
The clean-up is by default performed once a week by the report RSDB_WDB. It is scheduled by TCOLL (ST03 -> Collector and Performance DB -> Performance Monitor Collector -> Execution Times).
SAP introduced a BAdi to implement further, sophisticated logics to control the behavior of SQL Editor. Details can be found in SAP note 2618043.
This BAdi allows, for example, also an implementation to enable logging of SELECT statements. This might be relevant in systems which already utilize the Read Access Log (RAL) to log who accessed what data and when.
The authorization object S_DBCON=36 is very powerful and should not be granted on a routine basis. It allows to harm the data's integrity and availability.
The authorization object S_DBCON in combination with S_TABU_SQL is also very powerful and should not be granted on a routine basis." It allows to harm the data's confidentiality.
Credits and big thanks to Adrian, who did an awesome job walking with me trough all the construction refinements and helped in revealing technical details.
The SQL Editor is a full featured SQL command line and supports all SQL statements as listed, for example, in the SAP HANA SQL Reference Guide for SAP HANA Platform. It was designed to cover the exact scope as the SQL Editor of HANA Studio.
SQL Editor of DBACOCKPIT
Updates:
2022-06-08: Adjusted scope to SAP HANA as primary DB.
2022-05-23: Added info about default roles and profiles containing S_DBCON:ACTVT=36.
The danger
Since the SQL statements are executed on DB level as the connecting user, the privileges of this particular user are applied. For the ABAP system's primary database, it is the schema user SAPDAT, SAP<SID>, or SAPABAP<n>.
With this, the SQL Editor can be used, for example, to access all tables of the ABAP schema.
Please also note that on database level, there is no special handling of the client field (field 'MANDT'). This leads to data of any client can be accessed or modified, even if the SQL Editor is started, for example, in client 000.
Hint: This should be considered especially in scenarios where client 000 is managed by service providers, since this might open a door for accessing data in productive clients.
Access to the SQL Editor
Access to the SQL Editor is protected by the following authorization objects.
S_TCODE for the entry point to DBACOCKPIT. As there are several tcodes allowing to enter DBACOCKPIT and switching to the SQL Editor, you can find a summary below (without guarantee of being complete).
S_ADMI_FCD, S_RZL_ADM and S_DBCON which are required to start the DBACOCKPIT and to enter the SQL Editor.
Overview of involved authorization objects:
| Object | Attribute | Value | Operator |
| S_TCODE | TCD | DB01, DB02, DB03, DB12, DB12OLD, DB13, DB13C, DB20, DB26, DB2C, DB50, DB6CLP, DB6EXPLAIN, DB6PLAN, DBACOCKPIT, DBACOCKPIT_NWA, DBACOCKPIT_SOLMAN, ST04 | or |
| S_ADMI_FCD | S_ADMI_FCD | ST0R | |
| S_RZL_ADM | ACTVT | 01, 03 | or |
| S_DBCON | ACTVT | * | |
| S_DBCON | DBA_DBHOST | <dbhostname> | |
| S_DBCON | DBA_DBSID | <dbsid> | |
| S_DBCON | DBA_DBUSER | <dbuser> |
Authorizations for working with SQL Editor
There are two authorization objects controlling what a user can actually do with the SQL Editor:
S_DBCON and S_TABU_SQL
| Object | Attribute | Value |
| S_DBCON | ACTVT | 01, 03, 36 |
| S_TABU_SQL | ACTVT | 33 |
| TABLE | <tablename> | |
| DBSID | <dbname>, LOCL |
Please note: The implementation depends on which primary database is used by the ABAP system. What's written in this blogpost is valid for SAP HANA DB.
For IBM DB" and Sybase ASE additionally the parameter dbs/dba/ccms_maintenance is involved while for MaxDB in general only SELECT Statements are supported.
Differentiate between use cases
As of the documentation
At time of writing the documentation differentiates three use cases:
- read data
- read and modify database configuration
- do everything - no restriction
To map these use cases, a combination of S_DBCON and S_TABU_SQL is utilized.
Details about the combinations are outlined in https://help.sap.com/viewer/6b8fe8492ce14d24af5855c3d10701e3/202110.001/en-US/d4716e2d3f304b23846349....
In reality
As of my research, in reality there are four use cases:
- read data
- do everything - no restriction
- modify database configuration
- modify everything - no read data
While for 1. and 2. S_TABU_SQL must be additionally assigned to the user, for 3. and especially 4. this is not the case.
The description in the documentation does not make this clear. At time of writing there is stated:
"The SQL editor is enabled for all types of SQL statements.
You need to grant authorization for each individual table to be accessed. To specify tables and views to be accessed, use the authorization object S_TABU_SQL."
As of my observations this should read as
"The SQL editor is enabled for all types of SQL statements, except SELECT statements.
For SELECT, you need to grant authorization for each individual table to be accessed. To specify tables and views to be accessed, use the authorization object S_TABU_SQL."
In fact, S_TABU_SQL is not needed for INSERT, UPDATE, UPSERT, DELETE, ALTER, and all other manipulative SQL statements.
To run manipulative SQL statements, the user needs in total the following privileges in the ABAP system:
| Object | Attribute | Value | Operator |
| S_TCODE | TCD | DB01,DB02,DB03,DB12,DB12OLD,DB13,DB13C,DB20,DB26,DB2C,DB50,DB6CLP, DB6EXPLAIN,DB6PLAN,DBACOCKPIT,DBACOCKPIT_NWA,DBACOCKPIT_SOLMAN,ST04 | or |
| S_ADMI_FCD | S_ADMI_FCD | ST0R | |
| S_DBCON | ACTVT | 36 | |
| S_DBCON | DBA_DBHOST | <hostname> | |
| S_DBCON | DBA_DBSID | <DBSID> | |
| S_DBCON | DBA_DBUSER | <DBACOCKPITuser> | |
| S_RZL_ADM | ACTVT | 01, 03 | or |
No involvement of S_TABU_SQL here!
Default roles and profiles containing S_DBCON:ACTVT=36
I found the following roles and profiles delivered by SAP containing S_DBCON:ACTVT=36:
| role | HEC_BASIS_ADMIN_V3 | System Administration for TLO/PS in client 000 |
| role | HEC_BASIS_ADMIN_V6 | System Administration for TLO/PS in client 000 |
| role | HEC_SM_AGS_V2 | AGS Support in client 000 |
| role | SAP_DMIS_SLOP_BASIS_700 | SAP_DMIS_SLOP_BASIS_700 @IC\QSingle role@ SAP SLOP add. authorizations for Basis Rel. 700 |
| profile | &_SAP_ALL_24 | Generated partial profile for SAP_ALL |
| profile | SAP_ALL | All SAP System authorizations |
Audit Logging
For each SQL statement, except SELECT, an entry is written in the Audit Log of DBACOCKPIT. All events of any commands executed in the SQL Editor are logged as 'Data manipulation' in column Object.
Audit Log of DBACOCKPIT
Technically the audit logs are stored in the tables DB6AUDITDT and DB6AUDITHD.
By default, the audit logs are deleted after 90 days. The retention period can be adjusted by adjusting the field retain = <number of retention days> of table DB6PM_CLN where the field sysid = '<sapsid>' and objecttype = 'U' and stattype = 'D'.
Hint: To make the table DB6PM_CLN maintainable, please implement SAP note 3186701 including the manual tasks, which was created upon my request.
The clean-up is by default performed once a week by the report RSDB_WDB. It is scheduled by TCOLL (ST03 -> Collector and Performance DB -> Performance Monitor Collector -> Execution Times).
Further mitigation
SAP introduced a BAdi to implement further, sophisticated logics to control the behavior of SQL Editor. Details can be found in SAP note 2618043.
This BAdi allows, for example, also an implementation to enable logging of SELECT statements. This might be relevant in systems which already utilize the Read Access Log (RAL) to log who accessed what data and when.
Conclusion
The authorization object S_DBCON=36 is very powerful and should not be granted on a routine basis. It allows to harm the data's integrity and availability.
Hint: SAP agreed to add a check for S_DBCON=36 to the Security Check in SAP EarlyWatch Alert.
The authorization object S_DBCON in combination with S_TABU_SQL is also very powerful and should not be granted on a routine basis." It allows to harm the data's confidentiality.
Credits and big thanks to Adrian, who did an awesome job walking with me trough all the construction refinements and helped in revealing technical details.
- SAP Managed Tags:
9 Comments
