问题概述
某客户从11.2.0.1 升级迁移到19.16 版本后,应用反应部分查询功能无法使用报无权限,数据库中增加了ORA-01031: insufficient privileges 跟踪event进行分析
问题复现
创建三个测试用户
create user test1 identified by oracle123;
create user test2 identified by oracle123;
create user test3 identified by oracle123;分别给最小权限
grant connect to test1;
grant connect to test2;
grant connect to test3;
grant create view to test1;
grant create view to test2;
grant create view to test3;
grant create table to test1;
grant create table to test2;
grant create table to test3;
alter user test1 quota unlimited on users;
alter user test2 quota unlimited on users;
alter user test3 quota unlimited on users;首先使用test3 登录数据库创建test3 表
sqlplus test3/[email protected]:1521/pdb
SQL> create table test3 (id number,name varchar(10));
Table created.
SQL> insert into test3 values(1,'wsb');
1 row created.
SQL> commit;使用test2登录数据库创建v_test2 视图该视图依赖test3用户下面的表,需要给test2用户查询该表权限。
sqlplus test2/[email protected]:1521/pdb
create view v_test2 as select * from test3.test3;给test2 授权
SQL> grant select on test3.test3 to test2;
Grant succeeded.
SQL> create view v_test2 as select * from test3.test3;
View created.
SQL> select * from v_test2;
ID NAME
---------- --------------------
1 wsb使用test1用户登录数据库进行测试
sqlplus test1/[email protected]:1521/pdb
SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 17 10:30:49 2023
Version 19.16.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0
SQL> select * from test2.v_test2;
select * from test2.v_test2
*
ERROR at line 1:
ORA-00942: table or view does not exist由于还没给视图权限所以无法进行查询
使用dba用户给权限test1
SQL> grant select on test2.v_test2 to test1;
Grant succeeded.
SQL>再次使用test1 用户进行查询
SQL> select * from test2.v_test2;
select * from test2.v_test2
*
ERROR at line 1:
ORA-01031: insufficient privileges依然报错
把test3.test3表的查询权限给到test1用户
SQL> grant select on test3.test3 to test1;
Grant succeeded.再次查询依然报错
SQL> select * from test2.v_test2;
select * from test2.v_test2
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> select * from test3.test3;
ID NAME
---------- --------------------
1 wsb直接查询表正常
解决方案
由于test2.v_test2 视图是依赖于test3.test3 表所以test2用户针对该表需要有with grant option 权限 赋予该权限后正常
SQL> grant select on test3.test3 to test2 with grant option;
Grant succeeded.
其实不需要单独授权test3.test3 给test1 用户
revoke select on test3.test3 from test1;
最终test2的权限如下
GRANTEE OWNER TABLE_NAME PRIVILEGE GRANTA
------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------
TEST2 TEST3 TEST3 SELECT YES
TEST1 TEST2 V_TEST2 SELECT NO参考文档
To grant SELECT on a view to another user, either you must own all of the objects underlying the view or you must have been granted the SELECT object privilege WITH GRANT OPTION on all of those underlying objects. This is true even if the grantee already has SELECT privileges on those underlying objects.