In my last two blogs (see links below) I described approach how to analyze security relations in SAP HANA system. Using the excel tool you can very easily visualize relations between users and roles, internal relations between roles, privileges assigned to roles, and privileges assigned directly to individual users.

SAP HANA – Create your own security monitoring tool (part 1)

SAP HANA – Create your own security monitoring tool (part 2)

This approach is suitable for reviewing how security is built and if there are any deviations against the original design. However it is not very effective for answering the following two questions:

Question #1: Which users are having any authorizations against particular object?

Question #2: What is the list of object privileges that were granted to a particular user?

Reason why it is difficult to answer these questions is that the security system can have multiple layers (privilege is granted to role, which is granted to another role, which is granted … which is in the end granted to given user). User can receive different authorizations for the very same object from different roles. All this is making such analysis very difficult and not very transparent.

In this blog we will see how to deal with such requirement and how to repackage this functionality so that it can be easily accessed by other users.

Tables we will be using

In this example we will be using following tables:

SYS.USERS

SYS.GRANTED_ROLES

SYS.GRANTED_PRIVILEGES

From table SYS.USERS we will be using only column USER_NAME and other tables were described in my previous blogs (see links above).

SQL syntax SELECT … FROM … START WITH … CONNECT BY

In the example we will be using a special syntax of select statement that was designed for building the relationship tree based on hierarchical data.

Warning: This format of SQL statement is NOT (yet) officially supported and documented and therefore SAP might change its approach and not include it in further revisions. Also not all related features are available – for example “level” keyword that would give the result really nice formatting. (This blog was written and tested against SAP HANA revision 32.)

Warning: Statements below are using UNION operator. In revision 32 this could lead to crash of index server. Revision 33 should be fixing this issue. (not tested)

This SQL syntax is starting with one or multiple records in initial set (defined by query part START WITH). Then every record is processed in recursive manner and relevant child records are inserted in result set (based on query part CONNECT BY). This process is then repeated until no suitable record can be added.

If you are interested in more detailed description of this syntax – then I would suggest using Google – there are many web pages explaining how this SQL syntax works.

Question #1: Which users are having any authorizations against particular object?

This question can be answered by following select statement:

SELECT grantee_type, grantee, object_type, sub_object_type, granted_object

FROM (

  SELECT * FROM (

    SELECT NULL grantee_type, NULL grantee, ‘USER’ object_type, NULL sub_object_type,

      user_name granted_object

    FROM SYS.USERS

    UNION

    SELECT grantee_type, grantee, ‘ROLE’ object_type, NULL sub_object_type,

      role_name granted_object

    FROM SYS.GRANTED_ROLES

    UNION

    SELECT grantee_type, grantee, ‘PRIVILEGE’ object_type, object_type sub_object_type,

      IFNULL(schema_name || ‘:’, ”) || IFNULL(object_name || ‘:’, ”) ||

      IFNULL(column_name || ‘:’, ”) || IFNULL(privilege, ”) granted_object

    FROM SYS.GRANTED_PRIVILEGES

  ) ORDER BY grantee_type, grantee, object_type, sub_object_type, granted_object

)

START WITH object_type || granted_object = UPPER(‘USER’ || ‘U_TOMAS’)

CONNECT BY (grantee_type || grantee) = PRIOR (object_type || granted_object);

The statement first joins all records from all three tables while supplementing missing information by NULL values. Then the result is sorted and a hierarchical tree is created.

Initial record is user itself and child records are connected based on combination of fields object_type + granted_object. Field object_type can be either ‘ROLE’ or ‘PRIVILEGE’ and granted_object is name of this role or privilege.

Unfortunately revision 32 where this SQL statement was not developed is not supporting keyword “level” that would allow adding indentation so the result is not very easy to read.

Question #2: What is the list of object privileges that were granted to particular user?

Reversed approach can be used to answer the second question:

SELECT object_type, sub_object_type, granted_object, grantee_type, grantee

