使用数据库注释跟踪包含敏感数据的字段

最近，我在探索一个 Rails 应用时，注意到开发人员通过表的列定义将数据库列标记为具有敏感数据。各种数据库都支持列注释，但以下是 MySQL 对列注释的解释：

列的注释可以使用 COMMENT 选项指定，长度最多为 1024 个字符。注释由 SHOW CREATE TABLE 和 SHOW FULL COLUMNS 语句显示。它也显示在信息模式 COLUMNS 表的 COLUMN_COMMENT 列中。

在 MySQL中，我们可以使用如下语法在创建或修改表格时，添加一个注释(comment)如下：

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Example comment for name.',
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  -- ...
)

在 Laravel 中，我们可以在数据库迁移中使用 comment() 方法轻松地向现有字段或新字段添加注释：

$table->string('email')
    ->unique()
    ->comment('The user\'s email address they use the log in');

你可以通过数据库结构检测数据库字段的注释；以下是全新 Laravel 应用中 migrations 表格的示例：

并非所有数据库都支持表或列注释，但 MySQL 和 PostgreSQL 等数据库允许添加注释。如果你的数据库支持注释，它们可以作为列的有用描述符，并以各种方式帮助开发人员理解 schema。

使用注释标记敏感数据列

评论的使用范围可以从对专栏的用户友好评论到其他上下文信息。但是，如果我们使用类似 sensitive_data=true 的东西来标记具有敏感用户或系统信息的字段呢？

即使你的 Laravel 应用已经成熟，你也可以创建一个迁移来标记包含敏感信息的列，以审计你的数据，并在导出、数据库克隆或用户信息请求期间帮助删除敏感信息：

php artisan make:migration mark_columns_with_sensitive_data

我们可以在迁移中为任何要标记为敏感的表和列添加注释：

public function up(): void
{
    Schema::table('users', function (Blueprint $table) {
        $table->string('email')->comment('sensitive_data=true')->change();
        $table->string('name')->comment('sensitive_data=true')->change();
        $table->string('password')->comment('sensitive_data=true')->change();
    });
 
    Schema::table('profiles', function (Blueprint $table) {
        // ...
    });
}

运行迁移后，现在你将看到数据库中标记的字段有注释：

查找所有标记为敏感的表和列

我们现在在整个应用中都有有用的注释，开发人员可以将其用于上下文，但我们的新注释允许我们超越文档！我们可以在应用中找到所有标记为敏感的列，并将这些信息用于隐私、数据库克隆等。

虽然每个数据库都不同，但 MySQL 可以查询信息模式列表以查找标记为敏感的所有字段。此处 sensitive_data=true 是一个约定，你可以改成任何其他内容，但以下是你如何在数据库中找到所有列：

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_COMMENT LIKE '%sensitive_data=true%';

上例将产生如下输出：

更进一步

如果我们想要一种简单的方法来获取给定模型的所有敏感列名，该怎么办？可能有多种方法可以实现这一点，但如果我们创建一个应用 trait，我们的模型可以用来查找敏感列呢：

namespace App\Models\Concerns;
 
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
 
/**
 * @mixin \Illuminate\Database\Eloquent\Model
 */
trait InteractWithSensitiveColumns
{
    /**
     * @return Collection<string>
     */
    public function getSensitiveColumns(): Collection
    {
        return $this->getConnection()
            ->table('INFORMATION_SCHEMA.COLUMNS')
            ->where('TABLE_NAME', $this->getTable())
            ->where('COLUMN_COMMENT', 'like', '%sensitive_data=true%')
            ->pluck('COLUMN_NAME');
    }
}

让我们在用户模型实例中使用这个 trait，看看它是如何工作的：

请注意，使用上述 trait 会将你锁定道使用 MySQL 来处理使用此方法的模型。我不确定是否有一种与数据库无关的方法来获取这些信息，但请注意，上述 trait 是 MySQL 专用的。

更多

希望你已经看到了数据库注释对维护应用数据库有用的几种方法！无论是创建新应用还是处理现有应用，都可以立即开始使用注释！可以在数据库文档中通过 Laravel 的迁移和 Eloquent 模型了解更多关于管理数据库的信息。