mysqladm/server/sql/
user_operations.rs

1use indoc::formatdoc;
2use itertools::Itertools;
3use std::collections::BTreeMap;
4
5use serde::{Deserialize, Serialize};
6
7use sqlx::MySqlConnection;
8use sqlx::prelude::*;
9
10use crate::{
11    core::{
12        common::UnixUser,
13        database_privileges::DATABASE_PRIVILEGE_FIELDS,
14        protocol::{
15            CreateUserError, CreateUsersResponse, DropUserError, DropUsersResponse,
16            ListAllUsersError, ListAllUsersResponse, ListUsersError, ListUsersResponse,
17            LockUserError, LockUsersResponse, SetPasswordError, SetUserPasswordResponse,
18            UnlockUserError, UnlockUsersResponse,
19        },
20        types::MySQLUser,
21    },
22    server::{
23        common::{create_user_group_matching_regex, try_get_with_binary_fallback},
24        input_sanitization::{quote_literal, validate_name, validate_ownership_by_unix_user},
25    },
26};
27
28// NOTE: this function is unsafe because it does no input validation.
29async fn unsafe_user_exists(
30    db_user: &str,
31    connection: &mut MySqlConnection,
32) -> Result<bool, sqlx::Error> {
33    let result = sqlx::query(
34        r#"
35          SELECT EXISTS(
36            SELECT 1
37            FROM `mysql`.`user`
38            WHERE `User` = ?
39          )
40        "#,
41    )
42    .bind(db_user)
43    .fetch_one(connection)
44    .await
45    .map(|row| row.get::<bool, _>(0));
46
47    if let Err(err) = &result {
48        log::error!("Failed to check if database user exists: {:?}", err);
49    }
50
51    result
52}
53
54pub async fn create_database_users(
55    db_users: Vec<MySQLUser>,
56    unix_user: &UnixUser,
57    connection: &mut MySqlConnection,
58) -> CreateUsersResponse {
59    let mut results = BTreeMap::new();
60
61    for db_user in db_users {
62        if let Err(err) = validate_name(&db_user) {
63            results.insert(db_user, Err(CreateUserError::SanitizationError(err)));
64            continue;
65        }
66
67        if let Err(err) = validate_ownership_by_unix_user(&db_user, unix_user) {
68            results.insert(db_user, Err(CreateUserError::OwnershipError(err)));
69            continue;
70        }
71
72        match unsafe_user_exists(&db_user, &mut *connection).await {
73            Ok(true) => {
74                results.insert(db_user, Err(CreateUserError::UserAlreadyExists));
75                continue;
76            }
77            Err(err) => {
78                results.insert(db_user, Err(CreateUserError::MySqlError(err.to_string())));
79                continue;
80            }
81            _ => {}
82        }
83
84        let result = sqlx::query(format!("CREATE USER {}@'%'", quote_literal(&db_user),).as_str())
85            .execute(&mut *connection)
86            .await
87            .map(|_| ())
88            .map_err(|err| CreateUserError::MySqlError(err.to_string()));
89
90        if let Err(err) = &result {
91            log::error!("Failed to create database user '{}': {:?}", &db_user, err);
92        }
93
94        results.insert(db_user, result);
95    }
96
97    results
98}
99
100pub async fn drop_database_users(
101    db_users: Vec<MySQLUser>,
102    unix_user: &UnixUser,
103    connection: &mut MySqlConnection,
104) -> DropUsersResponse {
105    let mut results = BTreeMap::new();
106
107    for db_user in db_users {
108        if let Err(err) = validate_name(&db_user) {
109            results.insert(db_user, Err(DropUserError::SanitizationError(err)));
110            continue;
111        }
112
113        if let Err(err) = validate_ownership_by_unix_user(&db_user, unix_user) {
114            results.insert(db_user, Err(DropUserError::OwnershipError(err)));
115            continue;
116        }
117
118        match unsafe_user_exists(&db_user, &mut *connection).await {
119            Ok(false) => {
120                results.insert(db_user, Err(DropUserError::UserDoesNotExist));
121                continue;
122            }
123            Err(err) => {
124                results.insert(db_user, Err(DropUserError::MySqlError(err.to_string())));
125                continue;
126            }
127            _ => {}
128        }
129
130        let result = sqlx::query(format!("DROP USER {}@'%'", quote_literal(&db_user),).as_str())
131            .execute(&mut *connection)
132            .await
133            .map(|_| ())
134            .map_err(|err| DropUserError::MySqlError(err.to_string()));
135
136        if let Err(err) = &result {
137            log::error!("Failed to drop database user '{}': {:?}", &db_user, err);
138        }
139
140        results.insert(db_user, result);
141    }
142
143    results
144}
145
146pub async fn set_password_for_database_user(
147    db_user: &MySQLUser,
148    password: &str,
149    unix_user: &UnixUser,
150    connection: &mut MySqlConnection,
151) -> SetUserPasswordResponse {
152    if let Err(err) = validate_name(db_user) {
153        return Err(SetPasswordError::SanitizationError(err));
154    }
155
156    if let Err(err) = validate_ownership_by_unix_user(db_user, unix_user) {
157        return Err(SetPasswordError::OwnershipError(err));
158    }
159
160    match unsafe_user_exists(db_user, &mut *connection).await {
161        Ok(false) => return Err(SetPasswordError::UserDoesNotExist),
162        Err(err) => return Err(SetPasswordError::MySqlError(err.to_string())),
163        _ => {}
164    }
165
166    let result = sqlx::query(
167        format!(
168            "ALTER USER {}@'%' IDENTIFIED BY {}",
169            quote_literal(db_user),
170            quote_literal(password).as_str(),
171        )
172        .as_str(),
173    )
174    .execute(&mut *connection)
175    .await
176    .map(|_| ())
177    .map_err(|err| SetPasswordError::MySqlError(err.to_string()));
178
179    if result.is_err() {
180        log::error!(
181            "Failed to set password for database user '{}': <REDACTED>",
182            &db_user,
183        );
184    }
185
186    result
187}
188
189// NOTE: this function is unsafe because it does no input validation.
190async fn database_user_is_locked_unsafe(
191    db_user: &str,
192    connection: &mut MySqlConnection,
193) -> Result<bool, sqlx::Error> {
194    let result = sqlx::query(
195        r#"
196          SELECT COALESCE(
197            JSON_EXTRACT(`mysql`.`global_priv`.`priv`, "$.account_locked"),
198            'false'
199          ) != 'false'
200          FROM `mysql`.`global_priv`
201          WHERE `User` = ?
202          AND `Host` = '%'
203        "#,
204    )
205    .bind(db_user)
206    .fetch_one(connection)
207    .await
208    .map(|row| row.get::<bool, _>(0));
209
210    if let Err(err) = &result {
211        log::error!(
212            "Failed to check if database user is locked '{}': {:?}",
213            &db_user,
214            err
215        );
216    }
217
218    result
219}
220
221pub async fn lock_database_users(
222    db_users: Vec<MySQLUser>,
223    unix_user: &UnixUser,
224    connection: &mut MySqlConnection,
225) -> LockUsersResponse {
226    let mut results = BTreeMap::new();
227
228    for db_user in db_users {
229        if let Err(err) = validate_name(&db_user) {
230            results.insert(db_user, Err(LockUserError::SanitizationError(err)));
231            continue;
232        }
233
234        if let Err(err) = validate_ownership_by_unix_user(&db_user, unix_user) {
235            results.insert(db_user, Err(LockUserError::OwnershipError(err)));
236            continue;
237        }
238
239        match unsafe_user_exists(&db_user, &mut *connection).await {
240            Ok(true) => {}
241            Ok(false) => {
242                results.insert(db_user, Err(LockUserError::UserDoesNotExist));
243                continue;
244            }
245            Err(err) => {
246                results.insert(db_user, Err(LockUserError::MySqlError(err.to_string())));
247                continue;
248            }
249        }
250
251        match database_user_is_locked_unsafe(&db_user, &mut *connection).await {
252            Ok(false) => {}
253            Ok(true) => {
254                results.insert(db_user, Err(LockUserError::UserIsAlreadyLocked));
255                continue;
256            }
257            Err(err) => {
258                results.insert(db_user, Err(LockUserError::MySqlError(err.to_string())));
259                continue;
260            }
261        }
262
263        let result = sqlx::query(
264            format!("ALTER USER {}@'%' ACCOUNT LOCK", quote_literal(&db_user),).as_str(),
265        )
266        .execute(&mut *connection)
267        .await
268        .map(|_| ())
269        .map_err(|err| LockUserError::MySqlError(err.to_string()));
270
271        if let Err(err) = &result {
272            log::error!("Failed to lock database user '{}': {:?}", &db_user, err);
273        }
274
275        results.insert(db_user, result);
276    }
277
278    results
279}
280
281pub async fn unlock_database_users(
282    db_users: Vec<MySQLUser>,
283    unix_user: &UnixUser,
284    connection: &mut MySqlConnection,
285) -> UnlockUsersResponse {
286    let mut results = BTreeMap::new();
287
288    for db_user in db_users {
289        if let Err(err) = validate_name(&db_user) {
290            results.insert(db_user, Err(UnlockUserError::SanitizationError(err)));
291            continue;
292        }
293
294        if let Err(err) = validate_ownership_by_unix_user(&db_user, unix_user) {
295            results.insert(db_user, Err(UnlockUserError::OwnershipError(err)));
296            continue;
297        }
298
299        match unsafe_user_exists(&db_user, &mut *connection).await {
300            Ok(false) => {
301                results.insert(db_user, Err(UnlockUserError::UserDoesNotExist));
302                continue;
303            }
304            Err(err) => {
305                results.insert(db_user, Err(UnlockUserError::MySqlError(err.to_string())));
306                continue;
307            }
308            _ => {}
309        }
310
311        match database_user_is_locked_unsafe(&db_user, &mut *connection).await {
312            Ok(false) => {
313                results.insert(db_user, Err(UnlockUserError::UserIsAlreadyUnlocked));
314                continue;
315            }
316            Err(err) => {
317                results.insert(db_user, Err(UnlockUserError::MySqlError(err.to_string())));
318                continue;
319            }
320            _ => {}
321        }
322
323        let result = sqlx::query(
324            format!("ALTER USER {}@'%' ACCOUNT UNLOCK", quote_literal(&db_user),).as_str(),
325        )
326        .execute(&mut *connection)
327        .await
328        .map(|_| ())
329        .map_err(|err| UnlockUserError::MySqlError(err.to_string()));
330
331        if let Err(err) = &result {
332            log::error!("Failed to unlock database user '{}': {:?}", &db_user, err);
333        }
334
335        results.insert(db_user, result);
336    }
337
338    results
339}
340
341/// This struct contains information about a database user.
342/// This can be extended if we need more information in the future.
343#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
344pub struct DatabaseUser {
345    pub user: MySQLUser,
346    #[serde(skip)]
347    pub host: String,
348    pub has_password: bool,
349    pub is_locked: bool,
350    pub databases: Vec<String>,
351}
352
353impl FromRow<'_, sqlx::mysql::MySqlRow> for DatabaseUser {
354    fn from_row(row: &sqlx::mysql::MySqlRow) -> Result<Self, sqlx::Error> {
355        Ok(Self {
356            user: try_get_with_binary_fallback(row, "User")?.into(),
357            host: try_get_with_binary_fallback(row, "Host")?,
358            has_password: row.try_get("has_password")?,
359            is_locked: row.try_get("is_locked")?,
360            databases: Vec::new(),
361        })
362    }
363}
364
365const DB_USER_SELECT_STATEMENT: &str = r#"
366SELECT
367  `user`.`User`,
368  `user`.`Host`,
369  `user`.`Password` != '' OR `user`.`authentication_string` != '' AS `has_password`,
370  COALESCE(
371    JSON_EXTRACT(`global_priv`.`priv`, "$.account_locked"),
372    'false'
373  ) != 'false' AS `is_locked`
374FROM `user`
375JOIN `global_priv` ON
376  `user`.`User` = `global_priv`.`User`
377  AND `user`.`Host` = `global_priv`.`Host`
378"#;
379
380pub async fn list_database_users(
381    db_users: Vec<MySQLUser>,
382    unix_user: &UnixUser,
383    connection: &mut MySqlConnection,
384) -> ListUsersResponse {
385    let mut results = BTreeMap::new();
386
387    for db_user in db_users {
388        if let Err(err) = validate_name(&db_user) {
389            results.insert(db_user, Err(ListUsersError::SanitizationError(err)));
390            continue;
391        }
392
393        if let Err(err) = validate_ownership_by_unix_user(&db_user, unix_user) {
394            results.insert(db_user, Err(ListUsersError::OwnershipError(err)));
395            continue;
396        }
397
398        let mut result = sqlx::query_as::<_, DatabaseUser>(
399            &(DB_USER_SELECT_STATEMENT.to_string() + "WHERE `mysql`.`user`.`User` = ?"),
400        )
401        .bind(db_user.as_str())
402        .fetch_optional(&mut *connection)
403        .await;
404
405        if let Err(err) = &result {
406            log::error!("Failed to list database user '{}': {:?}", &db_user, err);
407        }
408
409        if let Ok(Some(user)) = result.as_mut() {
410            append_databases_where_user_has_privileges(user, &mut *connection).await;
411        }
412
413        match result {
414            Ok(Some(user)) => results.insert(db_user, Ok(user)),
415            Ok(None) => results.insert(db_user, Err(ListUsersError::UserDoesNotExist)),
416            Err(err) => results.insert(db_user, Err(ListUsersError::MySqlError(err.to_string()))),
417        };
418    }
419
420    results
421}
422
423pub async fn list_all_database_users_for_unix_user(
424    unix_user: &UnixUser,
425    connection: &mut MySqlConnection,
426) -> ListAllUsersResponse {
427    let mut result = sqlx::query_as::<_, DatabaseUser>(
428        &(DB_USER_SELECT_STATEMENT.to_string() + "WHERE `user`.`User` REGEXP ?"),
429    )
430    .bind(create_user_group_matching_regex(unix_user))
431    .fetch_all(&mut *connection)
432    .await
433    .map_err(|err| ListAllUsersError::MySqlError(err.to_string()));
434
435    if let Err(err) = &result {
436        log::error!("Failed to list all database users: {:?}", err);
437    }
438
439    if let Ok(users) = result.as_mut() {
440        for user in users {
441            append_databases_where_user_has_privileges(user, &mut *connection).await;
442        }
443    }
444
445    result
446}
447
448pub async fn append_databases_where_user_has_privileges(
449    db_user: &mut DatabaseUser,
450    connection: &mut MySqlConnection,
451) {
452    let database_list = sqlx::query(
453        formatdoc!(
454            r#"
455                SELECT `Db` AS `database`
456                FROM `db`
457                WHERE `User` = ? AND ({})
458            "#,
459            DATABASE_PRIVILEGE_FIELDS
460                .iter()
461                .map(|field| format!("`{}` = 'Y'", field))
462                .join(" OR "),
463        )
464        .as_str(),
465    )
466    .bind(db_user.user.as_str())
467    .fetch_all(&mut *connection)
468    .await;
469
470    if let Err(err) = &database_list {
471        log::error!(
472            "Failed to list databases for user '{}': {:?}",
473            &db_user.user,
474            err
475        );
476    }
477
478    db_user.databases = database_list
479        .map(|rows| {
480            rows.into_iter()
481                .map(|row| try_get_with_binary_fallback(&row, "database").unwrap())
482                .collect()
483        })
484        .unwrap_or_default();
485}
mysqladm/server/sql/user_operations.rs

mysqladm/server/sql/
user_operations.rs
