# Spark SQL场景的LakeFormation授权 在提交Spark SQL作业前，IAM用户需完成LakeFormation的IAM细粒度授权和SQL资源授权（包括元数据、数据库、表、列、函数及OBS路径），以确保作业能够正常访问所需数据。本节介绍Spark SQL场景的LakeFormation授权的具体操作。 #### 授权操作 使用LakeFormation资源需要分别完成LakeFormation API的IAM细粒度授权和LakeFormation SQL资源授权。Hive、DataSource和Iceberg格式中各SQL语句的权限请参考[Iceberg]和[Hive和DataSource]。 1. **LakeFormation的IAM细粒度授权：授权使用** **LakeFormation API。** IAM服务通常提供了用户和用户组的访问权限的方式。您可以在IAM控制台中创建自定义策略（Policy），定义可以调用哪些LakeFormation的API。然后，将这些策略附加到相应的用户或用户组上。 IAM授权的具体操作请参考[创建用户并授权使用LakeFormation](https://support.huaweicloud.com/usermanual-lakeformation/lakeformation_03_00004.html)。 2. **LakeFormation SQL资源授权：授权使用LakeFormation具体资源。** LakeFormation资源授权是指允许用户对特定资源（元数据、数据库、表、列、函数和OBS等）的访问的权限，以此来控制对LakeFormation的数据和元数据的访问。 在LakeFormation管理控制台，对资源授权。目前Spark SQL支持用户组、角色、用户三种主体类型。 了解LakeFormation SQL资源权限请参考[配置LakeFormation元数据权限](https://support.huaweicloud.com/usermanual-lakeformation/lakeformation_03_00014.html)。 ![](https://support.huaweicloud.com/usermanual-aidatalake/public_sys-resources/caution_3.0-zh-cn.png) - Catalog资源暂时不支持在DataArts Studio控制台进行SQL授权，请在LakeFormation管理控制台完成授权。 - Spark SQL使用外表时，需手动对OBS路径授权，否则将提示AccessDenied访问错误。对OBS路径授权的具体操作，请参见[配置LakeFormation元数据权限](https://support.huaweicloud.com/usermanual-lakeformation/lakeformation_03_00014.html)。 #### SQL鉴权 ![](https://support.huaweicloud.com/usermanual-aidatalake/public_sys-resources/caution_3.0-zh-cn.png) - 除了表格内的权限，IAM侧还需要配置lakeformation:policy:describe。 - 由于Spark集群启动时默认会校验default数据库，所以在执行任何SQL前，需要在LakeFormation控制台上先为目标Catalog下的default数据库授予DESCRIBE权限，授权操作请参考[配置LakeFormation元数据权限](https://support.huaweicloud.com/usermanual-lakeformation/lakeformation_03_00014.html)。 - 建议不要给lakeformation:policy:create权限，给了会被视为管理员，跳过SQL鉴权。 - [Iceberg] 表1Iceberg鉴权 | 类型 | SQL | LakeFormation SQL资源鉴权权限 | IAM权限 | |:---|:---|:---|:---| | **DDL语句** | ALTER DATABASE | database:DESCRIBE database:ALTER | "lakeformation:database:describe", "lakeformation:database:alter" | | **DDL语句** | ALTER TABLE | database:DESCRIBE table:DESCRIBE table:ALTER database:CREATE_TABLE column:SELECT或table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter", "lakeformation:table:create" | | **DDL语句** | ALTER VIEW | database:DESCRIBE database:CREATE_TABLE table:DESCRIBE table:ALTER column:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | ALTER TABLE Test_Iceberg ALTER COLUMN col TYPE BIGINT | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | ALTER TABLE Test_Iceberg ADD PARTITION FIELD col | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | ALTER TABLE Test_Iceberg DROP PARTITION FIELD col | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | ALTER TABLE Test_Iceberg REPLACE PARTITION FIELD col | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | ALTER TABLE Test_Iceberg WRITE writeSpec | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | ALTER TABLE Test_Iceberg SET IDENTIFIER_KW FIELDS fieldList | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | ALTER TABLE Test_Iceberg DROP IDENTIFIER_KW FIELDS fieldList | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | ALTER TABLE Test_Iceberg SET TBLPROPERTIES | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | ALTER TABLE Test_Iceberg create/Replace BRANCH Test_branch | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | ALTER TABLE Test_Iceberg create/Replace Tag Test_tag | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | ALTER TABLE Test_Iceberg DROP BRANCH Test_branch | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | ALTER TABLE Test_Iceberg DROP TAG Test_tag | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | CREATE DATABASE | database:DESCRIBE catalog:CREATE_DATABASE | "lakeformation:database:describe", "lakeformation:database:create" | | **DDL语句** | CREATE TABLE Test_Iceberg USING iceberg | database:DESCRIBE database:CREATE_TABLE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:create" | | **DDL语句** | CREATE VIEW | database:DESCRIBE database:CREATE_TABLE table:DESCRIBE（source\\target） table:DROP(target) column:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:create", "lakeformation:table:drop" | | **DDL语句** | CREATE TABLE Test_Iceberg AS SELECT xx From xx Where | table:DESCRIBE table:ALTER table:DROP table:INSERT database:CREATE_TABLE database:DESCRIBE column:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:create", "lakeformation:table:alter", "lakeformation:table:drop" | | **DDL语句** | DROP DATABASE | database:DESCRIBE database:DROP | "lakeformation:database:describe", "lakeformation:database:drop" | | **DDL语句** | DROP TABLE | database:DESCRIBE table:DESCRIBE table:DROP | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:drop" | | **DDL语句** | DROP VIEW | database:DESCRIBE table:DESCRIBE(target\\source) table:DROP(target) | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:drop" | | **DDL语句** | REPAIR TABLE | database:DESCRIBE table:DESCRIBE table:ALTER table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | TRUNCATE TABLE | database:DESCRIBE table:DESCRIBE table:SELECT table:UPDATE | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DDL语句** | REPLACE TABLE Test_Iceberg (col1..col2..) USING iceberg | database:DESCRIBE table:DESCRIBE table:ALTER table:UPDATE column:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DML语句** | INSERT INTO Test_Iceberg VALUES | table:DESCRIBE table:ALTER column:SELECT database:DESCRIBE | "lakeformation:table:describe", "lakeformation:table:alter", "lakeformation:database:describe" | | **DML语句** | UPDATE Test_Iceberg SET col = value WHERE | table:DESCRIBE table:ALTER column:SELECT database:DESCRIBE | "lakeformation:table:describe", "lakeformation:table:alter", "lakeformation:database:describe" | | **DML语句** | INSERT OVERWRITE Test_Iceberg VALUES | table:DESCRIBE table:ALTER table:UPDATE database:DESCRIBE | "lakeformation:table:describe", "lakeformation:table:alter", "lakeformation:database:describe" | | **DML语句** | MERGE INTO Test_Iceberg USING xx | database:DESCRIBE table:DESCRIBE table:ALTER table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DML语句** | DELETE FROM Test_Iceberg WHERE | database:DESCRIBE table:DESCRIBE table:ALTER table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **DML语句** | SET xx.wap.branch = Test_branch INSERT INTO Test_Iceberg VALUES | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe","lakeformation:table:describe", "lakeformation:table:alter" | | **DQL语句** | SELECT | database:DESCRIBE table:DESCRIBE column:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" | | **DQL语句** | EXPLAIN | 取决于执行SQL | 取决于执行SQL | | **Auxiliary语句** | DESCRIBE DATABASE | database:DESCRIBE | "lakeformation:database:describe" | | **Auxiliary语句** | DESCRIBE QUERY | database:DESCRIBE table:DESCRIBE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" | | **Auxiliary语句** | DESCRIBE TABLE | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | **Auxiliary语句** | SHOW COLUMNS | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | **Auxiliary语句** | SHOW CREATE TABLE | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | **Auxiliary语句** | SHOW DATABASES | catalog:LIST_DATABASE database:DESCRIBE | "lakeformation:database:describe" | | **Auxiliary语句** | SHOW PARTITIONS | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | **Auxiliary语句** | SHOW TABLE EXTENDED | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | **Auxiliary语句** | SHOW TABLES | database:LIST_TABLE database:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | **Auxiliary语句** | SHOW TBLPROPERTIES | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | **Auxiliary语句** | SHOW VIEWS | catalog:LIST_DATABASE database:LIST_TABLE database:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | **Auxiliary语句** | CALL system.rollback_to_snapshot(Test_Iceberg, snapshot_id) | database:DESCRIBE table:ALTER table:DESCRIBE table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **Auxiliary语句** | CALL system.rollback_to_timestamp(Test_Iceberg, timestamp) | database:DESCRIBE table:ALTER table:DESCRIBE table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **Auxiliary语句** | CALL system.set_current_snapshot(Test_Iceberg, snapshot_id) | database:DESCRIBE table:DESCRIBE table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" | | **Auxiliary语句** | CALL system.cherrypick_snapshot(Test_Iceberg, snapshot_id) | database:DESCRIBE table:DESCRIBE table:UPDATE table:ALTER table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **Auxiliary语句** | CALL system.fast_forward(Test_Iceberg, branch) | database:DESCRIBE table:DESCRIBE table:ALTER table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **Auxiliary语句** | CALL system.expire_snapshots(Test_Iceberg, older_than) | database:DESCRIBE table:DESCRIBE table:ALTER table:DELETE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **Auxiliary语句** | CALL system.remove_orphan_files(Test_Iceberg) | database:DESCRIBE table:DESCRIBE table:DELETE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" | | **Auxiliary语句** | CALL system.rewrite_data_files(Test_Iceberg) | database:DESCRIBE table:DESCRIBE table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" | | **Auxiliary语句** | CALL system.rewrite_manifests(Test_Iceberg) | database:DESCRIBE table:DESCRIBE table:ALTER table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **Auxiliary语句** | CALL system.rewrite_position_delete_files(Test_Iceberg) | database:DESCRIBE table:DESCRIBE table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" | | **Auxiliary语句** | CALL system.register_table(spark_catalog, Test_Iceberg) | database:DESCRIBE database:CREATE_TABLE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:create" | | **Auxiliary语句** | CALL system.ancestors_of(Test_Iceberg) | database:DESCRIBE table:DESCRIBE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" | | **Auxiliary语句** | CALL system.add_files(Test_Iceberg, Table_1,) | database:DESCRIBE table:DESCRIBE table:ALTER table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **Auxiliary语句** | CALL system.publish_changes(Test_Iceberg, wap1) | database:DESCRIBE table:DESCRIBE table:ALTER table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | **Auxiliary语句** | CALL system.snapshot(Test_Parquet, Test_Copy) | table:DESCRIBE database:CREATE_TABLE | "lakeformation:table:describe", "lakeformation:table:create" | | **Auxiliary语句** | CALL system.migrate(Test_Parquet) | table:DESCRIBE database:DESCRIBE database:CREATE_TABLE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:create", "lakeformation:table:alter" | | **Auxiliary语句** | CALL system.create_changelog_view(Test_Iceberg, '1', '4') | table:DESCRIBE table:ALTER table:SELECT | "lakeformation:table:describe", "lakeformation:table:alter" | - [Hive和DataSource] 表2Hive和DataSource鉴权 | 类型 | SQL语句 | LakeFormation SQL资源鉴权权限 | IAM权限 | |:---|:---|:---|:---| | DDL语句 | ALTER DATABASE | database:DESCRIBE database:ALTER | "lakeformation:database:describe", "lakeformation:database:alter" | | DDL语句 | ALTER TABLE | database:DESCRIBE table:DESCRIBE table:ALTER database:CREATE_TABLE column:SELECT或table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter", "lakeformation:table:create" | | DDL语句 | ALTER VIEW | database:DESCRIBE table:DESCRIBE column:SELECT table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | DDL语句 | CREATE DATABASE | database:DESCRIBE catalog:CREATE_DATABASE | "lakeformation:database:describe", "lakeformation:database:create" | | DDL语句 | CREATE OR REPLACE FUNCTION (CREATE) | database:DESCRIBE database:CREATE_FUNC | "lakeformation:database:describe", "lakeformation:function:describe", "lakeformation:function:create" | | DDL语句 | CREATE OR REPLACE FUNCTION (REPLACE) | database:CREATE_FUNC database:DESCRIBE function:DESCRIBE function:DROP | "lakeformation:database:describe", "lakeformation:function:describe", "lakeformation:function:create", "lakeformation:function:drop" | | DDL语句 | CREATE TABLE | database:DESCRIBE database:CREATE_TABLE | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:create" | | DDL语句 | CREATE VIEW | database:DESCRIBE database:CREATE_TABLE table:DESCRIBE（source\\target） table:DROP(target) column:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:create", "lakeformation:table:drop" | | DDL语句 | DROP DATABASE | database:DESCRIBE database:DROP | "lakeformation:database:describe", "lakeformation:database:drop" | | DDL语句 | DROP FUNCTION | database:DESCRIBE function:DESCRIBE function:DROP | "lakeformation:database:describe", "lakeformation:function:describe", "lakeformation:function:drop" | | DDL语句 | DROP TABLE | database:DESCRIBE table:DESCRIBE table:DROP | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:drop" | | DDL语句 | DROP VIEW | database:DESCRIBE table:DESCRIBE(target\\source) table:DROP(target) | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:drop" | | DDL语句 | REPAIR TABLE | database:DESCRIBE table:DESCRIBE table:ALTER table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | DDL语句 | TRUNCATE TABLE | database:DESCRIBE table:DESCRIBE table:SELECT table:UPDATE | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | DML语句 | INSERT TABLE | database:DESCRIBE table:DESCRIBE table:ALTER table:INSERT column:SELECT或table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | DML语句 | LOAD DATA | database:DESCRIBE table:DESCRIBE table:UPDATE table:ALTER table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | DQL语句 | SELECT | database:DESCRIBE table:DESCRIBE column:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" | | DQL语句 | EXPLAIN | 取决于执行SQL | - | | Auxiliary 语句 | ANALYZE TABLE | database:DESCRIBE table:DESCRIBE table:SELECT table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | | Auxiliary 语句 | DESCRIBE DATABASE | database:DESCRIBE | "lakeformation:database:describe" | | Auxiliary 语句 | DESCRIBE FUNCTION | database:DESCRIBE function:DESCRIBE | "lakeformation:database:describe", "lakeformation:function:describe" | | Auxiliary 语句 | DESCRIBE QUERY | database:DESCRIBE table:DESCRIBE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" | | Auxiliary 语句 | DESCRIBE TABLE | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | Auxiliary 语句 | REFRESH TABLE（spark sql禁用） | database:DESCRIBE table:DESCRIBE column:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" | | Auxiliary 语句 | REFRESH FUNCTION | database:DESCRIBE function:DESCRIBE | "lakeformation:database:describe", "lakeformation:function:describe" | | Auxiliary 语句 | SHOW COLUMNS | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | Auxiliary 语句 | SHOW CREATE TABLE | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | Auxiliary 语句 | SHOW DATABASES | catalog:LIST_DATABASE database:DESCRIBE | "lakeformation:database:describe" | | Auxiliary 语句 | SHOW FUNCTIONS | database:DESCRIBE | "lakeformation:database:describe", "lakeformation:function:describe" | | Auxiliary 语句 | SHOW PARTITIONS | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | Auxiliary 语句 | SHOW TABLE EXTENDED | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | Auxiliary 语句 | SHOW TABLES | database:LIST_TABLE database:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | Auxiliary 语句 | SHOW TBLPROPERTIES | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | | Auxiliary 语句 | SHOW VIEWS | catalog:LIST_DATABASE database:LIST_TABLE database:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" |