SAP HANA – How to analyze who has access to particular object
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…
Thanks Thomas for a wonderful blog
. This info is very useful for the security administrators .
Thanks,
Kiran .
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
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
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
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.
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
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?
Hello,
something like that (for Q #1) seems be OK :
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:
Kind regards
Frank
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
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
Hello,
I think you are looking for auditing - however this must be configured before you can use the results - more information here:
https://help.sap.com/saphelp_hanaplatform/helpdata/en/20/d3d56075191014af43d6487fcaa603/content.htm
Tomas
Thank you.
Srini
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
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):
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
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
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)
I need help running a report that will give the following output: DBA_NAME, User_deactivated, deactivation time, password change needed, is_password_enabled, Priviledges.
Here is what I have but I need the privileges and I an not sure how to go about it:
select
USER_NAME , USER_DEACTIVATED, DEACTIVATION_TIME, PASSWORD_CHANGE_NEEDED, IS_PASSWORD_ENABLED
from USERS
ORDER BY 1;
Thanks,
George Adjei