Tuesday, October 27, 2015

AUTHID CURRENT_USER

If USERA creates a function or procedure and allows USERB to run it, USERB does so with USERA’s permissions. However, if USERA adds the AUTHID CURRENT_USER clause to the code, USERB runs it with its own permissions. You can see what I mean in the example below, which I tested in an Oracle 11.1 database:
 
I created a user called USERB and allowed it to login to the database:
 
SQL> create user userb
  2  identified by userb
  3  /
 
User created.
 
SQL> grant create session to userb
  2  /
 
Grant succeeded.
 
SQL>
 
I created a user called USERA and allowed it to connect to the database and create tables and compiled code:
 
SQL> create user usera
  2  identified by usera
  3  default tablespace users
  4  quota unlimited on users
  5  /
 
User created.
 
SQL> grant create session,
  2  create table,
  3  create procedure to usera
  4  /
 
Grant succeeded.
 
SQL>
 
USERA connected to the database and created a table called CARS with 2 rows:
 
SQL> conn usera/usera
Connected.
SQL> create table cars as
  2  select 'AUSTIN' manufacturer from dual
  3  /
 
Table created.
 
SQL> insert into cars values('MORRIS')
  2  /
 
1 row created.
 
SQL> select count(*) from cars
  2  /
 
  COUNT(*)
----------
         2
 
SQL>
 
USERA then created a function to accept a table name parameter, count the number of rows in that table and return the value to the caller. USERA then allowed USERB to execute this function:
 
SQL> create function count_rows
  2  (tab in varchar2) return pls_integer
  3  is
  4  row_count pls_integer;
  5  begin
  6  execute immediate 'select count(*) from '||tab
  7  into row_count;
  8  return row_count;
  9  end count_rows;
10  /
 
Function created.
 
SQL> grant execute on count_rows to userb
  2  /
 
Grant succeeded.
 
SQL>
 
USERA created a copy of this function with the AUTHID CURRENT_USER clause:
 
SQL> create function count_rows_acu
  2  (tab in varchar2) return pls_integer
  3  authid current_user
  4  is
  5  row_count pls_integer;
  6  begin
  7  execute immediate 'select count(*) from '||tab
  8  into row_count;
  9  return row_count;
10  end count_rows_acu;
11  /
 
Function created.
 
SQL> grant execute on count_rows_acu to userb
  2  /
 
Grant succeeded.
 
SQL>
 
USERB then logged in and tried to look at USERA’s CARS table but failed, as you would expect:
 
SQL> conn userb/userb
Connected.
SQL> select count(*) from usera.cars
  2  /
select count(*) from usera.cars
                           *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL>
 
USERB then ran USERA’s first function to count the rows in the CARS table. This did not have the AUTHID CURRENT_USER clause so it ran with USERA’s permissions and there was no problem:
 
SQL> select usera.count_rows('cars') from dual
  2  /
 
USERA.COUNT_ROWS('CARS')
------------------------
                       2
 
SQL>
 
USERB then ran USERA’s second function to count the rows in the CARS table. This had the AUTHID CURRENT_USER clause so it ran with USERB’s permissions and failed:
 
SQL> select usera.count_rows_acu('cars') from dual
  2  /
select usera.count_rows_acu('cars') from dual
       *
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "USERA.COUNT_ROWS_ACU", line 7
 
SQL>

Friday, October 23, 2015

O7_DICTIONARY_ACCESSIBILITY

Long ago, in Oracle 7 I believe, a user with the SELECT ANY TABLE privilege could access tables and views owned by SYS. Also, a user with the EXECUTE ANY  PROCEDURE privilege could run code owned by SYS. Nowadays, this behaviour is controlled by the O7_DICTIONARY_ACCESSIBILITY initialisation parameter. The default value for this is FALSE, as you can see in the query below, which I ran in an Oracle 11.1 database:
 
SQL> conn / as sysdba
Connected.
SQL> l
  1  select value, isdefault
  2  from v$parameter
  3* where name = 'O7_DICTIONARY_ACCESSIBILITY'
SQL> /
 
VALUE                ISDEFAULT
-------------------- ---------
FALSE                TRUE
 
