Description: This document is translated/collated in Hive Official document https://cwiki.apache.org/confluence/display/Hive/SQL+Standard+Based+Hive+Authorization# Sqlstandardbasedhiveauthorization-objectprivilegecommands.
status of authorization prior to Hive version 0.13
Hive Default Authorization (Authorization (Legacy Mode))
It is not designed to prevent malicious users from accessing (manipulating) unauthorized data, helping users avoid unexpected actions, imperfect authorization mechanisms, and no authorization confirmation in many directives, such as Grant grant; authorization validation occurs during the compile phase of the hive SQL statement; Allows users to execute DFS directives, user-defined functions, and shell directives, which are likely to bypass the security of the client, provide granular access control by creating a view and authorizing the view (instead of authorizing the table on which the view depends), providing access to a few columns in the table.
Storage-based authorization (Storage Based Authorization in the Metastore Server)
Typically used for authorization of the Metastore Server API; After the Hive 0.12.0 release, support is started; Provides granular access control (column level, row level), although it protects the metadata in Metastore from being compromised by malicious users
SQL standard-based hive authorization (SQL Standards Based hive Authorization)The SQL standard-based hive authorization provides a third option for hive authorization, which is fully compatible with the SQL authorization model and is not a backward compatibility issue for current users and is therefore recommended. Once the user has migrated to this more secure authorization mechanism, the default authorization mechanism can be discarded. The SQL-based authorization model can be used in conjunction with the storage-based authorization model (Hive Metastore Server). As with Hive's default authorization mechanism, authorization confirms the compilation phase of the SQL statement that occurred. To ensure the security of the authorization model, the client also needs security assurances, which can be done in the following two ways: (1) User access must and can only be done through HiveServer2; (2) restricting user code and non-SQL instructions. Authorization confirmation is based on the identity of the user who submits the SQL instruction, but the SQL instruction is executed as a hive server user (that is, the process user of Hive server), so the hive server user must have the appropriate directory (file) permissions (depending on the SQL Directive, The required permissions are also different). Consistent with standard SQL as much as possible, but with a slight bias in implementation. There are some reasons to facilitate the migration of existing users (licensing-related), and others for ease of use. Under this authorization model control, users with access to tools (directives) such as the Hive CLI, HDFS commands, Pig command line, ' Hadoop jar ', and so on, are called privileged users. Within an organization (team), only some teams that need to perform ETL-related work need these special permissions, and the access to these tools is not HiveServer2, so they are not controlled by this authorization model. For users who need to access hive tables through the hive CLI, pig, and MapReduce, the appropriate permissions can be controlled by enabling storage Based Authorization in Hive Metastore server Other scenarios may need to be done in conjunction with the security mechanisms of Hadoop. Most users (using SQL statements and ODBC/JDBC access HiveServer2 for business analysis) can use this authorization model for permission control.
limitations of Hive directives and statementsDFS, add, delete, compile, reset are disabled when you use the SQL standard-based licensing model, and TRANSFORM clause is disabled. The set of instructions to change the hive configuration is limited to only certain users and can be configured by Hive.security.authorization.sqlstd.confwhitelist (Hive-site.xml). The ability to add or remove functions, macros, and permissions is restricted to users who have only the admin role to perform. In order for the user to be able to use (custom) functions, creating a permanent function (create permanent functions) functionality is added. The user with the role admin can execute the instruction add function, all the added functions can be used by all users.
PermissionsSELECT: Give permission to read an object, INSERT: Give permission to add data to an object (table), UPDATE: Give permission to perform an update operation on an object (table), delete: give permission to delete data on an object (table); All: Give all permissions on an object (converted to have the above four permissions).
ObjectPermissions are applied to the table or view, and the permissions described above do not support applying to the database. The database owner is used to confirm permissions for certain special operations; Hive allows users to use URIs in SQL statements, so URIs are also an object of hive. The permissions described above do not apply to URI objects. URIs usually point to a file or directory in the file system (HDFS) that is authorized based on the permissions of the user (the user who submits the SQL statement, the SQL statement that contains the URI object) on the file or directory.
Object ownerFor some special operations, whether the object owner of the object (table/view/database) determines whether there is permission to perform these special operations on the object. A user creates a table, view, or database that is the owner of the created table, view, or database. For a table or view, the owner has all the permissions to manipulate them (SELECT, INSERT, UPDATE, DELETE). The owner of the database can also be a role that can be set by the "ALTER database" directive to the owner of a database.
Users and RolesBoth users and roles can be assigned permissions. A user can belong to one or more roles. There are two roles of special significance: public and admin. All users are part of the role public, and we use that role and assign permissions to other users through the GRANT statement. When a user executes a query or instruction, the permissions granted by the user and the user's "current role (can be multiple)" permission are acknowledged. "Current role" can be viewed through "show roles". By default, "Current role" is all the roles that a user belongs to (except for role admin), and we can specify a role to be "current role" by the instruction "set role". Users who are database administrators are typically added to the role admin. Users with role admin can perform additional instructions, such as create role or drop role. They also have full access to the object, even if they are not explicitly authorized. Because the role admin does not belong to the "current role" by default, a user who belongs to the admin of the role wants to get the role admin permission, the "current role" must be switched to the role admin by the command set role first.
User (role) nameRole name capitalization is not sensitive. User name is case sensitive.
quoted Identifiers (Quoted Identifiers)In general, it is not recommended, this section is ignored.
Role Management Directives
Create RoleCREATE ROLE Role_name; Only the role admin has this permission. The role name all, default, and none are reserved.
Drop RoleDROP ROLE Role_name; Only the role admin has this permission.
Show Current Roles
SHOW current ROLES; Displays the user's current role list. There are two aspects involved in determining user permissions: Whether the user is assigned the appropriate permissions, or whether the user's role (possibly multiple) is assigned the appropriate permissions. The default current role contains all roles that the user belongs to, except for the role admin, even if the user belongs to role admin. All users can execute the directive.
Set RoleSET ROLE (role_name| All); If Role_name is specified, the role role_name becomes the only role in the current role. If you specify Role_name as all, the current role list is refreshed for use when the user is given a new role. If the user does not belong to the role represented by Role_name, an error is caused.
Show RolesSHOW ROLES; Lists all roles that currently exist. Only the role admin has this permission.
Grant RoleGRANT Role_name [, Role_name] ... To Principal_specification [, principal_specification] ... [with ADMIN OPTION]; Principal_specification:user USER | Role role assigns one or more roles to another role or user. If you specify with ADMIN OPTION, the user assigned to the role can assign the appropriate role to another user or role. If the grant statement above causes the role to loop, execution fails.
Revoke RoleREVOKE [ADMIN OPTION for] role_name [, Role_name] ... From Principal_specification [, principal_specification] ...; Principal_specification:user USER | Role role reclaims the specified role from the specified user or role.
Show Role GrantSHOW ROLE GRANT (user| ROLE) Principal_name; Principal_name is the user or role name. Lists the roles that are assigned to the specified user or role. All users can execute the directive.
Show PrincipalsSHOW Principals Role_name; Lists all roles and users that belong to the specified role. Only the role admin has this permission.
Manage Object Permissions
GrantGRANT Priv_type [, Priv_type] ... On Table_or_view_name to Principal_specification [, principal_specification] ... [with GRANT OPTION];
RevokeREVOKE [GRANT OPTION for] priv_type [, Priv_type] ... On Table_or_view_name from Principal_specification [, principal_specification] ...; Principal_specification:user USER | Role Role Priv_type:insert | SELECT | UPDATE | DELETE | All if a user has been assigned a "with GRANT OPTION" when given a table/view permission, the user can assign/recycle permissions from the resulting table/view to other users or roles. Note: When you reclaim permissions, Cascade is not supported in the current version, that is, only the permissions of the specified user or role can be reclaimed, and the permissions granted through those users or roles are not recycled.
Show GrantSHOW GRANT [Principal_name] On (all| ([TABLE] table_or_view_name) Principal_name is a user or role name. Attached: Hive Operation Permissions List
Codes
Y:privilege required.
Y + g:privilege "with GRANT OPTION" required.
| Action |
Select |
Insert |
Update |
Delete |
Ownership |
Admin |
URI Privilege (RWX Permission + Ownership) |
| CREATE TABLE |
|
|
|
|
Y (of database) |
|
Y (for Create external table–the location) |
| DROP TABLE |
|
|
|
|
Y |
|
|
| DESCRIBE TABLE |
Y |
|
|
|
|
|
|
| SHOW partitions |
Y |
|
|
|
|
|
|
| ALTER TABLE Location |
|
|
|
|
Y |
|
Y (for new location) |
| ALTER PARTITION Location |
|
|
|
|
Y |
|
Y (for new partition location) |
| ALTER TABLE ADD PARTITION |
|
Y |
|
|
|
|
Y (for partition location) |
| ALTER TABLE DROP PARTITION |
|
|
|
Y |
|
|
|
| ALTER TABLE (all of them except the ones above) |
|
|
|
|
Y |
|
|
| TRUNCATE TABLE |
|
|
|
|
Y |
|
|
| CREATE VIEW |
Y + G |
|
|
|
|
|
|
| ALTER VIEW PROPERTIES |
|
|
|
|
Y |
|
|
| ALTER VIEW RENAME |
|
|
|
|
Y |
|
|
| DROP VIEW PROPERTIES |
|
|
|
|
Y |
|
|
| DROP VIEW |
|
|
|
|
Y |
|
|
| ANALYZE TABLE |
Y |
Y |
|
|
|
|
|
| SHOW COLUMNS |
Y |
|
|
|
|
|
|
| SHOW TABLE STATUS |
Y |
|
|
|
|
|
|
| SHOW TABLE PROPERTIES |
Y |
|
|
|
|
|
|
| CREATE TABLE as SELECT |
Y (of input) |
|
|
|
Y (of database) |
|
|
| CREATE INDEX |
|
|
|
|
Y (of table) |
|
|
| DROP INDEX |
|
|
|
|
Y |
|
|
| ALTER INDEX REBUILD |
|
|
|
|
Y |
|
|
| ALTER INDEX PROPERTIES |
|
|
|
|
Y |
|
|
| SELECT |
Y |
|
|
|
|
|
|
| INSERT |
|
Y |
|
Y (for OVERWRITE) |
|
|
|
| UPDATE |
|
|
Y |
|
|
|
|
| DELETE |
|
|
|
Y |
|
|
|
| LOAD |
|
Y (Output) |
|
Y (Output) |
|
|
Y (Input location) |
| SHOW CREATE TABLE |
Y+g |
|
|
|
|
|
|
| CREATE FUNCTION |
|
|
|
|
|
Y |
|
| DROP FUNCTION |
|
|
|
|
|
Y |
|
| CREATE MACRO |
|
|
|
|
|
Y |
|
| DROP MACRO |
|
|
|
|
|
Y |
|
| Msck (Metastore check) |
|
|
|
|
|
Y |
|
| ALTER DATABASE |
|
|
|
|
|
Y |
|
| CREATE DATABASE |
|
|
|
|
|
|
Y (if custom location specified) |
| EXPLAIN |
Y |
|
|
|
|
|
|
| DROP DATABASE |
|
|
|
|
Y |
|
|
SQL Standard Based hive Authorization (SQL standards-based hive authorization)
