Dev's Weblog

Reading time: 3 - 4 minutes

A

This is a script that shows the hierarchical relationship between system privileges, roles and users.

select
A lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
A (
A /* THE USERS */
AAA select
AAAAA nullAAAA grantee,
AAAAA username granted_role
AAA from
AAAAA dba_users
AAA where
AAAAA username like upper('%&enter_username%')
A /* THE ROLES TO ROLES RELATIONS */
A union
AAA select
AAAAA grantee,
AAAAA granted_role
AAA from
AAAAA dba_role_privs
A /* THE ROLES TO PRIVILEGE RELATIONS */
A union
AAA select
AAAAA grantee,
AAAAA privilege
AAA from
AAAAA dba_sys_privs
A )
start with grantee is null
connect by grantee = prior granted_role;

System privileges to roles and users

A

This is also possible the other way round: showing the system privileges in relation to roles that have been granted this privilege and users that have been granted either this privilege or a role:

select
  lpad(' ', 2*level) || c "Privilege, Roles and Users"
from
  (
  /* THE PRIVILEGES */
    select
      null   p,
      name   c
    from
      system_privilege_map
    where
      name like upper('%&enter_privliege%')
  /* THE ROLES TO ROLES RELATIONS */ 
  union
    select
      granted_role  p,
      grantee       c
    from
      dba_role_privs
  /* THE ROLES TO PRIVILEGE RELATIONS */ 
  union
    select
      privilege     p,
      grantee       c
    from
      dba_sys_privs
  )
start with p is null
connect by p = prior c;

Object privileges

select
  case when level = 1 then own || '.' || obj || ' (' || typ || ')' else
  lpad (' ', 2*(level-1)) || obj || nvl2 (typ, ' (' || typ || ')', null)
  end
from
  (
  /* THE OBJECTS */
    select
      null          p1,
      null          p2,
      object_name   obj,
      owner         own,
      object_type   typ
    from
      dba_objects
    where
       owner not in
        ('SYS', 'SYSTEM', 'WMSYS', 'SYSMAN','MDSYS','ORDSYS','XDB', 'WKSYS', 'EXFSYS',
         'OLAPSYS', 'DBSNMP', 'DMSYS','CTXSYS','WK_TEST', 'ORDPLUGINS', 'OUTLN')
      and object_type not in ('SYNONYM', 'INDEX')
  /* THE OBJECT TO PRIVILEGE RELATIONS */
  union
    select
      table_name p1,
      owner      p2,
      grantee,
      grantee,
      privilege
    from
      dba_tab_privs
  /* THE ROLES TO ROLES/USERS RELATIONS */
  union
    select
      granted_role  p1,
      granted_role  p2,
      grantee,
      grantee,
      null
    from
      dba_role_privs
  )
start with p1 is null and p2 is null
connect by p1 = prior obj and p2 = prior own;

Reference :

http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html

Reading time: 1 - 2 minutes

I were stuck with OMS configuration while installing EMGC 10.2.0.1.

I checked in $OMS_HOME/sysman/log directory but couldn't find anything.

There was no latest file in $OMS_HOME/sysman/log which proved that OMS configuration is hanging.

I looked into $OMS_HOME/cfgtoollog/cfgfw/CfmLogger_2008-12-11_11-02-50-AM.log file and found that the last step was dropping SYSMAN used and it never completed it ,so i followed the followinf steps and then OMS configuration succeeded.

A

1. EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
2. EXEC sysman.setEMUserContext('',5);
3. REVOKE dba FROM sysman;
4. DECLARE
   CURSOR c1 IS
   SELECT owner, synonym_name name
   FROM dba_synonyms
   WHERE table_owner = 'SYSMAN'
   ;
   BEGIN
   FOR r1 IN c1 LOOP
   IF r1.owner = 'PUBLIC' THEN
   EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
   ELSE
   EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
   END IF;
   END LOOP;
   END;
5. DROP USER mgmt_view CASCADE;
6. DROP ROLE mgmt_user;
7. DROP USER sysman CASCADE;
8. drop public synonym MGMT_TARGET_BLACKOUTS;
9. drop public synonym SETEMVIEWUSERCONTEXT;

Reading time: < 1 minute

Script to Collect Data Guard Primary Site Diagnostic Information
Note:241374.1

Script to Collect Data Guard Physical Standby Diagnostic Information
Note:241438.1

Script to Collect Data Guard Logical Standby Table Information
Note:269954.1

Reading time: < 1 minute

A

Too many users logged in! Please try again later. This is the wonderful greeting you get from a NetApp or StoreVault device when another user is logged in to the console. Unfortunately for me, I frequently lose connection or close my RDP window without thinking about it and I used to just wait for the timeout before trying to login again.

You can just execute the following command to remotely logout the user and free up the session.

rsh <hostname> -l root:<password> logout telnet