1 // Copyright 2020 The Chromium Authors
2 // Use of this source code is governed by a BSD-style license that can be
3 // found in the LICENSE file.
5 #include "components/permissions/permission_auditing_database.h"
14 #include "base/check.h"
15 #include "base/files/file_path.h"
16 #include "sql/database.h"
17 #include "sql/meta_table.h"
18 #include "sql/statement.h"
19 #include "sql/transaction.h"
20 #include "third_party/abseil-cpp/absl/types/optional.h"
23 namespace permissions {
27 // For this database, schema migration is supported for at least 1 year.
28 // This means we can deprecate old versions that landed more than a year ago.
30 // Version 1: https://crrev.com/c/2435307 - 2020-11-04
32 // The current number of the database schema.
33 constexpr int kVersion = 1;
35 // The lowest version of the database schema such that a legacy code can still
36 // read/write the current database.
37 constexpr int kCompatibleVersion = 1;
41 PermissionAuditingDatabase::PermissionAuditingDatabase() = default;
43 PermissionAuditingDatabase::~PermissionAuditingDatabase() = default;
45 bool PermissionAuditingDatabase::Init(const base::FilePath& path) {
46 db_.set_histogram_tag("Permission Auditing Logs");
47 if (!db_.Open(path)) {
50 sql::MetaTable metatable;
51 if (!metatable.Init(&db_, kVersion, kCompatibleVersion)) {
55 if (metatable.GetCompatibleVersionNumber() > kVersion) {
59 if (!db_.DoesTableExist("uses")) {
60 if (!CreateSchema()) {
65 // TODO(anyone): perform migration if metatable.GetVersionNumber() < kVersion
69 bool PermissionAuditingDatabase::CreateSchema() {
70 sql::Transaction transaction(&db_);
71 if (!transaction.Begin()) {
74 if (!db_.Execute("CREATE TABLE uses("
75 " id INTEGER PRIMARY KEY AUTOINCREMENT,"
76 " origin TEXT NOT NULL,"
77 " content_setting_type INTEGER NOT NULL,"
78 " usage_start_time INTEGER NOT NULL,"
79 " usage_end_time INTEGER NOT NULL,"
80 " had_user_activation INTEGER NOT NULL,"
81 " was_foreground INTEGER NOT NULL,"
82 " had_focus INTEGER NOT NULL"
86 if (!db_.Execute("CREATE UNIQUE INDEX setting_origin_start_time ON "
87 "uses(origin, content_setting_type,"
88 "usage_start_time)")) {
91 if (!db_.Execute("CREATE INDEX setting_origin_end_time ON "
92 "uses(origin, content_setting_type,"
96 if (!db_.Execute("CREATE INDEX start_time ON "
97 "uses(usage_start_time)")) {
100 if (!db_.Execute("CREATE INDEX end_time ON "
101 "uses(usage_end_time)")) {
104 return transaction.Commit();
107 bool PermissionAuditingDatabase::StorePermissionUsage(
108 const PermissionUsageSession& session) {
109 DCHECK(session.IsValid());
110 sql::Statement statement(
111 db_.GetCachedStatement(SQL_FROM_HERE,
112 "INSERT INTO uses(origin, content_setting_type,"
113 "usage_start_time, usage_end_time,"
114 "had_user_activation, was_foreground, had_focus)"
115 "VALUES (?, ?, ?, ?, ?, ?, ?)"));
116 statement.BindString(0, session.origin.Serialize());
117 statement.BindInt(1, static_cast<int32_t>(session.type));
118 statement.BindTime(2, session.usage_start);
119 statement.BindTime(3, session.usage_end);
120 statement.BindBool(4, session.had_user_activation);
121 statement.BindBool(5, session.was_foreground);
122 statement.BindBool(6, session.had_focus);
124 sql::Transaction transaction(&db_);
125 if (!transaction.Begin()) {
128 if (!statement.Run()) {
131 return transaction.Commit();
134 std::vector<PermissionUsageSession>
135 PermissionAuditingDatabase::GetPermissionUsageHistory(ContentSettingsType type,
136 const url::Origin& origin,
137 base::Time start_time) {
138 DCHECK(!origin.opaque());
139 std::vector<PermissionUsageSession> sessions;
140 sql::Statement statement(db_.GetCachedStatement(
142 "SELECT usage_start_time, usage_end_time, had_user_activation,"
143 "was_foreground, had_focus "
145 "WHERE origin = ? AND content_setting_type = ? "
146 "AND usage_end_time >= ?"));
147 statement.BindString(0, origin.Serialize());
148 statement.BindInt(1, static_cast<int32_t>(type));
149 statement.BindTime(2, start_time.is_null() ? base::Time::Min() : start_time);
151 while (statement.Step()) {
152 sessions.push_back({.origin = origin,
154 .usage_start = statement.ColumnTime(0),
155 .usage_end = statement.ColumnTime(1),
156 .had_user_activation = statement.ColumnBool(2),
157 .was_foreground = statement.ColumnBool(3),
158 .had_focus = statement.ColumnBool(4)});
163 absl::optional<base::Time>
164 PermissionAuditingDatabase::GetLastPermissionUsageTime(
165 ContentSettingsType type,
166 const url::Origin& origin) {
167 DCHECK(!origin.opaque());
168 sql::Statement statement(
169 db_.GetCachedStatement(SQL_FROM_HERE,
170 "SELECT usage_end_time "
172 "WHERE origin = ? AND content_setting_type = ? "
173 "ORDER BY usage_end_time DESC "
175 statement.BindString(0, origin.Serialize());
176 statement.BindInt(1, static_cast<int32_t>(type));
177 absl::optional<base::Time> last_usage;
178 if (statement.Step()) {
179 last_usage = statement.ColumnTime(0);
184 bool PermissionAuditingDatabase::UpdateEndTime(ContentSettingsType type,
185 const url::Origin& origin,
186 base::Time start_time,
187 base::Time new_end_time) {
188 DCHECK(!origin.opaque());
189 DCHECK(!start_time.is_null());
190 DCHECK(!new_end_time.is_null());
191 DCHECK_LE(start_time, new_end_time);
192 sql::Statement statement(
193 db_.GetCachedStatement(SQL_FROM_HERE,
195 "SET usage_end_time = ? "
196 "WHERE origin = ? AND content_setting_type = ? "
197 "AND usage_start_time = ?"));
198 statement.BindTime(0, new_end_time);
199 statement.BindString(1, origin.Serialize());
200 statement.BindInt(2, static_cast<int32_t>(type));
201 statement.BindTime(3, start_time);
203 sql::Transaction transaction(&db_);
204 if (!transaction.Begin()) {
207 if (!statement.Run()) {
210 return transaction.Commit();
213 bool PermissionAuditingDatabase::DeleteSessionsBetween(base::Time start_time,
214 base::Time end_time) {
215 std::vector<int> ids;
216 sql::Statement statement(
217 db_.GetCachedStatement(SQL_FROM_HERE,
219 "WHERE usage_start_time BETWEEN ? AND ? "
220 "OR usage_end_time BETWEEN ? AND ?"));
221 statement.BindTime(0, start_time.is_null() ? base::Time::Min() : start_time);
222 statement.BindTime(1, end_time.is_null() ? base::Time::Max() : end_time);
223 statement.BindTime(2, start_time.is_null() ? base::Time::Min() : start_time);
224 statement.BindTime(3, end_time.is_null() ? base::Time::Max() : end_time);
226 sql::Transaction transaction(&db_);
227 if (!transaction.Begin()) {
230 if (!statement.Run()) {
233 return transaction.Commit();
236 } // namespace permissions