Sample Queries for Database UDCs

Using custom database controls? For each control, you'll provide a SQL statement to execute on your databases to get expected output.

Check out sample queries: MS SQL Sample Queries | Oracle Sample Queries | PostgreSQL/Pivotal Greenplum Sample Queries | SAP IQ Sample  Queries | Sybase Sample Queries

Learn more about creating UDCs: MS SQL Database Checks | Oracle Database Checks | PostgreSQL/Pivotal Greenplum Checks | SAP IQ Checks | Sybase 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 sys.server_file_audits

Type: Select query

DB Results:   

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:  

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:    

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:   

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:   

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:   

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

SQL Query:  

SELECT DatabaseName = rtrim(name), FileLocation = filename

FROM dbo.sysaltfiles where groupid = 1 ORDER BY name

Type: Select using ORDER BY keyword

DB Results:

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:

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:  

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:

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:

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:

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:

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:

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:

PostgreSQL/Pivotal Greenplum Sample Queries

Sample 1 - Fetch all the details about the pg_settings view

SQL Query:

select name, setting, category, short_desc from pg_catalog.pg_settings

Type: Select query

DB Results:

Sample 2 - List the current value of the log_checkpoints setting

SQL Query:

select name, setting from pg_catalog.pg_settings where name='log_checkpoints';

Type: Select query

DB Results:

SAP IQ Sample Queries

Sample 1 - From the SYSUSEROPTIONS view, fetch option settings that are in effect for users

SQL Query:

select o.user_name, convert(VARCHAR(30),setting) setting from sysuseroptions o, sysuser u where o.user_name = u.user_name and

[option]='min_password_length' and setting < 6 and o.user_name not like 'SYS%' and o.user_name not in ('dbo','PUBLIC','SA_DEBUG',

'diagnostics','rs_systabgroup') and u.password != 'null';

Type: Select query

DB Results:

Sample 2 - From SYSUSER system view, fetch users whose password expires at the next login

SQL Query:

select user_name from sysuser where expire_password_on_login=1;

Type: Select query

DB Results:

Sybase Sample Queries

Sample 1 - From the sysauditoptions table in the sybsecurity database, fetch the value of the logout option

SQL Query:

select name, val as value, sval as setting from sybsecurity.dbo.sysauditoptions where name='logout' and val!=3

Type: Select query

DB Results:

Sample 2 - Check whether the value of the 'select on syscomments.text' parameter is 'on' or 'off'

SQL Query:

select name as 'configuration name', case value when 0 then 'off' else 'on' end as 'configuration setting' from sysconfigures

where name = 'select on syscomments.text' and value != 0

Type: Select Query

DB Results:

Sample 3 - Find out the database server name and its version

SQL Query:

select @@servername as dbservername, @@version as dbserverversion

Type: Select Query

DB Results: