mz_mysql_util/
privileges.rs

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.
9
10use std::collections::{BTreeMap, BTreeSet};
11use std::sync::LazyLock;
12
13use fancy_regex::Regex;
14use mysql_async::prelude::Queryable;
15
16use crate::tunnel::MySqlConn;
17use crate::{MissingPrivilege, MySqlError, QualifiedTableRef};
18
19/// Allowlist of characters permitted in the value spliced into the
20/// `SHOW GRANTS ... USING <roles>` query. The value originates from the
21/// upstream MySQL server's `CURRENT_ROLE()` and must be conservatively
22/// validated to prevent SQL injection. Comma-separated role specs of the
23/// form `` `role`@`host` `` or `'role'@'host'` use only this character set.
24static ROLES_PATTERN: LazyLock<Regex> =
25    LazyLock::new(|| Regex::new(r#"^[A-Za-z0-9_.\-`'"@, \t]+$"#).expect("valid"));
26
27pub async fn validate_source_privileges(
28    conn: &mut MySqlConn,
29    tables: &[QualifiedTableRef<'_>],
30) -> Result<(), MySqlError> {
31    // MySQL doesn't have a great way to check privileges for the current user using SELECT
32    // statements on information_schema tables, since privileges are set at multiple levels
33    // and can be granted to users via roles, etc.
34    // Instead, the SHOW GRANTS statement coalesces these privileges and shows us what we have.
35
36    // First we need to see if we are using an account that is using any default-activated roles.
37    // This can return a comma-deliminated string of roles that we can use directly in the
38    // SHOW GRANTS query. This command is only possible on MySQL 8.0+, so we ignore any errors.
39    let roles: Option<String> = conn
40        .exec_first("SELECT CURRENT_ROLE()", ())
41        .await
42        .ok()
43        .and_then(|val: Option<String>| match val {
44            Some(inner) if inner != "NONE" => Some(inner),
45            _ => None,
46        });
47
48    // Obtain the privileges for the current user using any roles that are default activated.
49    // Validate the roles list against an allowlist before splicing it into the query: the value
50    // comes from the upstream server, and a hostile or compromised MySQL could inject SQL through
51    // it. The expected format is comma-separated role specs like `` `role`@`host` ``.
52    let grant_query = match roles.filter(|r| ROLES_PATTERN.is_match(r).unwrap_or(false)) {
53        None => "SHOW GRANTS FOR CURRENT_USER()".to_string(),
54        Some(roles) => format!("SHOW GRANTS FOR CURRENT_USER() USING {}", roles),
55    };
56
57    // Parse and collect the grants into a map of schema -> table -> privileges
58    let mut grant_map = BTreeMap::new();
59    for grant in conn.exec(grant_query, ()).await? {
60        let grant: String = grant;
61        if let Some(object_grant) = get_object_grant(&grant) {
62            grant_map
63                .entry(object_grant.object_schema)
64                .or_insert_with(BTreeMap::new)
65                .entry(object_grant.object_name)
66                .or_insert_with(BTreeSet::new)
67                .extend(object_grant.privileges);
68        }
69    }
70
71    // Check that we have the LOCK TABLES and SELECT privileges for each table
72    let mut errors = tables
73        .iter()
74        .flat_map(|table| {
75            ["LOCK TABLES", "SELECT"].iter().filter_map(|privilege| {
76                // Check both the wildcard schema and the specific schema
77                let privileged = [grant_map.get("*"), grant_map.get(table.schema_name)]
78                    .iter()
79                    .any(|schema_map| {
80                        // Check both the wildcard table and the specific table
81                        schema_map.map_or(false, |schema_map| {
82                            [schema_map.get("*"), schema_map.get(table.table_name)]
83                                .iter()
84                                .any(|privs| {
85                                    privs.map_or(false, |privs| {
86                                        privs.contains(*privilege)
87                                            || privs.contains("ALL PRIVILEGES")
88                                    })
89                                })
90                        })
91                    });
92
93                if !privileged {
94                    Some(MissingPrivilege {
95                        privilege: privilege.to_string(),
96                        qualified_table_name: format!("{}.{}", table.schema_name, table.table_name),
97                    })
98                } else {
99                    None
100                }
101            })
102        })
103        .collect::<Vec<_>>();
104
105    // Check that we have REPLICATION SLAVE priviliges at the global level
106    if !grant_map
107        .get("*")
108        .and_then(|schema_map| schema_map.get("*"))
109        .map(|privs| privs.contains("REPLICATION SLAVE") || privs.contains("ALL PRIVILEGES"))
110        .unwrap_or(false)
111    {
112        errors.push(MissingPrivilege {
113            privilege: "REPLICATION SLAVE".to_string(),
114            qualified_table_name: "*.*".to_string(),
115        });
116    }
117
118    if !errors.is_empty() {
119        Err(MySqlError::MissingPrivileges(errors))
120    } else {
121        Ok(())
122    }
123}
124
125/// Regex to parse a SHOW GRANTS line. Inspired by several stack overflow posts and
126/// adjusted to account for the different quoting styles across MySQL versions.
127/// If this regex matches then this is a grant on an actual object which looks like:
128///     GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `u1`@`localhost`
129///     GRANT SELECT ON `db1`.`table1` TO `my_user`@`localhost` WITH GRANT OPTION
130/// The regex needs to account for the possibility of a wildcard schema or table, and for the
131/// quote-char to be part of the table/schema name too.
132/// Group 1 is the list of privileges being granted
133/// Group 2 is either the wildcard * or a quoted database/schema
134/// Group 4 is the unquoted database/schema when the wildcard is not matched
135/// Group 5 is either the wildcard * or a quoted table
136/// Group 7 is the unquoted table when the wildcard is not matched
137/// Group 9 is the user being granted
138/// We use the `fancy_regex` crate to allow backreferences which are necessary to find the ending
139/// quote of each identifier since there are different quoting types across mysql versions.
140static GRANT_REGEX: LazyLock<Regex> = LazyLock::new(|| {
141    Regex::new(r#"GRANT (.+) ON (\*|(['`"])(.*)\3).(\*|(['`"])(.*)\6) TO (['`"])(.*)\8@.*"#)
142        .expect("valid")
143});
144
145#[derive(Debug, PartialEq, Eq)]
146struct MySqlObjectGrant {
147    privileges: BTreeSet<String>,
148    object_schema: String,
149    object_name: String,
150}
151
152/// Parses a returned row of a SHOW GRANTS statement to return a MySqlObjectGrant
153/// If the grant is not on an object (e.g. a grant of a role to a user), returns None
154fn get_object_grant(grant: &str) -> Option<MySqlObjectGrant> {
155    match GRANT_REGEX.captures(grant) {
156        Ok(None) => None,
157        Err(err) => {
158            tracing::warn!("Error parsing privilege grant: {}", err);
159            None
160        }
161        Ok(Some(captures)) => {
162            let object_schema = if captures.get(2).expect("valid").as_str() == "*" {
163                "*".to_string()
164            } else {
165                captures.get(4).expect("valid").as_str().to_string()
166            };
167            let object_name = if captures.get(5).expect("valid").as_str() == "*" {
168                "*".to_string()
169            } else {
170                captures.get(7).expect("valid").as_str().to_string()
171            };
172            let privileges = captures.get(1).expect("valid").as_str();
173            Some(MySqlObjectGrant {
174                privileges: privileges.split(", ").map(|s| s.to_string()).collect(),
175                object_schema,
176                object_name,
177            })
178        }
179    }
180}
181
182#[cfg(test)]
183mod tests {
184    use super::*;
185
186    #[mz_ore::test]
187    fn test_get_object_grant() {
188        // backticks and wildcard
189        let grant = "GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `u1`@`localhost`";
190        let expected_grant = MySqlObjectGrant {
191            privileges: ["SELECT", "INSERT", "UPDATE"]
192                .iter()
193                .map(|s| s.to_string())
194                .collect(),
195            object_schema: "db1".to_string(),
196            object_name: "*".to_string(),
197        };
198        assert_eq!(get_object_grant(grant), Some(expected_grant));
199
200        // single-quotes
201        let grant = "GRANT SUPER, CREATE TEMPORARY TABLES, LOCK TABLES ON 'db1'.'table1' TO `u1`@`localhost`";
202        let expected_grant = MySqlObjectGrant {
203            privileges: ["SUPER", "CREATE TEMPORARY TABLES", "LOCK TABLES"]
204                .iter()
205                .map(|s| s.to_string())
206                .collect(),
207            object_schema: "db1".to_string(),
208            object_name: "table1".to_string(),
209        };
210        assert_eq!(get_object_grant(grant), Some(expected_grant));
211
212        // wildcards
213        let grant = "GRANT ALL PRIVILEGES ON *.* TO `u1`@`localhost`";
214        let expected_grant = MySqlObjectGrant {
215            privileges: ["ALL PRIVILEGES"].iter().map(|s| s.to_string()).collect(),
216            object_schema: "*".to_string(),
217            object_name: "*".to_string(),
218        };
219        assert_eq!(get_object_grant(grant), Some(expected_grant));
220
221        // special chars
222        let grant = "GRANT SELECT, INSERT, UPDATE ON `таблица`.`mixED_CAse` TO `u1`@`localhost`";
223        let expected_grant = MySqlObjectGrant {
224            privileges: ["SELECT", "INSERT", "UPDATE"]
225                .iter()
226                .map(|s| s.to_string())
227                .collect(),
228            object_schema: "таблица".to_string(),
229            object_name: "mixED_CAse".to_string(),
230        };
231
232        assert_eq!(get_object_grant(grant), Some(expected_grant));
233
234        // quotes in names
235        let grant = "GRANT SUPER, CREATE TEMPORARY TABLES, LOCK TABLES ON `r`w`.`'sd'` TO `u1`@`localhost` IDENTIFIED BY PASSWORD `test`";
236        let expected_grant = MySqlObjectGrant {
237            privileges: ["SUPER", "CREATE TEMPORARY TABLES", "LOCK TABLES"]
238                .iter()
239                .map(|s| s.to_string())
240                .collect(),
241            object_schema: "r`w".to_string(),
242            object_name: "'sd'".to_string(),
243        };
244        assert_eq!(get_object_grant(grant), Some(expected_grant));
245    }
246}
mz_mysql_util/privileges.rs

mz_mysql_util/
privileges.rs
