CDH大数据平台 16Cloudera Manager Console之Sentry权限配置和测试(markdown新版)
一、Sentry权限控制和开启
启用 Sentry 权限控制
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sentry.html
https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hdfs_sentry_sync.html#xd_583c10bfdbd326ba–69adf108-1492ec0ce48–7f3a
https://docs.cloudera.com/documentation/enterprise/latest/topics/cm_sg_sentry_service.html#sentry_uris
https://docs.cloudera.com/documentation/enterprise/latest/topics/cdh_sg_hdfs_ext_acls.html#xd_583c10bfdbd326ba–6eed2fb8-14349d04bee–76a9
Setting Hive Warehouse Directory Permissions
Important: Enabling HDFS/Sentry synchronization obviates the need to explicitly set permissions on the Hive warehouse directory.
After synchronization is enabled,
all Hive databases and tables are owned by hive:hive and Sentry permissions on tables are automatically translated to HDFS ACLs on the underlying files.
The Hive warehouse directory (/user/hive/warehouse or any path you specify as hive.metastore.warehouse.dir
in your hive-site.xml) must be owned by the Hive user and group (hive:hive).
Using the default Hive warehouse directory - Permissions on the warehouse directory must be set as follows (see following Note for caveats):
771 on the directory itself (by default, /user/hive/warehouse)
771 on all subdirectories (for example, /user/hive/warehouse/mysubdir)
All files and subdirectories should be owned by hive:hive
hdfs dfs -chmod 771 /user/hive/warehouse;hdfs dfs -chown 771 /user/hive/warehouse/etl.;For Sentry/HDFS sync to work as expected, add the new warehouse URL to the list of Sentry Synchronization Path Prefixes.
The mapping of Sentry privileges to HDFS ACLs is as follows:
SELECT privilege -> Read access on the file.
INSERT privilege -> Write access on the file.
ALL privilege -> Read and Write access on the file
Enabling HDFS ACLs Using Cloudera Manager
1. Go to the Cloudera Manager Admin Console and navigate to the HDFS service.
2. Click the Configuration tab.
3. Select Scope > Service_name (Service-Wide)
4. Select Category > Security
5. Locate the Enable Access Control Lists property and select its checkbox to enable HDFS ACLs.
6. Enter a Reason for change, and then click Save Changes to commit the changes.
即： dfs.namenode.acls.enabled=trueCommands
To set and get file access control lists (ACLs), use the file system shell commands, setfacl and getfacl.
hdfs dfs -getfacl [-R] hdfs dfs -getfacl -R /userhdfs dfs -setfacl -m user:ben:rw- /user/hdfs/filehdfs dfs -setfacl -x user:alice /user/hdfs/filehdfs dfs -setfacl --set user::rw-,user:hadoop:rw-,group::r--,other::r-- /user/hdfs/file# 1. Make the files and sub-directories created within the content directory readable by team "hadoopdev":hdfs dfs -setfacl -m group:hadoopdev:r-x /project# 2. Set the default ACL setting for the parent directory:hdfs dfs -setfacl -m default:group:hadoopdev:r-x /project# 3. Create a sub-directory for the content you wish to share:hdfs dfs -mkdir /project/dev# 4. Inspect the new sub-directory ACLs to verify that HDFS has applied the new default values:hdfs dfs -getfacl -R /projectNote: At the time it is created, the default ACL is copied from the parent directory to the child directory.
Subsequent changes to the parent directory default ACL do not change the ACLs of the existing child directories.
Enabling the HDFS-Sentry Plugin
1. Go to the HDFS service.
2. Click the Configuration tab.
3. Select Scope > HDFS (Service-Wide).
4. Type Check HDFS Permissions in the Search box.
5. Select Check HDFS Permissions.
6. Select Enable Sentry Synchronization.
7. Locate the Sentry Synchronization Path Prefixes property or search for it by typing its name in the Search box.
8. Edit the Sentry Synchronization Path Prefixes property to list HDFS path prefixes where Sentry permissions should be enforced. Multiple HDFS path prefixes can be specified. By default, this property points to /user/hive/warehouse and must always be non-empty. If you are using a non-default location for the Hive warehouse, make sure you add it to the list of path prefixes. HDFS privilege synchronization will not occur for tables and databases located outside the HDFS regions listed here.
9. Click Save Changes.
10. Restart the cluster. Note that it may take an additional two minutes after cluster restart for privilege synchronization to take effect.
即: 勾选"启用 Sentry 同步"sentry.hdfs.integration.path.prefixes=/user/hive/warehousesentry.authorization-provider.hdfs-group=hive如果提示 "启用 HDFS/Sentry 同步时，必须正好有一个依赖 Sentry 的 Hive 服务，但是存在 0 个依赖性 Hive 服务"表示 hive 组件需要启用 "Sentry" 权限控制，即 在 hive 组件，选 "Sentry 服务"=Sentry ( 默认为 none )如果提示 "已为 Hive Server2 角色“HiveServer2 (cdh-master-v01)”启用 Hive Impersonation。应禁用 Hive Impersonation 以启用使用 Sentry 的 Hive 授权。"则配置 hive.server2.enable.doAs=false并且 勾选 "Hive Metastore Server Group 1"# 其他配置项# hiveNote:
1. Set hive.warehouse.subdir.inherit.perms to true in hive-site.xml to have permissions set on the warehouse directory applied to all subdirectories.
即 hive 中: hive.warehouse.subdir.inherit.perms=true # 默认已经为 true2. If a user has access to any object in the warehouse, that user will be able to execute use default. This ensures that use default commands issued by legacy applications work when Sentry is enabled.
3. Modifying permissions on the Hive warehouse directory (as detailed above) override the recommendations in the Hive section of the CDH 5 Installation Guide.
Disable impersonation for HiveServer2 in the Cloudera Manager Admin Console. HiveServer2 impersonation lets users execute queries and access HDFS files as the connected user rather than as the super user. Access policies are applied at the file level using the HDFS permissions specified in ACLs (access control lists). Enabling HiveServer2 impersonation bypasses Sentry from the end-to-end authorization process. Specifically, although Sentry enforces access control policies on tables and views within the Hive warehouse, it does not control access to the HDFS files that underlie the tables. This means that users without Sentry permissions to tables in the warehouse may nonetheless be able to bypass Sentry authorization checks and execute jobs and queries against tables in the warehouse as long as they have permissions on the HDFS files supporting the table. Use the following instructions to disable impersonation:
1. Go to the Hive service.
2. Click the Configuration tab.
3. Select Scope > HiveServer2.
4. Select Category > Main.
5. Uncheck the HiveServer2 Enable Impersonation checkbox.
6. Enter a Reason for change, and then click Save Changes to commit the changes
# 即 hive 组件中( 上面已经操作 )hive.server2.enable.doAs=falseIf you are using MapReduce, enable the Hive user to submit MapReduce jobs. # 个人认为MR引擎已经过时，先不修改
1. Open the Cloudera Manager Admin Console and go to the MapReduce service. # 即 “YARN (MR2 Included)”
2. Click the Configuration tab.
3. Select Scope > TaskTracker.
4. Select Category > Security.
5. Set the Minimum User ID for Job Submission property to zero (the default is 1000).
6. Enter a Reason for change, and then click Save Changes to commit the changes.
7. Repeat steps 1-6 for every TaskTracker role group for the MapReduce service that is associated with Hive.
8. Restart the MapReduce service.
# 即min.user.id=0If you are using YARN, enable the Hive user to submit YARN jobs.
1. Open the Cloudera Manager Admin Console and go to the YARN service.
2. Click the Configuration tab.
3. Select Scope > NodeManager.
4. Select Category > Security.
5. Ensure the Allowed System Users property includes the hive user. If not, add hive.
6. Enter a Reason for change, and then click Save Changes to commit the changes.
7. Repeat steps 1-6 for every NodeManager role group for the YARN service that is associated with Hive.
8. Restart the YARN service.
# 即 allowed.system.users 参数中应该包含 "hive" 用户 # 默认已包含Block the external applications from accessing the Hive metastore:
1. In the Cloudera Manager Admin Console, select the Hive service.
2. On the Hive service page, click the Configuration tab.
3. In the search well on the right half of the Configuration page, search for Hive Metastore Access Control and Proxy User Groups Override to locate the hadoop.proxyuser.hive.groups parameter and click the plus sign.
4. Enter hive into the text box and click the plus sign again.
5. Enter hue into the text box.
6. Enter sentry into the text box.
7. Click Save Changes.
Setting this parameter blocks access to the Hive metastore for non-service users. This effectively disables Hive CLI, Spark, and Sqoop applications from interacting with the Hive service. These application will still run, but after setting this parameter as described here, they will no longer be able to access the Hive metastore and all Hive queries will fail. Users running these tools must be part of the hive, hue, or sentry groups to access the Hive service. To allow greater access, additional user groups must be added to the proxy list.
# 即 "Hive Metastore 访问控制和代理用户组覆盖" 或者参数名为 hadoop.proxyuser.hive.groups，该参数中包含 hive,hue,sentryhadoop.proxyuser.hive.groups=hive,hue,sentryImportant: Ensure you have unchecked the Enable Sentry Authorization using Policy Files configuration property for both Hive and Impala under the Policy File Based Sentry category before you proceed.
# 即 不要勾选 "在数据库策略文件中允许 URI" ( 默认就没有勾选 )sentry.allow.uri.db.policyfile=false-------------------------------------------------------------------
-------------------------------------------------------------------
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_sentry_service_config.html#concept_z5b_42s_p4__section_lvc_4g4_rp
一. Enabling the Sentry Service for Hive
1. Go to the Hive service.
2. Click the Configuration tab.
3. Select Scope > Hive (Service-Wide).
4. Select Category > Main.
5. Locate the Sentry Service property and select Sentry.
6. Locate the Enable Stored Notifications in Database property and select it. ( 即：“Hive 仓库子目录继承权限” )
7. Enter a Reason for change, and then click Save Changes to commit the changes.
8. Restart the Hive service.
Enabling Sentry on Hive service places several HiveServer2 properties on a restricted list properties that cannot be modified at runtime by clients. See HiveServer2 Restricted Properties.
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_sentry_service_config.html#concept_i13_gbz_nv
“HiveServer2 Restricted Properties”
Enabling Sentry on Hive service places several HiveServer2 properties on a restricted list properties that cannot be modified at runtime by clients. This list is denoted by the hive.conf.restricted.list property and these properties are only configurable on the server side. The list includes:
hive.enable.spark.execution.enginehive.semantic.analyzer.hookhive.exec.pre.hookshive.exec.scratchdirhive.exec.local.scratchdirhive.metastore.uris,javax.jdo.option.ConnectionURLhadoop.bin.pathhive.session.idhive.aux.jars.pathhive.stats.dbconnectionstringhive.scratch.dir.permissionhive.security.command.whitelisthive.security.authorization.task.factoryhive.entity.capture.transformhive.access.conf.urlhive.sentry.conf.urlhive.access.subject.namehive.sentry.subject.namehive.sentry.active.role.set-------------------------------------------------------------------
-------------------------------------------------------------------
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_sentry_service_config.html#concept_z5b_42s_p4__section_lvc_4g4_rp
二. Enabling the Sentry Service for Impala
1. Enable the Sentry service for Hive (as instructed above).
2. Go to the Impala service.
3. Click the Configuration tab.
4. Select Scope > Impala (Service-Wide).
5. Select Category > Main.
6. Locate the Sentry Service property and select Sentry.
7. Enter a Reason for change, and then click Save Changes to commit the changes.
8. Restart Impala.
# 即 "Sentry 服务" 选 Sentry-------------------------------------------------------------------
-------------------------------------------------------------------
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_sentry_service_config.html#concept_z5b_42s_p4__section_lvc_4g4_rp
三. Enabling the Sentry Service for Solr ( 由于没有安装Solr组件，先忽略 )
Enable the Sentry service as follows:
1. Go to the Solr service.
2. Click the Configuration tab.
3. Select Scope > Solr (Service-Wide).
4. Select Category > Main.
5. Locate the Sentry Service property and select Sentry.
6. Enter a Reason for change, and then click Save Changes to commit the changes.
7. Restart Solr.
After enabling Sentry for Solr, you may want to configure authorization as described in Configuring Sentry Authorization for Cloudera Search.
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/search_sentry.html#search_sentry
-------------------------------------------------------------------
-------------------------------------------------------------------
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_sentry_service_config.html#concept_z5b_42s_p4__section_lvc_4g4_rp
四. Enabling the Sentry Service for Hue
Hue uses a Security app to make it easier to interact with Sentry. When you set up Hue to manage Sentry permissions, make sure that users and groups are set up correctly. Every Hue user connecting to Sentry must have an equivalent OS-level user account on all hosts so that Sentry can authenticate Hue users. Each OS-level user should also be part of an OS-level group with the same name as the corresponding user’s group in Hue.
For more information on using the Security app, see the related blog post.
参考：https://gethue.com/apache-sentry-made-easy-with-the-new-hue-security-app/
Enable the Sentry service as follows:
1. Enable the Sentry service for Hive and Impala (as instructed above).
2. Go to the Hue service.
3. Click the Configuration tab.
4. Select Scope > Hue (Service-Wide).
5. Select Category > Main.
6. Locate the Sentry Service property and select Sentry.
7. Enter a Reason for change, and then click Save Changes to commit the changes.
8. Restart Hue.
# 即 "Sentry 服务" 选 Sentry-------------------------------------------------------------------
-------------------------------------------------------------------
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_sentry_service_config.html#concept_z5b_42s_p4__section_lvc_4g4_rp
五. Add the Hive, Spark, and Hue Groups to Sentry’s Admin Groups
Add the user groups that need administrative privileges on the Sentry Server.
1. Go to the Sentry service.
2. Click the Configuration tab.
3. Select Scope > Sentry (Service-Wide).
4. Select Category > Main.
5. Locate the Admin Groups property and add the hive, spark, and hue groups to the list. If an end user is in one of these admin groups, that user has administrative privileges on the Sentry Server.
6. Enter a Reason for change, and then click Save Changes to commit the changes.
# hive, hue 默认已经有了，缺 spark# 即：sentry.service.admin.group=hive,hue,spark, ...-------------------------------------------------------------------
-------------------------------------------------------------------
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_sentry_service_config.html#concept_z5b_42s_p4__section_lvc_4g4_rp
六. Enabling Sentry for a Solr Collection ( 由于没有安装Solr组件，先忽略 )
-------------------------------------------------------------------
-------------------------------------------------------------------
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_sentry_service_config.html#concept_z5b_42s_p4__section_lvc_4g4_rp
七. Sample Sentry Configuration Files
The following is an example of a sentry-site.xml file.
Important: If you are using Cloudera Manager 4.6 (or lower), make sure you do not store sentry-site.xml in /etc/hive/conf ; that directory is regenerated whenever the Hive client configurations are redeployed. Instead, use a directory such as /etc/sentry to store the sentry file.
If you are using Cloudera Manager 4.7 (or higher), Cloudera Manager will create and deploy sentry-site.xml for you.See Managing the Sentry Service for more details on configuring Sentry with Cloudera Manager.
-------------------------------------------------------------------
-------------------------------------------------------------------
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_sentry_service_config.html#concept_z5b_42s_p4__section_lvc_4g4_rp
七. HiveServer2 Restricted Properties
Enabling Sentry on Hive service places several HiveServer2 properties on a restricted list properties that cannot be modified at runtime by clients. This list is denoted by the hive.conf.restricted.list property and these properties are only configurable on the server side. The list includes:
# 上面已经列出-------------------------------------------------------------------
-------------------------------------------------------------------
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_sentry_service_config.html#concept_z5b_42s_p4__section_lvc_4g4_rp
八. Configuring Pig and HCatalog for the Sentry Service
Once you have the Sentry service up and running, and Hive has been configured to use the Sentry service, there are some configuration changes you must make to your cluster to allow Pig, MapReduce (using HCatLoader, HCatStorer) and WebHCat queries to access Sentry-secured data stored in Hive.
Since the Hive warehouse directory is owned by hive:hive, with its permissions set to 771, with these settings, other user requests such as commands coming through Pig jobs, WebHCat queries, and MapReduce jobs, may fail. To give these users access, perform the following configuration changes:
1. Use HDFS ACLs to define permissions on a specific directory or file of HDFS. This directory/file is generally mapped to a database, table, partition, or a data file.
2. Users running these jobs should have the required permissions in Sentry to add new metadata or read metadata from the Hive Metastore Server. For instructions on how to set up the required permissions, see Hive SQL Syntax for Use with Sentry. You can use HiveServer2’s command line interface, Beeline to update the Sentry database with the user privileges.
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#concept_c2q_4qx_p4
Examples:
1. A user who is using Pig HCatLoader will require read permissions on a specific table or partition. In such a case, you can GRANT read access to the user in Sentry and set the ACL to read and run, on the file being accessed.
2. A user who is using Pig HCatStorer will require ALL permissions on a specific table. In this case, you GRANT ALL access to the user in Sentry and set the ACL to write and run on the table being used.
-------------------------------------------------------------------
-------------------------------------------------------------------
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_sentry_service_config.html#concept_z5b_42s_p4__section_lvc_4g4_rp
九. Securing the Hive Metastore
It’s important that the Hive metastore be secured. If you want to override the Kerberos prerequisite for the Hive metastore, set the sentry.hive.testing.mode property to true to allow Sentry to work with weaker authentication mechanisms. Add the following property to the HiveServer2 and Hive metastore’s sentry-site.xml:
# 没太明白：具体怎么操作 ？ # 参考: https://community.cloudera.com/t5/Support-Questions/Where-to-set-sentry-hive-testing-mode-property/m-p/60437sentry.hive.testing.mode=true# 即，在 "sentry-site.xml 的 Sentry 服务高级配置代码段（安全阀）" 里面添加 sentry.hive.testing.mode=trueImpala does not require this flag to be set.
Warning: Cloudera strongly recommends against enabling this property in production. Use Sentry’s testing mode only in test environments.
You can turn on Hive metastore security using the instructions in Cloudera Security.
-------------------------------------------------------------------
-------------------------------------------------------------------
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_sentry_service_config.html#concept_z5b_42s_p4__section_lvc_4g4_rp
十. Using User-Defined Functions with HiveServer2
The ADD JAR command does not work with HiveServer2 and the Beeline client when Beeline runs on a different host. As an alternative to ADD JAR, Hive’s auxiliary paths functionality should be used. There are some differences in the procedures for creating permanent functions and temporary functions when Sentry is enabled. For detailed instructions, see:
1. Registering a UDF in Hive
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/cm_mc_hive_udf.html#concept_t1x_srm_2r
OR
2. Creating Temporary Functions
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/cm_mc_hive_udf.html#concept_ry3_vrm_2r
-------------------------------------------------------------------
-------------------------------------------------------------------
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#concept_c2q_4qx_p4
十一. Hive SQL Syntax for Use with Sentry
Sentry permissions can be configured through GRANT and REVOKE statements issued either interactively or programmatically through the HiveServer2 SQL command line interface, Beeline (documentation available here). The syntax described below is very similar to the GRANT and REVOKE commands that are available in well-established relational database systems.
In HUE, the Sentry Admin that creates roles and grants privileges must belong to a group that has ALL privileges on the server. For example, you can create a role for the group that contains the hive or impala user, and grant ALL ON SERVER … WITH GRANT OPTION to that role:
CREATE ROLE ;GRANT ALL ON SERVER TO ROLE WITH GRANT OPTION;GRANT ROLE TO GROUP ;Important:
1. When Sentry is enabled, you must use Beeline to execute Hive queries. Hive CLI is not supported with Sentry and must be disabled. See Disabling Hive CLI for information on how to disable the Hive CLI.
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_sentry_overview.html#concept_q3l_5sw_jr__hiveclidisable
*1). Disabling Hive CLI
To execute Hive queries, you must use Beeline. Hive CLI is not supported with Sentry and therefore its access to the Hive Metastore must be disabled. This is especially necessary if the metastore has sensitive metadata. To do this, give the hive user read/execute permission to the Hive CLI file and then remove global execute permission, at least, to the Hive CLI file in the HIVE_HOME path. Assuming HIVE_HOME is /opt/cloudera/parcels/CDH/bin/hive, disable the HIVE CLI as follows:
# 以 root 用户，在 "客户端" 服务器执行$ setfacl -m u:hive:rx /opt/cloudera/parcels/CDH/bin/hive $ chmod 754 /opt/cloudera/parcels/CDH/bin/hive# 执行前，ls 显示类似如下：[hadoop@cdh-client-v01 ~]$ ls -l /opt/cloudera/parcels/CDH/bin/hive-rwxr-xr-x 1 root root 768 Nov 9 2019 /opt/cloudera/parcels/CDH/bin/hive# 执行后，ls 显示类似如下：[root@cdh-client-v01 ~]# ls -l /opt/cloudera/parcels/CDH/bin/hive-rwxr-xr--+ 1 root root 768 11月 9 2019 /opt/cloudera/parcels/CDH/bin/hiveHive fails to start if the hive user does not have access to the Hive CLI file.
*2). Using Hue to Manage Sentry Permissions
Hue supports a Security app to manage Sentry authorization. This allows users to explore and change table permissions. Here is a video blog that demonstrates its functionality.
参考：https://gethue.com/apache-sentry-made-easy-with-the-new-hue-security-app/
2. There are some differences in syntax between Hive and the corresponding Impala SQL statements. For Impala syntax, see SQL Statements.
3. No privilege is required to drop a function. Any user can drop a function.
Sentry supports column-level authorization with the SELECT privilege. Information about column-level authorization is in the Column-Level Authorization section of this page.
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#column_level_authorization
Column-Level Authorization
Sentry provides column-level authorization with the SELECT privilege. You can grant the SELECT privilege to a role for a subset of columns in a table. If a new column is added to the table, the role will not have the SELECT privilege on that column until it is explicitly granted.
You can grant and revoke the SELECT privilege on a set of columns with the following commands, respectively:
GRANT SELECT () ON TABLE TO ROLE ;REVOKE SELECT () ON TABLE FROM ROLE ;
Users with column-level authorization can execute the following commands on the columns that they have access to. Note that the commands will only return data and metadata for the columns that the user’s role has been granted access to.
SELECT FROM TABLE ;SELECT COUNT FROM TABLE ;SELECT FROM TABLE WHERE GROUP BY ;SHOW COLUMNS (FROM|IN) [(FROM|IN) ];
As a rule, a user with select access to columns in a table cannot perform table-level operations, however, if a user has SELECT access to all the columns in a table, that user can also execute the following command:
SELECT * FROM TABLE ;
Considerations for Column-Level Authorization
When you implement column-level authorization, consider the following:
1. When a user has column-level permissions, it may be confusing that they cannot execute a select * from statement even though they have select privileges on some of the columns in the table. Instead, the user must explicitely define the columns that they want to query.
2. Using views instead of column-level authorization requires additional administration, such as creating the view and administering the Sentry grants. In addition, a new view may be needed for a new role, and third-party applications must use a different view based on the role of the user.
3. With HDFS sync enabled, even if a user has been granted access to all columns of a table, the user will not have access ot the corresponding HDFS data files. This is because Sentry does not consider SELECT on all columns equivalent to explicitely being granted SELECT on the table.
4. Column-level access control for access from Spark SQL is not supported by the HDFS-Sentry plug-in
-------------------------------------------------------------------
-------------------------------------------------------------------
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#concept_c2q_4qx_p4
十二. ALTER DATABASE Statement
Use the ALTER TABLE statement to set or transfer ownership of an HMS database in Sentry. Object ownership must be enabled in Sentry to assign ownership to an object. For information on how to enable object ownership and the privileges an object owner has on the object, see Object Ownership.
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sentry_object_ownership.html
You can grant the OWNER privilege on a database to a role or a user with the following commands, respectively:
ALTER DATABASE SET OWNER ROLE ALTER DATABASE SET OWNER USER
-------------------------------------------------------------------
-------------------------------------------------------------------
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#concept_c2q_4qx_p4
十三. ALTER TABLE Statement
Use the ALTER TABLE statement to set or transfer ownership of an HMS database in Sentry. Object ownership must be enabled in Sentry to assign ownership to an object. For information on how to enable object ownership and the privileges an object owner has on the object, see Object Ownership.
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sentry_object_ownership.html
With a new CDH installation, object ownership is disabled by default in CDH 5.16. In CDH 6.1, object ownership with the WITH GRANT OPTION is enabled by default. For information about upgrading, see Upgrade Note below.
An object can only have one owner at a time. The owner can be an individual user or a role. The OWNER privilege cannot be revoked by a REVOKE command. However, ownership can transfer to another user or role. When ownership is transferred, the original owner loses ownership of the object. For information about transferring ownership of an object, see Assigning and Transferring Ownership.
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sentry_object_ownership.html#transferring_ownership
Assigning and Transferring Ownership
If an object owner has the ALL privileges with GRANT option, the user can transfer object ownership to another user or role. In addition, Sentry users, such as admin users, that have ALL WITH GRANT on the server can transfer or assign ownership of any object. When ownership is transferred in Sentry, the object owner in HMS is also changed.
An object owner that does not have the ALL privileges with GRANT option cannot transfer ownership of the object.
Note: When you enable object ownership, you cannot immediately assign ownership to the same user that owned the object before ownership was enabled. For example, if you want the hive user to have the OWNER privilege, you must enable object ownership, assign ownership to a temporary user (for example, an admin user) and then re-assign ownership to the hive user.
You can grant the OWNER privilege on a table to a role or a user with the following commands, respectively:
ALTER TABLE SET OWNER ROLE ;ALTER TABLE SET OWNER USER ;
In Hive, the ALTER TABLE statement also sets the owner of a view. Use the following commands to grant the OWNER privilege on a view:
ALTER TABLE SET OWNER ROLE ALTER TABLE SET OWNER USER
In Impala, use one of the following commands to grant the OWNER privilege to a view:
ALTER VIEW SET OWNER ROLE ;ALTER VIEW SET OWNER USER ;
Revoking Ownership
Once the OWNER privilege is granted, it cannot be revoked while object ownership is still enabled. However, ownership can be transferred with the ALTER DATABASE or ALTER TABLE operation.
If you disable object ownership, you can use the ALTER DATABASE or ALTER TABLE operation to remove ownership.
For example, object ownership is enabled and you have a user1 that has the OWNER privilege on table1. You can disable object ownership (set OWNER Privileges for Sentry Policy Database Objects to NONE) and then run the following command:
ALTER TABLE table1 SET OWNER USER user2;
In this case, neither user1 nor user2 will have the OWNER privilege on the table because object ownership is disabled.
-------------------------------------------------------------------
-------------------------------------------------------------------
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#create_role_statement
十四. CREATE ROLE Statement
The CREATE ROLE statement creates a role to which privileges can be granted. Privileges can be granted to roles, which can then be assigned to users. A user that has been assigned a role will only be able to exercise the privileges of that role.
Only users that have administrative privileges can create or drop roles. By default, the hive, impala and hue users have admin privileges in Sentry.
可以通过 Sentry 组件的 sentry.service.admin.group 参数 进行修改
CREATE ROLE ;
Note that role names are case-insensitive.
-------------------------------------------------------------------
-------------------------------------------------------------------
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#drop_role_statement
十五. DROP ROLE Statement
The DROP ROLE statement can be used to remove a role from the database. Once dropped, the role will be revoked for all users to whom it was previously assigned. Queries that are already executing will not be affected. However, since Hive checks user privileges before executing each query, active user sessions in which the role has already been enabled will be affected.
DROP ROLE ;
-------------------------------------------------------------------
-------------------------------------------------------------------
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#grant_role_statement
十六. GRANT ROLE Statement
The GRANT ROLE statement can be used to grant roles to groups. Only Sentry admin users can grant roles to a group.
GRANT ROLE [, ] TO GROUP [,GROUP ]
Sentry only allows you to grant roles to groups that have alphanumeric characters and underscores (_) in the group name. If the group name contains a non-alphanumeric character that is not an underscore, you can put the group name in backticks (`) to execute the command. For example, Sentry will return an error for the following command:
# return an errorGRANT ROLE test TO GROUP test-group;
To grant a role to this group, put the group name in backticks:
# OKGRANT ROLE test TO GROUP `test-group`;
-------------------------------------------------------------------
-------------------------------------------------------------------
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#revoke_role_statement
十七. REVOKE ROLE Statement
The REVOKE ROLE statement can be used to revoke roles from groups. Only Sentry admin users can revoke the role from a group.
REVOKE ROLE [, ] FROM GROUP [,GROUP ]
-------------------------------------------------------------------
-------------------------------------------------------------------
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#grant_privilege_statement
十八. GRANT Statement
Use the GRANT statement to grant privileges on an object to a role. The statement uses the following syntax:
GRANT [, ] ON TO ROLE [,ROLE ]
For example, you might enter the following statement:
GRANT SELECT ON TABLE feathered_dinosaurs TO ROLE archaeopteryx
The following table describes the privileges you can grant and the objects that they apply to:
Privilege Types
--------------------------------------------------------------
Privilege # Objects
--------------------------------------------------------------
All # Server, database, table, URI
CREATE # Server, database
INSERT # Server, database, table
REFRESH (Impala only) # Server, database, table
SELECT # Server, database, table, view, column
--------------------------------------------------------------
You can also grant the SELECT privilege on a specific column of a table with the following statement:
GRANT SELECT ON TABLE TO ROLE ;
-------------------------------------------------------------------
-------------------------------------------------------------------
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#grant_privilege_on_uri
十七. GRANT ON URIs (HDFS and S3A)
You can only grant the ALL privilege on a URI. See Granting Privileges on URIs for more information about using URIs with Sentry.
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/cm_sg_sentry_service.html#sentry_uris
If the GRANT for Sentry URI does not specify the complete scheme, or the URI mentioned in Hive DDL statements does not have a scheme, Sentry automatically completes the URI by applying the default scheme based on the HDFS configuration provided in the fs.defaultFS property. Using the same HDFS configuration, Sentry can also auto-complete URIs in case the URI is missing a scheme and an authority component.
# 即hdfs dfs -ls /# 等同于hdfs dfs -ls hdfs://odtscluster/
When a user attempts to access a URI, Sentry will check to see if the user has the required privileges. During the authorization check, if the URI is incomplete, Sentry will complete the URI using the default HDFS scheme. Note that Sentry does not check URI schemes for completion when they are being used to grant privileges. This is because users can GRANT privileges on URIs that do not have a complete scheme or do not already exist on the filesystem.
For example, in CDH 5.8 and later, the following CREATE EXTERNAL TABLE statement works even though the statement does not include the URI scheme.
GRANT ALL ON URI 'hdfs://namenode:XXX/path/to/table' TO ROLE ;CREATE EXTERNAL TABLE foo LOCATION 'namenode:XXX/path/to/table' TO ROLE ;
Similarly, the following CREATE EXTERNAL TABLE statement works even though it is missing scheme and authority components.
GRANT ALL ON URI 'hdfs://namenode:XXX/path/to/table' TO ROLE ;CREATE EXTERNAL TABLE foo LOCATION '/path/to/table'
Since Sentry supports both HDFS and Amazon S3, in CDH 5.8 and later, Cloudera recommends that you specify the fully qualified URI in GRANT statements to avoid confusion. If the underlying storage is a mix of S3 and HDFS, the risk of granting the wrong privileges increases. The following are examples of fully qualified URIs:
HDFS: hdfs://host:port/path/to/hdfs/tableS3: s3a://host:port/path/to/s3/table
Granting Privileges on URIs
Grant permissions on URIs to create functions, alter the location of a table, explicitly set the location of a table, and to import and export from a table with that location. A user must have ALL permissions on the URI to perform the following actions:
1. Export from a table to a location
2. Import from a table to a location
3. Insert into a table at an S3 location
4. Alter a table to change the location of the table
5. Create a function from a jar file (requires other administrative settings as well)
For example, if you create the following table with an S3 location:
CREATE EXTERNAL TABLE mytesttable (firstname STRING, lastname STRING, address STRING, city STRING, state STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION 's3a://mybucket/';# 我的测试GRANT ALL ON URI 'hdfs://wfcluster/user/etl/hive/etl.db/etl_test' TO ROLE etl;create external table etl_test( id int, cname varchar(30))ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/user/etl/hive/etl.db/etl_test';
To insert data into the table, you must have ALL on the URI ‘s3a://mybucket’ to execute the following command:
INSERT INTO TABLE mytesttable VALUES ('bilbo', 'baggends', 'bagend', 'hobbiton', 'shire');
The URI represents the HDFS path you specify as part of a GRANT statement. The URI in the statement can look like a UNIX path, but can also be prefixed with hdfs:// to clarify that it is a URI.
The following command is an example of a GRANT privilege on a URI:
GRANT ALL ON URI '/user/hive/warehouse/customers' TO ROLE t_rex
The only privilege you can grant on a URI is ALL. For details about the GRANT ALL ON URI statement, see GRANT ON URIs (HDFS and S3A).
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#grant_privilege_on_uri
When you grant ALL on a URI, those permissions extend into the subdirectories in that path. For example, if a role has ALL on the following URI:
hdfs://host:port/directory_A/directory_B
That role will also have ALL on these directories:
*1). hdfs://host:port/directory_A/directory_B/directory_C
*2). hdfs://host:port/directory_A/directory_B/directory_C/directory_D
*3). hdfs://host:port/directory_A/directory_B/directory_E
URIs are not applied as HDFS ACLs. The How-To article and video, How to Verify that HDFS ACLs are Synching with Sentry, gives an example of a GRANT statement on a URI that does not affect an HDFS ACL change. For information on what does trigger an HDFS ACL change, see Prompting HDFS ACL Changes.
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sentry_howto_verify_hdfs_sync.html
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hdfs_sentry_sync.html#acl_changes
-------------------------------------------------------------------
-------------------------------------------------------------------
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#revoke_privilege_statement
十八. REVOKE Statement
You can use the REVOKE statement to revoke previously-granted privileges that a role has on an object.
REVOKE [, ] ON FROM ROLE [,ROLE ]
For example, you can revoke previously-granted SELECT privileges on specific columns of a table with the following statement:
REVOKE SELECT ON TABLE FROM ROLE ;
-------------------------------------------------------------------
-------------------------------------------------------------------
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#grant_privilege_with_grant
十九. GRANT … WITH GRANT OPTION
You can add the WITH GRANT OPTION clause to a GRANT statement to allow the role to grant and revoke the privilege to and from other roles.
The WITH GRANT OPTION clause uses the following syntax:
GRANT ON TO ROLE WITH GRANT OPTION
When you use the WITH GRANT OPTION clause, the ability to grant and revoke privileges applies to the object container and all its children. For example, if you give GRANT privileges to a role at the database level, that role can grant and revoke privileges to and from the database and all the tables in the database.
Only a role with the GRANT option on a privilege can revoke that privilege from other roles. And you cannot revoke the GRANT privilege from a role without also revoking the privilege. To revoke the GRANT privilege, revoke the privilege that it applies to and then grant that privilege again without the WITH GRANT OPTION clause.
You can use the WITH GRANT OPTION clause with the following privileges:
1. ALL
2. CREATE
3. INSERT
4. REFRESH (Impala only)
5. SELECT
WITH GRANT OPTION Example
For example, if you grant a role the SELECT privilege with the following statement:
GRANT SELECT ON DATABASE coffee_database TO ROLE coffee_bean WITH GRANT OPTION
The coffee_bean role can grant SELECT privileges to other roles on the coffee_database and all the tables within that database.
When you revoke a privilege from a role, the GRANT privilege is also revoked from that role. For example, if you revoke SELECT privileges from the coffee_bean role with this command:
REVOKE SELECT ON DATABASE coffee_database FROM ROLE coffee_bean
The coffee_bean role can no longer grant SELECT privileges on the coffee_database or its tables.
To remove the WITH GRANT OPTION privilege from the coffee_bean role and still allow the role to have SELECT privileges on the coffee_database, you must run these two commands:
REVOKE SELECT ON coffee_database FROM ROLE coffee_bean;GRANT SELECT ON coffee_database TO ROLE coffee_bean;
-------------------------------------------------------------------
-------------------------------------------------------------------
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#set_role_statement
二十. SET ROLE Statement
Sentry enforces restrictions on queries based on the roles and privileges that the user has. A user can have multiple roles and a role can have multiple privileges.
The SET ROLE command enforces restrictions at the role level, not at the user level. When you use the SET ROLE command to make a role active, the role becomes current for the session. If a role is not current for the session, it is inactive and the user does not have the privileges assigned to that role. A user can only use the SET ROLE command for roles that have been granted to the user.
To list the roles that are current for the user, use the SHOW CURRENT ROLES command. By default, all roles that are assigned to the user are current.
SHOW CURRENT ROLES
You can use the following SET ROLE commands:
1. SET ROLE NONE
Makes all roles for the user inactive. When no role is current, the user does not have any privileges and cannot execute a query.
2. SET ROLE ALL
Makes all roles that have been granted to the user active. All privileges assigned to those roles are applied. When the user executes a query, the query is filtered based on those privileges.
3. SET ROLE role name
Makes a single role active. The privileges assigned to that role are applied. When the user executes a query, the query is filtered based on the privileges assigned to that role.
-------------------------------------------------------------------
-------------------------------------------------------------------
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#show_statement
二十一. SHOW Statement
1. Lists the database(s) for which the current user has database, table, or column-level access:
SHOW DATABASES;
2. Lists the table(s) for which the current user has table or column-level access:
SHOW TABLES;
3. Lists the column(s) to which the current user has SELECT access:
SHOW COLUMNS (FROM|IN) [(FROM|IN) ];
4. Lists all the roles in the system (only for sentry admin users):
SHOW ROLES;
5. Lists all the roles in effect for the current user session:
SHOW CURRENT ROLES;
6. Lists all the roles assigned to the given group name (only allowed for Sentry admin users and others users that are part of the group specified by group name):
SHOW ROLE GRANT GROUP group name;
7. The SHOW statement can also be used to list the privileges that have been granted to a role or all the grants given to a role for a particular object.
It lists all the grants for the given (only allowed for Sentry admin users and other users that have been granted the role specified by ). The following command will also list any column-level privileges:
SHOW GRANT ROLE ;
8. Lists all the grants for a role or user on the given (only allowed for Sentry admin users and other users that have been granted the role specified by ). The following command will also list any column-level privileges:
SHOW GRANT ROLE on ;SHOW GRANT USER on ;
9. Lists the roles and users that have grants on the Hive object. It does not show inherited grants from a parent object. It only shows grants that are applied directly to the object. This command is only available for Hive.
SHOW GRANT ON ;
10. In Hive, this statement lists all the privileges the user has on objects. In Impala, this statement shows the privileges the user has and the privileges the user’s roles have on objects.
SHOW GRANT USER ;
-------------------------------------------------------------------
-------------------------------------------------------------------
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#concept_ekq_rcw_bgb
二十二. Privileges
Sentry supports the following privilege types:
1. CREATE
The CREATE privilege allows a user to create databases, tables, and functions. Note that to create a function, the user also must have ALL permissions on the JAR where the function is located, i.e. GRANT ALL ON URI is required.
You can grant the CREATE privilege on a server or database with the following commands, respectively:
GRANT CREATE ON SERVER TO ROLE GRANT CREATE ON DATABASE TO ROLE
For example, you might enter the following command:
GRANT CREATE ON SERVER super_cool_server TO ROLE my_favorite_role
You can use the GRANT CREATE statement with the WITH GRANT OPTION clause. The WITH GRANT OPTION clause allows the granted role to grant the privilege to other roles on the system. See GRANT … WITH GRANT OPTION for more information about how to use the clause.
参考： https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#grant_privilege_with_grant
The following table shows the CREATE privilege scope:
--------------------------------------------------------------
Scope # Available Operations
--------------------------------------------------------------
Server # Create databases, tables, views, and functions
Database # Create tables and views in the database
Table # Not allowed
--------------------------------------------------------------
2. OWNER
The OWNER privilege gives a user or role special privileges on a database, table, or view in HMS. An object can only have one owner at a time. For more information about the OWNER privilege, see Object Ownership.
The owner of an object can execute any action on the object, similar to the ALL privilege. However, the object owner cannot transfer object ownership unless the ALL privileges with GRANT option is selected. You can specify the privileges that an object owner has on the object with the OWNER Privileges for Sentry Policy Database Objects setting in Cloudera Manager.
3. REFRESH (Impala Only)
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#refresh_privilege
The REFRESH privilege allows a user to execute commands that update metadata information on Impala databases and tables, such as the REFRESH and INVALIDATE METADATA commands. Keep in mind that metadata invalidation or refresh in Impala is an expensive procedure that can cause performance issues if it is overused.
You can grant the REFRESH privilege on a server, table, or database with the following commands, respectively:
GRANT REFRESH ON SERVER TO ROLE GRANT REFRESH ON DATABASE TO ROLE GRANT REFRESH ON TABLE TO ROLE
You can use the GRANT REFRESH statement with the WITH GRANT OPTION clause. The WITH GRANT OPTION clause allows the granted role to grant the privilege to other roles on the system. See GRANT … WITH GRANT OPTION for more information about how to use the clause.
The following table shows the REFRESH privilege scope:
-----------------------------------------------------------------
Scope # Available Operations
-----------------------------------------------------------------
Server # Invalidate the metadata of all tables on the server
Database # Invalidate the metadata of all tables in the database
Table # Invalidate and refresh the table metadata
-----------------------------------------------------------------
4. SELECT
The SELECT privilege allows a user to view table data and metadata. In additon, you can use the SELECT privilige to provide column-level authorization. See Column-Level Authorization below for details.
参考：https://docs.cloudera.com/documentation/enterprise/latest/topics/sg_hive_sql.html#column_level_authorization
You can grant the SELECT privilege on a server, table, or database with the following commands, respectively:
-------------------------------------------------------------------
-------------------------------------------------------------------
参考: https://gethue.com/apache-sentry-made-easy-with-the-new-hue-security-app/
二十四. Apache Sentry made easy with the new Hue Security App
vi /etc/hue/conf/hue.ini ( 在安装了 hue 相关服务的 服务器上执行 )
[libsentry]
Hostname or IP of server.
hostname=localhost
Port the sentry service is running on.
port=8038
Sentry configuration directory, where sentry-site.xml is located.
sentry_conf_dir=/etc/sentry/conf
-------------------------------------------------------------------
-------------------------------------------------------------------
实战：Hive + Sentry 权限测试:
beeline 连接测试 ( 需进一步测试： hive 高可用连接方法 )
beeline!connect jdbc:hive2://cdh-master-v01.wanfeng16899.com:10000/default;principal=hive/cdh-master-v01.wanfeng16899.com@WANFENG16899.COM# 或者beeline -u 'jdbc:hive2://cdh-master-v01.wanfeng16899.com:10000/default;principal=hive/cdh-master-v01.wanfeng16899.com@WANFENG16899.COM'
Sentry 组件的 sentry.service.admin.group 参数，添加 hadoop 用户 ( 否则：不能建 hive 数据库，更无法继续 )
0: jdbc:hive2://cdh-master-v01.sma> create database etl;
Error: Error while compiling statement: FAILED: SemanticException No valid privileges
User hadoop does not have privileges for CREATEDATABASE
The required privileges: Server=server1->action=create->grantOption=false; (state=42000,code=40000)
create admin role
create role admin;
create role sentry_r;
grant all on server server1 to role admin;
grant ALTERDATABASE_OWNER on server server1 to role admin;
grant role admin to group hive;
grant role admin to group hadoop;
grant role admin to user hadoop; # 报错 “Sentry does not allow privileges to be granted/revoked to/from: USER”
创建数据库
create database etl;
create database bigdata;
create database danalysis;
create database dim_odts;
alter database etl set owner user etl;
alter database bigdata set owner user bigdata;
alter database danalysis set owner user danalysis;
alter database dim_odts set owner user bigdata; # 如果执行后，再次执行将报错，类似如下：
创建数据库相关角色
create role etl; create role etl_r;
create role bigdata; create role bigdata_r;
create role danalysis; create role danalysis_r;
create role dim_odts; create role dim_odts_r;
create role hue_r; # only select privileges for hue
create role sentry_r; # only select privileges for superset
GRANT SELECT ON DATABASE etl TO ROLE etl_r WITH GRANT OPTION;
GRANT SELECT ON DATABASE bigdata TO ROLE bigdata_r WITH GRANT OPTION;
GRANT SELECT ON DATABASE danalysis TO ROLE danalysis_r WITH GRANT OPTION;
GRANT SELECT ON DATABASE dim_odts TO ROLE dim_odts_r WITH GRANT OPTION;
GRANT SELECT ON DATABASE bigdata TO ROLE hue_r WITH GRANT OPTION;
GRANT SELECT ON DATABASE danalysis TO ROLE hue_r WITH GRANT OPTION;
GRANT SELECT ON DATABASE dim_odts TO ROLE hue_r WITH GRANT OPTION;
GRANT SELECT ON DATABASE bigdata TO ROLE sentry_r WITH GRANT OPTION;
GRANT SELECT ON DATABASE danalysis TO ROLE sentry_r WITH GRANT OPTION;
GRANT SELECT ON DATABASE dim_odts TO ROLE sentry_r WITH GRANT OPTION;
GRANT ALL ON DATABASE etl TO ROLE etl WITH GRANT OPTION;
GRANT ALL ON DATABASE bigdata TO ROLE bigdata WITH GRANT OPTION;
GRANT ALL ON DATABASE danalysis TO ROLE danalysis WITH GRANT OPTION;
GRANT ALL ON DATABASE dim_odts TO ROLE dim_odts WITH GRANT OPTION;
grant role hue_r to group g_hue;
grant role sentry_r to group superset; # ipa 创建 superset 用户的时候，就默认创建了 superset 组 ?
grant role etl to group etl;
grant role dim_odts_r to group etl;
GRANT ALL ON URI ‘hdfs://wfcluster/user/etl/hive/etl.db/etl_test’ TO ROLE etl;
GRANT SELECT ON DATABASE dim_odts TO user superset;
0: jdbc:hive2://cdh-master-v01.sma> alter database bigdata set owner user bigdata;
Error: Error while compiling statement: FAILED: SemanticException No valid privileges
User hadoop does not have privileges for ALTERDATABASE_OWNER
The required privileges: Server=server1->Db=bigdata->action=*->grantOption=true; (state=42000,code=40000)
以上报错是因为：
Note: When you enable object ownership, you cannot immediately assign ownership to the same user that owned the object before ownership was enabled. For example, if you want the hive user to have the OWNER privilege, you must enable object ownership, assign ownership to a temporary user (for example, an admin user) and then re-assign ownership to the hive user.
进 etl 用户后，发现 show database 能看到 default & etl 数据库 ( 且能在该库下建表并插入数据)
[etl@cdh-client-v01 ~]$ kinit -kt H O M E / s e c u r i t y / k e y t a b s / {HOME}/security/keytabs/ HOME/security/keytabs/{USER}.keytab U S E R [ e t l @ c d h − c l i e n t − v 01 ] {USER} [etl@cdh-client-v01 ~] USER[etl@cdh−client−v01 ]
[etl@cdh-client-v01 ~]$
[etl@cdh-client-v01 ~]$ klist
Ticket cache: FILE:/tmp/krb5cc_1732600012
Default principal: etl@WANFENG16899.COM
Valid starting Expires Service principal
12/13/2020 23:08:33 12/14/2020 23:08:33 krbtgt/WANFENG16899.COM@WANFENG16899.COM
renew until 12/20/2020 23:08:33
[etl@cdh-client-v01 ~]$ beeline -u ‘jdbc:hive2://cdh-master-v01.wanfeng16899.com:10000/default;principal=hive/cdh-master-v01.wanfeng16899.com@WANFENG16899.COM’
WARNING: Use “yarn jar” to launch YARN applications.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.2-1.cdh6.3.2.p0.1605554/jars/log4j-slf4j-impl-2.8.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.2-1.cdh6.3.2.p0.1605554/jars/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Connecting to jdbc:hive2://cdh-master-v01.wanfeng16899.com:10000/default;principal=hive/cdh-master-v01.wanfeng16899.com@WANFENG16899.COM
Connected to: Apache Hive (version 2.1.1-cdh6.3.2)
Driver: Hive JDBC (version 2.1.1-cdh6.3.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 2.1.1-cdh6.3.2 by Apache Hive
0: jdbc:hive2://cdh-master-v01.sma> show databases;
INFO : Compiling command(queryId=hive_20201213230853_7643e51f-98e8-4bd5-a1c8-a0c552212fdf): show databases
INFO : Semantic Analysis Completed
INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:database_name, type:string, comment:from deserializer)], properties:null)
INFO : Completed compiling command(queryId=hive_20201213230853_7643e51f-98e8-4bd5-a1c8-a0c552212fdf); Time taken: 0.239 seconds
INFO : Executing command(queryId=hive_20201213230853_7643e51f-98e8-4bd5-a1c8-a0c552212fdf): show databases
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing command(queryId=hive_20201213230853_7643e51f-98e8-4bd5-a1c8-a0c552212fdf); Time taken: 0.09 seconds
INFO : OK
±---------------+
| database_name |
±---------------+
| default |
| etl |
±---------------+
2 rows selected (0.473 seconds)
进 bigdata 用户后，发现 show database 能看到 default & bigdata & dim_odts 数据库 ( 且能在 bigdata & dim_odts 库下建表并插入数据)
[bigdata@cdh-client-v01 ~]$ beeline -u ‘jdbc:hive2://cdh-master-v01.wanfeng16899.com:10000/default;principal=hive/cdh-master-v01.wanfeng16899.com@WANFENG16899.COM’
WARNING: Use “yarn jar” to launch YARN applications.
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.2-1.cdh6.3.2.p0.1605554/jars/log4j-slf4j-impl-2.8.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/cloudera/parcels/CDH-6.3.2-1.cdh6.3.2.p0.1605554/jars/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Connecting to jdbc:hive2://cdh-master-v01.wanfeng16899.com:10000/default;principal=hive/cdh-master-v01.wanfeng16899.com@WANFENG16899.COM
Connected to: Apache Hive (version 2.1.1-cdh6.3.2)
Driver: Hive JDBC (version 2.1.1-cdh6.3.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 2.1.1-cdh6.3.2 by Apache Hive
0: jdbc:hive2://cdh-master-v01.sma> show databases;
INFO : Compiling command(queryId=hive_20201214052716_f3245ee6-c7e9-43be-b7e6-7072461b6553): show databases
INFO : Semantic Analysis Completed
INFO : Returning Hive schema: Schema(fieldSchemas:[FieldSchema(name:database_name, type:string, comment:from deserializer)], properties:null)
INFO : Completed compiling command(queryId=hive_20201214052716_f3245ee6-c7e9-43be-b7e6-7072461b6553); Time taken: 0.159 seconds
INFO : Executing command(queryId=hive_20201214052716_f3245ee6-c7e9-43be-b7e6-7072461b6553): show databases
INFO : Starting task [Stage-0:DDL] in serial mode
INFO : Completed executing command(queryId=hive_20201214052716_f3245ee6-c7e9-43be-b7e6-7072461b6553); Time taken: 0.028 seconds
INFO : OK
±---------------+
| database_name |
±---------------+
| bigdata |
| default |
| dim_odts |
±---------------+
3 rows selected (0.256 seconds)
且 hive 对应的 hdfs 目录 /user/hive/warehouse/ 其权限均为 hive:hive，一切似乎尽在掌控之中
[hadoop@cdh-client-v01 ~]$ hdfs dfs -ls /user/hive/warehouse/;
Found 4 items
drwxrwx–x+ - hive hive 0 2020-12-14 05:29 /user/hive/warehouse/bigdata.db
drwxrwx–x+ - hive hive 0 2020-12-13 22:48 /user/hive/warehouse/danalysis.db
drwxrwx–x+ - hive hive 0 2020-12-14 05:29 /user/hive/warehouse/dim_odts.db
drwxrwx–x+ - hive hive 0 2020-12-13 23:10 /user/hive/warehouse/etl.db
[hadoop@cdh-client-v01 ~]$ hdfs dfs -ls /user/hive/warehouse/bigdata.db
Found 1 items
drwxrwx–x+ - hive hive 0 2020-12-14 05:29 /user/hive/warehouse/bigdata.db/t
[hadoop@cdh-client-v01 ~]$ hdfs dfs -ls /user/hive/warehouse/etl.db/;
Found 1 items
drwxrwx–x+ - hive hive 0 2020-12-13 23:11 /user/hive/warehouse/etl.db/t
[hadoop@cdh-client-v01 ~]$ hdfs dfs -ls /user/hive/warehouse/dim_odts.db/;
Found 1 items
drwxrwx–x+ - hive hive 0 2020-12-14 05:30 /user/hive/warehouse/dim_odts.db/t
测试查询权限 - 将 etl 数据库 的查询权限给 bigdata