SQL>
 
I created a user in this database and granted it the SELECT ANY TABLE and EXECUTE ANY PROCEDURE privileges:
 
SQL> create user andrew
  2  identified by reid
  3  /
 
User created.
 
SQL> grant create session,
  2  select any table,
  3  execute any procedure
  4  to andrew
  5  /
 
Grant succeeded.
 
SQL>
 
I logged in with this user and found that it could neither SELECT from DBA_TABLES nor EXEC SYS.DBMS_LOCK.SLEEP:
 
SQL> conn andrew/reid
Connected.
SQL> select count(*) from dba_tables
  2  /
select count(*) from dba_tables
                     *
ERROR at line 1:
ORA-00942: table or view does not exist
 
SQL> exec sys.dbms_lock.sleep(10);
BEGIN sys.dbms_lock.sleep(10); END;
 
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_LOCK' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
 
SQL>
 
I changed O7_DICTIONARY_ACCESSIBILITY to TRUE:
 
SQL> conn / as sysdba
Connected.
SQL> l
  1  alter system
  2  set o7_dictionary_accessibility = true
  3* scope = spfile
SQL> /
 
System altered.
 
SQL> startup force
ORACLE instance started.
 
Total System Global Area  158703616 bytes
Fixed Size                  2086736 bytes
Variable Size             104859824 bytes
Database Buffers           46137344 bytes
Redo Buffers                5619712 bytes
Database mounted.
Database opened.
SQL>
 
Then when I logged in with my user, it could SELECT from DBA_TABLES and EXEC SYS.DBMS_LOCK.SLEEP:
 
SQL> conn andrew/reid
Connected.
SQL> select count(*) from dba_tables
  2  /
 
  COUNT(*)
----------
      1021
 
SQL> exec sys.dbms_lock.sleep(10);
 
PL/SQL procedure successfully completed.
 
SQL>

Thursday, October 22, 2015

db_writer_processes

I wrote the first part of this example in 2012.
 
The database writer copies data blocks from the buffer cache onto disk. The db_writer_processes initialization parameter determines how many processes will do this task. Its default value is 1 or cpu_count / 8, whichever is greater. I found an Oracle 9 database on a Tru64 server with cpu_count set to 1:
 
SQL> l
  1  select value from v$parameter
  2* where name = 'cpu_count'
SQL> /
 
VALUE
------------------------------
1
 
SQL>
 
The database used the default value for db_writer_processes, which Oracle had calculated as 1:
 
SQL> select value, isdefault
  2  from v$parameter
  3  where name = 'db_writer_processes'
  4  /
 
VALUE                          ISDEFAULT
------------------------------ ---------
1                              TRUE
 
SQL>
 
(However, since I first wrote this, I have found documentation suggesting that db_writer_processes always defaulted to 1 in Oracle 9 and took no notice of cpu_count.)
 
I looked for this process in the operating system as follows (I had to squash the ps output a bit to make it fit the screen):
 
UNIX > ps -ef|grep dbw|grep TEST9
oracle 250424 1 0.0 02:07:18 ?? 0:00.87 ora_dbw0_TEST9
UNIX >
 
I found an Oracle 11 database on a Solaris server with cpu_count set to 16:
 
SQL> select value from v$parameter
  2  where name = 'cpu_count'
  3  /
 
VALUE
------------------------------
16
 
SQL>
 
It also used the default value for db_writer_processes, which Oracle had calculated as 2:
 
SQL> select value, isdefault
  2  from v$parameter
  3  where name = 'db_writer_processes'
  4  /
 
VALUE                          ISDEFAULT
------------------------------ ---------
2                              TRUE
 
SQL>
 
When I found these processes in the operating system, I saw that Oracle had given them consecutive numbers i.e. dbw0 and dbw1 (the ps output was squashed again to fit the screen):
 
UNIX > ps -ef|grep dbw|grep PROD11
oracle 12230 1 0 19:42:36 ? 1:24 ora_dbw0_PROD11
oracle 12232 1 0 19:42:36 ? 1:21 ora_dbw1_PROD11
UNIX >
 
