Saturday 27 August 2011

Brief on Oracle Cursors(Implicit,Explicit and Where Current of Feature)


In today’s article I will discuss two securities issues :

A) How to prevent user with ALTER USER privileges from changing password of sys and system.
B) How to prevent the user to alter his/her password.


I'll start with discussing “How to prevent user with alter user privileges from changing password of sys and system

If suppose one of the users in Db has “ALTER USER” privilege and he is misusing it by changing the passwords of another USER then, is it possible to stop a user from changing SYS/SYTEM password. This is an issue as the system privilege ALTER USER allows password changes of any user.

One way is to revoke “ALTER USER” grant.But it's always better to take preventive measures.

For this we have a native way. Here our PL-SQL code will come in handy.

I came up with the idea of a DDL trigger that fires on the use of an ALTER USER command and then blocks the user.

First I need to create my test user.

Login as SYS user:

SQL> create user test identified by test;

User created.

SQL> grant connect to test;

Grant succeeded.

SQL> grant alter user to test;

Grant succeeded.

SQL> alter user system identified by manager;

User altered.

SQL> show user
USER is "TEST"

Now I will create a trigger “STOP_ALTER_PASS” in SYS schema on test user (TEST schema has “ALTER USER” privilege)

Login as SYS user:

SQL> CREATE or REPLACE TRIGGER STOP_ALTER_PASS
BEFORE ALTER on test.schema
BEGIN
IF ora_sysevent='ALTER' and ora_dict_obj_type ='USER' and
(ora_dict_obj_name = 'SYSTEM' or ora_dict_obj_name = 'SYS')
THEN
RAISE_APPLICATION_ERROR(-20003,
'You Are Not Allowed To Alter SYSTEM/SYS User');
END IF;
END;
/

Note: “ora_sysevent” is the way you can handle the “SYSTEM EVENT”. When a trigger is fired, you can retrieve certain attributes about the event that fired the trigger. Each attribute is retrieved by a function call. To make these attributes available, you must first run the CATPROC.SQL script.

let's check  the robustness of the above trigger.

SQL> CONN TEST/TEST
Connected.

SQL> alter user system identified by manager;
alter user system identified by manager
                                *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1  
ORA-20003: You are not allowed to alter SYSTEM/SYS user.
ORA-06512: at line 5


SQL> show user
USER is "TEST"

So now it is possible to stop a user from changing SYS/SYTEM password using our native code.

 But,

B) What about if someone asks you to prevent user to alter his/her own password?

For this activity I will use Oracle’s Profile and PASSWORD_VERIFY_FUNCTION.

PASSWORD_VERIFY_FUNCTION: There is a default password verify function under $ORACLE_HOME/rdbms/admin with filename utlpwdmg.sql. This script creates a password verify function named "verify_function"

PASSWORD_VERIFY_FUNCTION verify_function; (Usage of Function) 

I will create my own function and refer it to PASSWORD_VERIFY_FUNCTION profile as a parameter.

So going ahead with above info, first I will revoke the alter user privilege from TEST schema.

SQL> revoke alter user from test;

Revoke succeeded.

Then I create password verify funtion
CREATE OR REPLACE FUNCTION verify_passwd (
username VARCHAR2, password VARCHAR2
, old_password VARCHAR2) RETURN boolean IS
BEGIN
raise_application_error(-20009, 'ERROR: Password cannot be changed');
END;
/
SQL> CREATE PROFILE testpwd LIMIT PASSWORD_VERIFY_FUNCTION verify_passwd;



SQL>
 alter user test profile testpwd;

SQL>
 conn test/test

I will try to change my own password as a test user

SQL> alter user test identified by test;
alter user test identified by test
*
ERROR at line 1:
ORA-28221: REPLACE not specified

SQL>
 alter user test identified by test replace test; ------------------ Why replace is used?? If you don’t have alter user privilege then you have to use 'replace' keyword to change your password(In case password verify function is on).

SQL> alter user test identified by test replace test;
 

alter user test identified by test replace test
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20009: ERROR: Password cannot be changed

SQL> show user
USER is "TEST"


This brings us to end of the topic .Hope you enjoy it. What I have written is purely my understanding so if you find any mistakes please correct me through your comments

