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.
Attention here: it’s not sufficient to create the procedure in the SYSTEM schema with some other DBA user. It absolutely has to be the SYSTEM user itself that creates this procedure.
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:
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.
Looks pretty cool. Might give the security team some things to think about.
Hey Lars,
brilliant idea and solution for not only one question. I just would recommend to add the procedure for the SYSTEMDB where you have to select the nameserver.ini (not the global.ini as for the tenants) for the auditing state. Another aspect is also a revoke procedure to do the other way around.
REVOKE FROM ;
EXEC ‘REVOKE ‘ || :PRIVILEGE || ‘ FROM ‘ || :GRANTEE || ‘ ‘ || :ADMIN_GRANT_OPTION;
Regards,
Jens
Hi Jens,
Thanks for being a steady reader of my irregular blog! Glad, you like the idea for this procedure.
Your proposed extensions definitively have merit and there are other possible additions that may be useful (e.g. enabling the granting of activated repository roles).
My approach here was to try and build the minimal working thing that satisfies the need without opening up all too many options to misuse it. If the grant is required to keep the “application” team going – this procedure allows for it. But it doesn’t allow for sneakily “cleaning up” grants that really should have been covered with proper role administration.
As I see it, I wanted the “duct tape” to be available AND visible enough to be recognised as not quite right.
I guess, the driving force behind this little tool is really not a functional one but a social/organisational one 😉
Coming back to your extensions: the code is on GitHub and free for everyone to extend, change, adapt.
Cheers,
Lars