Monday, April 28, 2014

SQL92_SECURITY

I tested the examples in this post in two Oracle 11.2 databases. In the first example, Andrew creates a table, inserts a row into it then grants UPDATE privilege on it to Fred, who is then able to work out the value in the table, without changing it permanently, using a series of UPDATE and ROLLBACK statements: 

SQL> conn / as sysdba
Connected.
SQL> select value from v$parameter
  2  where name = 'sql92_security'
  3  /
 
VALUE
------------------------------
FALSE
 
SQL> create user andrew
  2  identified by reid
  3  default tablespace users
  4  quota unlimited on users
  5  /
 
User created.
 
SQL> grant create session, create table
  2  to andrew
  3  /
 
Grant succeeded.
 
SQL> create user fred
  2  identified by bloggs
  3  /
 
User created.
 
SQL> grant create session to fred
  2  /
 
Grant succeeded.
 
SQL> conn andrew/reid
Connected.
SQL> create table tab1 (col1 number)
  2  /
 
Table created.
 
SQL> insert into tab1 values (5)
  2  /
 
1 row created.
 
SQL> grant update on tab1 to fred
  2  /
 
Grant succeeded.
 
SQL> conn fred/bloggs
Connected.
SQL> update andrew.tab1
  2  set col1 = 99
  3  where col1 > 10
  4  /
 
0 rows updated.
 
SQL> update andrew.tab1
  2  set col1 = 99
  3  where col1 > 0
  4  /
 
1 row updated.
 
SQL> rollback
  2  /
 
Rollback complete.
 
SQL> update andrew.tab1
  2  set col1 = 99
  3  where col1 > 5
  4  /
 
0 rows updated.
 
SQL> update andrew.tab1
  2  set col1 = 99
  3  where col1 > 3
  4  /
 
1 row updated.
 
SQL> rollback
  2  /
 
Rollback complete.
 
SQL> update andrew.tab1
  2  set col1 = 99
  3  where col1 > 4
  4  /
 
1 row updated.
 
SQL> rollback
  2  /
 
Rollback complete.
 
SQL> update andrew.tab1
  2  set col1 = 99
  3  where col1 = 5
  4  /
 
1 row updated.
 
SQL> rollback
  2  /
 
Rollback complete.
 
SQL> 

The example is trivial. Working out floating pointing numbers or long character strings in tables containing thousands of rows would take ages using this method. However, by using some clever PL/SQL, it would be possible, given time. This isn’t too much of a problem for me. After all, if a malicious person had UPDATE access to a table of mine, I’m sure he could cause far more damage by simply overwriting my data. But if you don’t like the idea of people guessing values in your tables, then the SQL92_SECURITY initialization parameter could be for you. In the example above, you may have noticed that it was turned off.
 
However, in the second example below, the parameter is turned on. It was run in a different database, as mentioned above, but the users were created in a similar fashion. This time, the UPDATE privilege does not allow Fred to run UPDATE statements with WHERE clauses. To do this, he needs SELECT access as well: 

SQL> conn / as sysdba
Connected.
SQL> select value from v$parameter
  2  where name = 'sql92_security'
  3  /
 
VALUE
------------------------------
TRUE
 
SQL> conn andrew/reid
Connected.
SQL> create table tab1 (col1 number)
  2  /
 
Table created.
 
SQL> insert into tab1 values (1)
  2  /
 
1 row created.
 
SQL> grant update on tab1 to fred
  2  /
 
Grant succeeded.
 
SQL> conn fred/bloggs
Connected.
SQL> update andrew.tab1 set col1 = 2
  2  /
 
1 row updated.
 
SQL> update andrew.tab1 set col1 = 3
  2  where col1 = 2
  3  /
update andrew.tab1 set col1 = 3
              *
ERROR at line 1:
ORA-01031: insufficient privileges
 
SQL> conn andrew/reid
Connected.
SQL> grant select on tab1 to fred
  2  /
 
Grant succeeded.
 
SQL> conn fred/bloggs
Connected.
SQL> update andrew.tab1 set col1 = 3
  2  where col1 = 2
  3  /
 
1 row updated.
 
SQL>

No comments: