玉面飞龙的BLOG

READ ONLY account to Oracle database

September 5th, 2008 | Categories: Boring | Tags: Oracle, Security

看来数据库中一些read only的用户也可以捣乱。

read only的权限可以执行select for update，虽然没有权限执行实际的DML，但是可以lock表或行。

如果给恶意或者初级的数据库用户使用，也会给系统带来安全和稳定的隐患。
SQL> create user readonly_user identified by readonly_user; User created.


SQL> grant create session to readonly_user;

Grant succeeded.
SQL> grant select any table to readonly_user;

Grant succeeded.
SQL> conn /

Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

DRIVER                         TABLE

PLAN_TABLE                     TABLE

T                              TABLE
SQL> conn readonly_user/readonly_user

Connected.

SQL> lock table oracle.t in exclusive mode;

lock table oracle.t in exclusive mode

                  *

ERROR at line 1:

ORA-01031: insufficient privileges
SQL> select * from oracle.t where rownum=1 for update;
        ID

----------

     19999
SQL> update oracle.t set id=0 where id=19999;

update oracle.t set id=0 where id=19999

              *

ERROR at line 1:

ORA-01031: insufficient privileges
SQL> conn /

Connected.

SQL> grant select on DRIVER to readonly_user;

Grant succeeded.
SQL>  conn readonly_user/readonly_user

Connected.

SQL> lock table oracle.driver in exclusive mode;

Table(s) Locked.
SQL> select * from oracle.driver  where rownum=1 for update;

        ID IND_PAD                                          N1         N2

---------- ---------------------------------------- ---------- ----------

         1 x                                                 0          0