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
19pub 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.
27
28    // 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.
31    let roles: Option<String> = conn
32        .exec_first("SELECT CURRENT_ROLE()", ())
33        .await
34        .ok()
35        .and_then(|val: Option<String>| match val {
36            Some(inner) if inner != "NONE" => Some(inner),
37            _ => None,
38        });
39
40    // Obtain the privileges for the current user using any roles that are default activated.
41    let grant_query = match roles {
42        None => "SHOW GRANTS FOR CURRENT_USER()".to_string(),
43        Some(roles) => format!("SHOW GRANTS FOR CURRENT_USER() USING {}", roles),
44    };
45
46    // Parse and collect the grants into a map of schema -> table -> privileges
47    let mut grant_map = BTreeMap::new();
48    for grant in conn.exec(grant_query, ()).await? {
49        let grant: String = grant;
50        if 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    }
59
60    // Check that we have the LOCK TABLES and SELECT privileges for each table
61    let 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
66                let 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
70                        schema_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                    });
81
82                if !privileged {
83                    Some(MissingPrivilege {
84                        privilege: privilege.to_string(),
85                        qualified_table_name: format!("{}.{}", table.schema_name, table.table_name),
86                    })
87                } else {
88                    None
89                }
90            })
91        })
92        .collect::<Vec<_>>();
93
94    // Check that we have REPLICATION SLAVE priviliges at the global level
95    if !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    }
106
107    if !errors.is_empty() {
108        Err(MySqlError::MissingPrivileges(errors))
109    } else {
110        Ok(())
111    }
112}
113
114/// 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});
133
134#[derive(Debug, PartialEq, Eq)]
135struct MySqlObjectGrant {
136    privileges: BTreeSet<String>,
137    object_schema: String,
138    object_name: String,
139}
140
141/// 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> {
144    match GRANT_REGEX.captures(grant) {
145        Ok(None) => None,
146        Err(err) => {
147            tracing::warn!("Error parsing privilege grant: {}", err);
148            None
149        }
150        Ok(Some(captures)) => {
151            let 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            };
156            let 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            };
161            let privileges = captures.get(1).expect("valid").as_str();
162            Some(MySqlObjectGrant {
163                privileges: privileges.split(", ").map(|s| s.to_string()).collect(),
164                object_schema,
165                object_name,
166            })
167        }
168    }
169}
170
171#[cfg(test)]
172mod tests {
173    use super::*;
174
175    #[mz_ore::test]
176    fn test_get_object_grant() {
177        // backticks and wildcard
178        let grant = "GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `u1`@`localhost`";
179        let 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        };
187        assert_eq!(get_object_grant(grant), Some(expected_grant));
188
189        // single-quotes
190        let grant = "GRANT SUPER, CREATE TEMPORARY TABLES, LOCK TABLES ON 'db1'.'table1' TO `u1`@`localhost`";
191        let 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        };
199        assert_eq!(get_object_grant(grant), Some(expected_grant));
200
201        // wildcards
202        let grant = "GRANT ALL PRIVILEGES ON *.* TO `u1`@`localhost`";
203        let 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        };
208        assert_eq!(get_object_grant(grant), Some(expected_grant));
209
210        // special chars
211        let grant = "GRANT SELECT, INSERT, UPDATE ON `таблица`.`mixED_CAse` TO `u1`@`localhost`";
212        let 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        };
220
221        assert_eq!(get_object_grant(grant), Some(expected_grant));
222
223        // quotes in names
224        let grant = "GRANT SUPER, CREATE TEMPORARY TABLES, LOCK TABLES ON `r`w`.`'sd'` TO `u1`@`localhost` IDENTIFIED BY PASSWORD `test`";
225        let 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        };
233        assert_eq!(get_object_grant(grant), Some(expected_grant));
234    }
235}
mz_mysql_util/privileges.rs

mz_mysql_util/
privileges.rs
