Oracle-like ROWNUM in MySQL
It is still possible in a single SQL.
SELECT @rownum:=@rownum+1 rownum, t.*FROM (SELECT @rownum:=0) r, mytable t;
Here I sporadically post some notes about interesting (to me at least) Oracle, MSSQL, MySQL, OLAP and datawarehousing things....
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;
SQL>create user hacker identified by password;
SQL>grant create session to hacker ;
SQL>create table towner.emp (id number primary key, sal number) tablespace users;
SQL>insert into towner.emp values (1,100);
1 row created.
SQL>SQL>grant select on towner.emp to hacker ;
SQL> SELECT U.USERNAME, U.ACCOUNT_STATUS, U.LOCK_DATE
FROM DBA_USERS U WHERE ACCOUNT_STATUS='OPEN';
We do not bother about expired and locked accounts.
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 not in ('SYS','SYSTEM')ORDER BY 1,2;
We see that above tables are potentially vulnerable for privilege escalation attack.
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
max(case when OBJ_PRIV='INSERT' then 'Y'
when SYS_PRIV='INSERT ANY TABLE' then 'Y'
end ) INSERTABLE,
max(case when OBJ_PRIV='UPDATE' then 'Y'
when SYS_PRIV='UPDATE ANY TABLE' then 'Y'
end ) UPDATEABLE,
max(case when OBJ_PRIV='DELETE' then 'Y'
when SYS_PRIV='DELETE ANY TABLE' then 'Y'
end ) DELETEABLE
INTO insertable, updateable, deleteable
SELECT P.PRIVILEGE OBJ_PRIV, S.PRIVILEGE SYS_PRIV
(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_ );
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
declare i char; u char; d char;
if user not in ('SYS','SYSTEM','TOWNER') then
sys.check_obj_privs( 'TOWNER', 'EMP', user, i, u, d);
if (inserting and i!='Y')
or(deleting and d!='Y')
or(updating and u!='Y')
raise_application_error(-20031, 'insufficient privileges');
HACKER>select * from session_roles;
1 row selected.
HACKER>select * from session_privs;
1 row selected.
HACKER>select id, sal from towner.emp;
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.
(censored code of specifically crafted dynamic view to TOWNER.EMP table)
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'