Database Module

Why the Database Module?

Database servers are some of the most complicated nodes in a network often because they are shared across many applications.  The database module collects information about the connectivity of each schema within your database host, and integrates client connections into our overall connectivity architecture.  Assessments with the Database Module active are able to map server dependency to the schema level so an organization can understand what particular data sets an application may be dependent on.

Overview

The Database Module has been released as a preview feature.

We support connectivity analytics for:

  • MySQL
  • Microsoft SQL Server (2008 and later)
  • Oracle

Unlike most RISC data collection modalities, the Database Module does not have an automated discovery process and must be manually configured. In order for collection to take place, the DB host information and an account with adequate permissions must be manually provided into the RN150. We recommend that a temporary dedicated user is created and used for analyzing the database. The required permissions for the temporary user are outlined below. We also recommend that the user is removed after the assessment ends.

Oracle Database users: please note that due to the way Oracle manages its dataspaces, we do collect usernames in their role as database schemas.

Using the Database Module

  1. Go to the RN-150 Dashboard and locate the “Additional Credentials” page at the bottom of the list.
  2. From the Credential Type drop-down menu, select “database.”
  3. Entered the connection information for your first database server, and hit “Add.”
  4. Enter the server’s IP and hit “test.” If the test is unsuccessful, press “cancel” to verify that the credentials were added correctly and retry.
  5. Enter and test connection information for each individual database server you would like to have analyzed. Oracle cluster database users should enter each server in a cluster separately and provide a direct connection to each server.

   

Account Permissions:

MySQL

The account provided must have SHOW DATABASES and SHOW PROCESS privileges. It also requires select privileges on *.*

MS SQL Server

The account provided must have VIEW SERVER STATE, VIEW DATABASE STATE, and VIEW ANY DATABASE permissions.

Oracle Database

The account provided must have select privileges on V$INSTANCE and V$SESSION as well as on the following DBA tables : DBA_USERS, DBA_TABLES, DBA_INDEXES, DBA_OBJECTS, DBA_SEGMENTS, and DBA_LOBS.

Queries run by the database module:

The specific queries run during the course of inventory and performance analysis are specific to the DBMS.

MySQL

select @@hostname h, @@version v

SELECT SCHEMA_NAME FROM information_schema.schemata

SELECT host, db, command, state, time FROM information_schema.processlist

select db, count(distinct(user)) userCount from information_schema.processlist group by db

select count(distinct(user)) userCount from information_schema.processlist

SELECT *, unix_timestamp(create_time) ct, unix_timestamp(update_time) ut, unix_timestamp(check_time) cht FROM INFORMATION_SCHEMA.TABLES

MS SQL Server

select SERVERPROPERTY ('ProductVersion') v, SERVERPROPERTY ('MachineName') h

SELECT name, database_id, create_date FROM sys.databases

SELECT name s FROM sys.databases

SELECT conn.client_net_address,

conn.client_tcp_port,

sess.status,

sess.last_request_start_time,

DB_NAME(sess.database_id) AS db

FROM sys.dm_exec_sessions sess

LEFT JOIN sys.dm_exec_connections conn

ON sess.session_id=conn.session_id

WHERE sess.is_user_process=1

select DB_NAME(database_id) as db, count(distinct(login_name)) userCount from sys.dm_exec_sessions group by DB_NAME(database_id)

select count(distinct(login_name)) userCount from sys.dm_exec_sessions

select
t.name as tableName,
s.name as secondarySchema,
datediff(s, '1970-01-01 00:00:00', max(t.create_date)) as createDate,
datediff(s, '1970-01-01 00:00:00', max(t.modify_date)) as updateDate,
max(p.rows) as RowCounts,
sum(a.total_pages*8) as totalSpaceKB,
sum(a.used_pages*8) as usedSpaceKB,
sum(case when i.index_id < 2 then a.data_pages*8 else 0 end) as dataSpaceKB,
sum(a.used_pages*8)-sum(case when i.index_id < 2 then a.data_pages*8 else 0 end) as indexSpaceKB
from $schema.sys.tables t
inner join $schema.sys.indexes i on t.object_id = i.object_id
inner join $schema.sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
inner join $schema.sys.allocation_units a on p.partition_id = a.container_id
inner join $schema.sys.schemas s on t.schema_id=s.schema_id
group by t.name, s.name

Oracle Database

SELECT HOST_NAME H, VERSION V FROM V$INSTANCE

SELECT username FROM dba_users u WHERE EXISTS (SELECT 1 FROM dba_objects o WHERE o.owner = u.username)

SELECT MACHINE, PORT, SCHEMANAME, STATUS, COMMAND, LAST_CALL_ET FROM v$session WHERE username IS NOT NULL

select schemaname DB, count(distinct(user)) USERCOUNT from v$session group by schemaname

select count(distinct(user)) userCount from v$session

select

table_name, owner, sum(decode(type,'table',bytes))/1024 tableKB,

sum(decode(type,'index',bytes))/1024 indexKB, sum(decode(type,'lob',bytes))/1024 lobKB,

sum(bytes)/1024 totalKB, sum(num_rows) numRows, max(last_anal) last_anal,

max(created) created, max(updated) updated, max(tbs) tablespace,

sum(decode(type,'table',bytes,'lob',bytes))/1024 totalDataKB,

sum(decode(type,'index',bytes,'lobidx',bytes))/1024 totalIdxKB

from (

select t.table_name table_name, 'table' type, t.owner, s.bytes, t.num_rows,

t.last_analyzed last_anal, o.created created, o.last_ddl_time updated, t.tablespace_name tbs

from dba_tables t left join dba_segments s

on s.segment_name=t.table_name and s.owner=t.owner

left join dba_objects o on t.table_name=o.object_name and t.owner=o.owner

where s.segment_type in ('TABLE','TABLE PARTITION','TABLE SUBPARTITION') or s.segment_type is null

union all select i.table_name table_name, 'index' type, i.owner, s.bytes, 0 num_rows,

null last_anal, null created, null updated, null tbs

from dba_segments s inner join dba_indexes i

on i.index_name = s.segment_name and s.owner = i.owner

where s.segment_type in ('INDEX','INDEX PARTITION','INDEX SUBPARTITION')

union all select l.table_name, 'lob' type, l.owner, s.bytes, 0 num_rows, null last_anal,

null created, null updated, null tbs

from dba_lobs l inner join dba_segments s on l.segment_name = s.segment_name and l.owner = s.owner

where s.segment_type in ('LOBSEGMENT','LOB PARTITION')

union all select l.table_name, 'lobidx' type, l.owner, s.bytes, 0 num_rows, null last_anal,

null created, null updated, null tbs

from dba_lobs l inner join dba_segments s on l.index_name = s.segment_name and s.owner = l.owner

where s.segment_type = 'LOBINDEX' )

group by table_name, owner