permission_auditing_database.cc 8.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236
  1. // Copyright 2020 The Chromium Authors. All rights reserved.
  2. // Use of this source code is governed by a BSD-style license that can be
  3. // found in the LICENSE file.
  4. #include "components/permissions/permission_auditing_database.h"
  5. #include <algorithm>
  6. #include <iostream>
  7. #include <limits>
  8. #include <memory>
  9. #include <string>
  10. #include <vector>
  11. #include "base/check.h"
  12. #include "base/files/file_path.h"
  13. #include "sql/database.h"
  14. #include "sql/meta_table.h"
  15. #include "sql/statement.h"
  16. #include "sql/transaction.h"
  17. #include "third_party/abseil-cpp/absl/types/optional.h"
  18. #include "url/gurl.h"
  19. namespace permissions {
  20. namespace {
  21. // For this database, schema migration is supported for at least 1 year.
  22. // This means we can deprecate old versions that landed more than a year ago.
  23. //
  24. // Version 1: https://crrev.com/c/2435307 - 2020-11-04
  25. // The current number of the database schema.
  26. constexpr int kVersion = 1;
  27. // The lowest version of the database schema such that a legacy code can still
  28. // read/write the current database.
  29. constexpr int kCompatibleVersion = 1;
  30. } // namespace
  31. PermissionAuditingDatabase::PermissionAuditingDatabase() = default;
  32. PermissionAuditingDatabase::~PermissionAuditingDatabase() = default;
  33. bool PermissionAuditingDatabase::Init(const base::FilePath& path) {
  34. db_.set_histogram_tag("Permission Auditing Logs");
  35. if (!db_.Open(path)) {
  36. return false;
  37. }
  38. sql::MetaTable metatable;
  39. if (!metatable.Init(&db_, kVersion, kCompatibleVersion)) {
  40. db_.Poison();
  41. return false;
  42. }
  43. if (metatable.GetCompatibleVersionNumber() > kVersion) {
  44. db_.Poison();
  45. return false;
  46. }
  47. if (!db_.DoesTableExist("uses")) {
  48. if (!CreateSchema()) {
  49. db_.Poison();
  50. return false;
  51. }
  52. }
  53. // TODO(anyone): perform migration if metatable.GetVersionNumber() < kVersion
  54. return true;
  55. }
  56. bool PermissionAuditingDatabase::CreateSchema() {
  57. sql::Transaction transaction(&db_);
  58. if (!transaction.Begin()) {
  59. return false;
  60. }
  61. if (!db_.Execute("CREATE TABLE uses("
  62. " id INTEGER PRIMARY KEY AUTOINCREMENT,"
  63. " origin TEXT NOT NULL,"
  64. " content_setting_type INTEGER NOT NULL,"
  65. " usage_start_time INTEGER NOT NULL,"
  66. " usage_end_time INTEGER NOT NULL,"
  67. " had_user_activation INTEGER NOT NULL,"
  68. " was_foreground INTEGER NOT NULL,"
  69. " had_focus INTEGER NOT NULL"
  70. ")")) {
  71. return false;
  72. }
  73. if (!db_.Execute("CREATE UNIQUE INDEX setting_origin_start_time ON "
  74. "uses(origin, content_setting_type,"
  75. "usage_start_time)")) {
  76. return false;
  77. }
  78. if (!db_.Execute("CREATE INDEX setting_origin_end_time ON "
  79. "uses(origin, content_setting_type,"
  80. "usage_end_time)")) {
  81. return false;
  82. }
  83. if (!db_.Execute("CREATE INDEX start_time ON "
  84. "uses(usage_start_time)")) {
  85. return false;
  86. }
  87. if (!db_.Execute("CREATE INDEX end_time ON "
  88. "uses(usage_end_time)")) {
  89. return false;
  90. }
  91. return transaction.Commit();
  92. }
  93. bool PermissionAuditingDatabase::StorePermissionUsage(
  94. const PermissionUsageSession& session) {
  95. DCHECK(session.IsValid());
  96. sql::Statement statement(
  97. db_.GetCachedStatement(SQL_FROM_HERE,
  98. "INSERT INTO uses(origin, content_setting_type,"
  99. "usage_start_time, usage_end_time,"
  100. "had_user_activation, was_foreground, had_focus)"
  101. "VALUES (?, ?, ?, ?, ?, ?, ?)"));
  102. statement.BindString(0, session.origin.Serialize());
  103. statement.BindInt(1, static_cast<int32_t>(session.type));
  104. statement.BindTime(2, session.usage_start);
  105. statement.BindTime(3, session.usage_end);
  106. statement.BindBool(4, session.had_user_activation);
  107. statement.BindBool(5, session.was_foreground);
  108. statement.BindBool(6, session.had_focus);
  109. sql::Transaction transaction(&db_);
  110. if (!transaction.Begin()) {
  111. return false;
  112. }
  113. if (!statement.Run()) {
  114. return false;
  115. }
  116. return transaction.Commit();
  117. }
  118. std::vector<PermissionUsageSession>
  119. PermissionAuditingDatabase::GetPermissionUsageHistory(ContentSettingsType type,
  120. const url::Origin& origin,
  121. base::Time start_time) {
  122. DCHECK(!origin.opaque());
  123. std::vector<PermissionUsageSession> sessions;
  124. sql::Statement statement(db_.GetCachedStatement(
  125. SQL_FROM_HERE,
  126. "SELECT usage_start_time, usage_end_time, had_user_activation,"
  127. "was_foreground, had_focus "
  128. "FROM uses "
  129. "WHERE origin = ? AND content_setting_type = ? "
  130. "AND usage_end_time >= ?"));
  131. statement.BindString(0, origin.Serialize());
  132. statement.BindInt(1, static_cast<int32_t>(type));
  133. statement.BindTime(2, start_time.is_null() ? base::Time::Min() : start_time);
  134. while (statement.Step()) {
  135. sessions.push_back({.origin = origin,
  136. .type = type,
  137. .usage_start = statement.ColumnTime(0),
  138. .usage_end = statement.ColumnTime(1),
  139. .had_user_activation = statement.ColumnBool(2),
  140. .was_foreground = statement.ColumnBool(3),
  141. .had_focus = statement.ColumnBool(4)});
  142. }
  143. return sessions;
  144. }
  145. absl::optional<base::Time>
  146. PermissionAuditingDatabase::GetLastPermissionUsageTime(
  147. ContentSettingsType type,
  148. const url::Origin& origin) {
  149. DCHECK(!origin.opaque());
  150. sql::Statement statement(
  151. db_.GetCachedStatement(SQL_FROM_HERE,
  152. "SELECT usage_end_time "
  153. "FROM uses "
  154. "WHERE origin = ? AND content_setting_type = ? "
  155. "ORDER BY usage_end_time DESC "
  156. "LIMIT 1"));
  157. statement.BindString(0, origin.Serialize());
  158. statement.BindInt(1, static_cast<int32_t>(type));
  159. absl::optional<base::Time> last_usage;
  160. if (statement.Step()) {
  161. last_usage = statement.ColumnTime(0);
  162. }
  163. return last_usage;
  164. }
  165. bool PermissionAuditingDatabase::UpdateEndTime(ContentSettingsType type,
  166. const url::Origin& origin,
  167. base::Time start_time,
  168. base::Time new_end_time) {
  169. DCHECK(!origin.opaque());
  170. DCHECK(!start_time.is_null());
  171. DCHECK(!new_end_time.is_null());
  172. DCHECK_LE(start_time, new_end_time);
  173. sql::Statement statement(
  174. db_.GetCachedStatement(SQL_FROM_HERE,
  175. "UPDATE uses "
  176. "SET usage_end_time = ? "
  177. "WHERE origin = ? AND content_setting_type = ? "
  178. "AND usage_start_time = ?"));
  179. statement.BindTime(0, new_end_time);
  180. statement.BindString(1, origin.Serialize());
  181. statement.BindInt(2, static_cast<int32_t>(type));
  182. statement.BindTime(3, start_time);
  183. sql::Transaction transaction(&db_);
  184. if (!transaction.Begin()) {
  185. return false;
  186. }
  187. if (!statement.Run()) {
  188. return false;
  189. }
  190. return transaction.Commit();
  191. }
  192. bool PermissionAuditingDatabase::DeleteSessionsBetween(base::Time start_time,
  193. base::Time end_time) {
  194. std::vector<int> ids;
  195. sql::Statement statement(
  196. db_.GetCachedStatement(SQL_FROM_HERE,
  197. "DELETE FROM uses "
  198. "WHERE usage_start_time BETWEEN ? AND ? "
  199. "OR usage_end_time BETWEEN ? AND ?"));
  200. statement.BindTime(0, start_time.is_null() ? base::Time::Min() : start_time);
  201. statement.BindTime(1, end_time.is_null() ? base::Time::Max() : end_time);
  202. statement.BindTime(2, start_time.is_null() ? base::Time::Min() : start_time);
  203. statement.BindTime(3, end_time.is_null() ? base::Time::Max() : end_time);
  204. sql::Transaction transaction(&db_);
  205. if (!transaction.Begin()) {
  206. return false;
  207. }
  208. if (!statement.Run()) {
  209. return false;
  210. }
  211. return transaction.Commit();
  212. }
  213. } // namespace permissions