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.
The Database Module has been released as a preview feature.
We support connectivity analytics for:
- Microsoft SQL Server (2008 and later)
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
- Go to the RN-150 Dashboard and locate the “Additional Credentials” page at the bottom of the list.
- From the Credential Type drop-down menu, select “database.”
- Entered the connection information for your first database server, and hit “Add.”
- 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.
- 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.
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.
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.
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
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