cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Audit Logs for deactivated database user SYSTEM

Former Member

on ‎12-04-2015 10:48 AM

0 Kudos


Hi experts,

As per recommendation, we have deactivated the database user SYSTEM in our HANA database. Also, we have created and enabled an audit policy to capture all actions for SYSTEM as we have set it up as an emergency user (to be activated in times of need).

What kept me wondering though is that I am still seeing logs from the view AUDIT_LOG for user SYSTEM, mostly select statements. Why am I still seeing these logs when SYSTEM has already been deactivated?

Please advise. Thank you.

Regards,

ANG

Show replies
Show replies
Answer

Accepted Solutions (0)

Answers (1)

Answers (1)

lucas_oliveira
Advisor
Advisor
‎12-07-2015 4:45 PM
0 Kudos

Hi ANG,

What kind of logs you're seeing there? Can you share those here please?

BRs,

Lucas de Oliveira

Show replies
Show replies
Former Member
‎12-07-2015 9:56 PM
0 Kudos

Hi Lucas,

Thanks for your response. I have attached below a sample of the logs for SYSTEM:

TIMESTAMPHOSTPORTSERVICE_NAMECONNECTION_IDCLIENT_HOSTCLIENT_IPCLIENT_PIDCLIENT_PORTUSER_NAMEAPPLICATION_USER_NAMEAUDIT_POLICY_NAMEEVENT_STATUSEVENT_LEVELEVENT_ACTIONSCHEMA_NAMEOBJECT_NAMEPRIVILEGE_NAMEROLE_NAMEGRANTEEGRANTABLEFILE_NAMESECTIONKEYPREV_VALUEVALUESTATEMENT_STRINGCOMMENT
9/13/15 2:25<HANADBserver>30,703indexserver402,716-1-1SYSTEMSystemUserActivitiesSUCCESSFULINFOSELECTSYSRS_TABLES_?????????select schema_name, table_name, partition_spec from sys.tables where is_column_table = 'TRUE' and is_temporary = 'FALSE' and partition_spec is not null and partition_spec like '%RANGE[DYNAMIC:%' and partition_spec not like 'RANGE%; RANGE[DYNAMIC:%';??
9/13/15 2:25<HANADBserver>30,703indexserver402,716-1-1SYSTEMSystemUserActivitiesSUCCESSFULINFOSELECTSYSCS_TABLES_?????????select schema_name, table_name, partition_spec from sys.tables where is_column_table = 'TRUE' and is_temporary = 'FALSE' and partition_spec is not null and partition_spec like '%RANGE[DYNAMIC:%' and partition_spec not like 'RANGE%; RANGE[DYNAMIC:%';??
9/13/15 2:25<HANADBserver>30,703indexserver402,716-1-1SYSTEMSystemUserActivitiesSUCCESSFULINFOSELECTSYSDUMMY?????????select schema_name, table_name, partition_spec from sys.tables where is_column_table = 'TRUE' and is_temporary = 'FALSE' and partition_spec is not null and partition_spec like '%RANGE[DYNAMIC:%' and partition_spec not like 'RANGE%; RANGE[DYNAMIC:%';??
9/13/15 2:25<HANADBserver>30,703indexserver402,716-1-1SYSTEMSystemUserActivitiesSUCCESSFULINFOSELECTSYSHAS_NEEDED_SYSTEM_PRIV?????????select schema_name, table_name, partition_spec from sys.tables where is_column_table = 'TRUE' and is_temporary = 'FALSE' and partition_spec is not null and partition_spec like '%RANGE[DYNAMIC:%' and partition_spec not like 'RANGE%; RANGE[DYNAMIC:%';??
9/13/15 2:25<HANADBserver>30,703indexserver402,716-1-1SYSTEMSystemUserActivitiesSUCCESSFULINFOSELECTSYSRS_TABLES_?????????select schema_name, table_name, partition_spec from sys.tables where is_column_table = 'TRUE' and is_temporary = 'FALSE' and partition_spec is not null and partition_spec like 'RANGE%; RANGE[DYNAMIC:%';?; RANGE[DYNAMIC:%';??
9/13/15 2:25<HANADBserver>30,703indexserver402,716-1-1SYSTEMSystemUserActivitiesSUCCESSFULINFOSELECTSYSCS_TABLES_?????????select schema_name, table_name, partition_spec from sys.tables where is_column_table = 'TRUE' and is_temporary = 'FALSE' and partition_spec is not null and partition_spec like 'RANGE%; RANGE[DYNAMIC:%';?; RANGE[DYNAMIC:%';??
9/13/15 2:25<HANADBserver>30,703indexserver402,716-1-1SYSTEMSystemUserActivitiesSUCCESSFULINFOSELECTSYSDUMMY?????????select schema_name, table_name, partition_spec from sys.tables where is_column_table = 'TRUE' and is_temporary = 'FALSE' and partition_spec is not null and partition_spec like 'RANGE%; RANGE[DYNAMIC:%';?; RANGE[DYNAMIC:%';??
9/13/15 2:25<HANADBserver>30,703indexserver402,716-1-1SYSTEMSystemUserActivitiesSUCCESSFULINFOSELECTSYSHAS_NEEDED_SYSTEM_PRIV?????????select schema_name, table_name, partition_spec from sys.tables where is_column_table = 'TRUE' and is_temporary = 'FALSE' and partition_spec is not null and partition_spec like 'RANGE%; RANGE[DYNAMIC:%';?; RANGE[DYNAMIC:%';??
9/13/15 2:40<HANADBserver>30,703indexserver402,716-1-1SYSTEMSystemUserActivitiesSUCCESSFULINFOSELECTSYSRS_TABLES_?????????select schema_name, table_name, partition_spec from sys.tables where is_column_table = 'TRUE' and is_temporary = 'FALSE' and partition_spec is not null and partition_spec like '%RANGE[DYNAMIC:%' and partition_spec not like 'RANGE%; RANGE[DYNAMIC:%';?; RANGE[DYNAMIC:%';?; RANGE[DYNAMIC:%';??
9/13/15 2:40<HANADBserver>30,703indexserver402,716-1-1SYSTEMSystemUserActivitiesSUCCESSFULINFOSELECTSYSCS_TABLES_?????????select schema_name, table_name, partition_spec from sys.tables where is_column_table = 'TRUE' and is_temporary = 'FALSE' and partition_spec is not null and partition_spec like '%RANGE[DYNAMIC:%' and partition_spec not like 'RANGE%; RANGE[DYNAMIC:%';?; RANGE[DYNAMIC:%';?; RANGE[DYNAMIC:%';??
lucas_oliveira
Advisor
Advisor
‎12-08-2015 1:23 AM
0 Kudos

Hello ANG,

It looks like an internal call actually. Not sure why to use a SYSTEM user instead

Anyhow, any chances someone reactivated it  executed a few statements and deactivated it again?

As per documentation Deactivate the SYSTEM User - SAP HANA Security Guide - SAP Library that's a possibility.

Maybe start monitoring ALTER USER would help investigate if that is the case.

Cheers,

Lucas de Oliveira

Former Member
‎12-09-2015 3:10 AM
0 Kudos

Hi Lucas,

The user SYSTEM was deactivated in our database since July of this year, and was never activated again. The latest logs we are seeing are recent, too (12/8 as of this writing). How can we know which processes are still using SYSTEM, and how can it still be used (even for internal calls) when it's already deactivated?

Btw, I checked the logs again and saw that were other actions like DELETE, DISCONNNECT SESSION, CONNECT, EXECUTE, UPDATE, CANCEL SESSION. See sample below:

Do you also have it deactivated in your own database and do you see audit logs?

Regards,

ANG

lbreddemann
Active Contributor
‎12-09-2015 5:29 AM
0 Kudos

Hi there

the SYSTEM user is one of the users that are owned by SAP HANA (others include _SYS_REPO, SYS, _SYS_STATISTICS...).

"Deactivating" the user merely means to disable the authentication of this user. That means, a new external session cannot be created, since SAP HANA won't authenticate the user - regardless of the form of authentication.

To make our SAP HANA internal processes work, they need to have some form of database connection too. This is provided via the so called embedded API (EAPI).

For the connections that are started via this embedded API, which is only available for C++ code embedded in SAP HANA. So, if you're not happen to develop core SAP HANA code, this API is not within reach.

Anyhow, connections done with this internal API don't do any authentication. They simply bypass it.

And the default user for such connections is... SYSTEM, correct.

One example for a usage of this would be the "AntiAger" process, which regularly checks, whether there are session active in the system that are open for a long time. This process uses the EAPI to run queries against M_ACTIVE_STATEMENTS.

So far so good. This means, it's perfectly normal to find those statements in the AUDIT trail.

Now a good next question would of course be: how can I distinguish between those internal sessions and potential external SYSTEM user usages?

Let's look at the AUDIT_LOG...

TIMESTAMP            HOST        PORTSERVICE_NAMECONNECTION_IDCLIENT_HOST                      CLIENT_IP    CLIENT_PIDCLIENT_PORTUSER_NAMEAPPLICATION_USER_NAMEAUDIT_POLICY_NAMEEVENT_STATUSEVENT_LEVELEVENT_ACTIONSCHEMA_NAMEOBJECT_NAME       
2015-12-09 06:23:59.0dewdftzldc0530703indexserver211861      laptop.emea.global.corp.sap10.42.142.19810944    49916      SYSTEM  I028297              catchSYSTEM      SUCCESSFUL  INFO      SELECT      SYS        P_INDEXES_       
2015-12-09 06:23:59.0dewdftzldc0530703indexserver211861      laptop.emea.global.corp.sap10.42.142.19810944    49916      SYSTEM  I028297              catchSYSTEM      SUCCESSFUL  INFO      SELECT      SYS        SERIES_DATA_     
2015-12-09 06:23:59.0dewdftzldc0530703indexserver211861      laptop.emea.global.corp.sap10.42.142.19810944    49916      SYSTEM  I028297              catchSYSTEM      SUCCESSFUL  INFO      SELECT      SYS        CS_TABLES_       
2015-12-09 06:23:58.0dewdftzldc0530703indexserver211862                                                    0        0          SYSTEM                      catchSYSTEM      SUCCESSFUL  INFO      SELECT      SYS        M_ACTIVE_STATEMENTS
2015-12-09 06:23:58.0dewdftzldc0530703indexserver211862                                                    0        0          SYSTEM                      catchSYSTEM      SUCCESSFUL  INFO      SELECT      SYS        DUMMY             
2015-12-09 06:23:58.0dewdftzldc0530703indexserver211862                                                    0        0          SYSTEM                      catchSYSTEM      SUCCESSFUL  INFO      SELECT      SYS        M_CONNECTIONS     

Easy to see: any external connection shows up with CLIENT information (HOST/IP-ADDRESS/PID) while the EAPI connections simply don't have a client to share information about.

Cheers,

Lars

lucas_oliveira
Advisor
Advisor
‎12-10-2015 4:27 PM
0 Kudos

Good point Lars,

Still, I think we might need another user for that, otherwise we'll keep seeing those on the audit logs.

BRs,

Lucas de Oliveira

Ask a Question