Allowing Only the Hive Administrator to Create Databases and Create Tables in the Default Database

Scenarios

By default, users who have the permission to create Hive tables can create databases and tables in the default database on the Hive client. You can also configure Hive to allow only the Hive administrator to create databases and tables in the default database. Other users can use the databases only after being authorized by the Hive administrator.

Notes and Constraints

• This section applies only to clusters with Kerberos authentication enabled.
• This section applies only to the scenario where Ranger authentication is not enabled for Hive.
• After this function is enabled, common users are not allowed to create a database or create a table in the default database. Exercise caution when enabling this function.
• The non-administrator users are not allowed to perform operations such as database creation, table script migration, and metadata re-creation.

Procedure

1. Log in to FusionInsight Manager, choose Cluster > Services > Hive, and click Configurations and then All Configurations.
2. Choose HiveServer(Role) > Customization, add a custom parameter to the hive-site.xml file, and set Name to hive.allow.only.admin.create and Value to true. The configuration ensures that only the Hive administrator can create databases and tables in the default database.
3. Click Save for versions earlier than MRS 3.6.0-LTS. Click Instances, select all Hive instances, choose More > Restart Instance, enter the user password, and click OK to restart all Hive instances.

   MRS 3.6.0-LTS or later: Click Save to save the settings. After the message "Operation succeeded" is displayed, click Finish. This parameter takes effect dynamically. You do not need to restart the Hive service.

4. Determine whether to enable this function on the Spark/Spark2x client.
   • If yes, go to 5.
   • If no, no further action is required.

5. Choose Cluster > Services > Spark, and click Configurations and then All Configurations. In the navigation pane on the left, choose SparkResource2x(Role) > Customization. In the hive-site.xml file, add a custom parameter with Name set to hive.allow.only.admin.create and Value set to true. Choose JDBCServer2x(Role) > Customization and add the custom parameter again.
6. Click Save to save the settings. Click Instances, select all Spark2x instances, click More and then Restart Instance, enter the user password, and click OK to restart all instances.
7. Download and install the Spark/Spark2x client again.
8. The following describes how to enable this function on the Hive client.
   1. Log in to the node where the client is installed as the client installation user.
   2. Go to the client installation directory, configure environment variables, and authenticate the user.
      1. Go to the client installation directory.

         cd /opt/hadoopclient

      2. Configure environment variables.

         source bigdata_env

      3. Authenticate the user. The user must have the permission to create Hive tables. Skip this step if Kerberos authentication is disabled for the cluster (in normal mode).

         kinit Component service user

         Example:

         kinit test

   3. Log in to the Hive client.

      beeline

   4. Create a database and create a table in the default database.

      Create a database.

      create database test;

      After the command is executed, the following error message is displayed, indicating that only users with administrator permissions can create databases:

      Figure 1 Error message reported when a database is created
      
      Create a table.

      create table test(name string);

      After the command is executed, the following error message is displayed, indicating that only users with administrator permissions can create tables in the default database:

      Figure 2 Error reported during table creation
      
   5. Bind the role with the Hive administrator permission to the user. For details, see Creating a Hive User and Binding the User to a Role.
   6. Create a database or create a table in the default database on the Hive client.