Oracle-like ROWNUM in MySQL

It needs sometimes to exactly mimic Oracle's ROWNUM where is no possibility to initiate a counter in previous statement by SET @rownum:=0;.

It is still possible in a single SQL.

SELECT @rownum:=@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, mytable t;

Things are even worse

Today I have found that this vulnerability can be exploited not only when a select privilege granted on a table, but also if select has been granted to a view. The view must keep keys of table though, that is to be updateable.
Even if a view is not updateable, oracle passes DMLs through security control, then it says that view is not updateable.
Select on a view can be granted to user directly or via role, or via chain of roles, it does not make a difference.

I have granted SELECT_CATALOG_ROLE to a role that is granted to another role that is granted to an account ("hacker"). And the attack returned

HACKER>update (...censored code to access DBA_USERS...)
4 set password='xxx' where username='HACKER'

set password='xxx' where username='HACKER'
*ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table

It means that if the view would preserve a key, that would be updateable.
And there are many views in SYS that are updatable.
Resume: be very careful when grant any access to dictionary views

Oracle security bug. select privilege escalation via view

I already read about the bug on Red Database Security page "Read-only user can modify data" http://www.red-database-security.com/advisory/oracle_modify_data_via_views.html

This problem has been discussed on some web forums. However no clear mitigation instructions were proposed. Initially it was simple: revoke CREATE VIEW from CONNECT role and enjoy. But Andrew Max has discovered something more dangerous. <http://andrewmax.blogspot.com/2006/04/yet-another-security-alert.html>
It has not taken too much of my time to crack oracle security just having "CREATE SESSION" system privilege and "SELECT" privilege on a table of other user (see example on Andrew Max blog). I will not provide you with details how I have managed to crack an oracle authorization control.
But I will try to provide you with some recommendations how temporarily to mitigate the threat.
First of all, check what accounts in database have direct "SELECT" privilege on tables of other's accounts. Check both the privilege that has been granted directly to account, and granted via some role or chain of roles.

Here is an example. I create users "hacker" and "towner" (table owner).
Hacker is a regular user that has only CREATE SESSION system privilege.
Towner will have a table and grant select to hacker, just SELECT object privilege.
I will not repeat the attack here. Trust me, hacker account can do whatever he wants with data in towner.emp table. That is delete, insert and update.

SQL> connect system...
SQL>create user towner identified by password 2 default tablespace users quota 1M on users;
User created.
SQL>create user hacker identified by password;
User created.
SQL>grant create session to hacker ;
Grant succeeded.
SQL>create table towner.emp (id number primary key, sal number) tablespace users;
Table created.
SQL>insert into towner.emp values (1,100);
1 row created.
SQL>commit;
Commit complete.
SQL>SQL>grant select on towner.emp to hacker ;
Grant succeeded.
SQL> SELECT U.USERNAME, U.ACCOUNT_STATUS, U.LOCK_DATE
FROM DBA_USERS U WHERE ACCOUNT_STATUS='OPEN';


USERNAME ACCOUNT_STATUS
------------------------------ ----------------
SYS OPEN
SYSTEM OPEN
DBSNMP OPEN
HACKER OPEN
TOWNER OPEN

We do not bother about expired and locked accounts.
We also can trust SYS, SYSTEM and DBSNMP*.
Lets look who have direct access to somebody's tables (this SQL does not show privileges acquired via roles, may be later I will write this. However the procedure below does traverse roles.)

SQL>col GRANTEE format A10
SQL>col TABLE format A40
SQL>SELECT P.GRANTEE, P.OWNER'.'P.TABLE_NAME "TABLE"
FROM DBA_TAB_PRIVS P, DBA_TABLES T, DBA_USERS U
WHERE P.OWNER=T.OWNER AND P.TABLE_NAME=T.TABLE_NAME
AND P.GRANTEE=U.USERNAME
AND U.ACCOUNT_STATUS='OPEN'
AND P.PRIVILEGE='SELECT'
AND P.GRANTEE not in ('SYS','SYSTEM')ORDER BY 1,2;
GRANTEE TABLE
---------- -----------------------
HACKER TOWNER.EMP
ETLJOB FINANCE.GENERAL_LEDGER

