Difference between revisions of "Handy scripts"

Revision as of 12:50, 5 September 2012

SQL

• Database Overview - from idevelopment online version
• Connecting as another user via proxy in SQL*Plus
• Password cracker and role and priv tools from Pete Finnigan

DBA privs tables

DBA_AQ_AGENT_PRIVS
DBA_COL_PRIVS
DBA_PRIV_AUDIT_OPTS
DBA_REPGROUP_PRIVILEGES
DBA_ROLE_PRIVS
DBA_RSRC_CONSUMER_GROUP_PRIVS
DBA_RSRC_MANAGER_SYSTEM_PRIVS
DBA_SYS_PRIVS
DBA_TAB_PRIVS
DBA_WM_SYS_PRIVS
DBA_WORKSPACE_PRIVS

badprivs.sql

select grantee, privilege, admin_option
from   sys.dba_sys_privs
where  (privilege like '% ANY %'
or     privilege in ('BECOME USER', 'UNLIMITED TABLESPACE')
or     admin_option = 'YES')
and    grantee not in ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
                      'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
                      'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
                      'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
                      'TIMESERIES_DBA')

This query will list the system privileges assigned to a user

SELECT LPAD(' ', 2*level) || granted_role "USER PRIVS"
FROM (
  SELECT NULL grantee,  username granted_role
  FROM dba_users
  WHERE username LIKE UPPER('%&uname%')
  UNION
  SELECT grantee, granted_role
  FROM dba_role_privs
  UNION
  SELECT grantee, privilege
  FROM dba_sys_privs)
START WITH grantee IS NULL
CONNECT BY grantee = prior granted_role;

or

SELECT path
FROM (
  SELECT grantee,
         sys_connect_by_path(privilege, ':')||':'||grantee path
  FROM (
    SELECT grantee, privilege, 0 role
    FROM dba_sys_privs
    UNION ALL
    SELECT grantee, granted_role, 1 role
    FROM dba_role_privs)
  CONNECT BY privilege=prior grantee
  START WITH role = 0)
WHERE grantee IN (
   SELECT username
   FROM dba_users
   WHERE lock_date IS NULL
   AND password != 'EXTERNAL'
   AND username != 'SYS')
OR grantee='PUBLIC'

shell

cmd

• Database Export - from DBA Oracle (Burleson)