List all privileges granted to user

Home

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.