16.2    Querying the System to Review Effective Privileges

Determining the exact privileges that a user will inherit is often difficult given the layers produced within our security models. User accounts can be granted roles that themselves are granted an additional layer of nested roles. Also, determining the exact number of grantees with specific privileges granted for a given object is often difficult. To help security administrators understand the effective or net result of privileges, several system views have been provided.

Eight different views are available to help security administrators review effective privileges. These views provide access in the areas of granted privileges, granted roles, accessible views, effective privilege grantees, effective structured privileges, effective privileges, effective role grantees, and effective roles. In the following sections, we’ll explore how each of these view types help security administrators manage SAP HANA security models.

16.2.1    Granted Privileges

When an administrator needs to view the privileges explicitly granted to an individual user or runtime role, the GRANTED_PRIVILEGES system view can be queried to list the privileges. This view doesn’t recursively check the privileges of roles granted to the grantee. To see the effective privileges assigned to a grantee, use the EFFECTIVE_PRIVILEGES system view, which we’ll discuss in Section 16.2.6.

Use the following SQL syntax to query the GRANTED_PRIVILEGES system view:

SELECT * FROM "SYS"."GRANTED_PRIVILEGES" 
WHERE GRANTEE = '<USER OR ROLE NAME>'; 

Note that the GRANTED_PRIVILEGES view doesn’t require a filter condition or statement within the WHERE clause. If executed without a filter, all grantees and their privileges will be returned. All columns within the view can be used to filter, sort, and group the data. Table 16.1 lists each available column and provides descriptions of the values that each column can return.

Column Name	Column Value Description
GRANTEE_SCHEMA_NAME	The grantee’s schema
GRANTEE	A runtime role or user account
GRANTEE_TYPE	Static value of either USER or ROLE
GRANTOR	User that granted the privilege
OBJECT_TYPE	Type of object granted
SCHEMA_NAME	The owning schema of the object
OBJECT_NAME	Object’s name
COLUMN_NAME	Column name
PRIVILEGE	Name of the granted privileges
IS_GRANTABLE	TRUE or FALSE: Was the privilege granted using WITH GRANT OPTION or WITH ADMIN OPTION?
IS_VALID	TRUE or FALSE: Is the privilege valid?

Table 16.1    Columns in GRANTED_PRIVILEGES View

16.2.2    Granted Roles

If an administrator needs to view the roles explicitly granted to an individual user or runtime role, the GRANTED_ROLES system view can be queried. This view doesn’t recursively check nested or effective roles granted to the grantee. To see the effective roles assigned to a grantee, use the EFFECTIVE_ROLES system view, which is discussed in Section 16.2.8.

Use the following SQL syntax to query the GRANTED_ROLES system view:

SELECT * FROM "SYS"."GRANTED_ROLES" 
WHERE GRANTEE = '<USER OR ROLE NAME>'; 

Note that the GRANTED_ROLES view doesn’t require a filter condition or statement within the WHERE clause. If executed without a filter, all grantees and their roles will be returned. All columns within the view can be used to filter, sort, and group the data. Table 16.2 lists each available column and provides descriptions of the values that each column can return.

Column Name	Column Value Description
GRANTEE_SCHEMA_NAME	The grantee’s schema
GRANTEE	A runtime role or user account
GRANTEE_TYPE	Static value of either USER or ROLE
ROLE_SCHEMA_NAME	The role’s schema
ROLE_NAME	The name of the granted role
GRANTOR	Name of the user account that granted the role
IS_GRANTABLE	TRUE or FALSE: Was the privilege granted using WITH GRANT OPTION or WITH ADMIN OPTION?
IS_GRANTED_BY_LDAP	TRUE or FALSE: Was the role granted by an LDAP group?

Table 16.2    Columns in GRANTED_ROLES View

16.2.3    Accessible Views

To list all catalog and column views that can be accessed by a given user, the ACCESSIBLE_VIEWS system view can be queried. The view must be filtered with the condition USER_NAME = 'Some User'. To return the accessible views for multiple users, use multiple USER_NAME = 'Some User' conditions, with an OR operator separating each condition clause. For example, the following SQL code will return the views that can be accessed by User Name 1 and User Name 2.

SELECT * from "SYS"."ACCESSIBLE_VIEWS" 
WHERE (USER_NAME = '<User Name 1>' 
OR USER_NAME = '<User Name 2>');

All columns within the view can be used to filter, sort, and group the data. Table 16.3 shows a list of each available column and provides description of the values that each column can return.

Column Name	Column Value Description
USER_NAME	Name of the user with access to the catalog or column view
SCHEMA_NAME	The view’s schema
VIEW_NAME	Name of the catalog or column view
ANALYTICAL_PRIVILEGE_NEEDED	TRUE or FALSE: Is an analytic privilege needed to query this view?

Table 16.3    Columns in ACCESSIBLE_VIEWS System View

16.2.4    Effective Privilege Grantees