If you don’t have enough database writer processes, you can apparently have problems with free buffer waits. I searched all our production databases and could only find two with any of these at all. This was one of them:
 
  1  select time_waited from v$system_event
  2* where event = 'free buffer waits'
SQL> /
 
TIME_WAITED
-----------
71
 
SQL>
 
As this figure is given in hundredths of a second, I decided to do nothing about it.
 
I created an Oracle 11.2.0.4 database in 2015 and set db_writer_processes to 36:
 
SQL> alter system set db_writer_processes = 36
  2  scope = spfile
  3  /
 
System altered.
 
SQL>
 
Then I bounced the database (this is not shown). When I looked for the database writer processes in the operating system, I saw that Oracle had called them dbw0 through dbw9 then dbwa through dbwz:
 
UNIX > ps -ef|grep dbw|grep BECHEDV1
oracle 50594 50565 0 17:27:03 ? 0:00 ora_dbwh_BECHEDV1
oracle 50583 50565 0 17:27:02 ? 0:00 ora_dbw6_BECHEDV1
oracle 50578 50565 0 17:27:02 ? 0:00 ora_dbw1_BECHEDV1
oracle 50611 50565 0 17:27:03 ? 0:00 ora_dbww_BECHEDV1
oracle 50589 50565 0 17:27:02 ? 0:00 ora_dbwc_BECHEDV1
oracle 50593 50565 0 17:27:03 ? 0:00 ora_dbwg_BECHEDV1
oracle 50600 50565 0 17:27:03 ? 0:00 ora_dbwn_BECHEDV1
oracle 50615 50565 0 17:27:03 ? 0:00 ora_dbwz_BECHEDV1
oracle 50612 50565 0 17:27:03 ? 0:00 ora_dbwx_BECHEDV1
oracle 50582 50565 0 17:27:02 ? 0:00 ora_dbw5_BECHEDV1
oracle 50580 50565 0 17:27:02 ? 0:00 ora_dbw3_BECHEDV1
oracle 50608 50565 0 17:27:03 ? 0:00 ora_dbwt_BECHEDV1
oracle 50588 50565 0 17:27:02 ? 0:00 ora_dbwb_BECHEDV1
oracle 50610 50565 0 17:27:03 ? 0:00 ora_dbwv_BECHEDV1
oracle 50579 50565 0 17:27:02 ? 0:00 ora_dbw2_BECHEDV1
oracle 50602 50565 0 17:27:03 ? 0:00 ora_dbwp_BECHEDV1
oracle 50595 50565 0 17:27:03 ? 0:00 ora_dbwi_BECHEDV1
oracle 50614 50565 0 17:27:03 ? 0:00 ora_dbwy_BECHEDV1
oracle 50598 50565 0 17:27:03 ? 0:00 ora_dbwl_BECHEDV1
oracle 50609 50565 0 17:27:03 ? 0:00 ora_dbwu_BECHEDV1
oracle 50590 50565 0 17:27:03 ? 0:00 ora_dbwd_BECHEDV1
oracle 50581 50565 0 17:27:02 ? 0:00 ora_dbw4_BECHEDV1
oracle 50586 50565 0 17:27:02 ? 0:00 ora_dbw9_BECHEDV1
oracle 50596 50565 0 17:27:03 ? 0:00 ora_dbwj_BECHEDV1
oracle 50584 50565 0 17:27:02 ? 0:00 ora_dbw7_BECHEDV1
oracle 50599 50565 0 17:27:03 ? 0:00 ora_dbwm_BECHEDV1
oracle 50604 50565 0 17:27:03 ? 0:00 ora_dbwr_BECHEDV1
oracle 50587 50565 0 17:27:02 ? 0:00 ora_dbwa_BECHEDV1
oracle 50585 50565 0 17:27:02 ? 0:00 ora_dbw8_BECHEDV1
oracle 50591 50565 0 17:27:03 ? 0:00 ora_dbwe_BECHEDV1
oracle 50603 50565 0 17:27:03 ? 0:00 ora_dbwq_BECHEDV1
oracle 50601 50565 0 17:27:03 ? 0:00 ora_dbwo_BECHEDV1
oracle 50606 50565 0 17:27:03 ? 0:00 ora_dbws_BECHEDV1
oracle 50597 50565 0 17:27:03 ? 0:00 ora_dbwk_BECHEDV1
oracle 50592 50565 0 17:27:03 ? 0:00 ora_dbwf_BECHEDV1
oracle 50577 50565 0 17:27:02 ? 0:00 ora_dbw0_BECHEDV1
UNIX >
 
