Page History

...

Privileges

● SELECT privilege -- – gives read access to an object.

● INSERT privilege -- – gives ability to add data to an object (table).

● UPDATE privilege -- – gives ability to run update queries on an object (table).

● DELETE privilege -- – gives ability to delete data in an object (table).

● ALL PRIVILEGES -- – gives all privileges (gets translated into all the above privileges).

...

There are two roles with special meaning -- *public* and *admin*– public and admin.
All users belong to the *public* role role. You use this role in your grant statement to grant a privilege to all users.

When a user runs a Hive query or command, the privileges granted to the user and her "current roles" are checked. The current roles can be seen using the "show current roles;" command. All of the user's roles except for the *admin* role role will be in the current roles by default, although you can use the "set role" command to set a specific role as the current role. See the command descriptions for details.

Users who do the work of a database administrator are expected to be added to the *admin* role role.
They have privileges for running additional commands such as "create role" and "drop role". They can also access objects that they haven’t been given explicit access to. However, a user who belongs to the admin role needs to run the "set role" command before getting the privileges of the admin role, as this role is not in current roles by default.

Role names are case insensitive. That is, “marketing” and “MarkEting” refer to same role.
User names are case sensitive. This is because, unlike role names, user names are not managed within Hive.

Role Management Commands

Create Role

Code Block

language	text

CREATE ROLE

...

 role_name

...

Creates a new role. Only the admin role has privilege for this.

The role names ALL, DEFAULT and NONE are reserved.

Drop Role

Code Block

language

...

text

DROP ROLE role_name

...

Drops the given role. Only the admin role has privilege for this.

Show Current Roles

Code Block

language	text

SHOW CURRENT ROLES;

Shows the list of current roles. All actions of the user are authorized by looking at the privileges of the user and all current roles of the user.

The default current roles has all roles for the user except for the admin role (even if the user belongs to the admin role as well).

