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>

ORA-01446

This was tested on Oracle 11.2. First I created a table and inserted 2 identical values in it:

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values (1)

  2  /

 

1 row created.

 

SQL> insert into tab1 values (1)

  2  /

 

1 row created.

SQL>

Then I looked at the rowids of the values I had inserted:

SQL> select rowid, col1 from tab1

  2  /

 

ROWID                    COL1

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

AAAmpvAAEAAAACLAAA          1

AAAmpvAAEAAAACLAAB          1

SQL>

Next I created a view and checked that it referred back to the underlying table and picked up the correct rowids again: 

SQL> create view view1

  2  as select col1 from tab1

  3  /

 

View created.

 

SQL> select rowid, col1 from view1

  2  /

 

ROWID                    COL1

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

AAAmpvAAEAAAACLAAA          1

AAAmpvAAEAAAACLAAB          1

SQL>

Finally I created a view to show the distinct values in the table. This worked as expected too: 

SQL> create view view2

  2  as select distinct col1 from tab1

  3  /

 

View created.

 

SQL> select col1 from view2

  2  /

 

      COL1

----------

         1

SQL> 

But when I tried to look at the rowids in this view, I got an ORA-01446 instead. The rowids in the table itself gave details of each row's physical location in the database. So when I passed these rows through a DISTINCT into a view, there were two occurrences of the value 1 in the table giving one DISTINCT value in the view. Oracle then did not know which row's rowid to associate with the value displayed: 

SQL> select rowid, col1 from view2

  2  /

select rowid, col1 from view2

       *

ERROR at line 1:

ORA-01446: cannot select ROWID from, or sample, a view

with DISTINCT, GROUP BY, etc.

 

SQL>

Does Having TIMED_STATISTICS on Affect Performance?

I was reading through an Oracle publication called Oracle 9i Database Performance Planning recently and saw the following:

In order to get meaningful database statistics, the TIMED_STATISTICS parameter must be enabled for the database instance. The performance impact of having TIMED_STATISTICS enabled is minimal compared to instance performance. The performance improvements and debugging value of a complete set of statistics make this parameter crucial to effective performance analysis.

I decided to check this out on my son's PC, which has 4 gigabytes of RAM and a 4 core CPU. The PC has Windows Vista and Oracle 11.2 installed.

I ran the first test below with TIMED_STATISTICS turned off. It recorded the start time, created a table, added several rows to it, did a full table scan, dropped the table, recorded the end time then checked what statistics were available:

SQL> conn / as sysdba
Connected.
SQL> alter session set statistics_level = basic
  2  /

Session altered.

SQL> alter session set timed_statistics = false
  2  /

Session altered.

SQL> select to_char(sysdate,'hh24:mi:ss') start_time
  2  from dual
  3  /

START_TIME
----------
18:30:03

SQL> create table tab1 tablespace users
  2  as select * from dba_tables
  3  /

Table created.

SQL> begin
  2   for a in 1..9 loop
  3    insert into tab1 select * from tab1;
  4   end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select count(*) from tab1 where owner = 'SYS'
  2  /

  COUNT(*)
----------
    489472

SQL> drop table tab1
  2  /

Table dropped.

SQL> select to_char(sysdate,'hh24:mi:ss') end_time
  2  from dual
  3  /

END_TIME
--------
18:30:51

SQL> select a.value/100
  2  from v$mystat a, v$sysstat b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'CPU used by this session'
  5  /

A.VALUE/100
-----------
        .06

SQL> select * from
  2  (select event, time_waited/100 seconds
  3   from v$session_event
  4   where wait_class != 'Idle'
  5   and sid = (select distinct sid from v$mystat)
  6   order by 2 desc)
  7  where rownum < 6
  8  /

EVENT                                          SECONDS
--------------------------------------------- --------
db file sequential read                              0
Disk file operations I/O                             0
SQL*Net message to client                            0

SQL>