FROM (

  SELECT * FROM (

    SELECT grantee_type, grantee, ‘ROLE’ object_type, NULL sub_object_type,

      role_name granted_object

    FROM SYS.GRANTED_ROLES

    UNION

    SELECT grantee_type, grantee, ‘PRIVILEGE’ object_type, object_type sub_object_type,

      IFNULL(schema_name || ‘:’, ”) || IFNULL(object_name || ‘:’, ”) ||

      IFNULL(column_name || ‘:’, ”) || IFNULL(privilege, ”) granted_object

    FROM SYS.GRANTED_PRIVILEGES

  ) ORDER BY grantee_type, grantee, object_type, sub_object_type, granted_object

)

START WITH object_type || sub_object_type || granted_object LIKE

  UPPER(‘PRIVILEGE’ || ‘SCHEMA’ || ‘DATA_MAN_ELECTRICITY%’)

CONNECT BY PRIOR (grantee_type || grantee) = (object_type || granted_object);

Again, first all records are merged together using UNION, sorted, and then the hierarchy is created.

Initial record is object – defined by type (either ‘ROLE’ or ‘PRIVILEGE’), subtype and name of object (name of object is concatenated string composed from schema_name, object_name, column_name and privilege).

You can determine available object subtypes using following query:

SELECT DISTINCT object_type FROM SYS.GRANTED_PRIVILEGES ORDER BY object_type;

Result of this query (example):

ANALYTICALPRIVILEGE

MONITORVIEW

PROCEDURE

REPO

SCHEMA

SEQUENCE

SYSTEMPRIVILEGE

TABLE

VIEW

As object name it is sufficient to provide name of queried object and substitute remaining parts with % placeholder because LIKE operator was used to determine object name.

Alternatively you can determine complete object name using following statement:

SELECT DISTINCT * FROM (

  SELECT object_type sub_object_type, IFNULL(schema_name || ‘:’, ”) ||

    IFNULL(object_name || ‘:’, ”) || IFNULL(column_name || ‘:’, ”) ||

    IFNULL(privilege, ”) granted_object

  FROM SYS.GRANTED_PRIVILEGES)

WHERE granted_object LIKE UPPER(‘DATA_MAN_ELECTRICITY%’)

ORDER BY sub_object_type, granted_object;

Result of this query (example):

SCHEMA    DATA_MAN_ELECTRICITY:ALTER

SCHEMA    DATA_MAN_ELECTRICITY:CREATE ANY

SCHEMA    DATA_MAN_ELECTRICITY:DELETE

SCHEMA    DATA_MAN_ELECTRICITY:DROP

SCHEMA    DATA_MAN_ELECTRICITY:EXECUTE

SCHEMA    DATA_MAN_ELECTRICITY:INDEX

SCHEMA    DATA_MAN_ELECTRICITY:INSERT

SCHEMA    DATA_MAN_ELECTRICITY:SELECT

SCHEMA    DATA_MAN_ELECTRICITY:UPDATE

Only user with privilege CATALOG READ can run above mentioned statements and get a full result. However there is way how to bypass this security limitation. In second part of this blog we will be “packaging” this functionality and creating role that will enable any user to run these queries without the need to grant them role CATALOG READ.

“Packaging approach”

Default behavior of SAP HANA in relation to stored procedures is that the defined code is executed with privileges of user that created given stored procedure and not with privileges of user that is executing the given stored procedure. This is very nicely explained in SAP HANA SQL Script Guide:

“You can specify the security mode. Privileges are always checked with the privileges of the definer of a procedure when the procedure is created. With security mode “definer”, which is the default, execution of the procedure is then performed with the privileges of the definer of the procedure. The other alternative is mode “invoker”. In this case, privileges are checked at runtime with the privileges of the caller of the function.”

Second nice feature of SAP HANA stored procedures is the ability to map the result of code execution as a newly created view. In such case you can execute stored procedure also by running SELECT statement against such view. Again there is nice explanation in SAP HANA SQL Script Guide:

“If a read-only procedure has exactly one table output parameter a RESULT VIEW can be specified. The name of the result view can be any valid SQL identifier. When a result view is defined for a procedure, it can be called from a SQL statement like a table or view reference.”

