One way to minimize usage of SYSTEM user in SAP HANA

The SYSTEM user is the built-in user designed to bootstrap the database. Bootstrapping entails creating the initial system setup, which includes a list of objects, user and roles, as well as the initial system configuration. The initial system setup should be enough to operate the database without needing to use the SYSTEM user ever again. This can be achieved via following steps:

Step 1. Create a user with USER ADMIN privilege.

The advantage of having this user is that you lock/unlock SYSTEM user as required.

Step 2. Create personalized administrator accounts.

The SYSTEM user is the most powerful database user and it is not intended for use in daily operations. Depending upon your organization setup, administrator accounts with lower level privileges should be set up. Sample users could be:

1. User Administrator…
2. Database Administrator…
3. Backup Administrator…
4. …

Each user should be granted a corresponding role. Multiple privileges can be combined and assigned to a role. Please refer to the blog Caution: System Privilege! Managing Critical System Privileges in SAP HANA while assigning privileges to users or roles. This is just an example, as organizations can have their own compliance and governance requirements. Do not create a copy of SYSTEM user because it will be another powerful user. The next logical step is to define and create roles for those administrative accounts.

Define role for administrative accounts.

A basis role and authorization concept should be implemented according to organization needs. A general recommendation is to create design-time roles with .hdirole as the extension. The design-time roles should be as granular as possible containing the minimum of number of privileges. You can refer to the following guides depending upon your SAP HANA version.

Step 3 a . Create granting procedure.

A granting procedure can be created to assign privileges and roles. For this, we need two additional users.

The first user will be named PRIVILEGE_PROCEDURE_GRANTOR_DEFINER and we will grant to this user all the required privileges for role developing with the GRANT / ADMIN option. This user will also create a database procedure named PRIVILEGE_PROCEDURE_GRANTOR_DEFINER.

The second database user will be named PRIVILEGE_PROCEDURE_GRANTOR_USER and this user will be granted only the EXECUTE privilege on procedure PRIVILEGE_PROCEDURE_GRANTOR_DEFINER.GRANT.

The code below is used for creating a procedure, which should be executed via the SYSTEM user. Please refer to Best practices and recommendations for developing roles in SAP® HANA for additional details.

CREATE USER PRIVILEGE_PROCEDURE_GRANTOR_USER PASSWORD <password>;


CREATE USER PRIVILEGE_PROCEDURE_GRANTOR_DEFINER PASSWORD <password>;

GRANT SELECT ON SCHEMA _SYS_STATISTICS TO 
PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH GRANT OPTION;

GRANT SELECT, INSERT, UPDATE, DELETE ON
 _SYS_SECURITY._SYS_PASSWORD_BLACKLIST TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER 
WITH GRANT OPTION;

GRANT CATALOG READ TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER 
WITH ADMIN OPTION; 
GRANT SERVICE ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER 
WITH ADMIN OPTION;
GRANT INIFILE ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER 
WITH ADMIN OPTION; 
GRANT TRACE ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER 
WITH ADMIN OPTION; 
GRANT SESSION ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION; 
GRANT VERSION ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION; 
GRANT LICENSE ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION; 
GRANT SAVEPOINT ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION; 
GRANT RESOURCE ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION; 
GRANT BACKUP OPERATOR TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION; 
GRANT BACKUP ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION;
GRANT CREATE SCHEMA TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION; 
GRANT EXPORT TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION; 
GRANT IMPORT TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION; 
GRANT AUDIT ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION; 
GRANT AUDIT OPERATOR TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION; 
GRANT USER ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION;
GRANT ROLE ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION;
GRANT ENCRYPTION ROOT KEY ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER 
WITH ADMIN OPTION;
GRANT SSL ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION;
GRANT TRUST ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION;
GRANT CERTIFICATE ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION;
GRANT LOG ADMIN TO PRIVILEGE_PROCEDURE_GRANTOR_DEFINER WITH ADMIN OPTION;

CONNECT PRIVILEGE_PROCEDURE_GRANTOR_DEFINER PASSWORD  <password> ;--Enter your changed password

