Marek Miklewicz
ba64342702
phpMyAdmin login was broken because phpmyadmin_install.sh never wired the private phpMyAdmin copy into the web server: no Apache Alias was registered via DirectAdmin CustomBuild, so the SSO redirect target was unreachable. Add configure_apache_alias()/apply_apache_alias() (Alias + Directory blocks, ./build rewrite_confs, httpd reload), detect the real Apache group instead of hardcoding diradmin:diradmin, stop silently swallowing chown/chmod failures, and verify an optional SHA256 for the downloaded phpMyAdmin tarball. Extend phpmyadmin_health_check.sh to detect a missing/incorrect Apache alias and to probe HTTP reachability. Security hardening: scope temporary phpMyAdmin SSO MySQL roles to 127.0.0.1 instead of '%', tighten SSO ticket file permissions to 0640 (they contain a plaintext MySQL password), and log MySQL connection failures for diagnosis instead of swallowing them silently. Bump version to 1.2.12 and rebuild the release archive.
1176 lines
38 KiB
PHP
1176 lines
38 KiB
PHP
<?php
|
|
declare(strict_types=1);
|
|
|
|
final class MySQLService
|
|
{
|
|
private const TMP_ROLE_PREFIX = 'da_tmp_phpmyadmin_';
|
|
public const TMP_ROLE_HOST = '127.0.0.1';
|
|
|
|
/** @var array{host:string,port:int,database:string,user:string,password:string,socket:string} */
|
|
private array $credentials;
|
|
|
|
private Settings $settings;
|
|
|
|
/** @var array<string,mysqli> */
|
|
private array $connections = [];
|
|
|
|
public function __construct(array $credentials, Settings $settings)
|
|
{
|
|
$database = $credentials['database'] ?? 'mysql';
|
|
if ($database === '*' || trim((string)$database) === '') {
|
|
$database = 'mysql';
|
|
}
|
|
|
|
$host = trim((string)($credentials['host'] ?? 'localhost'));
|
|
if ($host === '') {
|
|
$host = 'localhost';
|
|
}
|
|
|
|
$this->credentials = [
|
|
'host' => $host,
|
|
'port' => (int)($credentials['port'] ?? 3306),
|
|
'database' => (string)$database,
|
|
'user' => (string)($credentials['user'] ?? 'root'),
|
|
'password' => (string)($credentials['password'] ?? ''),
|
|
'socket' => (string)($credentials['socket'] ?? ''),
|
|
];
|
|
$this->settings = $settings;
|
|
}
|
|
|
|
public function ensureMetadataSchema(): void
|
|
{
|
|
$this->query(
|
|
'CREATE TABLE IF NOT EXISTS da_plugin_access_hosts (
|
|
da_user VARCHAR(255) NOT NULL,
|
|
role_name VARCHAR(255) NOT NULL,
|
|
host_pattern VARCHAR(255) NOT NULL,
|
|
note VARCHAR(180) NOT NULL DEFAULT \'\',
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (role_name, host_pattern),
|
|
KEY access_hosts_da_user_idx (da_user)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
|
|
);
|
|
|
|
$this->query(
|
|
'CREATE TABLE IF NOT EXISTS da_plugin_grant_profiles (
|
|
db_name VARCHAR(255) NOT NULL,
|
|
role_name VARCHAR(255) NOT NULL,
|
|
privileges TEXT NOT NULL,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (db_name, role_name)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
|
|
);
|
|
|
|
$this->query(
|
|
'CREATE TABLE IF NOT EXISTS da_plugin_database_owners (
|
|
db_name VARCHAR(255) NOT NULL,
|
|
da_user VARCHAR(255) NOT NULL,
|
|
role_name VARCHAR(255) NOT NULL,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (db_name),
|
|
KEY database_owners_da_user_idx (da_user),
|
|
KEY database_owners_role_idx (role_name)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
|
|
);
|
|
|
|
$this->query(
|
|
'CREATE TABLE IF NOT EXISTS da_plugin_sso_accounts (
|
|
role_name VARCHAR(255) NOT NULL,
|
|
host_pattern VARCHAR(255) NOT NULL,
|
|
expires_at INT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (role_name, host_pattern),
|
|
KEY sso_accounts_expires_idx (expires_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
|
|
);
|
|
|
|
$this->query(
|
|
'CREATE TABLE IF NOT EXISTS da_plugin_system_databases (
|
|
service_name VARCHAR(64) NOT NULL,
|
|
db_name VARCHAR(255) NOT NULL,
|
|
db_user VARCHAR(255) NOT NULL,
|
|
endpoint_mode VARCHAR(32) NOT NULL DEFAULT \'tcp\',
|
|
config_path VARCHAR(512) NOT NULL DEFAULT \'\',
|
|
enabled TINYINT(1) NOT NULL DEFAULT 1,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (service_name),
|
|
KEY system_databases_db_idx (db_name),
|
|
KEY system_databases_user_idx (db_user)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
|
|
);
|
|
}
|
|
|
|
/**
|
|
* @return array{host:string,port:int}
|
|
*/
|
|
public function connectionEndpoint(): array
|
|
{
|
|
return [
|
|
'host' => $this->credentials['host'],
|
|
'port' => (int)$this->credentials['port'],
|
|
];
|
|
}
|
|
|
|
public function serverVersion(): string
|
|
{
|
|
$row = $this->fetchOne('SELECT VERSION() AS version');
|
|
return trim((string)($row['version'] ?? ''));
|
|
}
|
|
|
|
public function countDatabasesForUser(string $daUser): int
|
|
{
|
|
$row = $this->fetchOne(
|
|
'SELECT COUNT(*) AS cnt
|
|
FROM information_schema.SCHEMATA
|
|
WHERE SCHEMA_NAME LIKE ? ESCAPE \'\\\\\'',
|
|
[$this->likePattern($daUser . '_')]
|
|
);
|
|
|
|
return isset($row['cnt']) ? (int)$row['cnt'] : 0;
|
|
}
|
|
|
|
/**
|
|
* @return array<int,array{name:string,owner:string,size:string}>
|
|
*/
|
|
public function listDatabasesForUser(string $daUser, bool $includeSize = true): array
|
|
{
|
|
$sizeSql = $includeSize
|
|
? 'COALESCE((
|
|
SELECT SUM(t.DATA_LENGTH + t.INDEX_LENGTH)
|
|
FROM information_schema.TABLES t
|
|
WHERE t.TABLE_SCHEMA = s.SCHEMA_NAME
|
|
), 0) AS size_bytes'
|
|
: '0 AS size_bytes';
|
|
|
|
$rows = $this->fetchAll(
|
|
'SELECT s.SCHEMA_NAME AS name,
|
|
COALESCE(o.role_name, \'\') AS owner,
|
|
' . $sizeSql . '
|
|
FROM information_schema.SCHEMATA s
|
|
LEFT JOIN da_plugin_database_owners o ON o.db_name = s.SCHEMA_NAME
|
|
WHERE s.SCHEMA_NAME LIKE ? ESCAPE \'\\\\\'
|
|
ORDER BY s.SCHEMA_NAME',
|
|
[$this->likePattern($daUser . '_')]
|
|
);
|
|
|
|
$out = [];
|
|
foreach ($rows as $row) {
|
|
$out[] = [
|
|
'name' => (string)$row['name'],
|
|
'owner' => (string)($row['owner'] ?? ''),
|
|
'size' => $this->formatBytes((int)($row['size_bytes'] ?? 0)),
|
|
];
|
|
}
|
|
|
|
return $out;
|
|
}
|
|
|
|
/**
|
|
* @return array<int,array{name:string,can_login:bool}>
|
|
*/
|
|
public function listRolesForUser(string $daUser): array
|
|
{
|
|
$rows = $this->fetchAll(
|
|
'SELECT DISTINCT User AS name
|
|
FROM mysql.user
|
|
WHERE User LIKE ? ESCAPE \'\\\\\'
|
|
ORDER BY User',
|
|
[$this->likePattern($daUser . '_')]
|
|
);
|
|
|
|
$out = [];
|
|
foreach ($rows as $row) {
|
|
$name = (string)($row['name'] ?? '');
|
|
if ($name === '' || strpos($name, self::TMP_ROLE_PREFIX) === 0) {
|
|
continue;
|
|
}
|
|
$out[] = [
|
|
'name' => $name,
|
|
'can_login' => true,
|
|
];
|
|
}
|
|
|
|
return $out;
|
|
}
|
|
|
|
/**
|
|
* @return array<string,array<int,string>>
|
|
*/
|
|
public function listDatabaseUsersMap(string $daUser): array
|
|
{
|
|
$rows = $this->fetchAll(
|
|
'SELECT db_name, role_name
|
|
FROM da_plugin_grant_profiles
|
|
WHERE db_name LIKE ? ESCAPE \'\\\\\'
|
|
AND role_name LIKE ? ESCAPE \'\\\\\'
|
|
ORDER BY db_name, role_name',
|
|
[$this->likePattern($daUser . '_'), $this->likePattern($daUser . '_')]
|
|
);
|
|
|
|
$map = [];
|
|
foreach ($rows as $row) {
|
|
$db = (string)($row['db_name'] ?? '');
|
|
$role = (string)($row['role_name'] ?? '');
|
|
if ($db === '' || $role === '') {
|
|
continue;
|
|
}
|
|
$map[$db] ??= [];
|
|
$map[$db][] = $role;
|
|
}
|
|
|
|
return $map;
|
|
}
|
|
|
|
/**
|
|
* @return array<string,array<int,string>>
|
|
*/
|
|
public function listRoleDatabasesMap(string $daUser): array
|
|
{
|
|
$dbMap = $this->listDatabaseUsersMap($daUser);
|
|
$out = [];
|
|
foreach ($dbMap as $db => $roles) {
|
|
foreach ($roles as $role) {
|
|
$out[$role] ??= [];
|
|
$out[$role][] = $db;
|
|
}
|
|
}
|
|
|
|
ksort($out);
|
|
return $out;
|
|
}
|
|
|
|
/**
|
|
* @return string[]
|
|
*/
|
|
public function listDatabaseUsers(string $daUser, string $database): array
|
|
{
|
|
$rows = $this->fetchAll(
|
|
'SELECT role_name
|
|
FROM da_plugin_grant_profiles
|
|
WHERE db_name = ?
|
|
AND role_name LIKE ? ESCAPE \'\\\\\'
|
|
ORDER BY role_name',
|
|
[$database, $this->likePattern($daUser . '_')]
|
|
);
|
|
|
|
$out = [];
|
|
foreach ($rows as $row) {
|
|
$role = (string)($row['role_name'] ?? '');
|
|
if ($role !== '') {
|
|
$out[] = $role;
|
|
}
|
|
}
|
|
return $out;
|
|
}
|
|
|
|
public function roleExists(string $role): bool
|
|
{
|
|
$row = $this->fetchOne('SELECT 1 AS ok FROM mysql.user WHERE User = ? LIMIT 1', [$role]);
|
|
return !empty($row);
|
|
}
|
|
|
|
public function databaseExists(string $dbName): bool
|
|
{
|
|
$row = $this->fetchOne('SELECT 1 AS ok FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = ? LIMIT 1', [$dbName]);
|
|
return !empty($row);
|
|
}
|
|
|
|
public function getDatabaseOwner(string $dbName): ?string
|
|
{
|
|
$row = $this->fetchOne('SELECT role_name FROM da_plugin_database_owners WHERE db_name = ?', [$dbName]);
|
|
if (empty($row['role_name'])) {
|
|
return null;
|
|
}
|
|
return (string)$row['role_name'];
|
|
}
|
|
|
|
/**
|
|
* @return array{name:string,owner:string,size:string,encoding:string,collation:string}
|
|
*/
|
|
public function getDatabaseInfo(string $dbName): array
|
|
{
|
|
$row = $this->fetchOne(
|
|
'SELECT s.SCHEMA_NAME AS name,
|
|
COALESCE(o.role_name, \'\') AS owner,
|
|
s.DEFAULT_CHARACTER_SET_NAME AS encoding,
|
|
s.DEFAULT_COLLATION_NAME AS collation,
|
|
COALESCE((
|
|
SELECT SUM(t.DATA_LENGTH + t.INDEX_LENGTH)
|
|
FROM information_schema.TABLES t
|
|
WHERE t.TABLE_SCHEMA = s.SCHEMA_NAME
|
|
), 0) AS size_bytes
|
|
FROM information_schema.SCHEMATA s
|
|
LEFT JOIN da_plugin_database_owners o ON o.db_name = s.SCHEMA_NAME
|
|
WHERE s.SCHEMA_NAME = ?',
|
|
[$dbName]
|
|
);
|
|
|
|
if (empty($row)) {
|
|
throw new RuntimeException('Nie znaleziono bazy danych: ' . $dbName);
|
|
}
|
|
|
|
return [
|
|
'name' => (string)($row['name'] ?? $dbName),
|
|
'owner' => (string)($row['owner'] ?? ''),
|
|
'size' => $this->formatBytes((int)($row['size_bytes'] ?? 0)),
|
|
'encoding' => (string)($row['encoding'] ?? 'utf8mb4'),
|
|
'collation' => (string)($row['collation'] ?? ''),
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @return array{tables:int,views:int,triggers:int,routines:int}
|
|
*/
|
|
public function getDatabaseObjectStats(string $dbName): array
|
|
{
|
|
$row = $this->fetchOne(
|
|
'SELECT
|
|
COALESCE(SUM(CASE WHEN TABLE_TYPE = \'BASE TABLE\' THEN 1 ELSE 0 END), 0) AS tables_cnt,
|
|
COALESCE(SUM(CASE WHEN TABLE_TYPE = \'VIEW\' THEN 1 ELSE 0 END), 0) AS views_cnt
|
|
FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA = ?',
|
|
[$dbName]
|
|
);
|
|
|
|
$triggerRow = $this->fetchOne(
|
|
'SELECT COUNT(*) AS cnt
|
|
FROM information_schema.TRIGGERS
|
|
WHERE TRIGGER_SCHEMA = ?',
|
|
[$dbName]
|
|
);
|
|
|
|
$routineRow = $this->fetchOne(
|
|
'SELECT COUNT(*) AS cnt
|
|
FROM information_schema.ROUTINES
|
|
WHERE ROUTINE_SCHEMA = ?',
|
|
[$dbName]
|
|
);
|
|
|
|
return [
|
|
'tables' => (int)($row['tables_cnt'] ?? 0),
|
|
'views' => (int)($row['views_cnt'] ?? 0),
|
|
'triggers' => (int)($triggerRow['cnt'] ?? 0),
|
|
'routines' => (int)($routineRow['cnt'] ?? 0),
|
|
];
|
|
}
|
|
|
|
/**
|
|
* @return string[]
|
|
*/
|
|
public function roleOwnedDatabases(string $role, string $daUser): array
|
|
{
|
|
$rows = $this->fetchAll(
|
|
'SELECT db_name
|
|
FROM da_plugin_database_owners
|
|
WHERE role_name = ?
|
|
AND db_name LIKE ? ESCAPE \'\\\\\'
|
|
ORDER BY db_name',
|
|
[$role, $this->likePattern($daUser . '_')]
|
|
);
|
|
|
|
$out = [];
|
|
foreach ($rows as $row) {
|
|
$name = (string)($row['db_name'] ?? '');
|
|
if ($name !== '') {
|
|
$out[] = $name;
|
|
}
|
|
}
|
|
return $out;
|
|
}
|
|
|
|
/**
|
|
* @return string[]
|
|
*/
|
|
public function roleAssignedDatabases(string $role, string $daUser): array
|
|
{
|
|
$rows = $this->fetchAll(
|
|
'SELECT db_name
|
|
FROM da_plugin_grant_profiles
|
|
WHERE role_name = ?
|
|
AND db_name LIKE ? ESCAPE \'\\\\\'
|
|
ORDER BY db_name',
|
|
[$role, $this->likePattern($daUser . '_')]
|
|
);
|
|
|
|
$out = [];
|
|
foreach ($rows as $row) {
|
|
$name = (string)($row['db_name'] ?? '');
|
|
if ($name !== '') {
|
|
$out[] = $name;
|
|
}
|
|
}
|
|
return $out;
|
|
}
|
|
|
|
/**
|
|
* @return array<int,array{host:string,note:string}>
|
|
*/
|
|
public function getRoleHostEntries(string $daUser, string $role): array
|
|
{
|
|
$rows = $this->fetchAll(
|
|
'SELECT host_pattern, note
|
|
FROM da_plugin_access_hosts
|
|
WHERE da_user = ? AND role_name = ?
|
|
ORDER BY host_pattern',
|
|
[$daUser, $role]
|
|
);
|
|
|
|
$map = [];
|
|
foreach ($rows as $row) {
|
|
$host = (string)$row['host_pattern'];
|
|
if ($host === '') {
|
|
continue;
|
|
}
|
|
$map[$host] = $this->sanitizeHostNote((string)($row['note'] ?? ''));
|
|
}
|
|
|
|
if ($this->roleExists($role)) {
|
|
$this->ensureDefaultRoleHostsProvisioned($role);
|
|
foreach ($this->listActualRoleHosts($role) as $host) {
|
|
if (!isset($map[$host])) {
|
|
$map[$host] = $this->requiredAccessHostNote($host);
|
|
}
|
|
}
|
|
foreach ($this->requiredAccessHosts() as $host) {
|
|
if (!isset($map[$host])) {
|
|
$map[$host] = $this->requiredAccessHostNote($host);
|
|
} elseif ($map[$host] === '') {
|
|
$map[$host] = $this->requiredAccessHostNote($host);
|
|
}
|
|
}
|
|
}
|
|
|
|
if (empty($map) && $this->roleExists($role)) {
|
|
$map['localhost'] = $this->requiredAccessHostNote('localhost');
|
|
}
|
|
|
|
ksort($map);
|
|
$out = [];
|
|
foreach ($map as $host => $note) {
|
|
$out[] = ['host' => $host, 'note' => $note];
|
|
}
|
|
return $out;
|
|
}
|
|
|
|
/**
|
|
* @return string[]
|
|
*/
|
|
public function getRoleHosts(string $daUser, string $role): array
|
|
{
|
|
$entries = $this->getRoleHostEntries($daUser, $role);
|
|
$out = [];
|
|
foreach ($entries as $entry) {
|
|
$out[] = $entry['host'];
|
|
}
|
|
return $out;
|
|
}
|
|
|
|
/**
|
|
* @param string[] $hosts
|
|
*/
|
|
public function replaceRoleHosts(string $daUser, string $role, array $hosts): void
|
|
{
|
|
$entries = [];
|
|
foreach ($hosts as $host) {
|
|
$entries[] = ['host' => (string)$host, 'note' => ''];
|
|
}
|
|
$this->replaceRoleHostsWithNotes($daUser, $role, $entries);
|
|
}
|
|
|
|
/**
|
|
* @param array<int,array{host:string,note:string}> $hostEntries
|
|
*/
|
|
public function replaceRoleHostsWithNotes(string $daUser, string $role, array $hostEntries): void
|
|
{
|
|
$normalized = [];
|
|
foreach ($hostEntries as $entry) {
|
|
$host = trim((string)($entry['host'] ?? ''));
|
|
if ($host === '') {
|
|
continue;
|
|
}
|
|
$normalized[$host] = $this->sanitizeHostNote((string)($entry['note'] ?? ''));
|
|
}
|
|
foreach ($this->requiredAccessHosts() as $requiredHost) {
|
|
if (!isset($normalized[$requiredHost])) {
|
|
$normalized[$requiredHost] = $this->requiredAccessHostNote($requiredHost);
|
|
} elseif ($normalized[$requiredHost] === '') {
|
|
$normalized[$requiredHost] = $this->requiredAccessHostNote($requiredHost);
|
|
}
|
|
}
|
|
|
|
$currentHosts = $this->listActualRoleHosts($role);
|
|
if (empty($currentHosts) && !$this->roleExists($role)) {
|
|
throw new RuntimeException('Użytkownik MySQL nie istnieje: ' . $role);
|
|
}
|
|
|
|
foreach (array_keys($normalized) as $host) {
|
|
if (!in_array($host, $currentHosts, true)) {
|
|
$this->cloneRoleToHost($role, $host);
|
|
}
|
|
}
|
|
|
|
foreach ($currentHosts as $host) {
|
|
if ($host === 'localhost' || isset($normalized[$host])) {
|
|
continue;
|
|
}
|
|
$this->dropRoleHost($role, $host);
|
|
}
|
|
|
|
$this->query('DELETE FROM da_plugin_access_hosts WHERE da_user = ? AND role_name = ?', [$daUser, $role]);
|
|
foreach ($normalized as $host => $note) {
|
|
$this->query(
|
|
'INSERT INTO da_plugin_access_hosts (da_user, role_name, host_pattern, note)
|
|
VALUES (?, ?, ?, ?)
|
|
ON DUPLICATE KEY UPDATE da_user = VALUES(da_user), note = VALUES(note), updated_at = CURRENT_TIMESTAMP',
|
|
[$daUser, $role, $host, $note]
|
|
);
|
|
}
|
|
|
|
foreach ($this->roleAssignedDatabases($role, $daUser) as $dbName) {
|
|
$profile = $this->getGrantProfile($dbName, $role);
|
|
if (!empty($profile)) {
|
|
$this->grantPrivileges($dbName, $role, $profile);
|
|
}
|
|
}
|
|
}
|
|
|
|
public function deleteRoleHosts(string $daUser, string $role): void
|
|
{
|
|
foreach ($this->listActualRoleHosts($role) as $host) {
|
|
if ($host === 'localhost') {
|
|
continue;
|
|
}
|
|
$this->dropRoleHost($role, $host);
|
|
}
|
|
$this->query('DELETE FROM da_plugin_access_hosts WHERE da_user = ? AND role_name = ?', [$daUser, $role]);
|
|
}
|
|
|
|
public function createRole(string $role, string $password): void
|
|
{
|
|
$qRole = $this->quoteLiteral($role);
|
|
$qPassword = $this->quoteLiteral($password);
|
|
$this->query('CREATE USER ' . $qRole . '@\'localhost\' IDENTIFIED BY ' . $qPassword);
|
|
}
|
|
|
|
public function changeRolePassword(string $role, string $password): void
|
|
{
|
|
$qPassword = $this->quoteLiteral($password);
|
|
$this->ensureDefaultRoleHostsProvisioned($role);
|
|
$hosts = $this->listActualRoleHosts($role);
|
|
if (empty($hosts)) {
|
|
throw new RuntimeException('Użytkownik MySQL nie istnieje: ' . $role);
|
|
}
|
|
|
|
foreach ($hosts as $host) {
|
|
$this->query(
|
|
'ALTER USER ' . $this->quoteLiteral($role) . '@' . $this->quoteLiteral($host) . ' IDENTIFIED BY ' . $qPassword
|
|
);
|
|
}
|
|
}
|
|
|
|
public function createDatabase(string $dbName, string $owner): void
|
|
{
|
|
$qDb = $this->quoteIdentifier($dbName);
|
|
$this->query('CREATE DATABASE ' . $qDb . ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
|
|
$this->query(
|
|
'INSERT INTO da_plugin_database_owners (db_name, da_user, role_name)
|
|
VALUES (?, ?, ?)
|
|
ON DUPLICATE KEY UPDATE da_user = VALUES(da_user), role_name = VALUES(role_name), updated_at = CURRENT_TIMESTAMP',
|
|
[$dbName, '', $owner]
|
|
);
|
|
$this->grantAllPrivileges($dbName, $owner);
|
|
}
|
|
|
|
public function dropDatabase(string $dbName): void
|
|
{
|
|
$this->query('DROP DATABASE IF EXISTS ' . $this->quoteIdentifier($dbName));
|
|
$this->query('DELETE FROM da_plugin_database_owners WHERE db_name = ?', [$dbName]);
|
|
$this->query('DELETE FROM da_plugin_grant_profiles WHERE db_name = ?', [$dbName]);
|
|
}
|
|
|
|
public function dropRole(string $role): void
|
|
{
|
|
foreach ($this->listActualRoleHosts($role) as $host) {
|
|
$this->dropRoleHost($role, $host);
|
|
}
|
|
$this->query('DELETE FROM da_plugin_access_hosts WHERE role_name = ?', [$role]);
|
|
$this->query('DELETE FROM da_plugin_grant_profiles WHERE role_name = ?', [$role]);
|
|
$this->query('DELETE FROM da_plugin_database_owners WHERE role_name = ?', [$role]);
|
|
$this->query('DELETE FROM da_plugin_sso_accounts WHERE role_name = ?', [$role]);
|
|
}
|
|
|
|
public function reassignOwnedObjects(string $dbName, string $fromRole, string $toRole): void
|
|
{
|
|
if ($fromRole === $toRole) {
|
|
return;
|
|
}
|
|
|
|
$this->query(
|
|
'UPDATE da_plugin_database_owners
|
|
SET role_name = ?, da_user = ?, updated_at = CURRENT_TIMESTAMP
|
|
WHERE db_name = ? AND role_name = ?',
|
|
[$toRole, '', $dbName, $fromRole]
|
|
);
|
|
if ($this->databaseExists($dbName) && $this->roleExists($toRole)) {
|
|
$this->grantAllPrivileges($dbName, $toRole);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @param string[] $privileges
|
|
*/
|
|
public function grantPrivileges(string $dbName, string $role, array $privileges): void
|
|
{
|
|
$privileges = array_values(array_unique($privileges));
|
|
if (empty($privileges)) {
|
|
throw new RuntimeException('Brak uprawnień do nadania.');
|
|
}
|
|
|
|
$this->ensureDefaultRoleHostsProvisioned($role);
|
|
$hosts = $this->listActualRoleHosts($role);
|
|
if (empty($hosts)) {
|
|
throw new RuntimeException('Użytkownik MySQL nie istnieje: ' . $role);
|
|
}
|
|
|
|
if (in_array('ALL', $privileges, true)) {
|
|
$this->grantAllPrivileges($dbName, $role);
|
|
$this->saveGrantProfile($dbName, $role, ['ALL']);
|
|
return;
|
|
}
|
|
|
|
$this->revokePrivilegesInternal($dbName, $role, false);
|
|
$grantSql = implode(', ', array_map([$this, 'mapPrivilegeCodeToSql'], $privileges));
|
|
foreach ($hosts as $host) {
|
|
$this->query(
|
|
'GRANT ' . $grantSql . ' ON ' . $this->quoteIdentifier($dbName) . '.* TO ' .
|
|
$this->quoteLiteral($role) . '@' . $this->quoteLiteral($host)
|
|
);
|
|
}
|
|
|
|
sort($privileges);
|
|
$this->saveGrantProfile($dbName, $role, $privileges);
|
|
}
|
|
|
|
public function revokePrivileges(string $dbName, string $role): void
|
|
{
|
|
$this->revokePrivilegesInternal($dbName, $role, true);
|
|
}
|
|
|
|
/**
|
|
* @return string[]
|
|
*/
|
|
public function getGrantProfile(string $dbName, string $role): array
|
|
{
|
|
$row = $this->fetchOne(
|
|
'SELECT privileges FROM da_plugin_grant_profiles WHERE db_name = ? AND role_name = ?',
|
|
[$dbName, $role]
|
|
);
|
|
|
|
if (empty($row['privileges'])) {
|
|
return [];
|
|
}
|
|
|
|
$parts = array_filter(array_map('trim', explode(',', (string)$row['privileges'])));
|
|
$parts = array_map('strtoupper', $parts);
|
|
return array_values(array_unique($parts));
|
|
}
|
|
|
|
/**
|
|
* @return array{ok:bool,output:string}
|
|
*/
|
|
public function syncHostRules(): array
|
|
{
|
|
if (!$this->settings->modifyServerConfiguration()) {
|
|
return ['ok' => true, 'output' => ''];
|
|
}
|
|
|
|
$script = $this->settings->hostSyncScript();
|
|
if (!is_file($script)) {
|
|
return ['ok' => false, 'output' => 'Brak skryptu synchronizacji hostów MySQL: ' . $script];
|
|
}
|
|
|
|
$cmd = escapeshellarg($script) . ' 2>&1';
|
|
$output = [];
|
|
$exitCode = 0;
|
|
exec($cmd, $output, $exitCode);
|
|
|
|
return [
|
|
'ok' => $exitCode === 0,
|
|
'output' => trim(implode("\n", $output)),
|
|
];
|
|
}
|
|
|
|
public function createTemporaryRole(string $role, string $password, int $expiresAt): void
|
|
{
|
|
$this->query(
|
|
'CREATE USER ' . $this->quoteLiteral($role) . '@' . $this->quoteLiteral(self::TMP_ROLE_HOST) .
|
|
' IDENTIFIED BY ' . $this->quoteLiteral($password)
|
|
);
|
|
$this->query(
|
|
'INSERT INTO da_plugin_sso_accounts (role_name, host_pattern, expires_at)
|
|
VALUES (?, ?, ?)
|
|
ON DUPLICATE KEY UPDATE expires_at = VALUES(expires_at)',
|
|
[$role, self::TMP_ROLE_HOST, (string)$expiresAt]
|
|
);
|
|
}
|
|
|
|
public function grantTemporaryAdminerAccess(string $dbName, string $role): void
|
|
{
|
|
$this->query(
|
|
'GRANT ALL PRIVILEGES ON ' . $this->quoteIdentifier($dbName) . '.* TO ' .
|
|
$this->quoteLiteral($role) . '@' . $this->quoteLiteral(self::TMP_ROLE_HOST)
|
|
);
|
|
}
|
|
|
|
public function dropTemporaryRole(string $role): void
|
|
{
|
|
$this->tryQuery('DROP USER IF EXISTS ' . $this->quoteLiteral($role) . '@' . $this->quoteLiteral(self::TMP_ROLE_HOST));
|
|
$this->query('DELETE FROM da_plugin_sso_accounts WHERE role_name = ?', [$role]);
|
|
}
|
|
|
|
public function cleanupExpiredAdminerRoles(string $rolePrefix = self::TMP_ROLE_PREFIX): int
|
|
{
|
|
$rows = $this->fetchAll(
|
|
'SELECT role_name
|
|
FROM da_plugin_sso_accounts
|
|
WHERE role_name LIKE ? ESCAPE \'\\\\\'
|
|
AND expires_at < ?
|
|
ORDER BY role_name',
|
|
[$this->likePattern($rolePrefix), (string)time()]
|
|
);
|
|
|
|
$removed = 0;
|
|
foreach ($rows as $row) {
|
|
$role = (string)($row['role_name'] ?? '');
|
|
if ($role === '') {
|
|
continue;
|
|
}
|
|
|
|
try {
|
|
$this->dropTemporaryRole($role);
|
|
$removed++;
|
|
} catch (Throwable $e) {
|
|
// Ignorujemy błędy cleanupu, aby nie blokować logowania.
|
|
}
|
|
}
|
|
|
|
return $removed;
|
|
}
|
|
|
|
private function grantAllPrivileges(string $dbName, string $role): void
|
|
{
|
|
$this->ensureDefaultRoleHostsProvisioned($role);
|
|
foreach ($this->listActualRoleHosts($role) as $host) {
|
|
$this->query(
|
|
'GRANT ALL PRIVILEGES ON ' . $this->quoteIdentifier($dbName) . '.* TO ' .
|
|
$this->quoteLiteral($role) . '@' . $this->quoteLiteral($host)
|
|
);
|
|
}
|
|
}
|
|
|
|
private function revokePrivilegesInternal(string $dbName, string $role, bool $removeProfile): void
|
|
{
|
|
foreach ($this->listActualRoleHosts($role) as $host) {
|
|
$this->tryQuery(
|
|
'REVOKE ALL PRIVILEGES, GRANT OPTION ON ' . $this->quoteIdentifier($dbName) . '.* FROM ' .
|
|
$this->quoteLiteral($role) . '@' . $this->quoteLiteral($host)
|
|
);
|
|
}
|
|
|
|
if ($removeProfile) {
|
|
$this->query('DELETE FROM da_plugin_grant_profiles WHERE db_name = ? AND role_name = ?', [$dbName, $role]);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @param string[] $privileges
|
|
*/
|
|
private function saveGrantProfile(string $dbName, string $role, array $privileges): void
|
|
{
|
|
sort($privileges);
|
|
$serialized = implode(',', $privileges);
|
|
$this->query(
|
|
'INSERT INTO da_plugin_grant_profiles (db_name, role_name, privileges)
|
|
VALUES (?, ?, ?)
|
|
ON DUPLICATE KEY UPDATE privileges = VALUES(privileges), updated_at = CURRENT_TIMESTAMP',
|
|
[$dbName, $role, $serialized]
|
|
);
|
|
}
|
|
|
|
/**
|
|
* @return string[]
|
|
*/
|
|
private function listActualRoleHosts(string $role): array
|
|
{
|
|
$rows = $this->fetchAll(
|
|
'SELECT Host AS host
|
|
FROM mysql.user
|
|
WHERE User = ?
|
|
ORDER BY CASE WHEN Host = \'localhost\' THEN 0 ELSE 1 END, Host',
|
|
[$role]
|
|
);
|
|
|
|
$hosts = [];
|
|
foreach ($rows as $row) {
|
|
$host = (string)($row['host'] ?? '');
|
|
if ($host !== '') {
|
|
$hosts[] = $host;
|
|
}
|
|
}
|
|
return array_values(array_unique($hosts));
|
|
}
|
|
|
|
private function ensureDefaultRoleHostsProvisioned(string $role): void
|
|
{
|
|
$hosts = $this->listActualRoleHosts($role);
|
|
if (empty($hosts)) {
|
|
return;
|
|
}
|
|
|
|
foreach ($this->requiredAccessHosts() as $requiredHost) {
|
|
if (!in_array($requiredHost, $hosts, true)) {
|
|
$this->cloneRoleToHost($role, $requiredHost);
|
|
$hosts[] = $requiredHost;
|
|
}
|
|
}
|
|
}
|
|
|
|
private function cloneRoleToHost(string $role, string $targetHost): void
|
|
{
|
|
$source = $this->fetchOne(
|
|
'SELECT Host AS host, plugin, authentication_string
|
|
FROM mysql.user
|
|
WHERE User = ?
|
|
ORDER BY CASE WHEN Host = \'localhost\' THEN 0 ELSE 1 END, Host
|
|
LIMIT 1',
|
|
[$role]
|
|
);
|
|
|
|
if (empty($source)) {
|
|
throw new RuntimeException('Nie można utworzyć hosta dla użytkownika MySQL bez konta źródłowego.');
|
|
}
|
|
|
|
$plugin = (string)($source['plugin'] ?? '');
|
|
$authString = (string)($source['authentication_string'] ?? '');
|
|
$quotedRole = $this->quoteLiteral($role);
|
|
$quotedHost = $this->quoteLiteral($targetHost);
|
|
|
|
if ($plugin !== '' && $authString !== '') {
|
|
$sql = 'CREATE USER ' . $quotedRole . '@' . $quotedHost .
|
|
' IDENTIFIED WITH ' . $this->quotePluginName($plugin) .
|
|
' AS ' . $this->quoteLiteral($authString);
|
|
} elseif ($authString !== '') {
|
|
$sql = 'CREATE USER ' . $quotedRole . '@' . $quotedHost .
|
|
' IDENTIFIED BY PASSWORD ' . $this->quoteLiteral($authString);
|
|
} else {
|
|
throw new RuntimeException('Nie można sklonować uwierzytelnienia użytkownika MySQL dla nowego hosta.');
|
|
}
|
|
|
|
$this->query($sql);
|
|
}
|
|
|
|
private function dropRoleHost(string $role, string $host): void
|
|
{
|
|
$this->tryQuery('DROP USER IF EXISTS ' . $this->quoteLiteral($role) . '@' . $this->quoteLiteral($host));
|
|
}
|
|
|
|
private function sanitizeHostNote(string $note): string
|
|
{
|
|
$note = preg_replace('/[\r\n\t]+/', ' ', $note) ?? '';
|
|
$note = preg_replace('/\s+/', ' ', $note) ?? '';
|
|
$note = trim($note);
|
|
|
|
if (strlen($note) > NamePolicy::MAX_HOST_COMMENT_LEN) {
|
|
$note = substr($note, 0, NamePolicy::MAX_HOST_COMMENT_LEN);
|
|
}
|
|
|
|
return $note;
|
|
}
|
|
|
|
/**
|
|
* @return string[]
|
|
*/
|
|
public function requiredAccessHosts(): array
|
|
{
|
|
$hosts = ['localhost'];
|
|
if (!$this->isRemoteDatabaseHost()) {
|
|
return $hosts;
|
|
}
|
|
|
|
foreach ($this->detectDirectAdminServerHosts() as $host) {
|
|
if (!in_array($host, $hosts, true)) {
|
|
$hosts[] = $host;
|
|
}
|
|
}
|
|
|
|
return $hosts;
|
|
}
|
|
|
|
private function requiredAccessHostNote(string $host): string
|
|
{
|
|
if ($host === 'localhost') {
|
|
return 'Dodawany automatycznie dla polaczen lokalnych.';
|
|
}
|
|
|
|
if (in_array($host, $this->requiredAccessHosts(), true)) {
|
|
return 'Adres serwera DirectAdmin dodawany automatycznie dla zdalnego MySQL.';
|
|
}
|
|
|
|
return '';
|
|
}
|
|
|
|
private function isRemoteDatabaseHost(): bool
|
|
{
|
|
$host = trim($this->credentials['host']);
|
|
return !$this->isLocalHost($host);
|
|
}
|
|
|
|
/**
|
|
* @return string[]
|
|
*/
|
|
private function detectDirectAdminServerHosts(): array
|
|
{
|
|
$hosts = [];
|
|
|
|
$manual = trim($this->settings->getString('MYSQL_PLUGIN_LOCAL_SERVER_HOST', ''));
|
|
if ($manual !== '') {
|
|
foreach (preg_split('/[\s,;]+/', $manual) ?: [] as $candidate) {
|
|
$candidate = trim((string)$candidate);
|
|
if ($this->isUsableRemoteAccessHost($candidate) && !in_array($candidate, $hosts, true)) {
|
|
$hosts[] = $candidate;
|
|
}
|
|
}
|
|
}
|
|
|
|
$serverAddr = trim(Http::server('SERVER_ADDR'));
|
|
if ($this->isUsableRemoteAccessHost($serverAddr) && !in_array($serverAddr, $hosts, true)) {
|
|
$hosts[] = $serverAddr;
|
|
}
|
|
|
|
$daConf = '/usr/local/directadmin/conf/directadmin.conf';
|
|
if (is_readable($daConf)) {
|
|
$lines = file($daConf, FILE_IGNORE_NEW_LINES);
|
|
if ($lines !== false) {
|
|
foreach ($lines as $line) {
|
|
$line = trim((string)$line);
|
|
if ($line === '' || $line[0] === '#') {
|
|
continue;
|
|
}
|
|
if (!preg_match('/^(?:ip|server_ip)\s*=\s*(.+)$/i', $line, $m)) {
|
|
continue;
|
|
}
|
|
$candidate = trim((string)$m[1]);
|
|
if ($this->isUsableRemoteAccessHost($candidate) && !in_array($candidate, $hosts, true)) {
|
|
$hosts[] = $candidate;
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
$resolved = @gethostbyname((string)php_uname('n'));
|
|
if ($this->isUsableRemoteAccessHost($resolved) && !in_array($resolved, $hosts, true)) {
|
|
$hosts[] = $resolved;
|
|
}
|
|
|
|
return $hosts;
|
|
}
|
|
|
|
private function isLocalHost(string $host): bool
|
|
{
|
|
$host = strtolower(trim($host));
|
|
return $host === '' || in_array($host, ['localhost', '127.0.0.1', '::1'], true);
|
|
}
|
|
|
|
private function isUsableRemoteAccessHost(string $host): bool
|
|
{
|
|
$host = trim($host);
|
|
if ($host === '' || $this->isLocalHost($host)) {
|
|
return false;
|
|
}
|
|
|
|
return filter_var($host, FILTER_VALIDATE_IP, FILTER_FLAG_IPV4) !== false;
|
|
}
|
|
|
|
private function likePattern(string $prefix): string
|
|
{
|
|
return str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $prefix) . '%';
|
|
}
|
|
|
|
private function quoteIdentifier(string $name): string
|
|
{
|
|
if (!preg_match('/^[A-Za-z0-9_]+$/', $name)) {
|
|
throw new RuntimeException('Nieprawidłowy identyfikator MySQL: ' . $name);
|
|
}
|
|
|
|
if (strlen($name) > NamePolicy::MAX_IDENTIFIER_LEN) {
|
|
throw new RuntimeException('Identyfikator MySQL przekracza 64 znaki: ' . $name);
|
|
}
|
|
|
|
return '`' . str_replace('`', '``', $name) . '`';
|
|
}
|
|
|
|
private function quotePluginName(string $name): string
|
|
{
|
|
if (!preg_match('/^[A-Za-z0-9_]+$/', $name)) {
|
|
throw new RuntimeException('Nieprawidłowa nazwa pluginu uwierzytelniania MySQL: ' . $name);
|
|
}
|
|
|
|
return $name;
|
|
}
|
|
|
|
private function quoteLiteral(string $value, string $database = ''): string
|
|
{
|
|
return '\'' . $this->connect($database)->real_escape_string($value) . '\'';
|
|
}
|
|
|
|
private function mapPrivilegeCodeToSql(string $code): string
|
|
{
|
|
return match ($code) {
|
|
'CREATE_VIEW' => 'CREATE VIEW',
|
|
'SHOW_VIEW' => 'SHOW VIEW',
|
|
'LOCK_TABLES' => 'LOCK TABLES',
|
|
default => $code,
|
|
};
|
|
}
|
|
|
|
private function formatBytes(int $bytes): string
|
|
{
|
|
if ($bytes < 1024) {
|
|
return $bytes . ' B';
|
|
}
|
|
|
|
$units = ['KB', 'MB', 'GB', 'TB'];
|
|
$value = $bytes;
|
|
foreach ($units as $unit) {
|
|
$value /= 1024;
|
|
if ($value < 1024 || $unit === 'TB') {
|
|
return number_format($value, $value >= 10 ? 0 : 1, '.', '') . ' ' . $unit;
|
|
}
|
|
}
|
|
|
|
return $bytes . ' B';
|
|
}
|
|
|
|
/**
|
|
* @param array<int,mixed> $params
|
|
*/
|
|
private function query(string $sql, array $params = [], string $database = ''): mysqli_result|bool
|
|
{
|
|
$conn = $this->connect($database);
|
|
|
|
if ($params !== []) {
|
|
$sql = $this->interpolateQuery($conn, $sql, $params);
|
|
}
|
|
|
|
$res = @$conn->query($sql);
|
|
|
|
if ($res === false) {
|
|
throw new RuntimeException(trim((string)$conn->error) . ' | SQL: ' . $sql);
|
|
}
|
|
|
|
return $res;
|
|
}
|
|
|
|
/**
|
|
* @param array<int,mixed> $params
|
|
*/
|
|
private function interpolateQuery(mysqli $conn, string $sql, array $params): string
|
|
{
|
|
$parts = explode('?', $sql);
|
|
if (count($parts) - 1 !== count($params)) {
|
|
throw new RuntimeException('Nieprawidłowa liczba parametrów SQL.');
|
|
}
|
|
|
|
$out = array_shift($parts);
|
|
foreach ($params as $index => $param) {
|
|
$value = is_bool($param) ? ($param ? '1' : '0') : (string)$param;
|
|
$out .= '\'' . $conn->real_escape_string($value) . '\'' . $parts[$index];
|
|
}
|
|
|
|
return (string)$out;
|
|
}
|
|
|
|
/**
|
|
* @param array<int,mixed> $params
|
|
*/
|
|
private function tryQuery(string $sql, array $params = [], string $database = ''): void
|
|
{
|
|
try {
|
|
$this->query($sql, $params, $database);
|
|
} catch (Throwable $e) {
|
|
// celowo ignorujemy błędy pomocnicze
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @param array<int,mixed> $params
|
|
* @return array<int,array<string,mixed>>
|
|
*/
|
|
private function fetchAll(string $sql, array $params = [], string $database = ''): array
|
|
{
|
|
$res = $this->query($sql, $params, $database);
|
|
if (!$res instanceof mysqli_result) {
|
|
return [];
|
|
}
|
|
|
|
$rows = $res->fetch_all(MYSQLI_ASSOC);
|
|
$res->free();
|
|
return is_array($rows) ? $rows : [];
|
|
}
|
|
|
|
/**
|
|
* @param array<int,mixed> $params
|
|
* @return array<string,mixed>
|
|
*/
|
|
private function fetchOne(string $sql, array $params = [], string $database = ''): array
|
|
{
|
|
$rows = $this->fetchAll($sql, $params, $database);
|
|
return $rows[0] ?? [];
|
|
}
|
|
|
|
private function connect(string $database = ''): mysqli
|
|
{
|
|
$targetDb = $database !== '' ? $database : $this->credentials['database'];
|
|
if (isset($this->connections[$targetDb])) {
|
|
return $this->connections[$targetDb];
|
|
}
|
|
|
|
mysqli_report(MYSQLI_REPORT_OFF);
|
|
$conn = mysqli_init();
|
|
if (!$conn instanceof mysqli) {
|
|
throw new RuntimeException('Nie udało się zainicjalizować połączenia MySQL.');
|
|
}
|
|
|
|
$socket = '';
|
|
if ($this->credentials['host'] === 'localhost' && $this->credentials['socket'] !== '') {
|
|
$socket = $this->credentials['socket'];
|
|
}
|
|
|
|
$ok = @$conn->real_connect(
|
|
$this->credentials['host'],
|
|
$this->credentials['user'],
|
|
$this->credentials['password'],
|
|
$targetDb,
|
|
$this->credentials['port'],
|
|
$socket !== '' ? $socket : null
|
|
);
|
|
|
|
if ($ok !== true) {
|
|
error_log(sprintf(
|
|
'[%s] MYSQL_CONNECT_FAIL host=%s port=%d database=%s error=%s',
|
|
date('c'),
|
|
$this->credentials['host'],
|
|
$this->credentials['port'],
|
|
$targetDb,
|
|
$conn->connect_error ?? 'unknown'
|
|
));
|
|
throw new RuntimeException('Nie udało się połączyć z MySQL.');
|
|
}
|
|
|
|
$conn->set_charset('utf8mb4');
|
|
$this->connections[$targetDb] = $conn;
|
|
return $conn;
|
|
}
|
|
}
|