Check user, roles and privileges

Below queries will help you check details regarding different roles and privileges granted to Oracle database users.

Roles granted to user

Query to check the granted roles to a user

SELECT * 
 FROM DBA_ROLE_PRIVS 
 WHERE GRANTEE = '&USER';

Privileges granted to user

Query to check privileges granted to a user

SELECT * 
 FROM DBA_TAB_PRIVS 
 WHERE GRANTEE = 'USER';

Privileges granted to role

Privileges granted to a role which is granted to a user

SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN 
(SELECT granted_role FROM DBA_ROLE_PRIVS WHERE GRANTEE = '&USER') order by 3;

System privileges granted to user

Query to check if user is having system privileges

SELECT * 
 FROM DBA_SYS_PRIVS 
 WHERE GRANTEE = '&USER';

Permissions granted to role

Query to check permissions granted to a role

select * from ROLE_ROLE_PRIVS where ROLE = '&ROLE_NAME';
select * from ROLE_TAB_PRIVS where ROLE = '&ROLE_NAME';
select * from ROLE_SYS_PRIVS where ROLE = '&ROLE_NAME';

Was this article helpful?

Related Articles

Comments

  1. #Check SYNC between Primary & Standby

    ps -ef|grep pmon

    export ORACLE_SID=

    sqlplus / as sysdba

    select name,open_mode from v$database;

    show parameter service;

    –In primary
    —————
    alter system switch logfile;

    archive log list;

    –Check the database sync:

    –In standby
    —————

    archive log list;

    select max(SEQUENCE#) from v$archived_log where applied = ‘YES’;

    –checking errors for standby sync:
    —————————————————–

    column “Error” FORMAT A40
    column “Archive_dest” Format A30
    column DB_UNIQUE_NAME format A20
    column VALID_NOW format A19
    column VALID_TYPE format A15
    column VALID_ROLE format A15
    set linesize 180
    SELECT DEST_ID “ID”, STATUS “DB_status”, DESTINATION “Archive_dest”, ERROR “Error”, DB_UNIQUE_NAME, VALID_NOW , VALID_TYPE, VALID_ROLE FROM V$ARCHIVE_DEST WHERE DESTINATION is not null;

    #Tablespace steps

    set linesize 200;
    col “Tablespace” for a22
    col “Used GB” for 99,999.99
    col “Free GB” for 99,999.99
    col “Total GB” for 99,999.99
    col “Used Percent%” for 99,999.99
    col “Free Percent%” for 99,999.99

    select df.tablespace_name “Tablespace”,df.totalspace “Total GB”,
    totalusedspace “Used GB”, (df.totalspace – tu.totalusedspace) “Free GB”,
    100 * (tu.totalusedspace/ df.totalspace) “Used Percent%”,
    100 * ( (df.totalspace – tu.totalusedspace)/ df.totalspace) “Free Percent%”
    from
    (select tablespace_name,sum(bytes) / 1048576/1024 TotalSpace from dba_data_files
    group by tablespace_name) df,
    (select sum(bytes)/(1024*1024*1024) totalusedspace, tablespace_name from dba_segments
    group by tablespace_name) tu
    where df.tablespace_name = tu.tablespace_name
    UNION
    select TABLESPACE_NAME “Tablespace”, (BYTES_USED+BYTES_FREE)/1024/1024/1024 “Total GB”,
    BYTES_USED/1024/1024/1024 “Used GB”,BYTES_FREE/1024/1024/1024 “Free GB”,
    100*BYTES_USED/(BYTES_USED+BYTES_FREE) “Used Percent%”,
    100*BYTES_FREE/(BYTES_USED+BYTES_FREE) “Free Percent%”
    from V$TEMP_SPACE_HEADER order by “Used Percent%”;

    #Session connected to a DB

    set lines 200
    set pages 2000
    set echo off
    set head on
    set feedback on
    col sid head “Sid” form 9999 trunc
    col serial# form 99999 trunc head “Ser#”
    col username form a8 trunc
    col osuser form a20 trunc
    col machine form a15 trunc head “Client|Machine”
    col program form a15 trunc head “Client|Program”
    col login form a11
    col “last call” form 9999999 trunc head “Last Call|In Secs”
    col status form a6 trunc
    select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser,substr(program||module,1,15) program,
    substr(machine,1,22) machine,to_char(logon_time,’ddMon hh24:mi’) login,
    last_call_et “last call”,status,sql_hash_value from v$session where username is not null;

    #Object count in Schema ABC

    select object_type,count(*) from dba_objects where owner=’ABC’ GROUP BY object_type;

    #DB size

    select db_name, data_gb+temp_gb db_size_gb, used_gb from
    (select sum(bytes/1024/1024/1024) data_gb from dba_data_files) df,
    (select sum(bytes/1024/1024/1024) temp_gb from dba_temp_files ) tf,
    (select sum(bytes/1024/1024/1024) used_gb from dba_segments) ds,
    (select name db_name from v$database) db;

    #temp tablespace

    SELECT A.tablespace_name tablespace, D.mb_total,
    SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
    D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
    FROM v$sort_segment A,
    (
    SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
    FROM v$tablespace B, v$tempfile C
    WHERE B.ts#= C.ts#
    GROUP BY B.name, C.block_size
    ) D
    WHERE A.tablespace_name = D.name
    GROUP by A.tablespace_name, D.mb_total;
    ——————————————————————————————————————-
    select TABLESPACE_NAME,BYTES_USED/1024/1024 “used_mb”,BYTES_FREE/1024/1024 “free_mb” from v$temp_space_header;

    #clearing audit or trace files

    #Clearing Audit/trace files in OS level:

    find *.aud -mtime +30 -exec rm {} \;

    Will delete upto 30 days

    #zip files older than x days in current location

    find . -mtime +x -print -exec gzip {} \;

    #delete files ending with “.arc “older than x days in /abc/xyz/123 location

    find /abc/xyz/123/*.arc.Z -mtime +7 -exec rm {} \;

    #swicth db to archive log mode

    ps -ef|grep pmon

    export ORACLE_SID=

    sqlplus / as sysdba

    select name,open_mode,log_mode from v$database;

    show parameter service;

    — put a database in archivelog mode
    ————————————–

    shutdown immediate;

    startup mount;

    alter database archivelog;

    alter database open;

    — put a database in noarchivelog mode
    —————————————-

    shutdown immediate;

    startup mount;

    alter database noarchivelog;

    alter database open;

    **FOLLOWING ARE THE STEPS TO BE FOLLOWED TO PERFORM A SWITCH OVER FROM PRIMARY TO STANDBY**

    –Switch over Activity
    ————————

    —In Primary (say server 1)

    select switchover_status from v$database;

    alter database commit to switchover to physical standby with session shutdown;

    startup nomount;

    alter database mount standby database;

    —In standby (say server 2)

    select switchover_status from v$database;

    alter database commit to switchover to primary;

    shutdown immediate;

    startup;

    —In new standby ( server 1 -old primary)

    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Leave a Comment