SQL usage syntax for hive Integrated Sentry

The Sentry permission control is configured by entering the GRANT and REVOKE statements via the Beeline (Hiveserver2 SQL Command line interface). The syntax is similar to some of the mainstream relational databases today. It is important to note that when the Sentry service is enabled, we must use the Beeline interface to perform hive queries, and the hive CLI does not support sentry.

CREATE ROLE Statement

The Create ROLE statement creates a character that can be empowered. Permissions can be assigned to roles, and then to individual users. A user who is assigned to a role can perform permissions on that role.

Only roles that have an administrator can create/drop roles. By default, Hive, Impala, and Hue users have administrator roles.

CREATE ROLE [Role_name];

DROP ROLE Statement

The DROP role statement can be used to remove a role from the database. Once removed, the role that was previously assigned to all users will be canceled. Statements that have been executed before are not affected. However, because hive checks the user's permissions before executing each query statement, a user session that is in active state is affected.

DROP ROLE [Role_name];

GRANT ROLE Statement

The grant role statement can be used to grant a role to a group. Only administrator users of Sentry can perform this operation.

GRANT ROLE Role_name [, Role_name] to GROUP (groupName) [, Group (GroupName)]

REVOKE ROLE Statement

The REVOKE role statement can be used to remove roles from a group. Only administrator users of Sentry can perform this operation.

REVOKE ROLE Role_name [, Role_name] from GROUP (groupName) [, Group (GroupName)]

GRANT (PRIVILEGE) Statement

Grant the permission of an object to a role that the user must be an administrator user for sentry.

GRANT (PRIVILEGE) [, (PRIVILEGE)] On (OBJECT) (object_name) to Role (RoleName) [, Role (RoleName)]

REVOKE (PRIVILEGE) Statement

Because only authenticated administrator users can create roles, only an administrator user can cancel the permissions of a group.

REVOKE (PRIVILEGE) [, (PRIVILEGE)] On (OBJECT) (object_name) from ROLE (roleName) [, Role (RoleName)]

GRANT (PRIVILEGE) ... With GRANT OPTION

In cdh5.2, you can delegate to other roles to grant and release permissions. For example, a role that has been granted permission with GRANT option can grant/revoke the same permissions to other roles. Therefore, if a role has all the permissions of a library and set with GRANT OPTION, the user of the role assignment can execute the Grant/revoke statement on the database and the tables in it.

Grant (PRIVILEGE) on (OBJECT) (object_name) to ROLE (RoleName) with Grant OPTION

Only a role with a special permission with the GRANT option or its parent permission can remove this permission from other roles. Once the following statement executes, all grant permissions associated with it will be dismissed.

REVOKE (Rivilege) on (bject) (Bject_name) from ROLE (RoleName)

Hive does not currently support the ability to give a role with Grant OPTION before releasing it. To remove the WITH GRANT option, you can remove the tag with GRANT option to re-attach it.

SET ROLE Statement

The SET role statement can select a role for the current session to take effect. A user can only enable the roles assigned to him. Any roles that do not exist and roles that are not available to the current user are not valid. If no roles are used, the user will use any of the permissions that belong to his role.

• Select a role to use:

  To enable a specific role:

• Use all the roles:

  To enable a specific role:

• Close all roles

  SET ROLE NONE;

SHOW Statement

• Displays the database for which the current user has permissions related to libraries, tables, and columns:

  SHOW DATABASES;

• Displays the table that the current user owns the table, column-related permissions;

  SHOW TABLES;

• Displays the columns for which the current user has SELECT permissions:

  SHOW COLUMNS (from| IN) table_name [(from| in) db_name];

• Displays all roles in the current system (only administrator users can perform):

  SHOW ROLES;

• Displays the roles that currently affect the current session:

  SHOW current ROLES;

• Displays all roles assigned to the specified group (only administrators and users within the specified group can execute)

  SHOW ROLE GRANT GROUP (groupName);

• The show statement can be used to display permissions granted to a role or to display all permissions for a particular object of a role.
  Displays all assigned permissions for the specified role. (Only the administrator user and the user assigned to the specified role can execute). The following statement also displays permissions at any column level.

  SHOW GRANT ROLE (roleName);

• Displays all assigned permissions for a role for the specified object (only the administrator user and the user assigned to the specified role can execute). The following statement also displays permissions at any column level.

  SHOW GRANT ROLE (roleName) on (OBJECT) (objectName);

SQL usage syntax for hive Integrated Sentry