We see that above tables are potentially vulnerable for privilege escalation attack.
Further we should figure out, who/what are these HACKER and REPORTJOB accounts. We should find out why these accounts do need direct SELECT privilege to tables.
Lets imagine that HACKER is a legitimate human user (not a real hacker at all). All he needs is to run his reports.
ETLJOB is an account that is used by some ETL procedure to load data warehouse.
It is totally on your discretion, to trust or not to trust.
How we can make sure that these accounts can not be used for attack?
The only way I see to temporary mitigate this vulnerability is to create triggers. Statement level triggers before insert/update/delete. As a fast remedy, triggers can hardcode usernames, allowing some users to proceed, and rising an exception for others. It is very simple and fast, but may be not flexible enough in more or less rapidly changing environments or databases with large number of users and complex role based security. It can create maintenance problem when DBA has to ajust triggers all the time after users permissions or roles changed. Better way to do it is to mimic Oracle's proper, planned behavior. Oracle gets information about access privileges from its dictionary, so we can do.
Here is an example of such trigger. I just have created it, did not test it thoroughly, did not benchmark. Thus you should test it if you going to apply it.

I will create procedure in a SYS schema first, to get easy acces to dictionary views. This proc checks if user has Insert, Update or Delete privileges on given table, directly or via roles.

create or replace procedure check_obj_privs(
owner in string, table_name in string, grantee in string,
insertable out char, updateable out char, deleteable out char
) as
owner_ DBA_TAB_PRIVS.OWNER%TYPE:=owner;
table_name_ DBA_TAB_PRIVS.TABLE_NAME%TYPE:=table_name;
grantee_ DBA_TAB_PRIVS.GRANTEE%TYPE:=grantee;
begin
SELECT
max(case when OBJ_PRIV='INSERT' then 'Y'
when SYS_PRIV='INSERT ANY TABLE' then 'Y'
else 'N'
end ) INSERTABLE,
max(case when OBJ_PRIV='UPDATE' then 'Y'
when SYS_PRIV='UPDATE ANY TABLE' then 'Y'
else 'N'
end ) UPDATEABLE,
max(case when OBJ_PRIV='DELETE' then 'Y'
when SYS_PRIV='DELETE ANY TABLE' then 'Y'
else 'N'
end ) DELETEABLE
INTO insertable, updateable, deleteable
FROM (
SELECT P.PRIVILEGE OBJ_PRIV, S.PRIVILEGE SYS_PRIV
FROM
(SELECT ROLE, GRANTED_ROLE
FROM ROLE_ROLE_PRIVS P
CONNECT BY PRIOR GRANTED_ROLE = ROLE
START WITH ROLE IN (SELECT S.GRANTED_ROLE
FROM DBA_ROLE_PRIVS S WHERE GRANTEE=grantee_)) R
LEFT OUTER JOIN DBA_TAB_PRIVS P ON P.GRANTEE IN (R.GRANTED_ROLE, R.ROLE, grantee_)
LEFT OUTER JOIN DBA_SYS_PRIVS S ON S.GRANTEE IN (R.GRANTED_ROLE, R.ROLE, grantee_)
AND S.PRIVILEGE IN ('INSERT ANY TABLE','UPDATE ANY TABLE','DELETE ANY TABLE')
WHERE P.OWNER=owner_ AND P.TABLE_NAME=table_name_ );
end;
/

grant execute on sys.check_obj_privs to public;

Then I create trigger.

create or replace trigger towner.PROTECT_EMP_BIUD
before insert or update or delete
on towner.emp
declare i char; u char; d char;
begin
if user not in ('SYS','SYSTEM','TOWNER') then
sys.check_obj_privs( 'TOWNER', 'EMP', user, i, u, d);
--dbms_output.put_line(i':'u':'d);
if (inserting and i!='Y')
or(deleting and d!='Y')
or(updating and u!='Y')
then
raise_application_error(-20031, 'insufficient privileges');
end if;
end if;
end;
/

Test
HACKER>select * from session_roles;
ROLE
------------------------------
SELECT_TNAME_EMP_ROLE
1 row selected.
HACKER>select * from session_privs;
PRIVILEGE
-----------------------------------
CREATE SESSION
1 row selected.
HACKER>select id, sal from towner.emp;
ID SAL
---------- ----------
1 100
1 row selected.
Hacker can select, that is OK.
HACKER>update towner.emp set sal=0;
update towner.emp set sal=0
*
ERROR at line 1:ORA-01031: insufficient privileges
Hacker can not update, which is OK too.

HACKER>UPDATE
(censored code of specifically crafted dynamic view to TOWNER.EMP table)
SET SAL=0;

UPDATE
*
ERROR at line 1:
ORA-20031: insufficient privileges
ORA-06512: at "TOWNER.PROTECT_EMP_BIUD", line 13
ORA-04088: error during execution of trigger 'TOWNER.PROTECT_EMP_BIUD'

It works!