CREATE PROCEDURE PRIVILEGE_PROCEDURE_GRANTOR_DEFINER.GRANT(
  IN PRIVILEGES TABLE (
    PRIVILEGE_TYPE NVARCHAR(128), -- 'SCHEMA_OBJECT_PRIVILEGE'
                                  -- 'GLOBAL_OBJECT_PRIVILEGE'
                                  -- 'SCHEMA_ROLE'
                                  -- 'GLOBAL_ROLE'
                                  -- 'SCHEMA_PRIVILEGE'
                                  -- 'SYSTEM_PRIVILEGE'
    PRIVILEGE_NAME NVARCHAR(256), -- cf. SYS.PRIVILEGES
    OBJECT_SCHEMA NVARCHAR(256),  -- NULL or schema
    OBJECT_NAME NVARCHAR(256),
    OBJECT_TYPE NVARCHAR(128),    -- NULL or 'REMOTE SOURCE'
    GRANTEE_SCHEMA NVARCHAR(256), -- NULL or schema
    GRANTEE_NAME NVARCHAR(256),
    GRANTABLE NVARCHAR(5)         -- 'TRUE' or 'FALSE'
  )
)
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
AS
BEGIN
  DECLARE ERROR CONDITION FOR SQL_ERROR_CODE 10000;
  DECLARE CURSOR PRIVILEGES_CURSOR FOR SELECT * FROM :PRIVILEGES;

  FOR PRIVILEGE AS PRIVILEGES_CURSOR
  DO
    DECLARE TO_GRANTEE_CLAUSE NVARCHAR(512);
    DECLARE GRANTABLE_CLAUSE NVARCHAR(512) = '';

    IF PRIVILEGE.GRANTEE_SCHEMA IS NULL THEN
      TO_GRANTEE_CLAUSE = ' TO "' || 
ESCAPE_DOUBLE_QUOTES(PRIVILEGE.GRANTEE_NAME) || '"';
    ELSE
      TO_GRANTEE_CLAUSE = ' TO "' || ESCAPE_DOUBLE_QUOTES(PRIVILEGE.GRANTEE_SCHEMA)
                                  || '"."' || ESCAPE_DOUBLE_QUOTES(PRIVILEGE.GRANTEE_NAME) 
|| '"';
    END IF;

    IF PRIVILEGE.GRANTABLE = 'TRUE' THEN
      IF PRIVILEGE.PRIVILEGE_TYPE = 'SYSTEM_PRIVILEGE' OR
         PRIVILEGE.PRIVILEGE_TYPE = 'GLOBAL_ROLE' OR
         PRIVILEGE.PRIVILEGE_TYPE = 'SCHEMA_ROLE' THEN
        GRANTABLE_CLAUSE = ' WITH ADMIN OPTION';
      ELSE
        GRANTABLE_CLAUSE = ' WITH GRANT OPTION';
      END IF;
    ELSEIF PRIVILEGE.GRANTABLE != 'FALSE' THEN
      SIGNAL ERROR SET MESSAGE_TEXT = 'unsupported value for GRANTABLE: '
                                      || PRIVILEGE.GRANTABLE;
    END IF;

    IF PRIVILEGE.PRIVILEGE_TYPE = 'SCHEMA_OBJECT_PRIVILEGE' THEN
      EXEC 'GRANT "' || ESCAPE_DOUBLE_QUOTES(PRIVILEGE.PRIVILEGE_NAME) || '"'
        || ' ON "' || ESCAPE_DOUBLE_QUOTES(PRIVILEGE.OBJECT_SCHEMA)
                   || '"."' || ESCAPE_DOUBLE_QUOTES(PRIVILEGE.OBJECT_NAME) || '" '
        || TO_GRANTEE_CLAUSE
        || GRANTABLE_CLAUSE;
    ELSEIF PRIVILEGE.PRIVILEGE_TYPE = 'GLOBAL_OBJECT_PRIVILEGE' THEN
      IF PRIVILEGE.OBJECT_TYPE = 'REMOTE SOURCE' THEN
        EXEC 'GRANT "' || ESCAPE_DOUBLE_QUOTES(PRIVILEGE.PRIVILEGE_NAME) || '"'
          || ' ON ' || PRIVILEGE.OBJECT_TYPE || ' "' 
|| ESCAPE_DOUBLE_QUOTES(PRIVILEGE.OBJECT_NAME) || '" '
          || TO_GRANTEE_CLAUSE
          || GRANTABLE_CLAUSE;
      ELSE
        SIGNAL ERROR SET MESSAGE_TEXT = 'unsupported value for OBJECT_TYPE 
for GLOBAL_OBJECT_PRIVILEGE: '
                                        || PRIVILEGE.OBJECT_TYPE;
      END IF;
    ELSEIF PRIVILEGE.PRIVILEGE_TYPE = 'SCHEMA_ROLE' THEN
      EXEC 'GRANT "' || ESCAPE_DOUBLE_QUOTES(PRIVILEGE.OBJECT_SCHEMA)
                     || '"."' || ESCAPE_DOUBLE_QUOTES(PRIVILEGE.OBJECT_NAME) 
