Skip to Content

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…

To report this post you need to login first.

16 Comments

You must be Logged on to comment or reply to a post.

    1. Tomas Krojzl Post author

      Hello Kiran,

       

      Thank you very much for your feedback. Is there any area in SAP HANA security that you think that I should address? I am having plans to write my next blog about security auditing but then I will probably move my focus to another topic.

       

      Tomas

      (0) 
  1. Julius von dem Bussche

    Out of curiosity: What happens if the creator of the code (in devlopment systems) does not have the access or even exist in the productive environment or they leave the company?

     

    Side comment: I regularly encounter customers who are confronted with new technologies and expect somthing similar to SUIM, ST01 and SU53 for them. Even if they are different and customer needs to adjust, they do expect something remotely comparable to get some information out of the user informatin and monitoring system features.It would be nice to see this as a basic requirement  with a bit of consistency for basic reporting in SAP’s various authorisation concepts with are available to customers.

     

    Cheers,

    Julius

    (0) 
    1. Tomas Krojzl Post author

      Hello,

       

      in productive system creator is whoever creates/imports the object – so creator would be person that did import…

       

      regarding side comment – some of the features are there (for example trace which can also be used for analysing failed authorizations) – however I agree it is not as easy to use as in NW.. but keep in mind that SAP HANA is still very young – I am sure that sooner or later these things will be added…

       

      Tomas

      (0) 
  2. Sean Holland

    hi Tomas, is this still valid for more up to date versions? I tried this in sql editor on 55 and it doesn’t like the connect by and start with syntax.

    (0) 
    1. Tomas Krojzl Post author

      Hello,

       

      it looks to me that SAP de-supported CONNECT BY statement… this is part of the risk using non-documented syntax…

       

      It is sad but I guess you will not be able to perform this analysis using statements above…

       

      Maybe Thomas Jung or Rich Heilman would be able to confirm this…

       

      Tomas

      (0) 
      1. Sean Holland

        Yes, it’s a shame, I didn’t actually want to do the security analysis, was just happy to see an example of connect by working on HANA, i was trying to use the connect by with partition to avoid of having cursor processing and I believed it could have been achieved using connect by. Be great if we could find out if this will be become a documented/supported feature in the future?

        (0) 
  3. Frank Buchholz

    Great blog to analyze the role concept!

     

    I’m not a SQL or HANA expert yet, therefore I like to ask following questions instead of experimenting myself to find the answer:

     

    1. Do these SQL statements work for analytic privileges, dynamic privileges, runtime roles, and design time roles or just for one of these concepts only? (Do I use the correct terms?)
    2. SAP recommends to use transportable design time roles instead of runtime roles. How can I verify that runtime roles either do not exist or at least are not granted to users?
    3. You can nest roles deeply, however, it’s much more easier to analyze the role concept if just one level of nesting is used. How can I identify deeply nested roles? One approach could be to search for roles which are granted and are grantees of other roles hoping not to find anything.

     

    Kind regards

    Frank 

    (0) 
    1. Tomas Krojzl Post author

      Hello,

       

      features you are mentioning (like transportable roles) are coming with SP05 – it seems that with this support package the statements are not functional anymore as we used non-documented syntax…

       

      see my response to previous comment…

       

      Tomas

      (0) 
  4. Srinivas kobbari

    Hi Tomas

     

    Is there a way in HANA to get the change document details for Users,Roles and Analytical Privileges?

     

    1.User created/Modified/Deleted by

    2.Role created/Modified/Deleted by

    3.Analytical privilege created/Modified/Deleted by

     

    Thanks

    Srini

    (0) 
  5. Srinivas kobbari

    Hi Tomas

     

    Is there a way in HANA Analytical privilege to exclude particular value…

     

    Scenario…..

     

    we have a region specific analytical privilege like REGION1 which has around 10 company codes…Right now the setup in AP is if we grant access to REGION1,user will get access to All company codes.

     

    But we want to exclude one company code out of 10.

    Do we have any option to exclude a particular value in Analytical privilege?

     

    I see that we have below operators available but no Exclude operator

     

    =

    <

    >

    <>

    Null

    Not Null

    Pattern

     

    Please advise.

     

    Thank you

    Srinivas

    (0) 
    1. Tomas Krojzl Post author

      Hello,

       

      it is some time since I was involved in security related topics (not doing security that much anymore) but as far as I know you cannot explicitly “ban” access – privileges are working in additive way – that means you have access to nothing unless granted – once granted then you got access and other privilege cannot remove this…

       

      so if privilege REGION1 will grant you access then other privilege cannot block it – so it will be required to ensure REGION1 does not give you access that you should not have…

       

      here following rule applies (copy from SAP guide):

      Multiple restrictions applied on the same column are combined by OR. However, restrictions across several columns are always combined by AND.

      Since I am not sure how you analytic privilege is built I can assume:

       

      1.) as part of REGION1 you grant access to 10 company codes => then you cannot undo this as you would manipulate same column so it would behave as OR => solution – add only 9 company codes and add 10th as part of separate privilege only to users that need this company code…

       

      2.) as part of REGION1 you do not touch any company codes => add restriction with operator “<>”

       

      In both cases you need to be sure there is no other analytic privilege that will grant that access as operator between privileges is also OR…

       

      Hope this helps…

       

      Tomas

      (0) 
      1. Nishant Sinha

        Hello Tomas,

         

        Can you please help me to write below query.

         

        Please write a query that returns which users have object/SQL privileges, beyond SELECT

        Please write a query that returns which users have package privileges, beyond SELECT

        Please write a query that looks at the GRANTED_PRIVILEGES view and returns the list of users and their analytic privileges.

        Please write a query that returns which users have application privileges, beyond SELECT

        Query to see current Upate and Patch Level

        Please write a query with users that have the ability to import packages.

        Please write a query with users that have the ability to export packages.

        Please write a query with users that have the ability to activate packages.

        Please write a query to show list of packages that were imported over a given period of time.

        Please write a query to show list of packages that were exported over a given period of time.

         

        Thank you advance

         

        Nishant

        (0) 
  6. Mashood K

    I am recieving errors while trying this out –

     

    * 257: sql syntax error: incorrect syntax near “START”: line 8 col 3 (at pos 436) SQLSTATE: HY000

     

    Isnt START WITH supported in SAP HANA ? If not what is the best method to do a recursive search on ROLES table.

    My requirement is to figure out all the privileges provided to a user. (Privileges granted directly and privileges granted using ROLES)

     

     

    (0) 

Leave a Reply