in modules/util/dump/schema_dumper.cc [2695:3108]
std::vector<Schema_dumper::Issue> Schema_dumper::dump_grants(
IFile *file, const common::Filtering_options &filters) {
std::vector<Issue> problems;
std::map<std::string, std::string> default_roles;
bool compatibility = opt_strip_restricted_grants;
log_debug("Dumping grants for server");
fputs("--\n-- Dumping user accounts\n--\n\n", file);
const auto roles = get_roles(filters.users());
const auto is_role = [&roles](const shcore::Account &a) {
return roles.end() != std::find(roles.begin(), roles.end(), a);
};
using get_grants_t =
std::function<std::vector<std::string>(const std::string &)>;
const get_grants_t get_grants_all = [this](const std::string &u) {
const auto result = query_log_and_throw("SHOW GRANTS FOR " + u);
std::vector<std::string> grants;
while (const auto row = result->fetch_one()) {
grants.emplace_back(row->get_string(0));
}
return grants;
};
std::unordered_map<std::string, std::vector<std::string>> all_grants_5_6;
const get_grants_t get_grants_5_6 = [&all_grants_5_6](const std::string &u) {
return all_grants_5_6.at(u);
};
const auto is_5_6 = m_cache
? m_cache->server_version.is_5_6
: m_mysql->get_server_version() < Version(5, 7, 0);
const auto &get_grants = is_5_6 ? get_grants_5_6 : get_grants_all;
using get_create_user_t = std::function<std::string(const std::string &)>;
const get_create_user_t get_create_user_5_7_or_8_0 =
[this](const std::string &u) -> std::string {
const auto result = query_log_and_throw("SHOW CREATE USER " + u);
if (const auto row = result->fetch_one()) {
return row->get_string(0);
}
return "";
};
const get_create_user_t get_create_user_5_6 = [this, &get_grants_all,
&all_grants_5_6](
const std::string &u) {
auto grants = get_grants_all(u);
std::string create_user;
if (!grants.empty()) {
// CREATE USER needs to have information about the authentication plugin,
// so that it can be processed in the subsequent steps, GRANT statement
// may lack this information, fetch it from the DB
std::string user;
std::string host;
shcore::split_account(u, &user, &host);
const auto result = query_log_and_throw(shcore::sqlformat(
"SELECT plugin FROM mysql.user WHERE User=? AND Host=?", user, host));
const auto plugin = result->fetch_one_or_throw()->get_string(0);
// first grant contains the information required to recreate the account
create_user = compatibility::convert_grant_to_create_user(
grants[0], plugin, &grants[0]);
all_grants_5_6.emplace(u, std::move(grants));
}
return create_user;
};
const auto &get_create_user =
is_5_6 ? get_create_user_5_6 : get_create_user_5_7_or_8_0;
std::vector<std::string> users;
for (const auto &u : get_users(filters.users())) {
const auto user = shcore::make_account(u);
if (u.user.find('\'') != std::string::npos) {
// we don't allow accounts with 's in them because they're incorrectly
// escaped in the output of SHOW GRANTS, which would generate invalid
// or dangerous SQL.
THROW_ERROR(SHERR_DUMP_ACCOUNT_WITH_APOSTROPHE, user.c_str());
}
auto create_user = get_create_user(user);
if (create_user.empty()) {
current_console()->print_error("No create user statement for user " +
user);
return problems;
}
create_user = shcore::str_replace(create_user, "CREATE USER",
"CREATE USER IF NOT EXISTS");
bool add_user = true;
if (opt_mysqlaas || opt_skip_invalid_accounts) {
const auto plugin =
compatibility::check_create_user_for_authentication_plugin(
create_user);
if (!plugin.empty()) {
// we're removing the user from the list even if
// opt_skip_invalid_accounts is not set, account is invalid in MDS, so
// other checks can be skipped
add_user = false;
problems.emplace_back(
"User " + user +
" is using an unsupported authentication plugin '" + plugin +
"'" +
(opt_skip_invalid_accounts
? ", this account has been removed from the dump"
: ""),
opt_skip_invalid_accounts
? Issue::Status::FIXED
: Issue::Status::USE_SKIP_INVALID_ACCOUNTS);
}
if (add_user &&
compatibility::check_create_user_for_empty_password(create_user)) {
if (is_role(u)) {
// the account with an empty password is a role, convert it to
// CREATE ROLE statement, so that it is loaded without problems when
// validate_password is active
create_user =
compatibility::convert_create_user_to_create_role(create_user);
} else {
add_user = false;
problems.emplace_back(
"User " + user + " does not have a password set" +
(opt_skip_invalid_accounts
? ", this account has been removed from the dump"
: ""),
opt_skip_invalid_accounts
? Issue::Status::FIXED
: Issue::Status::USE_SKIP_INVALID_ACCOUNTS);
}
}
}
if (add_user) {
fputs("-- begin user " + user + "\n", file);
auto default_role =
compatibility::strip_default_role(create_user, &create_user);
if (!default_role.empty())
default_roles.emplace(user, std::move(default_role));
fputs(create_user + ";\n", file);
fputs("-- end user " + user + "\n\n", file);
users.emplace_back(std::move(user));
}
}
for (const auto &user : users) {
std::set<std::string> restricted;
auto grants = get_grants(user);
for (auto &grant : grants) {
if (opt_mysqlaas || compatibility) {
auto mysql_table_grant =
compatibility::is_grant_on_object_from_mysql_schema(grant);
if (!mysql_table_grant.empty()) {
if (opt_strip_restricted_grants) {
grant.clear();
problems.emplace_back(
"User " + user +
" had explicit grants on mysql schema object " +
std::string{mysql_table_grant} + " removed",
Issue::Status::FIXED);
continue;
} else {
problems.emplace_back(
"User " + user +
" has explicit grants on mysql schema object: " +
std::string{mysql_table_grant},
Issue::Status::USE_STRIP_RESTRICTED_GRANTS);
}
}
// In MySQL <= 5.7, if a user has all privs, the SHOW GRANTS will say
// ALL PRIVILEGES, which isn't helpful for filtering out grants.
// Also, ALL PRIVILEGES can appear even in 8.0 for DB grants
std::string orig_grant = grant;
std::string schema;
grant = expand_all_privileges(grant, user, &schema);
// grants on specific user schemas don't need to be filtered
if (schema != "" && !is_system_schema(schema) && schema != "*") {
grant = orig_grant;
} else {
std::set<std::string> allowed_privs;
if (opt_mysqlaas || opt_strip_restricted_grants) {
allowed_privs = compatibility::k_mysqlaas_allowed_privileges;
}
std::string rewritten;
const auto privs = compatibility::check_privileges(
grant, compatibility ? &rewritten : nullptr, allowed_privs);
if (!privs.empty()) {
restricted.insert(privs.begin(), privs.end());
if (compatibility) grant = rewritten;
}
}
}
if (!grant.empty()) {
compatibility::Privilege_level_info priv;
bool comment_out = false;
if (compatibility::parse_grant_statement(grant, &priv)) {
// check if grant is on an existing object
using Level = compatibility::Privilege_level_info::Level;
const auto check_if_missing = [this, &priv, &problems, &user, &grant,
&comment_out](
const std::string &query) {
const auto result =
query_log_error(query, priv.schema, priv.object);
if (result->fetch_one()) {
// object exists
return;
}
std::string object_type;
switch (priv.level) {
case Level::TABLE:
object_type = "table";
break;
case Level::ROUTINE:
object_type = "routine";
break;
default:
throw std::logic_error("Unexpected object type");
}
if (opt_strip_invalid_grants) {
comment_out = true;
problems.emplace_back(
"User " + user + " had grant statement on a non-existent " +
object_type + " removed (" + grant + ")",
Issue::Status::FIXED);
} else {
problems.emplace_back(
"User " + user + " has grant statement on a non-existent " +
object_type + " (" + grant + ")",
Issue::Status::USE_STRIP_INVALID_GRANTS);
}
};
bool included_object = true;
switch (priv.level) {
case Level::GLOBAL:
// nothing to do
break;
case Level::SCHEMA:
// NOTE: grants on a missing schema are valid
// handle wildcard grants at schema level
if ((opt_mysqlaas || opt_ignore_wildcard_grants) &&
shcore::has_sql_wildcard(priv.schema)) {
std::string prefix =
"User " + user +
" has a wildcard grant statement at the database level (" +
grant + ")";
if (opt_ignore_wildcard_grants) {
problems.emplace_back(prefix + ", this is ignored",
Issue::Status::FIXED);
} else if (!partial_revokes()) {
// only report error if partial_revokes is disabled, if it's
// enabled we assume that grants are valid
problems.emplace_back(std::move(prefix),
Issue::Status::FIX_WILDCARD_GRANTS);
}
}
// check if schema is included in the dump
if (partial_revokes()) {
// if partial revokes are enabled, server treats wildcard
// characters as any other character, we trust that grants are
// referring to existing schemas and use exact match here
included_object = filters.schemas().is_included(priv.schema);
} else {
if (shcore::has_sql_wildcard(priv.schema)) {
if (shcore::has_unescaped_sql_wildcard(priv.schema)) {
// if there's an unescaped wildcard character we're unable
// to verify if such schema is included or not, we don't
// warn about these
included_object = true;
} else {
// schema name has only escaped wildcard characters, we're
// able to check if this schema is included, but we need to
// use wildcard match
included_object =
filters.schemas().matches_included(priv.schema);
}
} else {
// there are no wildcards, use exact match
included_object = filters.schemas().is_included(priv.schema);
}
}
break;
case Level::TABLE:
// grant on a missing table is invalid if it does not contain a
// CREATE privilege
if (0 == priv.privileges.count("CREATE")) {
check_if_missing(
"SELECT TABLE_NAME FROM information_schema.tables WHERE "
"TABLE_SCHEMA=? AND TABLE_NAME=?");
}
// check if table is included in the dump
included_object =
filters.tables().is_included(priv.schema, priv.object);
break;
case Level::ROUTINE:
// all grants on a missing routine are invalid
check_if_missing(
"SELECT ROUTINE_NAME FROM information_schema.routines WHERE "
"ROUTINE_SCHEMA=? AND ROUTINE_NAME=?");
// check if routine is included in the dump
// BUG#34764157 routine names are case insensitive, MySQL 5.7 has
// lower-case names of routines in grant statements
included_object =
filters.routines().is_included_ci(priv.schema, priv.object);
break;
case Level::ROLE:
for (const auto &role : priv.privileges) {
if (!filters.users().is_included(role)) {
problems.emplace_back(
"User " + user + " has a grant statement on a role " +
role + " which is not included in the dump (" +
grant + ")",
Issue::Status::WARNING);
}
}
break;
}
if (!included_object) {
problems.emplace_back("User " + user +
" has a grant statement on an object "
"which is not included in the dump (" +
grant + ")",
Issue::Status::WARNING);
}
}
if (comment_out) {
grant = "/* " + grant + " */";
}
}
}
if (!restricted.empty()) {
if (opt_strip_restricted_grants) {
problems.emplace_back(
"User " + user + " had restricted " +
(restricted.size() > 1 ? "privileges (" : "privilege (") +
shcore::str_join(restricted, ", ") + ") removed",
Issue::Status::FIXED);
} else {
problems.emplace_back(
"User " + user + " is granted restricted " +
(restricted.size() > 1 ? "privileges: " : "privilege: ") +
shcore::str_join(restricted, ", "),
Issue::Status::USE_STRIP_RESTRICTED_GRANTS);
}
}
grants.erase(std::remove_if(grants.begin(), grants.end(),
[](const auto &g) { return g.empty(); }),
grants.end());
if (!grants.empty()) {
fputs("-- begin grants " + user + "\n", file);
for (const auto &grant : grants) fputs(grant + ";\n", file);
fputs("-- end grants " + user + "\n\n", file);
}
}
for (const auto &df : default_roles) {
fputs("-- begin default role " + df.first + "\n", file);
fprintf(file, "SET %s TO %s;\n", df.second.c_str(), df.first.c_str());
fputs("-- end default role " + df.first + "\n\n", file);
}
fputs("-- End of dumping user accounts\n\n", file);
return problems;
}