Difference between revisions of "Handy scripts"
From dbawiki
| Line 65: | Line 65: | ||
AND username != 'SYS') | AND username != 'SYS') | ||
OR grantee='PUBLIC' | OR grantee='PUBLIC' | ||
| + | |||
| + | ===A database overview using the sys.plato package=== | ||
| + | exec plato.help; | ||
| + | or | ||
| + | exec plato.complete('HTM'); | ||
==shell== | ==shell== | ||
Revision as of 12:54, 5 September 2012
Contents
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'
A database overview using the sys.plato package
exec plato.help;
or
exec plato.complete('HTM');
