By using SAP HANA SQL Commands, we can perform following job
Finding index and primary keys of tables
SELECT IFNULL(CONSTRAINT,’NUNIQUE’),
INDEX_NAME,COLUMN_NAME
FROM INDEX_COLUMNS WHERE SCHEMA_NAME = ‘%s’
AND TABLE_NAME = ‘%s’
ORDER BY INDEX_OID,POSITION
Show details of users that have been logged-in
select * from “SYS”.”USERS” where “LAST_SUCCESSFUL_CONNECT” is not null order by 9 desc;
To get all Libraries
select to_char(definition) from public.libraries where schema_name=’EXT’;
Check invalid custom DB views
select * from “SYS”.”VIEWS” where schema_name not like ‘SAP%’ and is_valid = ‘FALSE’;
Check inactive custom DB objects
select * from “_SYS_REPO”.”INACTIVE_OBJECT” where “PACKAGE_ID” not like ‘sap%’;
Check which SAP language settings are being used by current user
select session_context(‘LOCALE_SAP’), session_context(‘LOCALE’) from dummy;
Search executed SAP HANA SQL statements –
To find out who deleted a table
select * from “SYS”.”M_EXECUTED_STATEMENTS” where “STATEMENT_STRING” LIKE ‘DROP TABLE%’;
Show custom settings within global.ini and indexserver.ini
select * from “SYS”.”M_INIFILE_CONTENTS” where (“LAYER_NAME” = ‘SYSTEM’ or “HOST” <> “)
and (“FILE_NAME” = ‘global.ini’ or “FILE_NAME” = ‘indexserver.ini’);
Show assigned user roles
select * from “SYS”.”GRANTED_ROLES” where “GRANTEE_TYPE” = ‘USER’;
Show assigned repository privileges
select * from “SYS”.”GRANTED_PRIVILEGES” where object_type = ‘REPO’;
Show objects owned by non-system users
select * from “SYS”.”OWNERSHIP” where owner_name not like ‘SAP%’ and owner_name not like ‘%SYS%’
order by 1,2;
Show Corresponding Tables referring to which Schema
SELECT BASE_SCHEMA_NAME, BASE_OBJECT_NAME,DEPENDENT_SCHEMA_NAME,
DEPENDENT_OBJECT_NAME,DEPENDENT_OBJECT_TYPE
FROM “SYS”.”OBJECT_DEPENDENCIES”
WHERE BASE_SCHEMA_NAME = ‘TCMP’ /* <== Schema Name */
AND BASE_OBJECT_NAME = 'CS_SALESORDER' /* <== Table Name */
Analyze expensive statement trace
select
to_varchar(“STATEMENT_START_TIME”,’DD.MM.YYYY’) “EXEC_DATE”,
to_varchar(“STATEMENT_START_TIME”,’HH24:MI:SS’) “EXEC_TIME”,
to_int(“DURATION_MICROSEC”/1000000) “DURATION_S”,
to_decimal(“MEMORY_SIZE”/1073741824,10,1) “MEM_GB”,
“RECORDS”,
“DB_USER”,
“APP_USER”,
“APPLICATION_NAME”,
“STATEMENT_STRING”,
length(“STATEMENT_STRING”) “SQL_LENGTH”,
OCCURRENCES_REGEXPR(‘JOIN’ FLAG ‘i’ IN “STATEMENT_STRING”) “JOIN”,
OCCURRENCES_REGEXPR(‘CASE’ FLAG ‘i’ IN “STATEMENT_STRING”) “DISTINCT”,
“ERROR_TEXT”,
“PARAMETERS”
from “SYS”.”M_EXPENSIVE_STATEMENTS”
where “OPERATION” in
(‘INSERT’,’SELECT’,’AGGREGATED_EXECUTION’) –exclude background activity
and “RECORDS” > 0
and to_varchar(“STATEMENT_START_TIME”, ‘YYYYMMDD’) = current_date
and to_int(to_varchar(“STATEMENT_START_TIME”,’HH24′))
between 8 and 17 –business hours
order by 3 desc;
Benefits of HANA Auditing
Most Common Functions used in Implementation
SELECT SESSION_USER “session user” FROM DUMMY;
SELECT TO_DATE(‘2010-01-12’, ‘YYYY-MM-DD’) “to date” FROM DUMMY;
SELECT TRIM (‘a’ FROM ‘aaa123456789aa’) “trim both” FROM DUMMY;
SELECT CURRENT_DATE “current date” FROM DUMMY;
SELECT
DAYS_BETWEEN (TO_DATE (‘2009-12-05’, ‘YYYY-MM-DD’),
TO_DATE(‘2010-01-05’, ‘YYYY-MM-DD’)) “days between”
FROM DUMMY;
SELECT UPPER (‘Ant’) “uppercase” FROM DUMMY;
SELECT CONCAT (‘C’, ‘at’) “concat” FROM DUMMY;
SELECT FLOOR (14.5) “floor” FROM DUMMY;
SELECT TO_DECIMAL(7654321.888888, 10, 3) “to decimal” FROM DUMMY;
SELECT REPLACE (‘DOWNGRADE DOWNWARD’,’DOWN’, ‘UP’) “replace” FROM DUMMY;
SELECT RTRIM (‘endabAabbabab’,’ab’) “rtrim” FROM DUMMY;
SELECT RIGHT(‘HI0123456789’, 20) “right” FROM DUMMY;
SELECT WEEK (‘2017-01-02’) FROM DUMMY;
SELECT LENGTH (‘length in char’) “length” FROM DUMMY;
SELECT SUBSTRING (‘1234567890’,4,2) “substring” FROM DUMMY;
SELECT WEEKDAY (TO_DATE (‘2010-12-31’, ‘YYYY-MM-DD’)) “week day” FROM DUMMY;
SELECT TO_VARCHAR (TO_DATE(‘2009-12-31’), ‘YYYY/MM/DD’) “to varchar” FROM DUMMY;
SELECT
YEARS_BETWEEN(TO_DATE(‘2001-01-01’),
TO_DATE(‘2003-03-14’)) “years_between”
FROM DUMMY;
SELECT YEAR (TO_DATE (‘2011-05-30’, ‘YYYY-MM-DD’)) “year” FROM DUMMY;
SELECT MONTH (‘2011-05-30’) “month” FROM DUMMY;
SELECT NOW () “now” FROM DUMMY;
SELECT LAST_DAY (TO_DATE(‘2010-01-04’, ‘YYYY-MM-DD’)) “last day” FROM DUMMY;
SELECT DAYOFYEAR (‘2021-05-30’) “dayofyear” FROM DUMMY;
SAP HANA SQL Commands for SOX/AUDIT Requirements
(Make sure auditing is enabled)
select * from audit_log where user_name=’USER’ and client_host LIKE ‘HOST%’ and (timestamp)>’2022-07-31 00:00:00′ order by 1;
select * from “SYS”.”M_CONNECTIONS” where client_host NOT LIKE ‘HOST%’ and USER_NAME LIKE ‘USER%’ and AUTHENTICATION_METHOD NOT LIKE ‘INTERNAL’;
select * from all_audit_log where user_name=’USER’ and EVENT_ACTION NOT LIKE ‘CONN%” and (timestamp)’2022-03-29 07:00:00′ order by 1;
select * from “PUBLIC”.”EFFECTIVE_ROLES” where USER_NAME=’USER’;
select * from audit_log where AUDIT_POLICY_NAME=’Grant’;
select * from audit_log where AUDIT_POLICY_NAME=’USER’ and EVENT_ACTION like ‘DROP%’ and GRANTEE=’USER’;
Read here Auditing in SAP HANA Cloud