/*
############################################################
# Auditor Name : Vikram Krishnan
# Audit Date : 3/31/2014
# Client Name :
# Description : Please type in a Description.
############################################################
*/
set echo on
REM **************************************************************************
REM Ernst and Young LLP UNIX Audit Script
REM Copyright - 2005
REM Updated by S. Rohit (rohit.iyer@sg.ey.com) 29Sept2005- fixed some bugs in code
REM Updated by Chad Woolf 25Aug2005- Added usability references, labels, and more associations
REM Updated by Peter Morin 29Apr2005 - Added HTML support for 8i
REM Updated by Peter Morin 15Mar2005 - Added MARKUP for HTML output
REM **************************************************************************
set echo off
set termout on
set heading on
set feedback off
set trimspool on
set linesize 200
set pagesize 200
set markup html on spool on
Spool EY_OracleDBDump.html
prompt SYS.ALL_TRIGGERS
prompt select all from sys.all_triggers table
prompt
prompt Control: Promote User Permissions
prompt Control: Triggers
select * from sys.all_triggers;
prompt AUDITING
prompt Control: Database Links
prompt Control: Log Application Account Activity
prompt Control: Log Failed Attempts to Access Objects
prompt Control: Log Failed Login Attempts
prompt Control: Log Firecall Accounts
prompt Control: Log SYSTEM Account Activity
prompt Control: Log Vendor Actions
prompt Control: Monitor Administrator Activities
prompt Control: Technical Review/Change Control Procedures
prompt Control: Log Failed Attempts to Issue Sensitive Commands
prompt Control: Privileged Account Review
prompt
prompt Display sys.dba_obj_audit_opts table
desc sys.dba_obj_audit_opts
prompt
prompt Display ins, upd, del from sys.dba_obj_audit_opts
prompt where owner = 'table owner' and object_name = 'tablename'
Select ins, upd, del
from sys.dba_obj_audit_opts
where owner = 'table owner' and object_name = 'tablename';
prompt
prompt Display sys.dba_stmt_audit_opts
prompt where audit_option='CREATE SESSION'
select *
from sys.dba_stmt_audit_opts
where audit_option='CREATE SESSION';
prompt
prompt Display sys.dba_stmt_audit_opts table
prompt where user_name includes 'DBA"
select substr(user_name,1,15) "User",
substr(audit_option,1,30) "Audit Option",
success, failure
from sys.dba_stmt_audit_opts
where user_name in ('SYS', 'SYSTEM')
or user_name like '%DBA%'
order by 1;
prompt
prompt Display sys.dba_stmt_audit_opts table
select *
from sys.dba_stmt_audit_opts;
prompt
prompt Display sys.dba_obj_audit_opts table
prompt has restrictions (see script detail)
select *
from sys.dba_obj_audit_opts
where alt <> '-/-'
or aud <> '-/-'
or com <> '-/-'
or del <> '-/-'
or gra <> '-/-'
or ind <> '-/-'
or ins <> '-/-'
or loc <> '-/-'
or ren <> '-/-'
or sel <> '-/-'
or upd <> '-/-'
or ref <> '-/-'
or exe <> '-/-' ;
prompt
prompt Display sys.dba_obj_audit_opts table
prompt where object_name = 'LINK$'
select *
from sys.dba_obj_audit_opts
where object_name = 'LINK$';
prompt
prompt Display timestamp, name, returncode
prompt from the sys.aud$ and sys.audit_actions tables
prompt where userid includes "DBA"
select timestamp#, name, returncode
from sys.aud$ a, sys.audit_actions b
where (userid in ('SYS', 'SYSTEM')
or userid like '%DBA%')
and a.action# = b.action;
prompt DBA_COL_PRIVS
prompt Control: Database Links
prompt Control: Privilege Management
prompt
prompt Display dba_col_privs table
select * from dba_col_privs;
prompt CONFIG - Output of Oracle Configuration Information
prompt Control: Adherence to Licensing Agreements
prompt Control: Archive log files
prompt Control: Auditing
prompt Control: Idle Session Timeout
prompt Control: Permissions on Control files
prompt Control: Restrict Ability To Connect
prompt Control: Trace files
prompt Control: Patch Maintenance
prompt
prompt Display V$VERSION table
select * from v$version;
prompt
prompt Display V$DATABASE table
select * from v$database;
prompt
prompt Display V$LICENSE table
select * from v$license;
prompt
prompt Display V$PARAMETER table
select * from v$parameter;
prompt
prompt Display V$CONTROLFILE table
select * from v$controlfile;
prompt
prompt Display V$PWFILE_USERS table
select * from v$pwfile_users;
prompt SYS.DBA_DB_LINKS
prompt Control: Database Links
prompt Control: Privilege Management
prompt
prompt Display select fields of the SYS.DBA_DB_LINKS table
SELECT OWNER, DB_LINK, USERNAME FROM SYS.DBA_DB_LINKS;
prompt DBA_JOBS
prompt Control: Job Scheduling Function
prompt
prompt Display dba_jobs table
select * from dba_jobs;
prompt
prompt Display dba_jobs_running table
select * from dba_jobs_running;
prompt DBA_OBJECTS_1
prompt Control: Production Objects
prompt
prompt Display select fields from dba_objects table
prompt where LAST_DDL_TIME is in the past 30 days
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME, CREATED FROM DBA_OBJECTS WHERE LAST_DDL_TIME > SYSDATE-30;
prompt DBA_PROFILES
prompt Control: Account Lockout
prompt Control: Idle Session Timeout
prompt Control: Password Composition
prompt Control: Password Expiration
prompt Control: Password History
prompt Control: Restrict Password Protected Roles
prompt Control: Concurrent Logins
prompt Control: Test Password Settings
prompt
prompt Display dba_profiles table
select * from dba_profiles;
prompt DBA_ROLE_PRIVS
prompt Control: Application Schema owner as DBA
prompt Control: Developer Roles
prompt Control: Developer Access to Production Environment
prompt Control: Restrict Role Functionality
prompt Control: Restrict WITH ADMIN Option
prompt Control: Role-based Privileges: Auditing
prompt Control: Role-based Privileges: Security Administration
prompt Control: Role-based Privileges: Process
prompt Control: Role-based Privileges: DBA
prompt Control: Role-based Privileges: Data Owner
prompt Control: CONNECT and RESOURCE role
prompt Control: PUBLIC Account Privileges
prompt Control: Restrict Password Protected Roles
prompt Control: Test Access to Privileged IT Functions
prompt Control: Test Access to Production Data
prompt Control: Test Logical Access Segregation of Duties
prompt
prompt Display dba_role_privs table
select *
from dba_role_privs
order by grantee, granted_role;
prompt DBA_ROLES
prompt Control: Restrict Role Functionality
prompt Control: Role-based Privileges: Auditing
prompt Control: Role-based Privileges: Security Administration
prompt Control: Role-based Privileges: Process
prompt Control: Role-based Privileges: Data Owner
prompt Control: Database Administration Segregation of Duties
prompt Control: Help Desk Database Security Function
prompt Control: Role-based Privileges: Administrator
prompt
prompt Display dba_roles table
select * from dba_roles;
prompt DBA_SEGMENTS_1
prompt Control: AUD$ Tablespace
prompt
prompt Display select fields from dba_segments table
prompt where tablespace_name contains 'SYSTEM' and segment_name contains 'AUD$'
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME
FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = '%SYSTEM%' AND SEGMENT_NAME = '?AUD$?';
prompt DBA_SEGMENTS_2
prompt Control: System Tablespace
prompt
prompt Display select fields from dba_segments table
prompt where tablespace_name includes 'SYSTEM' and owner does not include 'SYS'
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE
FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = '%SYSTEM%' AND OWNER <> '?SYS?';
prompt Control: Test for Access Assigned to PUBLIC Role
prompt Control: Test Access to Privileged IT Functions
prompt Control: Test Logical Access Segregation of Duties
prompt
prompt Display DBA_SYS_PRIVS table
prompt Fields: Grantee, Privilege, Admin_Option
SELECT substr(grantee,1,30) "Grantee",
substr(privilege,1,20) "Privilege",
substr(admin_option,1,3) "Admin_Option"
FROM DBA_SYS_PRIVS
WHERE
PRIVILEGE='CREATE USER' OR
PRIVILEGE='BECOME USER' OR
PRIVILEGE='ALTER USER' OR
PRIVILEGE='DROP USER' OR
PRIVILEGE='CREATE ROLE' OR
PRIVILEGE='ALTER ANY ROLE' OR
PRIVILEGE='DROP ANY ROLE' OR
PRIVILEGE='GRANT ANY ROLE' OR
PRIVILEGE='CREATE PROFILE' OR
PRIVILEGE='ALTER PROFILE' OR
PRIVILEGE='DROP PROFILE' OR
PRIVILEGE='CREATE ANY TABLE' OR
PRIVILEGE='ALTER ANY TABLE' OR
PRIVILEGE='DROP ANY TABLE' OR
PRIVILEGE='INSERT ANY TABLE' OR
PRIVILEGE='UPDATE ANY TABLE' OR
PRIVILEGE='DELETE ANY TABLE' OR
PRIVILEGE='CREATE ANY PROCEDURE' OR
PRIVILEGE='ALTER ANY PROCEDURE' OR
PRIVILEGE='DROP ANY PROCEDURE' OR
PRIVILEGE='CREATE ANY TRIGGER' OR
PRIVILEGE='ALTER ANY TRIGGER' OR
PRIVILEGE='DROP ANY TRIGGER' OR
PRIVILEGE='CREATE TABLESPACE' OR
PRIVILEGE='ALTER TABLESPACE' OR
PRIVILEGE='DROP TABLESPACES' OR
PRIVILEGE='ALTER DATABASE' OR
PRIVILEGE='ALTER SYSTEM';
prompt DBA_TAB_COLUMNS_1
prompt Control: Application Passwords
prompt
prompt Display select fields from dba_objects table, with restrictions (see script code)
prompt and object_name contains 'USER' (but
select object_name object, object_type typ, owner owner
from dba_objects where owner<>'SYS' and ((object_name like '%USER%' and object_name not like 'USER_%') or object_name like '%USR%' or object_name like '%PASSWD%' or object_name like '%PWD%'
or object_name like '%PASS%') and object_type in('VIEW','TABLE')
union select table_name object, column_name type, owner owner
from dba_tab_columns where owner<>'SYS' and (column_name like '%USER%'
or column_name like '%USR%' or column_name like '%PASSWD%'
or column_name like '%PWD%' or column_name like '%PASS%')
prompt DBA_TAB_PRIVS_11
prompt Control: Role-based Privileges: Operator
prompt
prompt Display sys.dba_tab_privs table
prompt where grantee does not exist in the sys.dba_roles table
SELECT GRANTEE, OWNER, TABLE_NAME FROM SYS.DBA_TAB_PRIVS WHERE GRANTEE NOT IN (SELECT ROLE FROM SYS.DBA_ROLES);
prompt DBA_TAB_PRIVS_12
prompt Control: Role-based Privileges: Developer
prompt
prompt Display sys.dba_tab_privs table
prompt where grantee is not in sys.dba_roles table
SELECT GRANTEE, OWNER, TABLE_NAME FROM SYS.DBA_TAB_PRIVS WHERE GRANTEE NOT IN (SELECT ROLE FROM SYS.DBA_ROLES);
prompt DBA_TAB_PRIVS_13
prompt Control: PUBLIC Account Privileges
prompt
prompt Display select fields from sys.dba_tab_privs table
prompt where grantee = PUBLIC
prompt
SELECT OWNER, TABLE_NAME, GRANTOR, PRIVILEGE FROM SYS.DBA_TAB_PRIVS WHERE GRANTEE = 'PUBLIC'
prompt DBA_TAB_PRIVS_2
prompt Control: ALL_USERS View
prompt
prompt Display select fields from dba_tab_privs table
prompt where table_name = 'ALL_USERS'
select grantee, privilege, table_name
from dba_tab_privs where table_name = 'ALL_USERS';
prompt DBA_TAB_PRIVS_4
prompt Control: Critical tables and views
prompt
prompt Display select fields from dba_tab_privs table
prompt where table_name contains 'USER$','DBA_USERS','LINK$','USER_HISTORY$'
SELECT GRANTEE, PRIVILEGE, TABLE_NAME FROM DBA_TAB_PRIVS WHERE TABLE_NAME IN ('USER$','DBA_USERS','LINK$','USER_HISTORY$');
prompt DBA_TAB_PRIVS_5
prompt Control: Database Links
prompt
prompt Display select fields from dba_tab_privs table
prompt where owner = 'SYS' and table_name = 'LINK$'
SELECT GRANTEE, PRIVILEGE FROM SYS.DBA_TAB_PRIVS WHERE OWNER = 'SYS' AND TABLE_NAME = 'LINK$';
prompt DBA_TAB_PRIVS_6
prompt Control: DBA Role views
prompt
prompt Display select fields from dba_tab_privs table
prompt where table_name contains 'DBA_ROLES', 'DBA_SYS_PRIVS', 'DBA_ROLE_PRIVS', 'ROLE_ROLE_PRIVS'
SELECT GRANTEE, PRIVILEGE, TABLE_NAME FROM DBA_TAB_PRIVS WHERE TABLE_NAME IN ('DBA_ROLES','DBA_SYS_PRIVS','DBA_ROLE_PRIVS','ROLE_ROLE_PRIVS');
prompt DBA_TAB_PRIVS_7
prompt Control: Development and Test databases
prompt Control: Embedded Account Information (Links)
prompt
prompt Display select fields from dba_tab_privs table
prompt where table_name is 'DBA_ROLES', 'DBA_SYS_PRIVS', 'DBA_ROLE_PRIVS', or 'ROLE_ROLE_PRIVS'
SELECT GRANTEE, PRIVILEGE, TABLE_NAME FROM DBA_TAB_PRIVS WHERE TABLE_NAME IN ('DBA_ROLES','DBA_SYS_PRIVS','DBA_ROLE_PRIVS','ROLE_ROLE_PRIVS');
prompt DBA_TAB_PRIVS_8
prompt Control: Oracle ConText
prompt
prompt Display select fields from dba_tab_privs
prompt where OWNER = CTXSYS
SELECT GRANTEE,PRIVILEGE, TABLE_NAME FROM DBA_TAB_PRIVS WHERE OWNER = 'CTXSYS';
prompt DBA_TAB_PRIVS_9
prompt Control: Privilege Management
prompt
prompt Display select fields of the sys.dba_tab_privs table
prompt where owner = SYS and table_name = LINK$
SELECT GRANTEE, PRIVILEGE FROM SYS.DBA_TAB_PRIVS WHERE OWNER = 'SYS' AND TABLE_NAME = 'LINK$';
prompt Control: Test Monitoring of User Access
prompt
prompt Display dba_tab_privs table
prompt Fields: Grantee, Owner, Table_Name, Privilege
SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME LIKE 'AUD%';
prompt Control: Test Access to Production Data
prompt
prompt Display dba_tab_privs table
prompt Fields: Grantee, Owner, Table_Name, Grantor, Privilege, Grantable, Hierarchy
Select substr(grantee,1,30) "Grantee",
substr(owner,1,20) "Owner",
substr(table_name,1,20) "Table_Name",
substr(grantor,1,20) "Grantor",
substr(privilege,1,20) "Privilege",
substr(grantable,1,3) "Grantable",
substr(hierarchy,1,3) "Hierarchy"
from dba_tab_privs WHERE GRANTABLE = 'YES';
prompt Control: Test Access to Privileged IT Functions
prompt Control: Test Access to Production Data
prompt Control: Test Default Accounts and Passwords
prompt Control: Test for Host-Based Authentication Methods
prompt Control: Test for Global and Enterprise Roles
prompt Control: Test Logical Access Segregation of Duties
prompt Control: Test New User Setup
prompt Control: Test Password Settings
prompt
prompt Display DBA_USERS table
prompt Fields: Username, User ID, Password, Account Status, Lock Date, Expiry date, Default tablespace, Created, Assigned Profile, Consumer Group, External Name
select substr(username,1,20) "Username",
substr(user_id,1,10) "User ID",
substr(password,1,20) "Password",
substr(account_status,1,20) "Account Status",
substr(Lock_date,1,11) "Lock date",
substr(Expiry_date,1,11) "Expiry date",
substr(Default_tablespace,1,15) "Def Tablespace",
substr(Created,1,10) "Created",
substr(Profile,1,10) "Assgn Profile",
substr(Initial_RSRC_Consumer_Group,1,20) "Consumer Grp",
substr(External_Name,1,10) "Ext Name"
from sys.dba_users
order by username;
prompt SYS.DBA_USERS
prompt Control: Account Lockout
prompt Control: Change Default Passwords
prompt Control: Host Based Authentication
prompt Control: Idle Session Timeout
prompt Control: Log Application Account Activity
prompt Control: Log Firecall Accounts
prompt Control: Monitor Administrator Activities
prompt Control: Network Traffic Encryption
prompt Control: Remote Host Based Authentication
prompt Control: Required Passwords
prompt Control: User SELECT Privileges
prompt Control: System Tablespace
prompt Control: Role-based Privileges: End Users
prompt Control: Intelligent Agent
prompt Control: Concurrent Logins
prompt Control: Help Desk Database Security Function
prompt Control: Passwords for Database Administration Accounts
prompt Control: Privileged Account Review
prompt Control: Unique IDs
prompt
prompt Display sys.dba_users table
prompt Fields: Username, User ID, Password, Account Status, Lock Date, Expiry date, Default tablespace, Created, Assigned Profile, Consumer Group, External Name
select substr(username,1,20) "Username",
substr(user_id,1,10) "User ID",
substr(password,1,20) "Password",
substr(account_status,1,20) "Account Status",
substr(Lock_date,1,11) "Lock date",
substr(Expiry_date,1,11) "Expiry date",
substr(Default_tablespace,1,15) "Def Tablespace",
substr(Created,1,10) "Created",
substr(Profile,1,10) "Assgn Profile",
substr(Initial_RSRC_Consumer_Group,1,20) "Consumer Grp",
substr(External_Name,1,10) "Ext Name"
from sys.dba_users
order by username;
prompt Control: Test Access to Data Modification Utilities (SQL*Plus)
prompt
prompt Display Product_Profile
SELECT * FROM PRODUCT_PROFILE;
prompt SYSTEM.PRODUCT_USER_PROFILE
prompt Control: Access Within Support Products
prompt Control: Restrict Access to SQL*Plus
prompt Control: Patch Maintenance
prompt
prompt Display product_user_profile table
select * from product_user_profile;
prompt Display sqlplus_product_profile table
select * from sqlplus_product_profile;
prompt Control: Test Access to Privileged IT Functions
prompt
prompt Display SYS.DBA_ROLE_PRIVS table
prompt Fields: Grantee, Granted_Role, Admin_Option, Default_Role
select substr(grantee,1,30) "Grantee",
substr(granted_role,1,20) "granted_role",
substr(admin_option,1,3) "Admin_Option",
substr(default_role,1,3) "Default_Role"
from SYS.DBA_ROLE_PRIVS WHERE ADMIN_OPTION = 'YES';
prompt Control: Test Password Settings
prompt
prompt Display SYS.DBA_STMT_AUDIT_OPTS table
prompt Fields: Grantee, Privilege, Admin_Option
SELECT USER_NAME, FAILURE FROM SYS.DBA_STMT_AUDIT_OPTS WHERE AUDIT_OPTION = 'CREATE SESSION';
prompt Control: Test for Host-Based Authentication Methods
prompt Control: Test Monitoring of User Access
prompt Control: Test Password Settings
prompt
prompt Display v$parameter2 table
prompt Fields: Num, Name, Type, Value, Isdefault, Isses_Modifiable, Issys_Modifiable, Ismodified, Isadjusted, Description, Ordinal, Update_Comment
Select substr(num,1,20) "Num",
substr(Name,1,20) "Name",
substr(type,1,20) "Type",
substr(value,1,20) "Value",
substr(isdefault,1,20) "Isdefault",
substr(isses_modifiable,1,20) "Isses_modifiable",
substr(issys_modifiable,1,20) "Issys_modifiable",
substr(ismodified,1,20) "Ismodified",
substr(isadjusted,1,20) "Isadjusted",
substr(description,1,20) "Description",
substr(ordinal,1,20) "Ordinal",
substr(update_comment,1,20) "Update_Comment"
from v$parameter2;
prompt Control: Test Monitoring of User Access
prompt
prompt Display Product_Component_Version
prompt Fields: Product, Version, Status
SELECT * FROM PRODUCT_COMPONENT_VERSION;
Prompt Control: Test Monitoring of User Access
prompt
prompt Display V$VERSION
prompt Fields: Banner
SELECT * FROM V$VERSION;
set echo off
set termout on
set heading on
set feedback off
set trimspool on
set linesize 200
set pagesize 200
set markup html on spool on
Spool EY_OracleDBDump_Dep.html
prompt DBA_DEPENDENCIES
prompt Control: Promote User Permissions
prompt
prompt Display dba_dependencies table
select * from dba_dependencies;
spool off
set markup html off
set echo off
set termout on
set heading on
set feedback off
set trimspool on
set linesize 200
set pagesize 200
set markup html on spool on
Spool EY_OracleDBDump_SysPrivs.html
prompt DBA_SYS_PRIVS
prompt Control: Exempt Access Policy privilege
prompt Control: Privilege Management
prompt Control: Role-based Privileges: Auditing
prompt Control: Triggers
prompt Control: Trace file creation privilege
prompt Control: SELECT ANY TABLE Permission
prompt Control: Role-based Privileges: Production Support
prompt Control: Role-based Privileges: End Users
prompt Control: Role-based Privileges: Developer
prompt Control: Role-based Privileges: Data Owner
prompt Control: BECOME USER Privilege
prompt Control: Access Through Third Party Tools
prompt
prompt Display dba_sys_privs table
select * from dba_sys_privs;
spool off
set markup html off
prompt end of script
spool off
set markup html off
############################################################
# Auditor Name : Vikram Krishnan
# Audit Date : 3/31/2014
# Client Name :
# Description : Please type in a Description.
############################################################
*/
set echo on
REM **************************************************************************
REM Ernst and Young LLP UNIX Audit Script
REM Copyright - 2005
REM Updated by S. Rohit (rohit.iyer@sg.ey.com) 29Sept2005- fixed some bugs in code
REM Updated by Chad Woolf 25Aug2005- Added usability references, labels, and more associations
REM Updated by Peter Morin 29Apr2005 - Added HTML support for 8i
REM Updated by Peter Morin 15Mar2005 - Added MARKUP for HTML output
REM **************************************************************************
set echo off
set termout on
set heading on
set feedback off
set trimspool on
set linesize 200
set pagesize 200
set markup html on spool on
Spool EY_OracleDBDump.html
prompt SYS.ALL_TRIGGERS
prompt select all from sys.all_triggers table
prompt
prompt Control: Promote User Permissions
prompt Control: Triggers
select * from sys.all_triggers;
prompt AUDITING
prompt Control: Database Links
prompt Control: Log Application Account Activity
prompt Control: Log Failed Attempts to Access Objects
prompt Control: Log Failed Login Attempts
prompt Control: Log Firecall Accounts
prompt Control: Log SYSTEM Account Activity
prompt Control: Log Vendor Actions
prompt Control: Monitor Administrator Activities
prompt Control: Technical Review/Change Control Procedures
prompt Control: Log Failed Attempts to Issue Sensitive Commands
prompt Control: Privileged Account Review
prompt
prompt Display sys.dba_obj_audit_opts table
desc sys.dba_obj_audit_opts
prompt
prompt Display ins, upd, del from sys.dba_obj_audit_opts
prompt where owner = 'table owner' and object_name = 'tablename'
Select ins, upd, del
from sys.dba_obj_audit_opts
where owner = 'table owner' and object_name = 'tablename';
prompt
prompt Display sys.dba_stmt_audit_opts
prompt where audit_option='CREATE SESSION'
select *
from sys.dba_stmt_audit_opts
where audit_option='CREATE SESSION';
prompt
prompt Display sys.dba_stmt_audit_opts table
prompt where user_name includes 'DBA"
select substr(user_name,1,15) "User",
substr(audit_option,1,30) "Audit Option",
success, failure
from sys.dba_stmt_audit_opts
where user_name in ('SYS', 'SYSTEM')
or user_name like '%DBA%'
order by 1;
prompt
prompt Display sys.dba_stmt_audit_opts table
select *
from sys.dba_stmt_audit_opts;
prompt
prompt Display sys.dba_obj_audit_opts table
prompt has restrictions (see script detail)
select *
from sys.dba_obj_audit_opts
where alt <> '-/-'
or aud <> '-/-'
or com <> '-/-'
or del <> '-/-'
or gra <> '-/-'
or ind <> '-/-'
or ins <> '-/-'
or loc <> '-/-'
or ren <> '-/-'
or sel <> '-/-'
or upd <> '-/-'
or ref <> '-/-'
or exe <> '-/-' ;
prompt
prompt Display sys.dba_obj_audit_opts table
prompt where object_name = 'LINK$'
select *
from sys.dba_obj_audit_opts
where object_name = 'LINK$';
prompt
prompt Display timestamp, name, returncode
prompt from the sys.aud$ and sys.audit_actions tables
prompt where userid includes "DBA"
select timestamp#, name, returncode
from sys.aud$ a, sys.audit_actions b
where (userid in ('SYS', 'SYSTEM')
or userid like '%DBA%')
and a.action# = b.action;
prompt DBA_COL_PRIVS
prompt Control: Database Links
prompt Control: Privilege Management
prompt
prompt Display dba_col_privs table
select * from dba_col_privs;
prompt CONFIG - Output of Oracle Configuration Information
prompt Control: Adherence to Licensing Agreements
prompt Control: Archive log files
prompt Control: Auditing
prompt Control: Idle Session Timeout
prompt Control: Permissions on Control files
prompt Control: Restrict Ability To Connect
prompt Control: Trace files
prompt Control: Patch Maintenance
prompt
prompt Display V$VERSION table
select * from v$version;
prompt
prompt Display V$DATABASE table
select * from v$database;
prompt
prompt Display V$LICENSE table
select * from v$license;
prompt
prompt Display V$PARAMETER table
select * from v$parameter;
prompt
prompt Display V$CONTROLFILE table
select * from v$controlfile;
prompt
prompt Display V$PWFILE_USERS table
select * from v$pwfile_users;
prompt SYS.DBA_DB_LINKS
prompt Control: Database Links
prompt Control: Privilege Management
prompt
prompt Display select fields of the SYS.DBA_DB_LINKS table
SELECT OWNER, DB_LINK, USERNAME FROM SYS.DBA_DB_LINKS;
prompt DBA_JOBS
prompt Control: Job Scheduling Function
prompt
prompt Display dba_jobs table
select * from dba_jobs;
prompt
prompt Display dba_jobs_running table
select * from dba_jobs_running;
prompt DBA_OBJECTS_1
prompt Control: Production Objects
prompt
prompt Display select fields from dba_objects table
prompt where LAST_DDL_TIME is in the past 30 days
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME, CREATED FROM DBA_OBJECTS WHERE LAST_DDL_TIME > SYSDATE-30;
prompt DBA_PROFILES
prompt Control: Account Lockout
prompt Control: Idle Session Timeout
prompt Control: Password Composition
prompt Control: Password Expiration
prompt Control: Password History
prompt Control: Restrict Password Protected Roles
prompt Control: Concurrent Logins
prompt Control: Test Password Settings
prompt
prompt Display dba_profiles table
select * from dba_profiles;
prompt DBA_ROLE_PRIVS
prompt Control: Application Schema owner as DBA
prompt Control: Developer Roles
prompt Control: Developer Access to Production Environment
prompt Control: Restrict Role Functionality
prompt Control: Restrict WITH ADMIN Option
prompt Control: Role-based Privileges: Auditing
prompt Control: Role-based Privileges: Security Administration
prompt Control: Role-based Privileges: Process
prompt Control: Role-based Privileges: DBA
prompt Control: Role-based Privileges: Data Owner
prompt Control: CONNECT and RESOURCE role
prompt Control: PUBLIC Account Privileges
prompt Control: Restrict Password Protected Roles
prompt Control: Test Access to Privileged IT Functions
prompt Control: Test Access to Production Data
prompt Control: Test Logical Access Segregation of Duties
prompt
prompt Display dba_role_privs table
select *
from dba_role_privs
order by grantee, granted_role;
prompt DBA_ROLES
prompt Control: Restrict Role Functionality
prompt Control: Role-based Privileges: Auditing
prompt Control: Role-based Privileges: Security Administration
prompt Control: Role-based Privileges: Process
prompt Control: Role-based Privileges: Data Owner
prompt Control: Database Administration Segregation of Duties
prompt Control: Help Desk Database Security Function
prompt Control: Role-based Privileges: Administrator
prompt
prompt Display dba_roles table
select * from dba_roles;
prompt DBA_SEGMENTS_1
prompt Control: AUD$ Tablespace
prompt
prompt Display select fields from dba_segments table
prompt where tablespace_name contains 'SYSTEM' and segment_name contains 'AUD$'
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME
FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = '%SYSTEM%' AND SEGMENT_NAME = '?AUD$?';
prompt DBA_SEGMENTS_2
prompt Control: System Tablespace
prompt
prompt Display select fields from dba_segments table
prompt where tablespace_name includes 'SYSTEM' and owner does not include 'SYS'
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE
FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = '%SYSTEM%' AND OWNER <> '?SYS?';
prompt Control: Test for Access Assigned to PUBLIC Role
prompt Control: Test Access to Privileged IT Functions
prompt Control: Test Logical Access Segregation of Duties
prompt
prompt Display DBA_SYS_PRIVS table
prompt Fields: Grantee, Privilege, Admin_Option
SELECT substr(grantee,1,30) "Grantee",
substr(privilege,1,20) "Privilege",
substr(admin_option,1,3) "Admin_Option"
FROM DBA_SYS_PRIVS
WHERE
PRIVILEGE='CREATE USER' OR
PRIVILEGE='BECOME USER' OR
PRIVILEGE='ALTER USER' OR
PRIVILEGE='DROP USER' OR
PRIVILEGE='CREATE ROLE' OR
PRIVILEGE='ALTER ANY ROLE' OR
PRIVILEGE='DROP ANY ROLE' OR
PRIVILEGE='GRANT ANY ROLE' OR
PRIVILEGE='CREATE PROFILE' OR
PRIVILEGE='ALTER PROFILE' OR
PRIVILEGE='DROP PROFILE' OR
PRIVILEGE='CREATE ANY TABLE' OR
PRIVILEGE='ALTER ANY TABLE' OR
PRIVILEGE='DROP ANY TABLE' OR
PRIVILEGE='INSERT ANY TABLE' OR
PRIVILEGE='UPDATE ANY TABLE' OR
PRIVILEGE='DELETE ANY TABLE' OR
PRIVILEGE='CREATE ANY PROCEDURE' OR
PRIVILEGE='ALTER ANY PROCEDURE' OR
PRIVILEGE='DROP ANY PROCEDURE' OR
PRIVILEGE='CREATE ANY TRIGGER' OR
PRIVILEGE='ALTER ANY TRIGGER' OR
PRIVILEGE='DROP ANY TRIGGER' OR
PRIVILEGE='CREATE TABLESPACE' OR
PRIVILEGE='ALTER TABLESPACE' OR
PRIVILEGE='DROP TABLESPACES' OR
PRIVILEGE='ALTER DATABASE' OR
PRIVILEGE='ALTER SYSTEM';
prompt DBA_TAB_COLUMNS_1
prompt Control: Application Passwords
prompt
prompt Display select fields from dba_objects table, with restrictions (see script code)
prompt and object_name contains 'USER' (but
select object_name object, object_type typ, owner owner
from dba_objects where owner<>'SYS' and ((object_name like '%USER%' and object_name not like 'USER_%') or object_name like '%USR%' or object_name like '%PASSWD%' or object_name like '%PWD%'
or object_name like '%PASS%') and object_type in('VIEW','TABLE')
union select table_name object, column_name type, owner owner
from dba_tab_columns where owner<>'SYS' and (column_name like '%USER%'
or column_name like '%USR%' or column_name like '%PASSWD%'
or column_name like '%PWD%' or column_name like '%PASS%')
prompt DBA_TAB_PRIVS_11
prompt Control: Role-based Privileges: Operator
prompt
prompt Display sys.dba_tab_privs table
prompt where grantee does not exist in the sys.dba_roles table
SELECT GRANTEE, OWNER, TABLE_NAME FROM SYS.DBA_TAB_PRIVS WHERE GRANTEE NOT IN (SELECT ROLE FROM SYS.DBA_ROLES);
prompt DBA_TAB_PRIVS_12
prompt Control: Role-based Privileges: Developer
prompt
prompt Display sys.dba_tab_privs table
prompt where grantee is not in sys.dba_roles table
SELECT GRANTEE, OWNER, TABLE_NAME FROM SYS.DBA_TAB_PRIVS WHERE GRANTEE NOT IN (SELECT ROLE FROM SYS.DBA_ROLES);
prompt DBA_TAB_PRIVS_13
prompt Control: PUBLIC Account Privileges
prompt
prompt Display select fields from sys.dba_tab_privs table
prompt where grantee = PUBLIC
prompt
SELECT OWNER, TABLE_NAME, GRANTOR, PRIVILEGE FROM SYS.DBA_TAB_PRIVS WHERE GRANTEE = 'PUBLIC'
prompt DBA_TAB_PRIVS_2
prompt Control: ALL_USERS View
prompt
prompt Display select fields from dba_tab_privs table
prompt where table_name = 'ALL_USERS'
select grantee, privilege, table_name
from dba_tab_privs where table_name = 'ALL_USERS';
prompt DBA_TAB_PRIVS_4
prompt Control: Critical tables and views
prompt
prompt Display select fields from dba_tab_privs table
prompt where table_name contains 'USER$','DBA_USERS','LINK$','USER_HISTORY$'
SELECT GRANTEE, PRIVILEGE, TABLE_NAME FROM DBA_TAB_PRIVS WHERE TABLE_NAME IN ('USER$','DBA_USERS','LINK$','USER_HISTORY$');
prompt DBA_TAB_PRIVS_5
prompt Control: Database Links
prompt
prompt Display select fields from dba_tab_privs table
prompt where owner = 'SYS' and table_name = 'LINK$'
SELECT GRANTEE, PRIVILEGE FROM SYS.DBA_TAB_PRIVS WHERE OWNER = 'SYS' AND TABLE_NAME = 'LINK$';
prompt DBA_TAB_PRIVS_6
prompt Control: DBA Role views
prompt
prompt Display select fields from dba_tab_privs table
prompt where table_name contains 'DBA_ROLES', 'DBA_SYS_PRIVS', 'DBA_ROLE_PRIVS', 'ROLE_ROLE_PRIVS'
SELECT GRANTEE, PRIVILEGE, TABLE_NAME FROM DBA_TAB_PRIVS WHERE TABLE_NAME IN ('DBA_ROLES','DBA_SYS_PRIVS','DBA_ROLE_PRIVS','ROLE_ROLE_PRIVS');
prompt DBA_TAB_PRIVS_7
prompt Control: Development and Test databases
prompt Control: Embedded Account Information (Links)
prompt
prompt Display select fields from dba_tab_privs table
prompt where table_name is 'DBA_ROLES', 'DBA_SYS_PRIVS', 'DBA_ROLE_PRIVS', or 'ROLE_ROLE_PRIVS'
SELECT GRANTEE, PRIVILEGE, TABLE_NAME FROM DBA_TAB_PRIVS WHERE TABLE_NAME IN ('DBA_ROLES','DBA_SYS_PRIVS','DBA_ROLE_PRIVS','ROLE_ROLE_PRIVS');
prompt DBA_TAB_PRIVS_8
prompt Control: Oracle ConText
prompt
prompt Display select fields from dba_tab_privs
prompt where OWNER = CTXSYS
SELECT GRANTEE,PRIVILEGE, TABLE_NAME FROM DBA_TAB_PRIVS WHERE OWNER = 'CTXSYS';
prompt DBA_TAB_PRIVS_9
prompt Control: Privilege Management
prompt
prompt Display select fields of the sys.dba_tab_privs table
prompt where owner = SYS and table_name = LINK$
SELECT GRANTEE, PRIVILEGE FROM SYS.DBA_TAB_PRIVS WHERE OWNER = 'SYS' AND TABLE_NAME = 'LINK$';
prompt Control: Test Monitoring of User Access
prompt
prompt Display dba_tab_privs table
prompt Fields: Grantee, Owner, Table_Name, Privilege
SELECT GRANTEE, OWNER, TABLE_NAME, PRIVILEGE FROM DBA_TAB_PRIVS WHERE TABLE_NAME LIKE 'AUD%';
prompt Control: Test Access to Production Data
prompt
prompt Display dba_tab_privs table
prompt Fields: Grantee, Owner, Table_Name, Grantor, Privilege, Grantable, Hierarchy
Select substr(grantee,1,30) "Grantee",
substr(owner,1,20) "Owner",
substr(table_name,1,20) "Table_Name",
substr(grantor,1,20) "Grantor",
substr(privilege,1,20) "Privilege",
substr(grantable,1,3) "Grantable",
substr(hierarchy,1,3) "Hierarchy"
from dba_tab_privs WHERE GRANTABLE = 'YES';
prompt Control: Test Access to Privileged IT Functions
prompt Control: Test Access to Production Data
prompt Control: Test Default Accounts and Passwords
prompt Control: Test for Host-Based Authentication Methods
prompt Control: Test for Global and Enterprise Roles
prompt Control: Test Logical Access Segregation of Duties
prompt Control: Test New User Setup
prompt Control: Test Password Settings
prompt
prompt Display DBA_USERS table
prompt Fields: Username, User ID, Password, Account Status, Lock Date, Expiry date, Default tablespace, Created, Assigned Profile, Consumer Group, External Name
select substr(username,1,20) "Username",
substr(user_id,1,10) "User ID",
substr(password,1,20) "Password",
substr(account_status,1,20) "Account Status",
substr(Lock_date,1,11) "Lock date",
substr(Expiry_date,1,11) "Expiry date",
substr(Default_tablespace,1,15) "Def Tablespace",
substr(Created,1,10) "Created",
substr(Profile,1,10) "Assgn Profile",
substr(Initial_RSRC_Consumer_Group,1,20) "Consumer Grp",
substr(External_Name,1,10) "Ext Name"
from sys.dba_users
order by username;
prompt SYS.DBA_USERS
prompt Control: Account Lockout
prompt Control: Change Default Passwords
prompt Control: Host Based Authentication
prompt Control: Idle Session Timeout
prompt Control: Log Application Account Activity
prompt Control: Log Firecall Accounts
prompt Control: Monitor Administrator Activities
prompt Control: Network Traffic Encryption
prompt Control: Remote Host Based Authentication
prompt Control: Required Passwords
prompt Control: User SELECT Privileges
prompt Control: System Tablespace
prompt Control: Role-based Privileges: End Users
prompt Control: Intelligent Agent
prompt Control: Concurrent Logins
prompt Control: Help Desk Database Security Function
prompt Control: Passwords for Database Administration Accounts
prompt Control: Privileged Account Review
prompt Control: Unique IDs
prompt
prompt Display sys.dba_users table
prompt Fields: Username, User ID, Password, Account Status, Lock Date, Expiry date, Default tablespace, Created, Assigned Profile, Consumer Group, External Name
select substr(username,1,20) "Username",
substr(user_id,1,10) "User ID",
substr(password,1,20) "Password",
substr(account_status,1,20) "Account Status",
substr(Lock_date,1,11) "Lock date",
substr(Expiry_date,1,11) "Expiry date",
substr(Default_tablespace,1,15) "Def Tablespace",
substr(Created,1,10) "Created",
substr(Profile,1,10) "Assgn Profile",
substr(Initial_RSRC_Consumer_Group,1,20) "Consumer Grp",
substr(External_Name,1,10) "Ext Name"
from sys.dba_users
order by username;
prompt Control: Test Access to Data Modification Utilities (SQL*Plus)
prompt
prompt Display Product_Profile
SELECT * FROM PRODUCT_PROFILE;
prompt SYSTEM.PRODUCT_USER_PROFILE
prompt Control: Access Within Support Products
prompt Control: Restrict Access to SQL*Plus
prompt Control: Patch Maintenance
prompt
prompt Display product_user_profile table
select * from product_user_profile;
prompt Display sqlplus_product_profile table
select * from sqlplus_product_profile;
prompt Control: Test Access to Privileged IT Functions
prompt
prompt Display SYS.DBA_ROLE_PRIVS table
prompt Fields: Grantee, Granted_Role, Admin_Option, Default_Role
select substr(grantee,1,30) "Grantee",
substr(granted_role,1,20) "granted_role",
substr(admin_option,1,3) "Admin_Option",
substr(default_role,1,3) "Default_Role"
from SYS.DBA_ROLE_PRIVS WHERE ADMIN_OPTION = 'YES';
prompt Control: Test Password Settings
prompt
prompt Display SYS.DBA_STMT_AUDIT_OPTS table
prompt Fields: Grantee, Privilege, Admin_Option
SELECT USER_NAME, FAILURE FROM SYS.DBA_STMT_AUDIT_OPTS WHERE AUDIT_OPTION = 'CREATE SESSION';
prompt Control: Test for Host-Based Authentication Methods
prompt Control: Test Monitoring of User Access
prompt Control: Test Password Settings
prompt
prompt Display v$parameter2 table
prompt Fields: Num, Name, Type, Value, Isdefault, Isses_Modifiable, Issys_Modifiable, Ismodified, Isadjusted, Description, Ordinal, Update_Comment
Select substr(num,1,20) "Num",
substr(Name,1,20) "Name",
substr(type,1,20) "Type",
substr(value,1,20) "Value",
substr(isdefault,1,20) "Isdefault",
substr(isses_modifiable,1,20) "Isses_modifiable",
substr(issys_modifiable,1,20) "Issys_modifiable",
substr(ismodified,1,20) "Ismodified",
substr(isadjusted,1,20) "Isadjusted",
substr(description,1,20) "Description",
substr(ordinal,1,20) "Ordinal",
substr(update_comment,1,20) "Update_Comment"
from v$parameter2;
prompt Control: Test Monitoring of User Access
prompt
prompt Display Product_Component_Version
prompt Fields: Product, Version, Status
SELECT * FROM PRODUCT_COMPONENT_VERSION;
Prompt Control: Test Monitoring of User Access
prompt
prompt Display V$VERSION
prompt Fields: Banner
SELECT * FROM V$VERSION;
set echo off
set termout on
set heading on
set feedback off
set trimspool on
set linesize 200
set pagesize 200
set markup html on spool on
Spool EY_OracleDBDump_Dep.html
prompt DBA_DEPENDENCIES
prompt Control: Promote User Permissions
prompt
prompt Display dba_dependencies table
select * from dba_dependencies;
spool off
set markup html off
set echo off
set termout on
set heading on
set feedback off
set trimspool on
set linesize 200
set pagesize 200
set markup html on spool on
Spool EY_OracleDBDump_SysPrivs.html
prompt DBA_SYS_PRIVS
prompt Control: Exempt Access Policy privilege
prompt Control: Privilege Management
prompt Control: Role-based Privileges: Auditing
prompt Control: Triggers
prompt Control: Trace file creation privilege
prompt Control: SELECT ANY TABLE Permission
prompt Control: Role-based Privileges: Production Support
prompt Control: Role-based Privileges: End Users
prompt Control: Role-based Privileges: Developer
prompt Control: Role-based Privileges: Data Owner
prompt Control: BECOME USER Privilege
prompt Control: Access Through Third Party Tools
prompt
prompt Display dba_sys_privs table
select * from dba_sys_privs;
spool off
set markup html off
prompt end of script
spool off
set markup html off
No comments:
Post a Comment