Skip to Content
-1

HANA Security - User Roles and Privileges SQL Script

Hello,

I am wanting to create a SQL script that will pull down the various user roles and privileges along with the grantor and details for the privileges (i.e. 'Create Any', 'Alter', 'Drop') for each user. Essentually a snap shot of all the user privileges in the system at that time.

Is this possible?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jan 25 at 12:04 AM

    Yes, that’s possible.

    Add comment
    10|10000 characters needed characters exceeded

    • I don’t have a script that I can share here.

      What the script needs to do is query the system views for privilege and role assignment and find what privileges and roles are directly assigned to a user. Do that for all users and you have your snapshot.

      If you want to capture role definitions, you basically do the same thing. The overall catch is to ensure that you got all roles that are assigned to any other role or user.

      Thinking about it - if the script simply does ALL roles you’re good without any tricky recursive queries.

      Besides this you might want to exercise some of your google-fu as this topic has been discussed several times.

      You also might want to see how design-time roles could help with the task of having privileges and roles in a “transportable” way.

  • Feb 11 at 08:33 PM

    The query that provided the results I was looking for is as follows:

    SELECT "P"."GRANTEE","ROLE_NAME","P"."GRANTOR","X"."GRANTOR","OBJECT_TYPE","SCHEMA_NAME","OBJECT_NAME","PRIVILEGE","P"."IS_GRANTABLE","IS_VALID"

    FROM "SYS"."GRANTED_ROLES" AS X

    LEFT JOIN "SYS"."GRANTED_PRIVILEGES" AS P

    ON "X"."GRANTEE" = "P"."GRANTEE";

    Add comment
    10|10000 characters needed characters exceeded