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>

Simple Example with REPLACE

The REPLACE function allows you to change a string of characters to another string of characters and can accept three parameters:

 

(1)    Input column name.

(2)    Old string value.

(3)    New string value.

 

You can see what I mean in the example below, which I tested on Oracle 11.2:

 

SQL> create table directory_name

  2  (location varchar2(30))

  3  /

 

Table created.

 

SQL> insert into directory_name

  2  values('/batch/prod/dir1')

  3  /

 

1 row created.

 

SQL> insert into directory_name

  2  values('/batch/prod/dir2')

  3  /

 

1 row created.

 

SQL> select location from directory_name

  2  /

 

LOCATION

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

/batch/prod/dir1

/batch/prod/dir2

 

SQL> update directory_name

  2  set location = replace(location,'prod','test')

  3  /

 

2 rows updated.

 

SQL> select location from directory_name

  2  /

 

LOCATION

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

/batch/test/dir1

/batch/test/dir2

 

SQL>

DECODE

I was tidying up some old E-mails recently and found this example from 2008. I think it may have been from a Spanish student who needed help with his homework. He had a table called COCHES. It contained details of car manufacturers (in the column called TIPO). Each one made cars with 3 or 5 doors. He needed to update the table with one SQL statement and change the values in the doors (PUERTAS) column from 3 to 5 and vice versa. I suggested he could do it with decode as shown below:

SQL> select * from coches;
 
TIPO          PUERTAS
---------- ----------
Austin              5
Morris              3
 
SQL> update coches set puertas = decode (puertas,3,5,5,3);
 
2 rows updated.
 
SQL> select * from coches;
 
TIPO          PUERTAS
---------- ----------
Austin              3
Morris              5
 
SQL>