It took 48 seconds to run and, as suggested in the Oracle publication, produced no statistics. I repeated the test with TIMED_STATISTICS turned on:

SQL> conn / as sysdba
Connected.
SQL> alter session set statistics_level = typical
  2  /

Session altered.

SQL> alter session set timed_statistics = true
  2  /

Session altered.

SQL> select to_char(sysdate,'hh24:mi:ss') start_time
  2  from dual
  3  /

START_TIME
----------
18:33:00

SQL> create table tab1 tablespace users
  2  as select * from dba_tables
  3  /

Table created.

SQL> begin
  2   for a in 1..9 loop
  3    insert into tab1 select * from tab1;
  4   end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select count(*) from tab1 where owner = 'SYS'
  2  /

  COUNT(*)
----------
    489472

SQL> drop table tab1
  2  /

Table dropped.

SQL> select to_char(sysdate,'hh24:mi:ss') end_time
  2  from dual
  3  /

END_TIME
--------
18:33:52

SQL> select a.value/100
  2  from v$mystat a, v$sysstat b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'CPU used by this session'
  5  /

A.VALUE/100
-----------
       7.77

SQL> select * from
  2  (select event, time_waited/100 seconds
  3   from v$session_event
  4   where wait_class != 'Idle'
  5   and sid = (select distinct sid from v$mystat)
  6   order by 2 desc)
  7  where rownum < 6
  8  /

EVENT                                          SECONDS
--------------------------------------------- --------
db file scattered read                              19
log file switch (checkpoint incomplete)             10
enq: RO - fast object reuse                          8
log buffer space                                     5
db file sequential read                              2

SQL>

It took 52 seconds to run, which was slightly longer than the first test, but, at the end, I could see that proper statistics had been recorded in views such as V$MYSTAT and V$SESSION_EVENT.

I repeated the two tests four more times and put the results in the table below:

Tests  TIMED_STATISTICS Off   TIMED_STATISTICS On

            (seconds)              (seconds)

 1+2           48                     52
 3+4           50                     55
 5+6           51                     60
 7+8           56                     59
 9+10          56                     50

ORA-02019

I had not seen this problem for a long time so, when it happened again recently, it took me a while to see the cause. I therefore decided to record it for future reference. First I created a database link:

ORCL /export/home/oracle/andrew > sqlplus /

SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 17 11:55:54 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create database link link1.world

  2  connect to andrew

  3  identified by reid

  4  using 'REMOTEDB'
  5  /

Database link created.

SQL>

… but when I tried to use the link, I got an ORA-02019:

SQL> select * from dual@link1

  2  /

select * from dual@link1

                   *

ERROR at line 1:

ORA-02019: connection description for remote database not found

SQL>

I checked the database’s GLOBAL_NAME:

SQL> l

  1* select * from global_name

SQL> /

GLOBAL_NAME

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

ORCL

SQL>

I changed it as follows:

SQL> alter database rename global_name to ORCL.WORLD

  2  /

Database altered.

SQL> select * from global_name

  2  /

GLOBAL_NAME

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

ORCL.WORLD

SQL>

… and the database link worked:

SQL> select * from dual@link1

  2  /

D

-

X

SQL>

ORA-01430

This was tested on Oracle 11.2. If you try to use a column name twice in the same table, you get an ORA-01430: 

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> alter table tab1 add (col1 number)

  2  /

alter table tab1 add (col1 number)

                      *

ERROR at line 1:

ORA-01430: column being added already exists in table

 

SQL>

log file switch (archiving needed)

I was running a test in Oracle 11.2 and the SQL below, which usually takes around 1 minute, seemed to be running forever:

SQL> begin
  2   for a in 1..8 loop
  3    insert into tab1 select * from tab1;
  4   end loop;
  5  end;
  6  /

I looked up the session's SID in V$SESSION to see if there was a row with wait_time set to zero. This means that the session is currently waiting on the event concerned. I saw a wait event I had not seen before:

SQL> select event, seconds_in_wait
  2  from v$session
  3  where sid = 191
  4  and wait_time = 0
  5  /

EVENT                               SECONDS_IN_WAIT
----------------------------------- ---------------
log file switch (archiving needed)             2596

SQL>

To double check the diagnosis, I waited for 10 seconds:

SQL> exec dbms_lock.sleep(10);

PL/SQL procedure successfully completed.

SQL>

... and when I looked again, the seconds_in_wait had increased by 10 seconds too, proving that the session was spending all its time waiting on this event:

SQL> select event, seconds_in_wait
  2  from v$session
  3  where sid = 191
  4  and wait_time = 0
  5  /

EVENT                               SECONDS_IN_WAIT
----------------------------------- ---------------
log file switch (archiving needed)             2606

SQL>

In most situations, you would have to clear some space from the archive area to allow the log file switch to proceed. However, the test I was running did not need the database to be in ARCHIVELOG mode so I closed the database and mounted it:

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1375704 bytes
Variable Size             318767656 bytes
Database Buffers          197132288 bytes
Redo Buffers                5832704 bytes
Database mounted.
SQL>

However, when I tried to put the database in NOARCHIVELOG mode, I saw an error which I had not seen before - 2 new things in 1 day!

SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback
database is enabled

SQL>

Well, I was doing some flahsback testing about a week ago but that is finished for now so I turned it off:

SQL> alter database flashback off
  2  /

Database altered.

SQL>

Then I was able to put the database in NOARCHIVELOG mode and get on with my original test, which I hope to report in a future post:

SQL> alter database noarchivelog
  2  /

Database altered.

SQL> alter database open
  2  /

Database altered.

SQL>

ORA-00942

These examples were tested on Oracle 11.2. If you get an ORA-00942, this might mean that a table does not exist:

 

SQL> conn / as sysdba

Connected.

SQL> drop user andrew cascade

  2  /

 

User dropped.

 

SQL> drop user john cascade

  2  /

 

User dropped.

 

SQL> grant dba to andrew identified by reid

  2  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> select count(*) from blah

  2  /

select count(*) from blah

                     *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SQL> 

If that is the case, creating the table will make the error go away: 

SQL> create table blah (col1 number)

  2  /

 

Table created.

 

SQL> select count(*) from blah

  2  /

 

  COUNT(*)

----------

         0

 

SQL> 

But in the next example, table ANDREW.BLAH exists as we have just created it. The problem is that JOHN does not have permission to look at it: 

SQL> grant create session to john

  2  identified by smith

  3  /

 

Grant succeeded.

 

SQL> conn john/smith

Connected.

SQL> select count(*) from andrew.blah

  2  /

select count(*) from andrew.blah

                            *

ERROR at line 1:

ORA-00942: table or view does not exist

 

SQL> 

When that happens, you have to GRANT appropriate access to make the error disappear: 

SQL> conn andrew/reid

Connected.

SQL> grant select on blah to john

  2  /

 

Grant succeeded.

 

SQL> conn john/smith

Connected.

SQL> select count(*) from andrew.blah

  2  /

 

  COUNT(*)

----------

         0

 

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>

ORA-01749

This was tested on an Oracle 11.2 database. You cannot GRANT or REVOKE object privileges to or from yourself:

 

SQL> show user

USER is "ANDREW"

SQL> grant select on blah to john

  2  /

 

Grant succeeded.

 

SQL> grant select on blah to andrew

  2  /

grant select on blah to andrew

                        *

ERROR at line 1:

ORA-01749: you may not GRANT/REVOKE privileges to/from

yourself

 

SQL> revoke select on blah from john

  2  /

 

Revoke succeeded.

 

SQL> revoke select on blah from andrew

  2  /

revoke select on blah from andrew

                           *

ERROR at line 1:

ORA-01749: you may not GRANT/REVOKE privileges to/from

yourself

 

SQL>