Background
ALTR DB can export Database query audit details and anomaly (definition of an anomaly could be helpful) details to a customer provided S3 bucket.
To enable for your organization please reach out to ALTR support to provide your bucket details, which data you would like exported (query audits and/or anomalies) and to obtain the AWS account number and your client-id which will need to be referenced in your AWS IAM Role.
For optimal performance, ensure the bucket is located in the US East (N. Virginia) us-east-1 Region.
Setting up the IAM Role and Policy:
IAM Role
The role name needs to begin with "ALTRPublishSIEMData"
{
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Principal": {
"AWS": "arn:aws:iam::<ALTR-PROVIDED-ACCOUNT-ID>:root"
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"sts:ExternalId": ["<client-id>"]
}
}
}
]
}
IAM Policy
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "ALTR0",
"Effect": "Allow",
"Action": "s3:PutObject",
"Resource": "arn:aws:s3:::<bucket>/*"
}
]
}
S3 Query and Audit Object Details
Once the above configuration is complete, objects will start to populate in the provided bucket with the below structure of filenames.
Query audit objects will be written as compressed in TGZ and should appear in the bucket about every 5 minutes.
Anomaly audit objects will be written as JSON files and should appear individually as anomaly events occur in ALTR DB.
Query Audits:
ClientID/altrdb_query_audit/yyyy/mm/dd/ClientID_query_yyyymmdd_hh_mm_ss_ssss.tgz
Anomaly Audits:
ClientID/altrdb_anomaly_audit/yyyy/mm/dd/ClientID_anomaly_yyyymmdd_hh_mm_ss_ssss.json
Structure of JSON
Example of Query Audit:
{
"query": "SELECT * FROM `users`; -- literal_altruser",
"host": "mysqltestvm0.demo.internal",
"user": "devuser@apitestvm0.demo.internal",
"time": "2020-05-05T15:25:24.386Z",
"dbname": "testdb_altr",
"columnList": [{
"tablename": "users",
"colname": "id",
"coltype": "INT"
}, {
"tablename": "users",
"colname": "firstName",
"coltype": "VARCHAR"
}, {
"tablename": "users",
"colname": "lastName",
"coltype": "VARCHAR"
}],
"databaseType": "com.mysql.jdbc.Driver",
"rowCount": 1,
"returnType": 2,
"resultSetIndex": -1,
"clientId": "8626985d-473e-4b09-ab05-7dd551966b20",
"organizationName": "TJ Org",
"serverId": 6,
"serverName": "TJs Server",
"ip": "::ffff:38.103.96.227",
"os": "Linux",
"serverStatus": "active",
"userQueryTag": {
"userId": "User-Id-1",
"groupId": 1
},
"schema_version": "v1.0.0"
}
Example of an Anomaly:
{
"id": 144,
"dt": "2020-04-30T00:49:33.000Z",
"clientId": "8626985d-473e-4b09-ab05-7dd551966b20",
"user": {
"id": 0,
"trackingId": "",
"accessStatus": ""
},
"groups": [{
"id": 1,
"name": "super_group",
"description": "the super group"
}],
"application": {
"id": 6,
"name": "TJs Server",
"description": "TJs Server",
"creationDate": "2020-04-29T17:46:18.000Z",
"serverStatus": "active",
"accessStatus": "active"
},
"fields": [{
"columnName": "firstName",
"databaseName": "testdb_altr",
"tableName": "users"
}, {
"columnName": "lastName",
"databaseName": "testdb_altr",
"tableName": "users"
}],
"machineId": 0,
"locks": [{
"id": 7,
"name": "super_lock"
}],
"threshold": {
"id": 7,
"name": "anomaly_generate",
"accessRate": 1,
"accessRateUnit": "day",
"actionTaken": "Generate Anomaly"
},
"clientName": "TJ Org"
}