/** * @param Account $account */ protected function dropDatabase(Account $account) { // User current users credentials to see available databases $conn = $this->connect($account->getDatabase()); $databases = $conn->fetchArray("SHOW DATABASES WHERE `Database` = '{$account->getDatabase()}';"); if (is_array($databases) && in_array($account->getDatabase(), $databases)) { $privileges = $conn->fetchAll("\n SELECT\n *\n FROM information_schema.schema_privileges\n WHERE\n TABLE_SCHEMA = '{$account->getDatabase()}'\n AND SUBSTR(SUBSTRING_INDEX(GRANTEE, '\\'@', 1), 2) != '{$account->getUser()}';\n "); // Drop only if no other users have access to the db if (count($privileges) == 0) { $conn->exec("DROP DATABASE {$account->getDatabase()};"); } } $conn->close(); }
/** * @param Account $account * @param Connection $conn * @param $tokenInfo * @param $buckets * @throws DBALException */ protected function grantPermissionsTry(Account $account, Connection $conn, $tokenInfo, $buckets) { $userName = $account->getUser(); $allowedBuckets = array(); if ($account->getType() == 'transformations' || $account->getType() == 'sandbox' || $account->getType() == 'luckyguess') { foreach ($buckets as $bucket) { if ((substr($bucket["id"], 0, 3) == 'in.' || substr($bucket["id"], 0, 4) == 'out.') && $bucket["backend"] == 'redshift' && in_array($bucket["id"], array_keys($tokenInfo["bucketPermissions"]))) { $allowedBuckets[] = strtolower($bucket["id"]); } } } // Set custom permissions if (count($allowedBuckets) > 0) { // Tables $query = "\n SELECT TRIM(schemaname) AS schema, TRIM(tablename) AS table\n FROM pg_tables\n WHERE TRIM(schemaname) IN ('" . join("', '", $allowedBuckets) . "');\n "; $tablesInRs = $conn->fetchAll($query); $schemaNames = array(); if (count($tablesInRs)) { $tableIds = array(); foreach ($tablesInRs as $tableInRs) { if (substr($tableInRs["table"], 0, 6) != '__temp') { $tableIds[] = '"' . strtolower($tableInRs["schema"]) . '"."' . strtolower($tableInRs["table"]) . '"'; $schemaNames[] = '"' . strtolower($tableInRs["schema"]) . '"'; } } $query = "\n GRANT SELECT\n ON " . join(', ', $tableIds) . "\n TO {$userName};\n "; $conn->exec($query); } // Views $query = "\n SELECT TRIM(schemaname) AS schema, TRIM(viewname) AS view\n FROM pg_views\n WHERE TRIM(schemaname) IN ('" . join("', '", $allowedBuckets) . "');\n "; $viewsInRs = $conn->fetchAll($query); if (count($viewsInRs)) { $viewIds = array(); foreach ($viewsInRs as $viewInRs) { $viewIds[] = '"' . strtolower($viewInRs["schema"]) . '"."' . strtolower($viewInRs["view"]) . '"'; $schemaNames[] = '"' . strtolower($viewInRs["schema"]) . '"'; } $query = "\n GRANT SELECT\n ON " . join(', ', $viewIds) . "\n TO {$userName};\n "; $conn->exec($query); } // Schemas if (count($schemaNames)) { $schemaNames = array_unique($schemaNames); $query = "\n GRANT USAGE\n ON SCHEMA " . join(', ', $schemaNames) . "\n TO {$userName};\n "; $conn->exec($query); } } // system tables if ($account->getType() == "transformations") { $query = "\n \t\t\t\tGRANT SELECT\n \t\t\t\tON SVV_TABLE_INFO\n \t\t\t\tTO {$account->getUser()};\n \t\t\t"; $conn->exec($query); } // Grant access to its own schema if ($account->getType() == 'read') { $conn->exec("GRANT USAGE ON SCHEMA \"{$account->getSchema()}\" TO \"{$account->getUser()}\";"); $conn->exec("GRANT SELECT ON ALL TABLES IN SCHEMA \"{$account->getSchema()}\" TO \"{$account->getUser()}\";"); } else { $conn->exec("GRANT ALL ON SCHEMA \"{$account->getSchema()}\" TO \"{$account->getUser()}\";"); } }
/** * @param Account $account */ protected function grantPrivileges(Account $account) { $conn = $this->connect(); $conn->exec("GRANT ALL ON `{$account->getDatabase()}`.* TO '{$account->getUser()}';"); $conn->exec("FLUSH PRIVILEGES;"); $conn->close(); }
/** * Stop the running container. * @param Account $account * @throws \Exception */ public function dropAccount(Account $account) { /** @var Account\Docker $account */ try { $ecsClient = $this->getEcsClient(); $response = $ecsClient->describeTasks(['cluster' => $this->getCluster(), 'tasks' => [$account->getArn()]])->toArray(); // Stop the task if it really exists if (count($response['tasks']) > 0) { $ecsClient = $this->getEcsClient(); $response = $ecsClient->stopTask(['cluster' => $this->getCluster(), 'reason' => 'dropAccount', 'task' => $account->getArn()])->toArray(); $this->checkResponseSuccess($response); } } catch (\Exception $e) { $this->logException(Logger::ERROR, "Can't stop task {$account->getUser()}", $e); throw $e; } }