Can Session PGA exceed PGA target??



Frequently asked questions: How and what will be amount of RAM used by my session (PGA)?? Can Session PGA can exceed PGA target??

PGA_AGGREGATE_TARGET: Its' a goal of an upper limit.

1)A serial session: Will use a small % of pga target area, about 5% or less. so for e.g. If u have set pga target to 100MB one would expect to use no more than about 5MB per work area(Sort/Hash)

As the workload on server goes up(More concurrent queries, concurrent users) the amount of PGA memory allocated to one session goes down. The Db will try to keep the sum of all PGA under the threshold.

2)A parallel query by theory may use up to 30% of Pga target.


As per theory we can use PGA target to control overall amount of PGA memory used by the instance. The Instance will attempt to stay within the bounds of PGA target, but if it can not, it will not stop processing. It will be forced to exceed the threshold.

SQL> set autotrace off
SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      vineet
SQL>



SQL> sho parameter pga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 194M

 For proving it i am creating a package.


Package created.

SQL>CREATE OR REPLACE package demo_pkg
 as type array is table of char(2000) index by binary_integer;
g_data array;
end;
/
Package created.

Now we'll measure the amount of memory our session is currently using in PGA/UGA





SQL> select a.name,to_char(b.value,'999,999,999')value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.name like '%ga memory%'
  2  ;

NAME                                              VALUE
---------------------------------------------------------------- 
session uga memory                                1,282,380
session uga memory max                            1,282,380
session pga memory                                2,088,532
session pga memory max                            2,088,532---->2 MB



So our session is using 2 MB of memory.Now we'll run a query against BIG_TABLE

SQL> set autotrace traceonly statistics;
SQL> select * from big_table order by 1,2,3,4;

50863 rows selected.


Statistics
----------------------------------------------------------
        406  recursive calls
          0  db block gets
        754  consistent gets
        700  physical reads
          0  redo size
    3631364  bytes sent via SQL*Net to client
      37686  bytes received via SQL*Net from client
       3392  SQL*Net roundtrips to/from client
          3  sorts (memory)-----------------------Memory Sort
          0  sorts (disk)-------------------------Not used any Temp segment
      50863  rows processed

As one can easily see that sort was done in memory

We again fire our statitsics query to find out the value our session is taking

SQL> set autotrace off
SQL> select a.name,to_char(b.value,'999,999,999')value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.nam
e like '%ga memory%';

NAME                                             VALUE
----------------------------------------------------------------
session uga memory                              1,282,380
session uga memory max                          6,571,832
session pga memory                              2,088,532
session pga memory                       7,462,484->  Memory Increased




SQL> begin
  2  for i in 1..100000
  3  loop
  4  demo_pkg.g_data(i):='x';
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select a.name,to_char(b.value,'999,999,999')value from v$statname a,v$mystat b where a.statistic#=b.statistic# and a.nam
e like '%ga memory%';

NAME                                    VALUE
---------------------------------------------------------------- 
session uga memory                     313,021,948
session uga memory max                 313,021,948
session pga memory                     313,843,284
session pga memory                     313,843,284-> Here one can refer it exceeded PGA Target 194MB


We already exceeded PGA that Db itself can’t control. But Oracle Db is aware of what we have done. It does not ignore the memory it can not control. Rather it recognizes that the memory is being used. So if we re run the same query we see that this time we sorted to disk.

SQL> set autotrace traceonly statistics;
SQL> select * from big_table order by 1,2,3,4;

50863 rows selected.


Statistics
----------------------------------------------------------
          6  recursive calls
          4  db block gets
        702  consistent gets
        705  physical reads
          0  redo size
    3631364  bytes sent via SQL*Net to client
      37686  bytes received via SQL*Net from client
       3392  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)-------------------------See here sorting is on disk now
      50863  rows processed


So beacuse some PGA memory is outside of oracle's control,it is easy for us to exceed the PGA target..
SO PGA is not a hard limit!! 



Enjoy Learning!!



Does Select Statement Generates Redo???


Surprising to many people, but “In some cases select statement generates redo too?? “

Background:

I was working on one scenario regarding Oracle Statistics (Tracing statistics like sorts, consistent gets etc). So I created one table