By combining these two aspects we can create a view that can be called by a simple select statement, will contain all logic inside the view definition, and can be made accessible to any user just by granting select privilege against this view.

Question #1: Which users are having any authorizations against a particular object?

First we will need to define table type that will be used inside stored procedure definition. Be sure to execute statements below with some user with appropriate privileges – ideally user SYSTEM.

DROP TYPE SYSTEM.T_USER_PRIVILEGE_TREE;

CREATE TYPE SYSTEM.T_USER_PRIVILEGE_TREE AS TABLE (grantee_type VARCHAR(5000),

  grantee NVARCHAR(256), object_type NVARCHAR(256),

  sub_object_type NVARCHAR(256), granted_object NVARCHAR(5000));

Then we can create stored procedure itself.

DROP PROCEDURE SYSTEM.GET_USER_PRIVILEGES;

DROP VIEW SYSTEM.USER_PRIVILEGES;

CREATE PROCEDURE SYSTEM.GET_USER_PRIVILEGES

  (IN USERNAME NVARCHAR(256), OUT USER_PRIVILEGES SYSTEM.T_USER_PRIVILEGE_TREE)

  LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW SYSTEM.USER_PRIVILEGES AS

BEGIN

  USER_PRIVILEGES =

    SELECT grantee_type, grantee, object_type, sub_object_type, granted_object

    FROM (

      SELECT * FROM (

        SELECT NULL grantee_type, NULL grantee, ‘USER’ object_type, NULL sub_object_type,

          user_name granted_object

        FROM SYS.USERS

        UNION

        SELECT grantee_type, grantee, ‘ROLE’ object_type, NULL sub_object_type,

          role_name granted_object

        FROM SYS.GRANTED_ROLES

        UNION

        SELECT grantee_type, grantee, ‘PRIVILEGE’ object_type, object_type sub_object_type,

          IFNULL(schema_name || ‘:’, ”) || IFNULL(object_name || ‘:’, ”) ||

          IFNULL(column_name || ‘:’, ”) || IFNULL(privilege, ”) granted_object

        FROM SYS.GRANTED_PRIVILEGES

      ) ORDER BY grantee_type, grantee, object_type, sub_object_type, granted_object

    )

    START WITH object_type || granted_object = UPPER(‘USER’ || USERNAME)

    CONNECT BY (grantee_type || grantee) = PRIOR (object_type || granted_object);

END;

Question #2: What is the list of object privileges that were granted to a particular user?

Again first we need to define table type:

DROP TYPE SYSTEM.T_OBJECT_PRIVILEGE_TREE;

CREATE TYPE SYSTEM.T_OBJECT_PRIVILEGE_TREE AS TABLE (object_type NVARCHAR(256),

  sub_object_type NVARCHAR(256), granted_object NVARCHAR(5000),

  grantee_type VARCHAR(5000), grantee NVARCHAR(256));

Then we can create stored procedure:

DROP PROCEDURE SYSTEM.GET_OBJECT_PRIVILEGES;

DROP VIEW SYSTEM.OBJECT_PRIVILEGES;

CREATE PROCEDURE SYSTEM.GET_OBJECT_PRIVILEGES

  (IN OBJECTNAME NVARCHAR(256), OUT OBJECT_PRIVILEGES SYSTEM.T_OBJECT_PRIVILEGE_TREE)

  LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW SYSTEM.OBJECT_PRIVILEGES AS

BEGIN

  OBJECT_PRIVILEGES =

    SELECT object_type, sub_object_type, granted_object, grantee_type, grantee

    FROM (

      SELECT * FROM (

        SELECT grantee_type, grantee, ‘ROLE’ object_type, NULL sub_object_type,

          role_name granted_object

        FROM SYS.GRANTED_ROLES

        UNION

        SELECT grantee_type, grantee, ‘PRIVILEGE’ object_type, object_type sub_object_type,

          IFNULL(schema_name || ‘:’, ”) || IFNULL(object_name || ‘:’, ”) ||

          IFNULL(column_name || ‘:’, ”) || IFNULL(privilege, ”) granted_object

        FROM SYS.GRANTED_PRIVILEGES

      ) ORDER BY grantee_type, grantee, object_type, sub_object_type, granted_object

    )

    START WITH object_type || sub_object_type || granted_object LIKE

      UPPER(‘PRIVILEGE’ || OBJECTNAME)

    CONNECT BY PRIOR (grantee_type || grantee) = (object_type || granted_object);

