Lines
0 %
Functions
use indoc::formatdoc;
use itertools::Itertools;
use std::collections::BTreeMap;
use serde::{Deserialize, Serialize};
use sqlx::MySqlConnection;
use sqlx::prelude::*;
use crate::{
core::{
common::UnixUser,
database_privileges::DATABASE_PRIVILEGE_FIELDS,
protocol::{
CreateUserError, CreateUsersResponse, DropUserError, DropUsersResponse,
ListAllUsersError, ListAllUsersResponse, ListUsersError, ListUsersResponse,
LockUserError, LockUsersResponse, SetPasswordError, SetUserPasswordResponse,
UnlockUserError, UnlockUsersResponse,
},
types::MySQLUser,
server::{
common::{create_user_group_matching_regex, try_get_with_binary_fallback},
input_sanitization::{quote_literal, validate_name, validate_ownership_by_unix_user},
};
// NOTE: this function is unsafe because it does no input validation.
async fn unsafe_user_exists(
db_user: &str,
connection: &mut MySqlConnection,
) -> Result<bool, sqlx::Error> {
let result = sqlx::query(
r#"
SELECT EXISTS(
SELECT 1
FROM `mysql`.`user`
WHERE `User` = ?
)
"#,
.bind(db_user)
.fetch_one(connection)
.await
.map(|row| row.get::<bool, _>(0));
if let Err(err) = &result {
log::error!("Failed to check if database user exists: {:?}", err);
}
result
pub async fn create_database_users(
db_users: Vec<MySQLUser>,
unix_user: &UnixUser,
) -> CreateUsersResponse {
let mut results = BTreeMap::new();
for db_user in db_users {
if let Err(err) = validate_name(&db_user) {
results.insert(db_user, Err(CreateUserError::SanitizationError(err)));
continue;
if let Err(err) = validate_ownership_by_unix_user(&db_user, unix_user) {
results.insert(db_user, Err(CreateUserError::OwnershipError(err)));
match unsafe_user_exists(&db_user, &mut *connection).await {
Ok(true) => {
results.insert(db_user, Err(CreateUserError::UserAlreadyExists));
Err(err) => {
results.insert(db_user, Err(CreateUserError::MySqlError(err.to_string())));
_ => {}
let result = sqlx::query(format!("CREATE USER {}@'%'", quote_literal(&db_user),).as_str())
.execute(&mut *connection)
.map(|_| ())
.map_err(|err| CreateUserError::MySqlError(err.to_string()));
log::error!("Failed to create database user '{}': {:?}", &db_user, err);
results.insert(db_user, result);
results
pub async fn drop_database_users(
) -> DropUsersResponse {
results.insert(db_user, Err(DropUserError::SanitizationError(err)));
results.insert(db_user, Err(DropUserError::OwnershipError(err)));
Ok(false) => {
results.insert(db_user, Err(DropUserError::UserDoesNotExist));
results.insert(db_user, Err(DropUserError::MySqlError(err.to_string())));
let result = sqlx::query(format!("DROP USER {}@'%'", quote_literal(&db_user),).as_str())
.map_err(|err| DropUserError::MySqlError(err.to_string()));
log::error!("Failed to drop database user '{}': {:?}", &db_user, err);
pub async fn set_password_for_database_user(
db_user: &MySQLUser,
password: &str,
) -> SetUserPasswordResponse {
if let Err(err) = validate_name(db_user) {
return Err(SetPasswordError::SanitizationError(err));
if let Err(err) = validate_ownership_by_unix_user(db_user, unix_user) {
return Err(SetPasswordError::OwnershipError(err));
match unsafe_user_exists(db_user, &mut *connection).await {
Ok(false) => return Err(SetPasswordError::UserDoesNotExist),
Err(err) => return Err(SetPasswordError::MySqlError(err.to_string())),
format!(
"ALTER USER {}@'%' IDENTIFIED BY {}",
quote_literal(db_user),
quote_literal(password).as_str(),
.as_str(),
.map_err(|err| SetPasswordError::MySqlError(err.to_string()));
if result.is_err() {
log::error!(
"Failed to set password for database user '{}': <REDACTED>",
&db_user,
);
async fn database_user_is_locked_unsafe(
SELECT COALESCE(
JSON_EXTRACT(`mysql`.`global_priv`.`priv`, "$.account_locked"),
'false'
) != 'false'
FROM `mysql`.`global_priv`
AND `Host` = '%'
"Failed to check if database user is locked '{}': {:?}",
err
pub async fn lock_database_users(
) -> LockUsersResponse {
results.insert(db_user, Err(LockUserError::SanitizationError(err)));
results.insert(db_user, Err(LockUserError::OwnershipError(err)));
Ok(true) => {}
results.insert(db_user, Err(LockUserError::UserDoesNotExist));
results.insert(db_user, Err(LockUserError::MySqlError(err.to_string())));
match database_user_is_locked_unsafe(&db_user, &mut *connection).await {
Ok(false) => {}
results.insert(db_user, Err(LockUserError::UserIsAlreadyLocked));
format!("ALTER USER {}@'%' ACCOUNT LOCK", quote_literal(&db_user),).as_str(),
.map_err(|err| LockUserError::MySqlError(err.to_string()));
log::error!("Failed to lock database user '{}': {:?}", &db_user, err);
pub async fn unlock_database_users(
) -> UnlockUsersResponse {
results.insert(db_user, Err(UnlockUserError::SanitizationError(err)));
results.insert(db_user, Err(UnlockUserError::OwnershipError(err)));
results.insert(db_user, Err(UnlockUserError::UserDoesNotExist));
results.insert(db_user, Err(UnlockUserError::MySqlError(err.to_string())));
results.insert(db_user, Err(UnlockUserError::UserIsAlreadyUnlocked));
format!("ALTER USER {}@'%' ACCOUNT UNLOCK", quote_literal(&db_user),).as_str(),
.map_err(|err| UnlockUserError::MySqlError(err.to_string()));
log::error!("Failed to unlock database user '{}': {:?}", &db_user, err);
/// This struct contains information about a database user.
/// This can be extended if we need more information in the future.
#[derive(Debug, Clone, PartialEq, Serialize, Deserialize)]
pub struct DatabaseUser {
pub user: MySQLUser,
#[serde(skip)]
pub host: String,
pub has_password: bool,
pub is_locked: bool,
pub databases: Vec<String>,
impl FromRow<'_, sqlx::mysql::MySqlRow> for DatabaseUser {
fn from_row(row: &sqlx::mysql::MySqlRow) -> Result<Self, sqlx::Error> {
Ok(Self {
user: try_get_with_binary_fallback(row, "User")?.into(),
host: try_get_with_binary_fallback(row, "Host")?,
has_password: row.try_get("has_password")?,
is_locked: row.try_get("is_locked")?,
databases: Vec::new(),
})
const DB_USER_SELECT_STATEMENT: &str = r#"
SELECT
`user`.`User`,
`user`.`Host`,
`user`.`Password` != '' OR `user`.`authentication_string` != '' AS `has_password`,
COALESCE(
JSON_EXTRACT(`global_priv`.`priv`, "$.account_locked"),
) != 'false' AS `is_locked`
FROM `user`
JOIN `global_priv` ON
`user`.`User` = `global_priv`.`User`
AND `user`.`Host` = `global_priv`.`Host`
"#;
pub async fn list_database_users(
) -> ListUsersResponse {
results.insert(db_user, Err(ListUsersError::SanitizationError(err)));
results.insert(db_user, Err(ListUsersError::OwnershipError(err)));
let mut result = sqlx::query_as::<_, DatabaseUser>(
&(DB_USER_SELECT_STATEMENT.to_string() + "WHERE `mysql`.`user`.`User` = ?"),
.bind(db_user.as_str())
.fetch_optional(&mut *connection)
.await;
log::error!("Failed to list database user '{}': {:?}", &db_user, err);
if let Ok(Some(user)) = result.as_mut() {
append_databases_where_user_has_privileges(user, &mut *connection).await;
match result {
Ok(Some(user)) => results.insert(db_user, Ok(user)),
Ok(None) => results.insert(db_user, Err(ListUsersError::UserDoesNotExist)),
Err(err) => results.insert(db_user, Err(ListUsersError::MySqlError(err.to_string()))),
pub async fn list_all_database_users_for_unix_user(
) -> ListAllUsersResponse {
&(DB_USER_SELECT_STATEMENT.to_string() + "WHERE `user`.`User` REGEXP ?"),
.bind(create_user_group_matching_regex(unix_user))
.fetch_all(&mut *connection)
.map_err(|err| ListAllUsersError::MySqlError(err.to_string()));
log::error!("Failed to list all database users: {:?}", err);
if let Ok(users) = result.as_mut() {
for user in users {
pub async fn append_databases_where_user_has_privileges(
db_user: &mut DatabaseUser,
) {
let database_list = sqlx::query(
formatdoc!(
SELECT `Db` AS `database`
FROM `db`
WHERE `User` = ? AND ({})
DATABASE_PRIVILEGE_FIELDS
.iter()
.map(|field| format!("`{}` = 'Y'", field))
.join(" OR "),
.bind(db_user.user.as_str())
if let Err(err) = &database_list {
"Failed to list databases for user '{}': {:?}",
&db_user.user,
db_user.databases = database_list
.map(|rows| {
rows.into_iter()
.map(|row| try_get_with_binary_fallback(&row, "database").unwrap())
.collect()
.unwrap_or_default();