Introduction
Lately, I installed an application into Oracle 12.1.0.2 to verify that it works in given version.Later, I had a need to compare grants and I exported all grants (using custom SQL) of application schemas.
There were hundreds of grants missing.
I spent several hours and discovered finally that the grants are there (in database) but Oracle is not returning them to me (SQL just produced wrong result).
Test case
Here is artificial test case to demonstrate the same issue based on Oracle data dictionary rows. You just need Oracle 12.1.0.2 default database installation.It is repeatable using SQL*Plus 12.1.0.2 (Linux 64), 12.1.0.2 (Win64) or 11.2.0.4 (Win32)
Interesting is that it is not repeatable using TOAD on top of Oracle Client 11.2.0.4 (Win32). I have not had enough time to investigate the reason.
I compared execution plan using SQL*Trace and I found that execution plan for correct result (using TOAD) and wrong result (using SQL*Plus) is the same.
Here are 2 identical SQL statements executed having one difference. First SQL is counting rows and the Second SQL is retrieving all the "counted" rows.
Note: given test case is executed against Pluggable Database. If the same test case is executed against regular physical database then the corresponding number of rows are 264 and 266.
-- First, lets count rows
SQL>
SQL>
SELECT
count(*)
FROM
(
2
SELECT
p.*
3 FROM
DBA_TAB_PRIVS p
4
INNER
JOIN
DBA_OBJECTS o ON
(p.owner
=
o.owner
AND
p.table_name
=
o.object_name)
5
)
6 WHERE
7 (
owner IN
('SYS')
AND
grantee IN
('DVSYS'
)
)
8 OR
(
owner IN
('XDB')
AND
grantee IN
('SELECT_CATALOG_ROLE',
'PUBLIC')
)
9
/
COUNT(*)
----------
306
-- Now, lets retrieve all 306 rows
SQL>
SQL>
SELECT
grantee FROM
(
2
SELECT
p.*
3 FROM
DBA_TAB_PRIVS p
4
INNER
JOIN
DBA_OBJECTS o ON
(p.owner
=
o.owner
AND
p.table_name
=
o.object_name)
5
)
6 WHERE
7 (
owner IN
('SYS')
AND
grantee IN
('DVSYS'
)
)
8 OR
(
owner IN
('XDB')
AND
grantee IN
('SELECT_CATALOG_ROLE',
'PUBLIC')
)
9
/
/
-
/
-- NB! 306 rows expected, but only 304 rows returned
304
rows
selected.
-- one possible workaround
-- NB! Please consult Oracle Support before using this on production system!
SQL>
SQL>
alter
session
set
"_optimizer_dsdir_usage_control"=0;
Session
altered.
-- count(*) is the same
SQL>
SQL>
SELECT
count(*)
FROM
(
2
SELECT
p.*
3 FROM
DBA_TAB_PRIVS p
4
INNER
JOIN
DBA_OBJECTS o ON
(p.owner
=
o.owner
AND
p.table_name
=
o.object_name)
5
)
6 WHERE
7 (
owner IN
('SYS')
AND
grantee IN
('DVSYS'
)
)
8 OR
(
owner IN
('XDB')
AND
grantee IN
('SELECT_CATALOG_ROLE',
'PUBLIC')
)
9
/
COUNT(*)
----------
306
-- and same amount of rows are returned as well
SQL>
SQL>
SELECT
grantee FROM
(
2
SELECT
p.*
3 FROM
DBA_TAB_PRIVS p
4
INNER
JOIN
DBA_OBJECTS o ON
(p.owner
=
o.owner
AND
p.table_name
=
o.object_name)
5
)
6 WHERE
7 (
owner IN
('SYS')
AND
grantee IN
('DVSYS'
)
)
8 OR
(
owner IN
('XDB')
AND
grantee IN
('SELECT_CATALOG_ROLE',
'PUBLIC')
)
9
/
/
-
/
306
rows
selected.
References
Bug 20784165 - WRONG RESULT FOR SELECT DBA_TAB_PRIVS JOIN DBA_OBJECTS
Registered in March 2015
-- end --
No comments:
Post a Comment