SUDO grant – a compromise

A long intro

One of the defining characteristics of “enterprise” software is that these systems are used across many parts of organisations and sometimes across many organisations. As with all shared resources commonly agreeing to who is responsible for what with these resources can help a great deal to avoid confusion and false finger-pointing in case something goes pear-shaped (which eventually will happen).

This very simple scenario is behind the setup of many organisation: this team “owns” the web application, that team “has” the application server infrastructure, yet another group takes care of the database and the storage servers. Each of these groups usually take great care of who gets access to “their” part of the system.

Now, imagine you are using a BW on HANA system and the arrangement you have is that “the BASIS”-team looks after everything that has to do with installing, upgrading, setting up, etc. the HANA database, but cannot, under any circumstances be bothered, to deal with anything happening “inside” the database. These “application-level” tasks are left to the BW team.

So far so good. Now imagine further, that every once in the blue moon the BW team requires a privilege on HANA level that has not so far been granted to any user or role before. Maybe the need for this privilege has not been seen before, maybe it is a new privilege, who cares? What is important is that the BW team gets the privilege and pronto!

If it turns out that the SYSTEM user is the only user in the database that can currently grant the privilege, the organisational answer to this requirement is: go and ask the BASIS team to grant the procedure.
That would work if the BASIS team would simply comply with the requirement and is available to do so.
Sometimes, it turns out, neither of these preconditions are fulfilled and the BW team would now be looking down a series of emails and meetings to explain and rectify why this privilege is required now, how they haven’t thought about it before, and what has the BASIS team to do with that.

Sounds familiar? Congratulation, you clearly have experience with working in SAP in larger organisations.

A technical solution to an organisational problem

So, we want to grant a privilege for which do not have the admin rights. How do we do that?
How do you do that in, say, Linux?

That’s right: you just type sudo in front of whatever you want to do and, assuming your user is in the SUDOERS group, Bob’s your uncle.

Why not do this in the HANA DB?

Well, because there is no sudo command!?
That’s right, again. HANA (like most/all serious DBMS) does not come with a built-in way to circumvent your carefully crafted setup of roles and privileges.

Looks like we need to build a sudo then, doesn’t it?

And that’s what I have done: the following is a SUDO_GRANT procedure that allows users that have the SUDO_GRANTERS role assigned to them, to execute grant commands as the SYSTEM user. And that without the need for SYSTEM to be logged on or even active (y’all of course know that SYSTEM should be deactivated in a productive environment, right?).

Since we don’t want to invite chaos, anarchy, and wrong but hurtful accusations into the lives of everyone touching this system, the procedure requires that database auditing has been set up and is active when someone tries to run the procedure.

Let’s go through this one step at a time.

The main “trick” of how this procedure works is right there in the procedure header:

create or replace procedure "SUDO_GRANT"
                        ( IN PRIVILEGE NVARCHAR(256)
                        , IN OBJECT    NVARCHAR(800) 
                        , IN GRANTEE   NVARCHAR(256)
                        , IN ADMIN_GRANT_OPTION NVARCHAR(17) DEFAULT '')
    LANGUAGE SQLSCRIPT
    SQL SECURITY DEFINER                -- this setting enables to grant as SYSTEM
    DEFAULT SCHEMA SYSTEM
AS
BEGIN
...

The SQL SECURITY DEFINER setting makes sure that the procedure is executed in the security context of the user that creates it. That’s the “DEFINER” of the procedure.

In other words, we need the SYSTEM user to actually create our SUDO_GRANT procedure to make this work.

With this out of the way, let’s look at the parameters of the procedure. It takes a PRIVILEGE, an OBJECT NAME, a GRANTEE, and the ADMIN_GRANT_OPTION.
Since the point of the procedure is only to grant privileges as SYSTEM and not to run arbitrary commands as SYSTEM, it really is restricted to only do GRANTs. Nothing else.
With this limited scope, the parameters simply mirror those of the GRANT statement.

Taking precautions

In order to make sure this procedure is set up correctly and that auditing has been activated for it, we have some checks for this:

BEGIN
DECLARE IS_DB_AUDIT_ACTIVE NVARCHAR(5) := 'FALSE';
DECLARE IS_AUDIT_POLICY_ACTIVE NVARCHAR(5) := 'FALSE';
DECLARE PROCEDURE_OWNER_NAME NVARCHAR(256);
DECLARE SUDO_GRANT_IN_WRONG_SCHEMA CONDITION FOR SQL_ERROR_CODE 10000;
DECLARE SUDO_GRANT_NOT_OWNED_BY_SYSTEM CONDITION FOR SQL_ERROR_CODE 10003;
DECLARE DB_AUDIT_INACTIVE CONDITION FOR SQL_ERROR_CODE 10001;
DECLARE SUDO_GRANT_AUDIT_INACTIVE CONDITION FOR SQL_ERROR_CODE 10002; 
DECLARE EXIT HANDLER FOR SQLEXCEPTION RESIGNAL;
-- check that the sudo_grant procedure is, in fact, in the SYSTEM schema
-- and has the name SUDO_GRANT. We need this for the auditing to work.
    if    (::CURRENT_OBJECT_NAME != 'SUDO_GRANT'
        or ::CURRENT_OBJECT_SCHEMA != 'SYSTEM') then
            SIGNAL SUDO_GRANT_IN_WRONG_SCHEMA
            SET MESSAGE_TEXT = 'SUDO_GRANT procedure is not in SYSTEM schema or is not called SUDO_GRANT. Re-install the procedure as SUDO_GRANT into the SYSTEM schema.';
    end if;
