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.
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.
Reference : https://help.sap.com