Google Cloud SQL-捕获错误的登录信息 [英] Google Cloud SQL - Catch bad logins

问题描述

我有一个现有的MySQL(5.7版)实例，由Google Cloud SQL托管(托管).当有人尝试用错误的用户名\密码连接我的数据库时，我想收到通知.

I have an existing MySQL (version 5.7) instance hosted (managed) by Google Cloud SQL. I want to get a notification when someone is trying to connect my database with a bad username\password.

我的想法是在Google Stackrive日志中查找它，但它不存在.

My idea was to look for it on the Google Stackrive logs, but it's not there.

有收集这些信息的选项吗?

There is an option to collect this information?

我尝试将实例与gcloud连接，但不幸的是，它无法正常工作.

I tried to connect the instance with gcloud but unfortunately, it's not working.

$ gcloud sql connect mydb
Whitelisting your IP for incoming connection for 5 minutes...done.
ERROR: (gcloud.sql.connect) It seems your client does not have ipv6 connectivity and the database instance does not have an ipv4 address. Please request an ipv4 address for this database instance.

这是因为只能在内部网络内部访问数据库.我搜索了-internal-ip 之类的标志，但没有找到.

It's because the database is accessible only inside the internal network. I searched for flags like --internal-ip but didn't find one.

但是，我猜测如果我尝试从数据库编辑器(工作台)访问数据库并没有什么区别.所以我做到了:

However, I was guessing that it's not making any difference if I'll try to access the database from my DB editor (workbench). So I did it:

搜索@Christopher建议的查询-但不存在.

Searching for the query that @Christopher advised - but it's not there.

我错过了什么?

我的Stackdrive的屏幕截图:

Screenshot of my Stackdrive:

即使我删除了此( resource.labels.database_id ="***" )条件-结果也相同.

Even if I remove this (resource.labels.database_id="***") condition - the result is the same.

推荐答案

有收集这些信息的选项吗?

There is an option to collect this information?

Stackdriver Logging是收集有关谁试图使用错误凭据连接到您的Google Cloud SQL实例的信息的最佳选择之一.

One of the best options to collect information about who is trying to connect to your Google Cloud SQL instance with wrong credentials is Stackdriver Logging.

为重现此步骤，我使用gcloud命令连接到Cloud SQL实例:

To reproduce this steps, I connected to the Cloud SQL instance using the gcloud command:

gcloud sql connect [CLOUD_SQL_INSTANCE]

我不完全确定是否使用mysql命令行会发生一些变化，但是如果确实如此，您应该只查找新的日志消息，并更新最后一个布尔项(从第4点开始)上).

I am not entirely sure if using the mysql command line something will change along the lines, but in case it does, you should only look for the new log message, and update the last boolean entry (from point 4 on).

1. 进入 Stackdriver→日志记录部分.
2. 要获取所需的信息，我们将使用高级日志查询.高级日志查询是可以从任意数量的日志中指定一组日志条目的表达式.可以在Logs Viewer，Logging API或gcloud命令行工具中使用高级日志查询.它们是从日志中获取信息的强大工具.
3. > 此处 您将找到如何在日志中获取和启用高级日志查询的方法.
4. 高级日志查询只是布尔表达式，它们指定项目中所有日志条目的子集.为了找出谁输入了错误的凭据进入运行MySQL的Cloud SQL实例，我们将使用以下查询:

1. Go under Stackdriver → Logging section.
2. To get the information we are looking for, we will use advanced log queries. Advanced log queries are expressions that can specify a set of log entries from any number of logs. Advanced logs queries can be used in the Logs Viewer, the Logging API, or the gcloud command-line tool. They are a powerful tool to get information from logs.
3. Here you will find how to get and enable advanced log queries in your logs.
4. Advanced log queries are just boolean expressions that specify a subset of all the log entries in your project. To find out who has enter with wrong credentials into your Cloud SQL instance running MySQL, we will use the following queries:

    resource.type="cloudsql_database" 
    resource.labels.database_id="[PROJECT_ID]:[CLOUD_SQL_INSTANCE]" 
    textPayload:"Access denied for user"

其中 [PROJECT_ID] 与对应于您的项目ID ，而 [CLOUD_SQL_INSTANCE] 对应于Cloud SQL实例的名称您要进行监督.

Where [PROJECT_ID] corresponds to your project ID and [CLOUD_SQL_INSTANCE] corresponds to the name of the Cloud SQL instance you would like to supervise.

如果您注意到，对应于 textPayload 的最后一个布尔表达式使用:运算符.

If you notice, the last boolean expression corresponding to textPayload uses the : operator.

如>在此处此处 通过使用:运算符，我们正在日志条目字段中查找与任何子字符串的匹配项，因此每个与指定字符串匹配的日志，在这种情况下为:"拒绝访问用户" .

As described here by using the : operator we are looking for matches with any sub string in the log entry field, so every log that matches the string specified, which in this case is: "Access denied for user".

如果现在某些用户输入了错误的凭据，您应该在日志中看到类似以下的消息:

If now some user enters the wrong credentials, you should see a message like the following appear within your logs:

[TIMEFRAME][Note] Access denied for user 'USERNAME'@'[IP]' (using password: YES)

这是在用户输入错误的凭据时使用GCP产品之一向您发送通知的问题.

From here is a matter of using one of GCP products to send you a notification when a user enters the wrong credentials.

希望对您有帮助.

这篇关于Google Cloud SQL-捕获错误的登录信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！