Users to roles and system privileges

 

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

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

System privileges to roles and users

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

Enterprise Manager Grid Control ( OMS Problem )

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.

 

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;

Data Guard Diagnostic Scripts

Page moved to ,

http://sysdbaonline.com/?p=349

NetApp Too many users logged in! Please try again later.

Page moved to,

http://sysdbaonline.com/?p=348