MS SQL Database Activity

The Databases Dashboard shows the log activity from the Snare MSSQL agent that collects and monitors user activity on Microsoft SQL Instances and database servers. The Snare SQL agent can monitor many key aspects of the database and collect logs that cover all user administrative activity as well as normal user activity. In general the agent is mostly used to monitor just the DBA level or administrative user activity on the database as the applications usually has role based access controls in place to control what a user can do.   A DBA or system administrator can override any technical controls from an application level as they have direct access to the database. The can run any form of database update, delete, truncate tables, export data out of the database procedures and change content all outside of the control of the application.  While most DBAs and sysadmins are in trusted roles, if their access is compromised then the attacker can use their credentials to perform nefarious activity. Understanding and knowing of what activity is approved and what is not on the database helps to detect any unauthorized changes or a loss of confidentiality to sensitive data. Some parts of the dashboard only show data for the last 4 hours as some SQL systems can generate massive events. If longer search times are desired then its best to use the event search feature to search for logs over longer time period.

The key aspects of the dashboard are:

• MSSQL Database activity over time - this shows the general event rate over time. When spikes occur then then its easily viable on the graph. A summary of the databases and instances being monitored are shown. The UI does limit the number of databases to 50.

• Login Logout Activity - a line chart showing SQL Server can have either local SQL logins/logouts or use windows integrated authentication for login/logout and access. This includes all login and logouts from the database level

• MSSQL Server by Events - this shows a summary view of all event types coming from the monitored databases or SQL instances. 

• Server Query Tracking - this chart shows the details of the actual SQL query that was being run on the system

• User Rights DDL - this chart shows user rights DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in database. 

  Examples of DDL commands:

  • CREATE – is used to create the database or its objects (like table, index, function, views, store procedure and triggers).

  • DROP – is used to delete objects from the database.

  • ALTER-is used to alter the structure of the database.

  • TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records are removed.

  • COMMENT –is used to add comments to the data dictionary.

  • RENAME –is used to rename an object existing in the database.

• SQL Object Access - this will show the object details that are being monitored and the activity that was detected. 

• Server Privileges and User Rights - this chart shows users that are using elevated user rights to perform a function. This includes internationalization of when a user performs a execute login as another user. 

• Server Logins and Logouts by User - this covers the activity at the user level rather than the event id type.

• DB Activity - this chart shows which database is generating more activity than other databases. NOTE a database is contained within an SQL Instance and an Instance can run one or more databases 

• DB User Activity - this chat shows which users are generating more activity than other users. 

• Account Admin - this chart shows the usage of the admin account or anyone who is using the SYSADMIN role in SQL Server. SQL Server logins added or removed, Windows rights used, passwords and properties are modified

• When an event is selected the log will show the user rights when used by DML(Data Manipulation Language) : The SQL commands that deals with the manipulation of data present in database belong to DML or Data Manipulation Language and this includes most of the SQL statements.

  Examples of DML:

  • SELECT – is used to retrieve data from the a database.

  • INSERT – is used to insert data into a table.

  • UPDATE – is used to update existing data within a table.

  • DELETE – is used to delete records from a database table.

• MS SQL Logs - this shows a number status block overview of all systems and the event rate for today.

• MS SQL Logins - shows the logins for today.

• MS SQL Server failed logins - shows the number of failed logins for today.

• Refer to the Snare MSSQL user guide for more details on all the event ids related to each function

Open

Open