Tuesday, March 1, 2011

Check Database User privileges and Roles

You can check the particular user privileges and roles (system and object) by the use of following script.
This script gives all the privileges and roles granted to a particular user by setting usercheck environment.

set lines 110 pages 1000 ver off
col role for a16
col pv for a75 hea 'PRIVILEGE OR ROLE'
bre on role on type skip 1

define usercheck = 'USERNAME'

select grantee, 'ROL' type, granted_role pv
from dba_role_privs where grantee = '&usercheck' union
select grantee, 'PRV' type, privilege pv
from dba_sys_privs where grantee = '&usercheck' union
select grantee, 'OBJ' type,
max(decode(privilege,'INSERT',',INSERT'))||' ON '||object_type||' "'||a.owner||'.'||table_name||'"' pv
from dba_tab_privs a, dba_objects b
where a.owner=b.owner and a.table_name = b.object_name and a.grantee='&usercheck'
group by a.owner,table_name,object_type,grantee union
select username grantee, '---' type, 'empty user ---' pv from dba_users
where not username in (select distinct grantee from dba_role_privs) and
not username in (select distinct grantee from dba_sys_privs) and
not username in (select distinct grantee from dba_tab_privs) and username like '%&usercheck%'
group by username
order by grantee, type, pv;


  1. really excellent work .appreciate for the efforts u always put in ...great going ..move on ..

    select sum(getmisses)/sum(gets)"miss ratio" from v$rowcache;

    miss ratio
    select sum(getmisses)/sum(gets)"Miss ratio" from v$rowcache;

    Miss ratio
    select sum(getmisses)/sum(gets)"Miss ratio" from v$rowcache;

    Miss ratio


    three different values in three dbs can u tell why and what .......? mail me

  2. Your databases are fine at this point. All the 3 are far below 15%.

    Oracle places the data in cache before it does any operation on those datas (update/retrieve) and gives the o/p for easy retrieval of the same data when accessed again. Also oracle places the whole data block in the cache rather than the single data(record) alone. So when data is accessed from the same table and does not find the data in the data block which it has placed in cache of that particular table, this is called cache miss. Hence it has to place a new data block which contains the required data which is called as cache gets. Also cache has to be tuned enough to get the records from the cache to avoid high i/o which will become bottleneck for performance.

    Hope this helps you.
    You may correct me if I miss something.


I don't know what you think about my creation, but every words of you help me grow better and stronger!!