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 is not applicable when Kerberos authentication is disabled for the cluster (in normal mode) and Ranger authentication is disabled 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 (ACL Authentication)

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.

Procedure (Ranger Authentication)

• After this operation, common users cannot create databases or tables, or switch to the default or other users' databases using the use command. However, they can still use the databases and tables they previously created.
• After a common user is authorized on the Ranger page, the user can still create databases and tables, and switch between databases using the use command.
• This function is not supported when LakeFormation is interconnected.

1. Log in to MRS Manager as user admin, choose Cluster > Cluster Properties, and check and record the authentication mode of the cluster.
2. Choose Cluster > Services > Ranger and click the hyperlink on the right of Ranger Web UI.
   • If the authentication mode is security mode, and the user admin is defined as a User type in Ranger, access is limited to the Access Manager and Security Zone pages.

     Click the username in the upper right corner of the page, choose Log Out from the drop-down list, log in to the Ranger web UI as user rangeradmin, and perform 3.

   • If the authentication mode is normal mode, and the user admin is defined as an Admin type in Ranger, go to 3.

3. On the home page, click a component plug-in name (for example, Hive) in the HADOOP SQL area.
4. Click the modify icon in the row where the all - database policy is located. In the Allow Conditions area, delete the permission control rule whose Select Group is public and Permissions is create, and click Save.
   Figure 3 Deleting a database permission control rule
   

5. Click the modify icon in the row where the default database tables columns policy is located. In the Allow Conditions area, delete the permission control rule whose Select Group is public and Permissions is create, and click Save.
   Figure 4 Deleting permission control rules of the default Hive databases, tables, and columns
   

6. Wait for 1 minute. After the Hive component synchronizes the modified policy, the configured function will take effect and only the Hive administrator will be able to create databases and create tables in the default database.