I read somewhere that the maximum value for db_writer_processes is 36 but Oracle allowed me to change it to 37:
 
SQL> alter system set db_writer_processes = 37
  2  scope = spfile
  3  /
 
System altered.
 
SQL>
 
Then I bounced the database again:
 
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area  521936896 bytes
Fixed Size                  2252448 bytes
Variable Size             306184544 bytes
Database Buffers          205520896 bytes
Redo Buffers                7979008 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
UNIX >
 
However, when I checked in the operating system, there were still only 36 database writer processes:
 
UNIX >  ps -ef|grep dbw|grep BECHEDV1|wc -l
      36
UNIX >
 
… and there was a message in the alert log telling me that db_writer_processes had been adjusted:
 
NOTE: db_writer_processes has been changed from 37 to  36ue to NUMA requirements.

Wednesday, October 21, 2015

LOGICAL_READS_PER_SESSION

One of the resources you can limit in a profile is called logical_reads_per_session. According to the Oracle documentation, it is used to:
Specify the permitted number of data blocks read in a session, including blocks read from memory and disk. I decided to try it out in an Oracle 11.1 database.
 
I created a profile called for_andrew, which would limit logical_reads_per_session to 10000:

SQL> conn / as sysdba
Connected.
SQL> create profile for_andrew
  2  limit logical_reads_per_session 10000
  3  /
 
Profile created.
 
SQL>

I set resource_limit to true so that Oracle would enforce the logical_reads_per_session limit:

SQL> alter system set resource_limit = true
  2  /
 
System altered.
 
SQL>

I created a user with the new profile:

SQL> create user andrew
  2  identified by reid
  3  profile for_andrew
  4  /
 
User created.
 
SQL> grant create session,
  2  select any dictionary to andrew
  3  /
 
Grant succeeded.
 
SQL>

I logged in with this user, ran some SQL against dba_tables and counted the session logical reads this had consumed. For the purposes of this simple demonstration, I ignored any overhead which the 2nd piece of SQL may have incurred:

SQL> conn andrew/reid
Connected.
SQL> select max(last_analyzed) from dba_tables
  2  /
 
MAX(LAST_
---------
18-OCT-15
 
SQL> select value from v$sesstat a, v$statname b
  2  where sid =
  3  (select distinct sid from v$mystat)
  4  and a.statistic# = b.statistic#
  5  and name = 'session logical reads'
  6  /
 
       VALUE
------------
        6936
 
SQL>

I logged in again ran some SQL against dba_indexes and counted the session logical reads this had consumed. Again, I ignored any overhead which may have been incurred by checking the session logical reads figure in v$sesstat:

SQL> conn andrew/reid
Connected.
SQL> select max(last_analyzed) from dba_indexes
  2  /
 
MAX(LAST_
---------
18-OCT-15
 
SQL> select value from v$sesstat a, v$statname b
  2  where sid =
  3  (select distinct sid from v$mystat)
  4  and a.statistic# = b.statistic#
  5  and name = 'session logical reads'
  6  /
 
       VALUE
------------
        7341
 
SQL>

The 2 statements together used over 14000 session logical reads. I guessed that if I tried to run them in the same session, this would exceed the limit set by the logical_reads_per_session parameter in the user’s profile. I logged in again and tried to do this. As expected, the 1st SQL worked but the 2nd failed with an ORA-02394:

SQL> conn andrew/reid
Connected.
SQL> select max(last_analyzed) from dba_tables
  2  /
 
MAX(LAST_
---------
18-OCT-15
 
SQL> select max(last_analyzed) from dba_indexes
  2  /
select max(last_analyzed) from dba_indexes
                               *
ERROR at line 1:
ORA-02394: exceeded session limit on IO usage, you are
being logged off
 
SQL>