1// Copyright Materialize, Inc. and contributors. All rights reserved.
2//
3// Use of this software is governed by the Business Source License
4// included in the LICENSE file.
5//
6// As of the Change Date specified in that file, in accordance with
7// the Business Source License, use of this software will be governed
8// by the Apache License, Version 2.0.
910use std::collections::{BTreeMap, BTreeSet};
11use std::sync::LazyLock;
1213use fancy_regex::Regex;
14use mysql_async::prelude::Queryable;
1516use crate::tunnel::MySqlConn;
17use crate::{MissingPrivilege, MySqlError, QualifiedTableRef};
1819pub async fn validate_source_privileges(
20 conn: &mut MySqlConn,
21 tables: &[QualifiedTableRef<'_>],
22) -> Result<(), MySqlError> {
23// MySQL doesn't have a great way to check privileges for the current user using SELECT
24 // statements on information_schema tables, since privileges are set at multiple levels
25 // and can be granted to users via roles, etc.
26 // Instead, the SHOW GRANTS statement coalesces these privileges and shows us what we have.
2728 // First we need to see if we are using an account that is using any default-activated roles.
29 // This can return a comma-deliminated string of roles that we can use directly in the
30 // SHOW GRANTS query. This command is only possible on MySQL 8.0+, so we ignore any errors.
31let roles: Option<String> = conn
32 .exec_first("SELECT CURRENT_ROLE()", ())
33 .await
34.ok()
35 .and_then(|val: Option<String>| match val {
36Some(inner) if inner != "NONE" => Some(inner),
37_ => None,
38 });
3940// Obtain the privileges for the current user using any roles that are default activated.
41let grant_query = match roles {
42None => "SHOW GRANTS FOR CURRENT_USER()".to_string(),
43Some(roles) => format!("SHOW GRANTS FOR CURRENT_USER() USING {}", roles),
44 };
4546// Parse and collect the grants into a map of schema -> table -> privileges
47let mut grant_map = BTreeMap::new();
48for grant in conn.exec(grant_query, ()).await? {
49let grant: String = grant;
50if let Some(object_grant) = get_object_grant(&grant) {
51 grant_map
52 .entry(object_grant.object_schema)
53 .or_insert_with(BTreeMap::new)
54 .entry(object_grant.object_name)
55 .or_insert_with(BTreeSet::new)
56 .extend(object_grant.privileges);
57 }
58 }
5960// Check that we have the LOCK TABLES and SELECT privileges for each table
61let mut errors = tables
62 .iter()
63 .flat_map(|table| {
64 ["LOCK TABLES", "SELECT"].iter().filter_map(|privilege| {
65// Check both the wildcard schema and the specific schema
66let privileged = [grant_map.get("*"), grant_map.get(table.schema_name)]
67 .iter()
68 .any(|schema_map| {
69// Check both the wildcard table and the specific table
70schema_map.map_or(false, |schema_map| {
71 [schema_map.get("*"), schema_map.get(table.table_name)]
72 .iter()
73 .any(|privs| {
74 privs.map_or(false, |privs| {
75 privs.contains(*privilege)
76 || privs.contains("ALL PRIVILEGES")
77 })
78 })
79 })
80 });
8182if !privileged {
83Some(MissingPrivilege {
84 privilege: privilege.to_string(),
85 qualified_table_name: format!("{}.{}", table.schema_name, table.table_name),
86 })
87 } else {
88None
89}
90 })
91 })
92 .collect::<Vec<_>>();
9394// Check that we have REPLICATION SLAVE priviliges at the global level
95if !grant_map
96 .get("*")
97 .and_then(|schema_map| schema_map.get("*"))
98 .map(|privs| privs.contains("REPLICATION SLAVE") || privs.contains("ALL PRIVILEGES"))
99 .unwrap_or(false)
100 {
101 errors.push(MissingPrivilege {
102 privilege: "REPLICATION SLAVE".to_string(),
103 qualified_table_name: "*.*".to_string(),
104 });
105 }
106107if !errors.is_empty() {
108Err(MySqlError::MissingPrivileges(errors))
109 } else {
110Ok(())
111 }
112}
113114/// Regex to parse a SHOW GRANTS line. Inspired by several stack overflow posts and
115/// adjusted to account for the different quoting styles across MySQL versions.
116/// If this regex matches then this is a grant on an actual object which looks like:
117/// GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `u1`@`localhost`
118/// GRANT SELECT ON `db1`.`table1` TO `my_user`@`localhost` WITH GRANT OPTION
119/// The regex needs to account for the possibility of a wildcard schema or table, and for the
120/// quote-char to be part of the table/schema name too.
121/// Group 1 is the list of privileges being granted
122/// Group 2 is either the wildcard * or a quoted database/schema
123/// Group 4 is the unquoted database/schema when the wildcard is not matched
124/// Group 5 is either the wildcard * or a quoted table
125/// Group 7 is the unquoted table when the wildcard is not matched
126/// Group 9 is the user being granted
127/// We use the `fancy_regex` crate to allow backreferences which are necessary to find the ending
128/// quote of each identifier since there are different quoting types across mysql versions.
129static GRANT_REGEX: LazyLock<Regex> = LazyLock::new(|| {
130 Regex::new(r#"GRANT (.+) ON (\*|(['`"])(.*)\3).(\*|(['`"])(.*)\6) TO (['`"])(.*)\8@.*"#)
131 .expect("valid")
132});
133134#[derive(Debug, PartialEq, Eq)]
135struct MySqlObjectGrant {
136 privileges: BTreeSet<String>,
137 object_schema: String,
138 object_name: String,
139}
140141/// Parses a returned row of a SHOW GRANTS statement to return a MySqlObjectGrant
142/// If the grant is not on an object (e.g. a grant of a role to a user), returns None
143fn get_object_grant(grant: &str) -> Option<MySqlObjectGrant> {
144match GRANT_REGEX.captures(grant) {
145Ok(None) => None,
146Err(err) => {
147tracing::warn!("Error parsing privilege grant: {}", err);
148None
149}
150Ok(Some(captures)) => {
151let object_schema = if captures.get(2).expect("valid").as_str() == "*" {
152"*".to_string()
153 } else {
154 captures.get(4).expect("valid").as_str().to_string()
155 };
156let object_name = if captures.get(5).expect("valid").as_str() == "*" {
157"*".to_string()
158 } else {
159 captures.get(7).expect("valid").as_str().to_string()
160 };
161let privileges = captures.get(1).expect("valid").as_str();
162Some(MySqlObjectGrant {
163 privileges: privileges.split(", ").map(|s| s.to_string()).collect(),
164 object_schema,
165 object_name,
166 })
167 }
168 }
169}
170171#[cfg(test)]
172mod tests {
173use super::*;
174175#[mz_ore::test]
176fn test_get_object_grant() {
177// backticks and wildcard
178let grant = "GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `u1`@`localhost`";
179let expected_grant = MySqlObjectGrant {
180 privileges: ["SELECT", "INSERT", "UPDATE"]
181 .iter()
182 .map(|s| s.to_string())
183 .collect(),
184 object_schema: "db1".to_string(),
185 object_name: "*".to_string(),
186 };
187assert_eq!(get_object_grant(grant), Some(expected_grant));
188189// single-quotes
190let grant = "GRANT SUPER, CREATE TEMPORARY TABLES, LOCK TABLES ON 'db1'.'table1' TO `u1`@`localhost`";
191let expected_grant = MySqlObjectGrant {
192 privileges: ["SUPER", "CREATE TEMPORARY TABLES", "LOCK TABLES"]
193 .iter()
194 .map(|s| s.to_string())
195 .collect(),
196 object_schema: "db1".to_string(),
197 object_name: "table1".to_string(),
198 };
199assert_eq!(get_object_grant(grant), Some(expected_grant));
200201// wildcards
202let grant = "GRANT ALL PRIVILEGES ON *.* TO `u1`@`localhost`";
203let expected_grant = MySqlObjectGrant {
204 privileges: ["ALL PRIVILEGES"].iter().map(|s| s.to_string()).collect(),
205 object_schema: "*".to_string(),
206 object_name: "*".to_string(),
207 };
208assert_eq!(get_object_grant(grant), Some(expected_grant));
209210// special chars
211let grant = "GRANT SELECT, INSERT, UPDATE ON `таблица`.`mixED_CAse` TO `u1`@`localhost`";
212let expected_grant = MySqlObjectGrant {
213 privileges: ["SELECT", "INSERT", "UPDATE"]
214 .iter()
215 .map(|s| s.to_string())
216 .collect(),
217 object_schema: "таблица".to_string(),
218 object_name: "mixED_CAse".to_string(),
219 };
220221assert_eq!(get_object_grant(grant), Some(expected_grant));
222223// quotes in names
224let grant = "GRANT SUPER, CREATE TEMPORARY TABLES, LOCK TABLES ON `r`w`.`'sd'` TO `u1`@`localhost` IDENTIFIED BY PASSWORD `test`";
225let expected_grant = MySqlObjectGrant {
226 privileges: ["SUPER", "CREATE TEMPORARY TABLES", "LOCK TABLES"]
227 .iter()
228 .map(|s| s.to_string())
229 .collect(),
230 object_schema: "r`w".to_string(),
231 object_name: "'sd'".to_string(),
232 };
233assert_eq!(get_object_grant(grant), Some(expected_grant));
234 }
235}