我创建了一个我认为是非常标准的用户,可以访问可以查询单个表的单个角色:
create user new_user;
alter user new_user set password = 'some_pw';
create role new_role;
alter user new_user set default_warehouse = 'compute_wh';
alter user new_user set default_role = 'new_role';
grant role new_role to user new_user;
grant usage on warehouse compute_wh to role new_role;
grant usage on schema my_schema to new_role;
grant usage on database my_db to role new_role;
grant select on my_db.my_schema.my_table to role new_role;
GRANT OPERATE ON WAREHOUSE COMPUTE_WH TO ROLE new_role;
然而,当我在SQL客户端(如DBeaver(中设置该用户时,我无法运行任何查询:
USE WAREHOUSE COMPUTE_WH; -- fails even here
USE DATABASE my_db;
SELECT * FROM my_db.my_schema.my_table;
SQL错误[2043][02000]:SQL编译错误:对象没有存在,或者无法执行操作。
我可能缺少哪些额外权限?
使用仓库COMPUTE_WH;--即使在这里也会失败
如果用户有权访问仓库但不能使用它,则可能表示它已挂起。我将添加operate
特权:
GRANT OPERATE ON WAREHOUSE COMPUTE_WH TO ROLE NEW_ROLE;
并检查自动恢复是否打开或明确启动仓库:
ALTER WAREHOUSE IF EXISTS COMPUTE_WH RESUME IF SUSPENDED;
USE WAREHOUSE COMPUTE_WH;
编辑:
要检查当前角色,可以使用以下代码:
SELECT CURRENT_ROLE();
如果角色与anticiapted不同,则可以使用进行更改
USE ROLE COMPUTE_WH;