创建角色RL_WRITE并授予表user1的grant update后。表1向user2授予角色时,出现了权限不足的错误。
CREATE ROLE RL_WRITE;
GRANT UPDATE ON user1.table1 TO RL_WRITE;
GRANT RL_WRITE to user2;
以user2登录,运行命令:
update user1.table1 set datmov = to_date('18/10/21','dd/mm/yy');
*01031. 00000 - "insufficient privileges"
*Cause: An attempt was made to perform a database operation without
the necessary privileges.
*Action: Ask your database administrator or designated security
administrator to grant you the necessary privileges*
我不明白可能缺少什么。
我找到了问题所在。User user2
已经有了RL_READ_ONLY
的角色,我创建了RL_WRITE
,然后把它授予user2
。
但是user2
被设置为ALTER USER user2 DEFAULT ROLE RL_READ_ONLY
,因此,即使在为角色创建了GRANT UPDATE ON user1.table1 TO RL_WRITE
并将此角色授予user2
之后,更新命令也不起作用。
解决方案/strong>只有在执行命令ALTER USER user2 DEFAULT ROLE ALL;
之后,user2
才能够执行角色RL_WRITE
已经拥有权限的更新、插入和删除命令。
GRANTSELECT,更新user1。