When an administrator needs to view a list of grantees with assigned privileges, the EFFECTIVE_PRIVILEGE_GRANTEES system view can be queried. This view is designed to list grantees that have the specified access to a particular catalog object, system privilege, or analytic privilege. This view can be queried using a variety of different filter values. At a minimum, the OBJECT_TYPE and PRIVILEGES columns must be included within the filter condition WHERE clause. Depending on the value specified in the OBJECT_TYPE column, additional columns must also be included in the WHERE clause.

When you need to list grantees with a specific level of access to a schema, use the SQL syntax shown in Listing 16.1.

SELECT * FROM EFFECTIVE_PRIVILEGE_GRANTEES 
WHERE OBJECT_TYPE = 'SCHEMA' 
AND SCHEMA_NAME = '<schema_name>' 
AND PRIVILEGE = '<privilege>';

Listing 16.1    Example SQL Syntax for Identifying Users with Specific Privileges Granted to a Specific Schema

The filter conditions in the SQL code must include OBJECT_TYPE = 'SCHEMA', with additional filter conditions on the SCHEMA_NAME and PRIVILEGES columns.

When you need to list grantees with a specific level of access to a given catalog object, use the SQL syntax shown in Listing 16.2.

SELECT * FROM EFFECTIVE_PRIVILEGE_GRANTEES 
WHERE OBJECT_TYPE = '<object_type>' 
AND SCHEMA_NAME = '<schema_name>' 
AND OBJECT_NAME = '<object_name>' 
AND PRIVILEGE = '<privilege>';

Listing 16.2    Example SQL Syntax for Identifying Users with Specific Privileges Granted to a Specific Catalog Object

As shown in Listing 16.2, four filter conditions must be satisfied to identify users with the specified privileges on the specified catalog object. The filter must include the OBJECT_TYPE filter condition. For example, condition values TABLE, VIEW, PROCEDURE, INDEX, or other catalog objects are possible. The condition for the SCHEMA_NAME column must contain the object’s schema name. The condition for the OBJECT_NAME column must include the name of the object in question. The PRIVILEGE condition must include the object privileges. For example, SELECT, EXECUTE, DELETE, ALTER, INSERT, UPDATE, or DELETE can be included with this condition. A grantee will only be listed if the privileges are explicitly granted to the object. As a result, you should always check the object’s schema for the given permission in addition to the object itself. Recall that objects inherit privileges from their schemas; however, inherited privileges will not be calculated with the SQL statement in this section.

When you need to list grantees with a specific system privilege, use the following SQL syntax:

SELECT * FROM EFFECTIVE_PRIVILEGE_GRANTEES 
WHERE OBJECT_TYPE = 'SYSTEMPRIVILEGE' 
AND PRIVILEGE = '<privilege>';

The OBJECT_TYPE = 'SYSTEMPRIVILEGE' filter condition must be included, and the PRIVILEGES column must be included within the WHERE clause.

16.2.5    Effective Structured Privileges

To view the effective filter conditions applied for a given user and view, the EFFECTIVE_STRUCTURED_PRIVILEGES view can be queried. When queried, three columns must be included in the WHERE clause: ROOT_OBJECT_NAME, ROOT_SCHEMA_NAME, and USER_NAME. The ROOT_OBJECT_NAME column filter must reference the name of the catalog or column view, the ROOT_SCHEMA_NAME column filter must reference the root object’s schema, and the USER_NAME column filter must reference the user that will query the view specified in the root object filter condition.

The SQL code shown in Listing 16.3 can be customized to match objects within your environment.

SELECT * 
FROM "SYS"."EFFECTIVE_STRUCTURED_PRIVILEGES"
WHERE ROOT_SCHEMA_NAME = '_SYS_BIC' AND
ROOT_OBJECT_NAME = '<Column View Name>'
AND USER_NAME = '<User Name>';

Listing 16.3    Example SQL Statement Template for Querying the EFFECTIVE_STRUCTURED_PRIVILEGES View

The SQL code shown in Listing 16.4 is an example for identifying restrictions applied to the TEST_SVC user when querying the enowa.infoview/CV_INTERNET_SALES column view.

SELECT * 
FROM "SYS"."EFFECTIVE_STRUCTURED_PRIVILEGES"
WHERE ROOT_SCHEMA_NAME = '_SYS_BIC' AND
ROOT_OBJECT_NAME = 'enowa.infoview/CV_INTERNET_SALES'
AND USER_NAME = 'TEST_SVC3';

Listing 16.4    SQL Code to Query EFFECTIVE_PRIVILEGE_GRANTEES View

All columns within the view can be used to filter, sort, and group the data. Table 16.4 shows a list of each available column and provides descriptions of the values that each column can return.