Any user can run this command.SET ROLE (<

Set Role

Code Block

language	text

SET ROLE (role_name

...

|ALL);

If a < role_name > is specified, then that role becomes the only role in current roles.

Setting role_name to ALL refereshes refreshes the list of current roles (in case new roles were granted to the user) and sets them to the default list of roles.

If a role the user does not belong to is specified as the < role_name>, it will result in an error.

Show Roles

Code Block

language	text

SHOW ROLES;

List all currently existing roles.

Only admin the admin role has privilege for this.

Grant Role

Code Block

language	text

GRANT ROLE

...

 role_name

...

[,

...

 role_name

...

] ...

...


TO principal_specification [, principal_specification] ...

...

 
[ WITH ADMIN OPTION ]

...

;

principal_specification

...


  : USER user

...


  | ROLE role

Grant one or more roles to other roles or users.

If “WITH ADMIN OPTION” is specified, then the user gets privileges to grant the role to other users/roles.

If the grant statement ends up creating a cycling relationship between roles, the command will fail with an error.

Revoke Role

Code Block

language	text

REVOKE ROLE

...

 role_name

...

[,

...

 role_name

...

] ...

...


FROM principal_specification [, principal_specification] ...

...

 ;

principal_specification

...


  : USER user

...


  | ROLE role

Revokes the membership of the roles from the user/roles in the FROM clause.

Currently revoking just the ADMIN OPTION is not possible. You need to revoke the principal’s role grant, and then grant the role to principal without admin option.

Show Role Grant

Code Block

language	text

SHOW ROLE GRANT (USER|ROLE)

...

 principal_name

...

Where < principal_name > is the name of a user or role.

Lists all roles the given user or role has been granted.

Currently any user can run this command. But this is likely to change in future to allow users to see only their own role grants, and additional privileges would be needed to see role grants of other users.

Example of Show Role Grant

Code Block

language	text

0: jdbc:hive2://localhost:

...

10000> GRANT role1 TO USER user1;

...


No rows affected (0.058 seconds)

...



0: jdbc:hive2://localhost:

...

10000> SHOW ROLE GRANT USER user1

...

;
+---------+---------------+----------------+----------+

...


|  role   | grant_option  |   grant_time   | grantor  |
+---------+---------------+----------------+----------+

...


| public  | false         | 0              |          |
| role1   | false         | 1398284083000  | uadmin   |
+---------+---------------+----------------+----------+

Show Principals

Code Block

language	text

SHOW PRINCIPALS

...

 role_name

...

Lists all roles and users who belong to this role.

Only the admin role has privilege for this.0: jdbc

Example of Show Principals

Code Block

language	text

0: jdbc:hive2://localhost:

...

10000> SHOW PRINCIPALS role1;

...


+-----------------+-----------------+---------------+----------+---------------+----------------+

...


| principal_

...

name  | principal_

...

type  | grant_

...

option  | grantor  | grantor_type  |   grant_time   |
+-----------------+-----------------+---------------+----------+---------------+----------------+

...


| role2           | ROLE            | false         | uadmin   | USER          | 1398285926000  |
| role3           | ROLE            | true          | uadmin   | USER          | 1398285946000  |
| user1           | USER            | false         | uadmin   | USER          | 1398285977000  |
+-----------------+-----------------+---------------+----------+---------------+----------------+

Managing Object Privileges

GRANT

...

Object Privilege Commands

Grant

Code Block

language	text

GRANT
    priv_type [, priv_type ] ...

...


    ON table_or_view_name

...


    TO principal_specification [, principal_specification] ...

...

 ;
    [WITH GRANT OPTION]

...

Revoke

Code Block

language	text

REVOKE

REVOKE

...

priv_type [, priv_type ] ...

...


    ON table_or_view_name

...


    FROM principal_specification [, principal_specification] ...

...

 ;

principal_specification

...


  : USER user

...


  | ROLE role

...

If a user is granted a privilege WITH GRANT OPTION on a user or a role, then the user can also grant other users the privileges it has on those tables.

Note that in case of the REVOKE statement, the DROP-BEHAVIOR option of CASCADE is not currently supported (which is in SQL standard). As a result, the revoke statement will not drop any dependent privileges. For details on CASCADE behavior, you can check the postgres Postgres revoke documentation.

Show Grant

Code Block

language	text

SHOW GRANT [principal_name] ON (ALL| ([TABLE]

...

 table_or_view_name

...

Currently any user can run this command. But this is likely to change in the future to allow users to see only their own privileges, and additional privileges would be needed to see privileges of other users.

Examples

...

of Managing Object Privileges

Find out the privileges user ashutosh has on table hivejiratable.:

Code Block

language	text

0: jdbc:hive2://localhost:

...

10000> show grant user ashutosh on table hivejiratable;

...


+-----------+----------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+

...


| database  |     table      | partition  | column  | principal_name  | principal_type  | privilege  | grant_option  |   grant_time   | grantor  |
+-----------+----------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+

...


| default   | hivejiratable  |            |         | ashutosh        | USER            | DELETE     | false         | 1398303419000  | thejas   |
| default   | hivejiratable  |            |         | ashutosh        | USER            | SELECT     | false         | 1398303407000  | thejas   |
+-----------+----------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+

Find out the privileges user ashutosh has on all objects.:

Code Block

language	text

0: jdbc:hive2://localhost:

...

10000> show grant user ashutosh on

...

 all;                               
+-----------+-------------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+

...


| database  |       table       | partition  | column  | principal_name  | principal_type  | privilege  | grant_option  |   grant_time   | grantor  |
+-----------+-------------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+

...


| default   | hivecontributors  |            |         | ashutosh        | USER            | DELETE     | false         | 1398303576000  | thejas   |
| default   | hivecontributors  |            |         | ashutosh        | USER            | INSERT     | false         | 1398303576000  | thejas   |
| default   | hivecontributors  |            |         | ashutosh        | USER            | SELECT     | false         | 1398303576000  | thejas   |
| default   | hivejiratable     |            |         | ashutosh        | USER            | DELETE     | false         | 1398303419000  | thejas   |
| default   | hivejiratable     |            |         | ashutosh        | USER            | SELECT     | false         | 1398303407000  | thejas   |
+-----------+-------------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+

Find out the privileges all users have on table hivejiratable.:

Code Block

language	text

0: jdbc:hive2://localhost:

...

10000> show grant on table hivejiratable;

...


+-----------+----------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+

...


| database  |     table      | partition  | column  | principal_name  | principal_type  | privilege  | grant_option  |   grant_time   | grantor  |
+-----------+----------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+

...


| default   | hivejiratable  |            |         | ashutosh        | USER            | DELETE     | false         | 1398303419000  | thejas   |
| default   | hivejiratable  |            |         | ashutosh        | USER            | SELECT     | false         | 1398303407000  | thejas   |
| default   | hivejiratable  |            |         | navis           | USER            | INSERT     | false         | 1398303650000  | thejas   |
| default   | hivejiratable  |            |         | navis           | USER            | SELECT     | false         | 1398303650000  | thejas   |
| default   | hivejiratable  |            |         | public          | ROLE            | SELECT     | false         | 1398303481000  | thejas   |
| default   | hivejiratable  |            |         | thejas          | USER            | DELETE     | true          | 1398303380000  | thejas   |
| default   | hivejiratable  |            |         | thejas          | USER            | INSERT     | true          | 1398303380000  | thejas   |
| default   | hivejiratable  |            |         | thejas          | USER            | SELECT     | true          | 1398303380000  | thejas   |
| default   | hivejiratable  |            |         | thejas          | USER            | UPDATE     | true          | 1398303380000  | thejas   |
+-----------+----------------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+

Privileges Required for Hive Operations

Codes :

Y - Privilege : Privilege required.

Y + G = Privilege ': Privilege "WITH GRANT OPTION' " required.

Action	Select	Insert	Delete	Ownership	Admin	URI privilege Privilege (RWX permission Permission + Ownership)
CREATE TABLE			Y -Of Database	Y (of database)		Y (For for create external table - table – the location)
DROP TABLE				Y
DESCRIBE TABLE	Y
SHOW PARTITIONS	Y
ALTER TABLE LOCATION				Y		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 (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
QUERY (insert, select queries)	Y (input)	Y (output)	Y (output)
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

Configuration

Set the following to in hive-site.xml:

hive.server2.enable.doAs to false.
hive.users.in.admin.role to the list of comma-separated users who need to be added to admin role.

Start HiveServer2 with the following additional

...

command-line options

...

:

-hiveconf hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactory
-hiveconf hive.security.authorization.enabled=true
-hiveconf hive.security.authenticator.manager=org.apache.hadoop.hive.ql.security.SessionStateUserAuthenticator

.

-hiveconf hive.metastore.uris=' '

Known Issues

HIVE-6985 - sql – SQL std auth - privileges grants to public role not being honored

HIVE-6919 - – Hive sql std auth select query fails on partitioned tables

HIVE-6921 - – Index creation fails with sql SQL std auth turned on

HIVE-6957 - – SQL authorization does not work with HS2 binary mode and Kerberos auth

References

For information on the SQL standard for security see:●

ISO 9075 Part 1 Framework sections 4.2.6, 4.6.11

...

ISO 9075 Part 2 Foundation sections 4.35 and 12

...

Space shortcuts

Child pages

Versions Compared

Old Version 18

New Version 19

Key

Privileges

Role Management Commands

Create Role

Drop Role

Show Current Roles

Set Role

Show Roles

Grant Role

Revoke Role

Show Role Grant

Example of Show Role Grant

Show Principals

Example of Show Principals

Managing Object Privileges

Object Privilege Commands

Grant

Revoke

Show Grant

Examples

of Managing Object Privileges

Privileges Required for Hive Operations

Configuration

Known Issues

References