Sample Queries for Database UDC

Using custom database controls? For each control you'll provide a SQL statement to execute on your databases to get expected output. Check out these samples for MS SQL and Oracle databases..

You can also learn more about creating database udcs here: MS SQL Database Checks | Oracle Database Checks | Sybase Database Checks

MS SQL Sample Queries

Sample 1 - Lists the status of max_rollover_files parameter defined on the database

SQL Query:  

select name, is_state_enabled, max_rollover_files, log_file_path from master.sys.server_file_audits

Type: Select query

DB Results:   

query sample1

 

Sample 2 - Lists the 'Windows Group Authentication logins accounts' on the hosts

SQL Query:  

select p.name, p.type_desc from sys.server_principals p where p.type='U' and p.name NOT LIKE '##%'

Type: Select Query with NOT LIKE operator

DB Results:  

query sample2

 

Sample 3 - Status of the 'BadPasswordCount' for the SQL Server authentication logins accounts

SQL Query:  

select p.name, p.is_disabled, cast(LOGINPROPERTY(p.name,'BadPasswordCount') AS varchar(10)) as BadPasswordCount from sys.sql_logins p where p.name NOT LIKE '##%%'

Type: Select sub query with CAST function  

DB Results:    

query sample3

Sample 4 -  List of accounts with password policy complexity not checked  

SQL Query:  

select name from sys.sql_logins where type = 'S' and is_policy_checked <> '1'

Type: Operator <> in where Clause

DB Results:   

query sample4

Sample 5 - Lists all users assigned to fixed database roles

SQL Query:  

select name from (select user_name(r.role_principal_id) role,u.name from sys.database_role_members r, sys.database_principals u where r.member_principal_id = u.principal_id and u.principal_id<>1 and r.role_principal_id  in (select principal_id from sys.database_principals where is_fixed_role=1)) as t1 group by name

Type: Sub query with Select statement

DB Results:   

query sample5

Sample 6 -  Lists Login names that have one or more Server Roles assigned to them on the server

SQL Query:  

SELECT loginname = p.name, serverrolename = r.name, logintype = p.type_desc FROM sys.server_role_members m JOIN sys.server_principals p ON m.member_principal_id = p.principal_id JOIN sys.server_principals r ON m.role_principal_id = r.principal_id

Type: Select with JOINS  

DB Results:   

query sample6

Sample 7 - Lists the location of data directory in the database

SQL Query:  

SELECT DatabaseName = rtrim(name), FileLocation = filename

FROM master.dbo.sysaltfiles where groupid = 1 ORDER BY name

Type: Select using ORDER BY keyword

DB Results:

query sample7

Sample 8 - Lists the last_request_start_time setting for user session in current SQL Server instance(minutes)

SQL Query:  

select cast(session_id as varchar(10)) as session_id, login_name, status, cast(last_request_start_time as varchar(30)) as last_request_start_time, cast(DATEDIFF(minute,convert(varchar(30), last_request_start_time), convert(varchar(30), GETDATE())) as varchar(15)) AS  'Minutes_to_Now' from sys.dm_exec_sessions where is_user_process = 1

Type: Select using CAST Function

DB Results:

query sample8

Sample 9 -  Returns the list of sysadmin Fixed Server Role Members

SQL Query:  select SUSER_NAME(rm.member_principal_id) as loginname from sys.server_role_members rm, sys.server_principals lgn where rm.role_principal_id >=3 AND rm.role_principal_id <=10 AND rm.role_principal_id = lgn.principal_id and SUSER_NAME(lgn.principal_id) = 'sysadmin'

Type: Select query

DB Results:  

query sample9

Oracle Sample Queries

Sample 1 - Check if the audit_file_dest is valid

SQL Query:  

Select name,value from gv$parameter p, gv$instance i where name = 'audit_file_dest' and p.inst_id=i.inst_id

Type: Select query

DB Results:

query sample1 oracle

Sample 2 - Lists the access restrictions set for PUBLIC on UTL_FILE package

SQL Query:  

Select distinct grantee, privilege, table_name from dba_tab_privs where table_name = 'UTL_FILE' and privilege = 'EXECUTE' and grantee not in ('OUTLN','SYSTEM','PERFSTAT','AQ_USER_ROLE','HS_ADMIN_ROLE','EXP_FULL_DATABASE','IMP_FULL_DATABASE','EXECUTE_CATALOG_ROLE','AQ_ADMINISTRATOR_ROLE','SYS', 'ORDPLUGINS', 'SQLTXPLAIN', 'WKSYS', 'MDSYS', 'DMSYS', 'WMSYS', 'CTXSYS', 'ORACLE_OCM', 'SPOTLIGHT', 'SYSMAN') order by grantee, privilege, table_name

Type: Select query with ORDER BY Keyword

DB Results:

query sample2 oracle

Sample 3 - Checks for Roles with Password requirements

SQL Query:  

Select role, password_required from dba_roles where role not in ( 'CONNECT','CTXSYS','DBA','DELETE_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','EXP_FULL_DATABASE','IMP_FULL_DATABASE','OEM_ADVISOR','OEM_MONITOR','RESOURCE','SELECT_CATALOG_ROLE') and password_required = 'NO

Type: Select query NOT IN Operator

DB Results:

query sample3 oracle

Sample 4 - Lists the Users and/or Roles that have access to the Oracle Enterprise Manager (OEM)

SQL Query:  

Select username account from dba_users where username = 'DBSNMP' UNION select role account from dba_roles where role in ('SNMPAGENT','OEM_MONITOR','OEM_ADVISOR')

Type: Select query with UNION Operator

DB Results:

query smaple4 oracle

Sample 5 - Specifies how long to lock the account after the failed login attempts is met

SQL Query:  

Select distinct username, p.limit,p.resource_name from dba_users u, dba_profiles p where u.profile = p.profile and resource_name = 'PASSWORD_LOCK_TIME'

Type: Select query with DISTINCT Operator

DB Results:

query sample5 oracle

Sample 6 - Selects usernames from dba_users table

SQL Query:  

select USERNAME from dba_users where profile = 'DEFAULT' and username not in ('SYS', 'SYSTEM', 'OUTLN,’MGMT’,’WKSYS’,’SH’,'BI’,’ANONYMOUS’) and username not like 'BAL_%

Type: Select query with NOT IN operator

DB Results:

query sample6 oracle