-- also check that the sudo_grant procedure is OWNED by SYSTEM in case 
-- someone installs it with into the SYSTEM schema but is not doing that 
-- as the SYSTEM user.   
    SELECT 
        MAX(owner_name) 
        INTO procedure_owner_name
    FROM 
        (SELECT owner_name 
            FROM ownership
            WHERE 
                object_name = 'SUDO_GRANT'
                AND schema_name = 'SYSTEM'
        UNION ALL
            SELECT '' AS owner_name
            FROM DUMMY
        );
    IF  (:procedure_owner_name != 'SYSTEM') THEN 
        SIGNAL SUDO_GRANT_NOT_OWNED_BY_SYSTEM
        SET MESSAGE_TEXT = 'SUDO_GRANT procedure is not owned by the SYSTEM user. Re-install the procedure logged in as SYSTEM.';
    END IF;
-- ensure that auditing is active before doing anything.
    SELECT 
            max(IS_DB_AUDIT_ACTIVE)  
       into IS_DB_AUDIT_ACTIVE
    FROM 
        (   select 
            'FALSE' as IS_DB_AUDIT_ACTIVE
            from dummy
        union all
            select 
                upper(value) as IS_DB_AUDIT_ACTIVE
            from 
                m_inifile_contents 
            where 
                file_name = 'global.ini' 
            and section = 'auditing configuration'
            and key = 'global_auditing_state'
        );
    
    if :IS_DB_AUDIT_ACTIVE != 'TRUE' then
        SIGNAL DB_AUDIT_INACTIVE 
        SET MESSAGE_TEXT = 'Database Auditing is currently not active. Activate Auditing before using SUDO_GRANT procedure.';
    end if;
-- check that "our" audit policy is present and active    
    select 
        max(IS_AUDIT_POLICY_ACTIVE) 
        into IS_AUDIT_POLICY_ACTIVE
    from
        (   select
                'FALSE' as IS_AUDIT_POLICY_ACTIVE
            from
                dummy
        union all
            select
                    IS_AUDIT_POLICY_ACTIVE
            from
                audit_policies
            where
                audit_policy_name = 'SUDO_GRANT'
            or (    event_action = 'EXECUTE' 
                and OBJECT_TYPE  = 'PROCEDURE' 
                and object_name  = 'SUDO_GRANT')
        );
    if :IS_AUDIT_POLICY_ACTIVE != 'TRUE' then
        SIGNAL SUDO_GRANT_AUDIT_INACTIVE 
        SET MESSAGE_TEXT = 'SUDO_GRANT auditing policy not active or not set up. Create and/or activate the audit policy for the SUDO_GRANT procedure before using it.';
    end if;

This looks like a whole lot of code but really is only checking four things:

• is the procedure created in the SYSTEM schema?
• is the procedure created by and owned by SYSTEM?
• is DB auditing active in this DB?
• is the auditing policy for SUDO_GRANT installed and active?

In case any of the questions above is not answered with a whole heartedly “YES!” the procedure raises an error and aborts.

This is, after all, a security sensitive functionality and we want everyone to not easily make mistakes.

Now, the action…

Finally, after all the checks, the command, executed as SYSTEM:

-- OK, if we reached this point, we know the auditing is active and we proceed with the next steps
    EXEC 'GRANT ' || :PRIVILEGE || ' ON ' || :OBJECT || ' TO ' || :GRANTEE || ' ' || :ADMIN_GRANT_OPTION;

And that really is all to it.

Let’s see it in action!

call SYSTEM.sudo_grant 
     (privilege => 'EXECUTE'
    , object    => '"SYS"."GET_INSUFFICIENT_PRIVILEGE_ERROR_DETAILS"'
    , grantee   => '_SYS_REPO'
    , admin_grant_option => 'WITH GRANT OPTION');

I know what you’re thinking: this is it? After all that build-up this is all there is to it? Yes, indeed.

Fairly boring, as the procedure call just mirrors the GRANT statement.

What remains

To make the above procedure call work, two more things need to be configured:

• the auditing
• a privilege group that allows running the procedure and that is assigned to the user that calls the procedure

Auditing

-- 2) create and enable the audit policies
CREATE AUDIT POLICY "SUDO_GRANT" AUDITING ALL EXECUTE ON "SYSTEM"."SUDO_GRANT" LEVEL INFO;
ALTER AUDIT POLICY "SUDO_GRANT" ENABLE;

SUDO_GRANTERS role

-- 3) setup the SUDO_GRANTERS role
CREATE role SUDO_GRANTERS;
GRANT EXECUTE ON "SYSTEM"."SUDO_GRANT" TO SUDO_GRANTERS;

The compromise

With the procedure available and the auditing in place, our application level team now can take care of their users’ HANA privileges without having to bother the BASIS team too much.
At the same time, everybody can be assured that any execution of the procedure will be recorded and won’t go unnoticed, in case something “fishy” happens with the HANA system.

The complete code can be found on GitHub:

sudo_grant.sql

Make sure to run the commands in this SQL file as SYSTEM user and one by one. It is not a full-fledged installation script, but just a file with the commands required to set this procedure up.

There you go, now you know.

Stay safe, stay healthy, and stay kind.