How To List all privileges granted to user

A privilege can be assigned to user directly or indirectly(via roles) ,sometimes this makes difficult to analyze users authorization.We will use EFFECTIVE_PRIVILEGES view to list users privilege.

Requirement :  You want to know all direct or indirect privileges assigned to an user.
Prerequisite : You need a database user with DATA ADMIN or CATALOG READ system privilege to query about other users. All users can query their own data , no special privileges required .
Steps :
  • Connect to HANA system and open SQL console in HANA studio
  • Execute following SQL

 

SELECT * FROM “PUBLIC”.”EFFECTIVE_PRIVILEGES” where USER_NAME = ‘anyUserName’;

 

Note : Its mandatory to specify a user name in the WHERE clause of above query , otherwise it will throw error.
SAP HANA EFFECTIVE_PRIVILEGES View
Above screen shows all privileges assigned to user VKTMP.
For example :
  • User VKTMP has “CREATE ANY” privilege for NAFTA Schema and VKTMP can grant this privilege to others.
  • User has default PUBLIC role assigned which gave him access to Views like M_BLOCKED_TRANSACTION, M_ES_TABLES etc.

 

EFFECTIVE_PRIVILEGES view has following columns:
Column name
Description
USER_NAME
Name of the user for whom effective privileges are shown
GRANTEE
User or role that has the privilege
GRANTEE_TYPE
‘USER’ or ‘ROLE’
GRANTOR
User or role that provided the privilege
GRANTOR_TYPE
‘USER’ or ‘ROLE’
OBJECT_TYPE
Type of the granted object like: ‘TABLE’, ‘SCHEMA’, …
SCHEMA_NAME
Schema name the object belongs to
OBJECT_NAME
Object name of granted object
COLUMN_NAME
Column name
PRIVILEGE
Privilege granted
IS_GRANTABLE
Privilege was granted ‘WITH GRANT OPTION’, ‘WITH ADMIN OPTION’: ‘TRUE’, ‘FALSE’
IS_VALID
Privilege is valid or it became invalid because of implicit revoking: ‘TRUE’, ‘FALSE’
One can club this EFFECTIVE_PRIVILEGES view information with EFFECTIVE_ROLES view to get more information.
eg :  SELECT * FROM “PUBLIC”.”EFFECTIVE_ROLES” where USER_NAME = ‘anyUserName’;
Above SQL will list all the roles assigned directly to user and roles that were inherited(assigned) with some other roles.

Leave a Reply