Column Name	Column Value Description
ROOT_SCHEMA_NAME	The column or catalog view’s schema. This column must be included in the filter.
ROOT_OBJECT_NAME	The column or catalog view’s name. This column must be included in the filter.
USER_NAME	The name of the user account you’re testing. The view will return the effective restrictions applicable for the user.
USER_ID	The numeric user ID of the user account you’re testing. The view will return the effective restrictions applicable for the user.
EFFECTIVE_FILTER	All effective filter conditions combined into a single statement.
STRUCTURED_PRIVILEGE_NAME	Name of the structured privileges.
STRUCTURED_PRIVILEGE_ID	Numeric ID of the structured privileges.
STRUCTURED_PRIVILEGE_FILTER	Single filter condition applied by the given structured privilege’s name.
STRUCTURED_PRIVILEGE_STATUS	Indicates the status of the structured privileges. Possible values are APPLIED, NOT GRANTED, NO MATCHING ATTRIBUTE, or NO FILTER VALUES FOUND.

Table 16.4    Columns in EFFECTIVE_STRUCTURED_PRIVILEGES View

16.2.6    Effective Privileges

To see a complete listing of privileges that have been granted directly and indirectly, the EFFECTIVE_PRIVILEGES view can be queried. This view returns the net effective privileges, even if they’re granted from roles. The view provides the source of the privileges and a list of objects related to the granted privileges.

The following example SQL statement can be used to list all the privileges granted to a particular user account:

SELECT * FROM "SYS"."EFFECTIVE_PRIVILEGES" 
WHERE USER_NAME = '<USER_NAME>'; 

Replace the <USER_NAME> variable with the user account in question. This view provides access to several columns that can be used to sort, group, and filter the view. Table 16.5 provides a listing of the key columns available in this view.

Column Name	Column Value Description
USER_NAME	The name of the user related to the privileges.
GRANTEE_SCHEMA_NAME	The grantee schema name.
GRANTEE	The name of the user or role that provided the privilege.
GRANTEE_TYPE	The type of grantee; outputs either USER or ROLE.
GRANTOR	The name of the user or role that granted the privilege.
GRANTOR_TYPE	The type of grantor; outputs either USER or ROLE.
OBJECT_TYPE	The catalog object type or the type of privilege.
SCHEMA_NAME	If object type is schema, the name of the schema is returned.
OBJECT_NAME	When applicable, the name of the object, for which the privilege is applied, is returned.
COLUMN_NAME	The column name.
PRIVILEGE	The name of the privileges that has been granted.
IS_GRANTABLE	When TRUE, the column indicates that the WITH GRANT OPTION, WITH ADMIN OPTION has been granted.
IS_VALID	Returns TRUE or FALSE; when TRUE, the privilege is currently active and valid.

Table 16.5    Columns in EFFECTIVE_PRIVILEGES View

16.2.7    Effective Role Grantees

To generate a list of users and roles that have been granted a role, the EFFECTIVE_ROLE_GRANTEES view can be used. This view requires that the ROLE_NAME column be included in the WHERE clause as a filter condition. To query this view, use the following SQL syntax:

SELECT * FROM "SYS"."EFFECTIVE_ROLE_GRANTEES" 
WHERE ROLE_NAME = '<role_name>'; 

The view must be queried with a WHERE clause containing a filter condition on the ROLE_NAME column. Other columns within the view can also be used for sorting, filtering, and grouping. Table 16.6 lists the available columns.

Column Name	Column Value Description
ROLE_SCHEMA_NAME	The role’s schema name.
ROLE_NAME	The name of the role in question.
GRANTEE	The name of the user or role that has been granted the role in question.
GRANTEE_TYPE	The type of grantee in question. Choose USER or ROLE.
GRANTOR	The name of the user that granted the role.
GRANTED_ROLE_NAME	The name of the role that provided the granted role. When the granted role name and role are the same value, the role is directly granted. When the granted role name is different, the role was inherited from another role.
IS_GRANTABLE	When TRUE, this column indicates that the WITH GRANT OPTION or WITH ADMIN OPTION has been granted, and the user can grant this role to other users or roles.

Table 16.6    Columns in EFFECTIVE_ROLE_GRANTEES View

16.2.8    Effective Roles

To view a complete list of roles that have been directly and indirectly granted to a given user, the EFFECTIVE_ROLES view can be used. This view is useful for listing the roles that a user has been granted, both directly and through role inheritance. To query this view, use the following SQL syntax:

SELECT * FROM "SYS"."EFFECTIVE_ROLES" 
WHERE USER_NAME = '<USER_NAME>';

The USER_NAME column must be used in the WHERE clause as a filter condition. Table 16.7 provides a list of the available columns.

Column Name	Column Value Description
USER_NAME	Name of the given user related to the roles that are listed.
GRANTEE	Lists the user name when the role is directly granted to the user. A role name is listed when the role is granted from the listed role name.
GRANTEE_TYPE	Indicates the type of grantee. The USER or ROLE option will be listed.
GRANTOR	The user that granted the role.
ROLE_SCHEMA_NAME	The role’s schema name.
ROLE_NAME	The name of the role.
IS_GRANTABLE	When TRUE, this column indicates that the WITH ADMIN OPTION has been granted, and the user can grant this role to other users or roles.

Table 16.7    Columns in EFFECTIVE_ROLES View