SparkSQL-SQL鉴权
Hive和DataSource
- 除了表格内的权限，IAM侧还需要配置lakeformation:policy:describe。
- 由于Spark集群启动时默认会校验default数据库，所以在执行任何SQL前，需要在LakeFormation控制台上先为目标Catalog下的default数据库授予DESCRIBE权限，授权操作请参考配置LakeFormation元数据权限。
- 建议不要给lakeformation:policy:create权限，给了会被视为管理员，跳过SQL鉴权。
| 类型 | SQL语句 | LakeFormation SQL资源鉴权权限 | IAM权限 |
|---|---|---|---|
| DDL语句 | ALTER DATABASE | database:DESCRIBE database:ALTER | "lakeformation:database:describe", "lakeformation:database:alter" |
| 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" | |
| ALTER VIEW | database:DESCRIBE table:DESCRIBE column:SELECT table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | |
| CREATE DATABASE | database:DESCRIBE catalog:CREATE_DATABASE | "lakeformation:database:describe", "lakeformation:database:create" | |
| CREATE OR REPLACE FUNCTION (CREATE) | database:DESCRIBE database:CREATE_FUNC | "lakeformation:database:describe", "lakeformation:function:describe", "lakeformation:function:create" | |
| 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" | |
| CREATE TABLE | database:DESCRIBE database:CREATE_TABLE | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:create" | |
| 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" | |
| DROP DATABASE | database:DESCRIBE database:DROP | "lakeformation:database:describe", "lakeformation:database:drop" | |
| DROP FUNCTION | database:DESCRIBE function:DESCRIBE function:DROP | "lakeformation:database:describe", "lakeformation:function:describe", "lakeformation:function:drop" | |
| DROP TABLE | database:DESCRIBE table:DESCRIBE table:DROP | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:drop" | |
| DROP VIEW | database:DESCRIBE table:DESCRIBE(target\source) table:DROP(target) | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:drop" | |
| REPAIR TABLE | database:DESCRIBE table:DESCRIBE table:ALTER table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | |
| 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" |
| LOAD DATA | database:DESCRIBE table:DESCRIBE table:UPDATE table:ALTER table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" | |
| DR语句 | SELECT | database:DESCRIBE table:DESCRIBE column:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" |
| EXPLAIN | 取决于执行sql | - | |
| Auxiliary 语句 | ANALYZE TABLE | database:DESCRIBE table:DESCRIBE table:SELECT table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| DESCRIBE DATABASE | database:DESCRIBE | "lakeformation:database:describe" | |
| DESCRIBE FUNCTION | database:DESCRIBE function:DESCRIBE | "lakeformation:database:describe", "lakeformation:function:describe" | |
| DESCRIBE QUERY | database:DESCRIBE table:DESCRIBE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" | |
| DESCRIBE TABLE | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | |
| REFRESH TABLE（spark sql禁用） | database:DESCRIBE table:DESCRIBE column:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" | |
| REFRESH FUNCTION | database:DESCRIBE function:DESCRIBE | "lakeformation:database:describe", "lakeformation:function:describe" | |
| SHOW COLUMNS | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | |
| SHOW CREATE TABLE | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | |
| SHOW DATABASES | catalog:LIST_DATABASE database:DESCRIBE | "lakeformation:database:describe" | |
| SHOW FUNCTIONS | database:DESCRIBE | "lakeformation:database:describe", "lakeformation:function:describe" | |
| SHOW PARTITIONS | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | |
| SHOW TABLE EXTENDED | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | |
| SHOW TABLES | database:LIST_TABLE database:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | |
| SHOW TBLPROPERTIES | database:DESCRIBE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" | |
| SHOW VIEWS | catalog:LIST_DATABASE database:LIST_TABLE database:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" |
Iceberg
| 命令 | SQL语句 | LakeFormation SQL资源鉴权权限 | IAM权限 |
|---|---|---|---|
| 展示库 | SHOW DATABASES | catalog:LIST_DATABASE database:DESCRIBE | "lakeformation:database:describe" |
| 创建库 | CREATE DATABASE | database:DESCRIBE catalog:CREATE_DATABASE | "lakeformation:database:describe", "lakeformation:database:create" |
| 删除库 | DROP DATABASE | database:DESCRIBE database:DROP | "lakeformation:database:describe", "lakeformation:database:drop" |
| 修改库 | ALTER DATABASE | database:DESCRIBE database:ALTER | "lakeformation:database:describe", "lakeformation:database:alter" |
| 展示所有表 | SHOW TABLES | database:LIST_TABLE database:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe" |
| 创建表 | CREATE TABLE tablename USING ICEBERG | database:DESCRIBE database:CREATE_TABLE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:create" |
| 从查询创建表 | CREATE TABLE tablename 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" |
| 删除表 | DROP TABLE tablename | table:DESCRIBE table:DROP database:DESCRIBE | "lakeformation:table:describe", "lakeformation:table:drop", "lakeformation:database:describe" |
| 写数据 | INSERT INTO tablename VALUES | table:DESCRIBE table:ALTER column:SELECT database:DESCRIBE | "lakeformation:table:describe", "lakeformation:table:alter", "lakeformation:database:describe" |
| 更新数据 | UPDATE tablename SET col = value WHERE | table:DESCRIBE table:ALTER column:SELECT database:DESCRIBE | "lakeformation:table:describe", "lakeformation:table:alter", "lakeformation:database:describe" |
| 覆写 | INSERT OVERWRITE tablename VALUES | table:DESCRIBE table:ALTER table:UPDATE database:DESCRIBE | "lakeformation:table:describe", "lakeformation:table:alter", "lakeformation:database:describe" |
| 替换Iceberg数据 | MERGE INTO tablename USING xx | database:DESCRIBE table:DESCRIBE table:ALTER table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 从Iceberg表删除 | DELETE FROM tablename WHERE | database:DESCRIBE table:DESCRIBE table:ALTER table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 替换已有字段结构 | REPLACE TABLE tablename (col1..clo2..) USING iceberg | database:DESCRIBE table:DESCRIBE table:ALTER table:UPDATE column:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 字段类型转换 | ALTER TABLE tablename ALTER COLUMN col TYPE BIGINT | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 添加分区字段 | ALTER TABLE tablename ADD PARTITION FIELD col | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 删除分区字段 | ALTER TABLE tablename DROP PARTITION FIELD col | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 替换分区字段 | ALTER TABLE tablename REPLACE PARTITION FIELD col | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 设置写入分布和排序 | ALTER TABLE tablename WRITE writeSpec | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 设置标识字段 | ALTER TABLE tablename SET IDENTIFIER_KW FIELDS fieldList | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 删除标识字段 | ALTER TABLE tablename DROP IDENTIFIER_KW FIELDS fieldList | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 设置表属性 | ALTER TABLE tablename SET TBLPROPERTIES | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 创建或替换分支 | ALTER TABLE tablename CREATE/REPLACE BRANCH Test_branch | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 创建或替换标签 | ALTER TABLE tablename CREATE/Replace Tag Test_tag | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 删除分支 | ALTER TABLE tablename DROP BRANCH Test_branch | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 删除标签 | ALTER TABLE tablename DROP TAG Test_tag | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 分支写 | SET spark.wap.branch = Test_branch INSERT INTO tablename VALUES | database:DESCRIBE table:DESCRIBE table:ALTER | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 回滚到快照 | CALL SYSTEM.ROLLBACK_TO_SNAPSHOT(tablename, snapshot_id) | database:DESCRIBE table:ALTER table:DESCRIBE table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 回滚到时间戳 | CALL SYSTEM.ROLLBACK_TO_TIMESTAMP(tablename, timestamp) | database:DESCRIBE table:ALTER table:DESCRIBE table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 设置当前快照 | CALL SYSTEM.SET_CURRENT_SNAPSHOT(tablename, snapshot_id) | database:DESCRIBE table:DESCRIBE table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" |
| 挑选快照 | CALL SYSTEM.CHERRYPICK_SNAPSHOT(tablename, snapshot_id) | database:DESCRIBE table:DESCRIBE table:UPDATE table:ALTER table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 快进分支 | CALL SYSTEM.FAST_FORWARD(tablename, branch) | database:DESCRIBE table:DESCRIBE table:ALTER table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 过期快照 | CALL SYSTEM.EXPIRE_SNAPSHOTS(tablename, older_than) | database:DESCRIBE table:DESCRIBE table:ALTER table:DELETE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 删除孤立文件 | CALL SYSTEM.REMOVE_ORPHAN_FILES(tablename) | database:DESCRIBE table:DESCRIBE table:DELETE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" |
| 重写数据文件 | CALL SYSTEM.REWRITE_DATA_FILES(tablename) | database:DESCRIBE table:DESCRIBE table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" |
| 重写清单文件 | CALL SYSTEM.REWRITE_MANIFESTS(tablename) | database:DESCRIBE table:DESCRIBE table:ALTER table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 重写位置删除文件 | CALL SYSTEM.REWRITE_POSITION_DELETE_FILES(tablename) | database:DESCRIBE table:DESCRIBE table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" |
| 执行表注册操作 | CALL SYSTEM.REGISTER_TABLE(spark_catalog, tablename) | database:DESCRIBE database:CREATE_TABLE table:DESCRIBE | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:create" |
| 追溯祖先链 | CALL SYSTEM.ANCESTORS_OF(tablename) | database:DESCRIBE table:DESCRIBE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe" |
| 添加内容到表 | CALL SYSTEM.ADD_FILES(tablename, table_1) | database:DESCRIBE table:DESCRIBE table:ALTER table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 生成新快照 | CALL SYSTEM.PUBLISH_CHANGES(tablename, wap1) | database:DESCRIBE table:DESCRIBE table:ALTER table:UPDATE table:SELECT | "lakeformation:database:describe", "lakeformation:table:describe", "lakeformation:table:alter" |
| 创建快照表 | CALL SYSTEM.SNAPSHOT(tablename, test_copy_name) | table:DESCRIBE database:CREATE_TABLE | "lakeformation:table:describe", "lakeformation:table:create" |
| 正式改造为Iceberg表 | 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" |
| 查询快照之间的变更 | CALL SYSTEM.CREATE_CHANGELOG_VIEW(tablename, '1', '4') | table:DESCRIBE table:ALTER table:SELECT | "lakeformation:table:describe", "lakeformation:table:alter" |
