Comment analyser mes journaux d’audit à l’aide de Redshift Spectrum ?

Brève description

Avant d’utiliser Redshift Spectrum, effectuez les tâches suivantes :

1.    Activez vos journaux d’audit.

Remarque : L’affichage de vos journaux d’audit dans votre compartiment Amazon Simple Storage Service (Amazon S3) peut prendre un certain temps.

2.    Créez un rôle AWS Identity and Access Management (IAM).

3.    Associez le rôle IAM à votre cluster Amazon Redshift.

Pour interroger vos journaux d’audit dans Redshift Spectrum, créez des tables externes, puis configurez-les pour qu’elles pointent vers un dossier commun (utilisé par vos fichiers). Utilisez ensuite la colonne $path masquée et la fonction regex pour créer des vues qui génèrent les lignes pour votre analyse.

Résolution

Pour interroger vos journaux d’audit dans Redshift Spectrum, procédez comme suit :

1.    Créez un schéma externe :

create external schema s_audit_logs
from data catalog
database 'audit_logs'
iam_role 'arn:aws:iam::your_account_number:role/role_name' create external database if not exists

Remplacez your_account_number pour qu’il corresponde à votre numéro de compte réel. Pour role_name, spécifiez le rôle IAM associé à votre cluster Amazon Redshift.

2.    Créez les tables externes.

Remarque : dans les exemples suivants, remplacez bucket_name, your_account_id et votre région par le nom de votre compartiment, votre ID de compte et votre région AWS.

Créez une table des journaux d’activité utilisateur :

create external table s_audit_logs.user_activity_log(
	logrecord varchar(max)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS INPUTFORMAT
	'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
	'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
	's3://bucket_name/logs/AWSLogs/your_account_id/redshift/region'

Créez une table de journal des connexions :

CREATE EXTERNAL TABLE s_audit_logs.connections_log(
	event varchar(60), recordtime varchar(60),
	remotehost varchar(60), remoteport varchar(60),
	pid int, dbname varchar(60),
	username varchar(60), authmethod varchar(60),
	duration int, sslversion varchar(60),
	sslcipher varchar(150), mtu int,
	sslcompression varchar(70), sslexpansion varchar(70),
	iamauthguid varchar(50), application_name varchar(300))
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS INPUTFORMAT
	'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
	'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://bucket_name/logs/AWSLogs/your_account_id/redshift/region';

Créez une table de journal utilisateur :

create external table s_audit_log.user_log(
	userid varchar(255),
	username varchar(500),
	oldusername varchar(500),
	usecreatedb varchar(50),
	usesuper varchar(50),
	usecatupd varchar(50),
	valuntil varchar(50),
	pid varchar(50),
	xid varchar(50),
	recordtime varchar(50))
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
STORED AS INPUTFORMAT
	'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
	'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://bucket_name/logs/AWSLogs/your_account_id/redshift/region’;

3.    Créez un schéma local pour consulter les journaux d’audit :

create schema audit_logs_views

4.    Pour accéder aux tables externes, créez des vues dans une base de données à l’aide de l’option WITH NO SCHEMA BINDING :

CREATE VIEW audit_logs_views.v_connections_log AS
select *
FROM s_audit_logs.connections_log
WHERE "$path" like '%connectionlog%'
with no schema binding;

Les fichiers renvoyés sont limités par la colonne masquée $path afin qu’ils correspondent aux entrées du journal de connexion.

Dans l’exemple suivant, la colonne $path masquée et la fonction regex limitent les fichiers renvoyés pour **v \ _connections \ _log : v_connections_log **:

CREATE or REPLACE VIEW audit_logs_views.v_useractivitylog AS
SELECT    logrecord,
          substring(logrecord,2,24) as recordtime,
          replace(regexp_substr(logrecord,'db=[^" "]*'),'db=','') as db,
          replace(regexp_substr(logrecord,'user=[^" "]*'),'user=','') AS user,
          replace(regexp_substr(logrecord, 'pid=[^" "]*'),'pid=','') AS pid,
          replace(regexp_substr(logrecord, 'userid=[^" "]*'),'userid=','') AS userid,
          replace(regexp_substr(logrecord, 'xid=[^" "]*'),'xid=','') AS xid,
          replace(regexp_substr(logrecord, '][^*]*'),']','') AS query
   FROM s_audit_logs.user_activity_log
   WHERE "$path" like '%useractivitylog%'
   with no schema binding;

Les fichiers renvoyés correspondent aux entrées du journal useractivitylog.

Remarque : il existe une limitation liée aux requêtes à plusieurs lignes dans les journaux d’activité utilisateur. Il est recommandé d’interroger directement les enregistrements du journal des colonnes.

Informations connexes

Analysez les journaux d’audit des bases de données pour garantir la sécurité et la conformité à l’aide d’Amazon Redshift Spectrum

STL_CONNECTION_LOG