Please Note for testing purpose:  Db Block Size is 8k
                                                Buffer Cache Size is 4M


SQL> create table test(x char(2000),y char(2000),z char(2000));

Every row will be 6k so it means only one row in one block.
 
Then for checking the statistics I fired auto-trace command as stated below

SQL> set autotrace traceonly statistics

Then I inserted the 10000 rows.

SQL> insert into test select 'x','y','z' from all_objects where rownum<=10000;

10000 rows created.


Statistics
----------------------------------------------------------
       2274  recursive calls
      46503  db block gets
      12033  consistent gets
          7  physical reads
   66050648  redo size
        821  bytes sent via SQL*Net to client
        822  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed


Then I finally commit the data.

SQL> commit;

Commit complete.


SQL> select * from test; 

10000 rows selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      13669  consistent gets
          0  physical reads
     263680  redo size--------------------à“263680  redo size” 
 60514555  bytes sent via SQL*Net to client
       7846  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed


So what I see here is only statistics which looks ok to me. But after double checking above result, I found “263680 redo size”. This is suprising

 What went wrong as I am the only user in Db.

So fired the select statement again

SQL> select * from test ;

10000 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      10008  consistent gets
          0  physical reads
          0  redo size
   60514555  bytes sent via SQL*Net to client
       7846  bytes received via SQL*Net from client
        668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

Redo size is again 0(0 redo size)


 I took a step ahead to find” Why Select Statement generates redo??” I reached one of the internals of Oracle” Block Cleanout”


What is Block Cleanout and Why select statement generated redo: Whenever one fire any dml then oracle automatically acquire lock. In oracle data locks are actually the attributes of the data and they are stored in block header. The transaction will simply go to the data and locks it( if it is not locked). When a row is locked by a transaction, that information is placed in the block header where the row is located. When another transaction wishes to acquire the lock on the same row, it has to travel to the block containing the row anyway, and upon reaching there, it can easily identify that the row is locked, from the block header.
So what portion of the block header contains information on locking? It is a simple data structure called Interested Transaction List (ITL), a linked list data structure that maintains information on transaction address and row-id. What basically it contains is “TRANSACTION ID” which is unique to transactions and represents roll back segment number, slot number, sequence number.

 


When the transaction is released ID is left behind. So whenever another session comes to lock the same block which was released earlier checks this ID and see the status whether the transaction is active or not. If not then lock it else wait.

In order to reach our subject we need to understand one of the step of COMMIT?? 

One of the steps of COMMIT is to revisit our blocks if they are still in SGA, if they are accessible (no one modifying them), and then clean out the transaction information known as COMMIT CLEANOUT. Database Commit can clean out the blocks so that “select” which is just for reading will have not to clean it out.


***In Oracle---commit is related with commit list which is 20 blocks long and Oracle will allocate as many as of these list till it needs up to a point. If the sum of the blocks we modify exceeds 10% of the buffer cache size. *** For e.g. ------if blocks in cache is 5000 so oracle will maintain list of 500 blocks. Upon commit, oracle will process 500 blocks for clean out( remove transaction and lock info) and if the blocks are more than this then commit will delayed it and skips it(doesn’t clean them).

In this case when any other session comes say a ‘select’ statement on the same blocks then it will check the block header for all the blocks and tries to clean out the transaction and lock info if present: and if transaction information is still there (but not active) then it will generate redo information (because block become dirty as select statement is cleaning info from the blocks)

So from above its clear select statement generated redo’s (sometimes).

Do remember if blocks are perfectly inside 10% of buffer cache (as commit cleanout says) then there will be no delayed cleanout i.e. no redo generation

See below

SQL> set autotrace traceonly statistics

SQL> insert into test select 'x','y','z' from all_objects where rownum<=500;

500 rows created.


Statistics
----------------------------------------------------------
       2155  recursive calls
       4863  db block gets
       1755  consistent gets
          5  physical reads
    6615428  redo size
        830  bytes sent via SQL*Net to client
        821  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       500  rows processed

SQL> commit;

Commit complete.

SQL> select * from test;

500 rows selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1005  consistent gets
          0  physical reads
          0  redo size----------   (No redo generated. Commit already clean the information)
    6051955  bytes sent via SQL*Net to client
       1246  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       500  rows processed