|| '" '
        || TO_GRANTEE_CLAUSE
        || GRANTABLE_CLAUSE;
    ELSEIF PRIVILEGE.PRIVILEGE_TYPE = 'GLOBAL_ROLE' THEN
      EXEC 'GRANT "' || ESCAPE_DOUBLE_QUOTES(PRIVILEGE.OBJECT_NAME) || '" '
        || TO_GRANTEE_CLAUSE
        || GRANTABLE_CLAUSE;
    ELSEIF PRIVILEGE.PRIVILEGE_TYPE = 'SCHEMA_PRIVILEGE' THEN
      EXEC 'GRANT "' || ESCAPE_DOUBLE_QUOTES(PRIVILEGE.PRIVILEGE_NAME) || '"'
        || ' ON SCHEMA "' || ESCAPE_DOUBLE_QUOTES(PRIVILEGE.OBJECT_NAME) || '" '
        || TO_GRANTEE_CLAUSE
        || GRANTABLE_CLAUSE;
    ELSEIF PRIVILEGE.PRIVILEGE_TYPE = 'SYSTEM_PRIVILEGE' THEN
      EXEC 'GRANT "' || ESCAPE_DOUBLE_QUOTES(PRIVILEGE.PRIVILEGE_NAME) || '"'
        || TO_GRANTEE_CLAUSE
        || GRANTABLE_CLAUSE;
    ELSE
      SIGNAL ERROR SET MESSAGE_TEXT = 'unsupported value for PRIVILEGE_TYPE: '
                                      || PRIVILEGE.PRIVILEGE_TYPE;
    END IF;
  END FOR;
END;

GRANT EXECUTE ON PRIVILEGE_PROCEDURE_GRANTOR_DEFINER.GRANT 
TO PRIVILEGE_PROCEDURE_GRANTOR_USER;-- The EXECUTE right on GRANT procedure should be restricted only to PRIVILEGE_PROCEDURE_GRANTOR_USER user

Similarly a REVOKE procedure can be coded.

The PRIVILEGE_PROCEDURE_GRANTOR_DEFINER can be now deactivated. Granting of roles and privileges can be done by calling the GRANT procedure with the PRIVILEGE_PROCEDURE_GRANTOR_USER user.

Step 3 b . Calling granting procedure.

First, we will create a temporary table to pass as a parameter to GRANT procedure.

CREATE LOCAL TEMPORARY TABLE #MY_PRIVILEGES 
(
    PRIVILEGE_TYPE NVARCHAR(128), -- 'SCHEMA_OBJECT_PRIVILEGE'
                                  -- 'GLOBAL_OBJECT_PRIVILEGE'
                                  -- 'SCHEMA_ROLE'
                                  -- 'GLOBAL_ROLE'
                                  -- 'SCHEMA_PRIVILEGE'
                                  -- 'SYSTEM_PRIVILEGE'
    PRIVILEGE_NAME NVARCHAR(256), -- cf. SYS.PRIVILEGES
    OBJECT_SCHEMA NVARCHAR(256),  -- NULL or schema
    OBJECT_NAME NVARCHAR(256),
    OBJECT_TYPE NVARCHAR(128),    -- NULL or 'REMOTE SOURCE'
    GRANTEE_SCHEMA NVARCHAR(256), -- NULL or schema
    GRANTEE_NAME NVARCHAR(256),
    GRANTABLE NVARCHAR(5)         -- 'TRUE' or 'FALSE'
  );
 

We can now insert values to this temporary table.

If you want to assign the USER ADMIN privilege to a user “test1”, you can use following INSERT statement:

 INSERT INTO #MY_PRIVILEGES VALUES('SYSTEM_PRIVILEGE', 'USER ADMIN',NULL, NULL, NULL, NULL, 'test1', 'TRUE'); 

Now you can call the GRANT procedure with the temporary table as shown below

  CALL "PRIVILEGE_PROCEDURE_GRANTOR_DEFINER"."GRANT" (#MY_PRIVILEGES); 

If you want to assign an SAP_INTERNAL_HANA_SUPPORT role to a user “test2”, you can use following INSERT statement:

  INSERT INTO #MY_PRIVILEGES VALUES('GLOBAL_ROLE', NULL, NULL,
'SAP_INTERNAL_HANA_SUPPORT', NULL, NULL, 'test2', 'TRUE');

And then call the GRANT procedure:

  CALL "PRIVILEGE_PROCEDURE_GRANTOR_DEFINER"."GRANT" (#MY_PRIVILEGES); 

After every call, you can clear the temporary table to avoid unnecessary errors, by executing the following SQL statement:

 TRUNCATE TABLE #MY_PRIVILEGES 

At any point, you can check the entries in your temporary table, by executing following SQL statement:

 Select * from #MY_PRIVILEGES 

Step 4: Log in with User Administrator and deactivate the SYSTEM user.

After you have set up all required administrative accounts and roles for managing database operations, you can now have a look at SAP Note 2493657 to deactivate the SYSTEM User in SAP HANA. You can assign privileges and roles by calling the GRANT procedure with the grantor user defined in Step 3b.

If you have questions regarding SAP HANA, please post your question here.