END;

Role definition and view execution

Now we can define security to grant other users right to use these views.

CREATE ROLE TECHNICAL_SECURITY_MONITOR;

GRANT SELECT ON SYSTEM.USER_PRIVILEGES TO TECHNICAL_SECURITY_MONITOR;

GRANT SELECT ON SYSTEM.OBJECT_PRIVILEGES TO TECHNICAL_SECURITY_MONITOR;

Authorized user can then call stored procedures in following way:

SELECT * FROM SYSTEM.USER_PRIVILEGES WITH PARAMETERS (‘placeholder’ = (‘$$username$$’, ‘U_TOMAS’));

This will return all authorizations for user U_TOMAS:

?     ?                       USER       ?                    U_TOMAS

USER  U_TOMAS                 ROLE       ?                    COMPOSITE_BIGDATA

ROLE  COMPOSITE_BIGDATA       ROLE       ?                    ANALYTIC_BIGDATA

ROLE  ANALYTIC_BIGDATA        PRIVILEGE  ANALYTICALPRIVILEGE  _SYS_BI_CP_ALL:EXECUTE

ROLE  ANALYTIC_BIGDATA        PRIVILEGE  SCHEMA               _SYS_BIC:SELECT

ROLE  COMPOSITE_BIGDATA       ROLE       ?                    DATA_BODS_BIGDATA_READ

ROLE  DATA_BODS_BIGDATA_READ  PRIVILEGE  SCHEMA               DATA_BODS_BIGDATA:SELECT

ROLE  COMPOSITE_BIGDATA       ROLE       ?                    DATA_MAN_BIGDATA_DDL

ROLE  DATA_MAN_BIGDATA_DDL    PRIVILEGE  SCHEMA               DATA_MAN_BIGDATA:ALTER

ROLE  DATA_MAN_BIGDATA_DDL    PRIVILEGE  SCHEMA               DATA_MAN_BIGDATA:CREATE ANY

ROLE  DATA_MAN_BIGDATA_DDL    PRIVILEGE  SCHEMA               DATA_MAN_BIGDATA:DROP

As I wrote above – result is not very well formatted because of missing support for “level” functionality. Hopefully it will be incorporated in later revisions.

Previous result can be interpreted in following way:

(USER) U_TOMAS

  (ROLE) COMPOSITE_BIGDATA

    (ROLE) ANALYTIC_BIGDATA

      (PRIVILEGE) ANALYTICALPRIVILEGE  _SYS_BI_CP_ALL:EXECUTE

      (PRIVILEGE) SCHEMA               _SYS_BIC:SELECT

    (ROLE) DATA_BODS_BIGDATA_READ

      (PRIVILEGE) SCHEMA               DATA_BODS_BIGDATA:SELECT

    (ROLE) DATA_MAN_BIGDATA_DDL

      (PRIVILEGE) SCHEMA               DATA_MAN_BIGDATA:ALTER

      (PRIVILEGE) SCHEMA               DATA_MAN_BIGDATA:CREATE ANY

      (PRIVILEGE) SCHEMA               DATA_MAN_BIGDATA:DROP

Second view can be called in the following way:

SELECT * FROM SYSTEM.OBJECT_PRIVILEGES WITH PARAMETERS (‘placeholder’ = (‘$$objectname$$’, ‘SCHEMADATA_MAN_ELECTRICITY%’));

Parameter details were explained above.

Possibility for integration in SAP HANA Studio

Currently you have to call the above-mentioned statements manually. It would be nice to automate this by adding a new item in context menu of SAP HANA Studio objects like user, schema, table, etc.

If someone from the SAP team responsible for SAP HANA Studio development is reading this blog – can you please forward the link to developers? Hopefully they will like the idea…