Oihana PHP System

MysqlPrivilegeTrait uses trait:short, trait:short

Provides methods to manage MySQL privileges for users on databases and tables.

Includes operations to grant, revoke, inspect, and flush privileges, and parse GRANT statements.

Requires a connected PDO instance and uses MysqlAssertionsTrait for input validation.

Tags
author

Marc Alcaraz (ekameleon)

since
1.0.0

Table of Contents

Constants

DEFER_ASSIGNMENT  = 'deferAssignment'
The 'deferAssignment' parameter constant.
PDO  = 'pdo'
The 'pdo' parameter constant.
SCHEMA  = 'schema'
The 'schema' parameter constant.

Properties

$deferAssignment  : bool|null
Indicates if the the constructor is called before setting properties.
$pdo  : PDO|null
The PDO reference.
$schema  : string|mixed|null
The internal schema to use in the PDO fetch processes.

Methods

bindValues()  : void
Bind named parameters to a prepared PDO statement.
fetch()  : mixed|null
Execute a SELECT query and fetch a single result.
fetchAll()  : array<string|int, mixed>
Execute a SELECT query and fetch all results.
fetchColumn()  : mixed
Execute a query and return the value of a single column from the first row.
fetchColumnArray()  : array<int, string>
Fetch a list of single-column results.
flushPrivileges()  : bool
Flushes MySQL privileges.
getGrants()  : array<int, string>
Retrieves all GRANT statements for a given user.
getPrivilegesSummary()  : string
Returns a human-readable summary of privileges granted to a user.
grantAllPrivileges()  : bool
Grants all privileges on a database to a user.
grantPrivilege()  : bool
Grants specific privileges on a database or table to a user.
hasAllPrivilegesOnDatabase()  : bool
Checks if the given user has ALL PRIVILEGES on a specific database.
hasAnyPrivilege()  : bool
Checks whether the user has at least one privilege on a given database or table.
hasGlobalAllPrivileges()  : bool
Checks whether the given user has ALL PRIVILEGES globally (i.e., ON *.*).
hasPrivilege()  : bool
Checks whether a user has a specific privilege on a database or table.
initializeDefaultFetchMode()  : void
Set the default fetch mode on the statement.
initPDO()  : PDO|null
Initialize the PDO instance from a config array or dependency injection container.
isConnected()  : bool
Indicates if the PDO is connected.
listDatabasesWithPrivileges()  : array<int, string>
Lists all databases on which a user has at least one privilege.
listPrivileges()  : array<string, array<string|int, string>>
Lists parsed privileges granted to a user.
revokeAllPrivileges()  : bool
Revokes all privileges from a user at the global level (*.*).
revokePrivilege()  : bool
Revokes specific privileges from a user on a database or table.
revokePrivileges()  : bool
Revokes all privileges from a user on a specific database.
assertHost()  : void
Validates a MySQL host string.
assertIdentifier()  : void
Validates a MySQL identifier such as a database name, user name, or table name.

Constants

DEFER_ASSIGNMENT

The 'deferAssignment' parameter constant.

public mixed DEFER_ASSIGNMENT = 'deferAssignment'

PDO

The 'pdo' parameter constant.

public mixed PDO = 'pdo'

SCHEMA

The 'schema' parameter constant.

public mixed SCHEMA = 'schema'

Properties

$deferAssignment

Indicates if the the constructor is called before setting properties.

public bool|null $deferAssignment = false

Only if the schema property is defined.

$pdo

The PDO reference.

public PDO|null $pdo = null

$schema

The internal schema to use in the PDO fetch processes.

public string|mixed|null $schema = null

Methods

bindValues()

Bind named parameters to a prepared PDO statement.

public bindValues(PDOStatement $statement[, array<string|int, mixed> $bindVars = [] ]) : void
Parameters
$statement : PDOStatement

The PDO statement.

$bindVars : array<string|int, mixed> = []

Associative array of bindings. Supports:

  • ['id' => 5]
  • ['id' => [5, PDO::PARAM_INT]]

fetch()

Execute a SELECT query and fetch a single result.

public fetch(string $query[, array<string|int, mixed> $bindVars = [] ]) : mixed|null

The result is returned as an object or as a mapped schema class if defined. Alteration is applied via AlterDocumentTrait.

Parameters
$query : string

The SQL query to execute.

$bindVars : array<string|int, mixed> = []

Optional bindings for the query.

Tags
throws
ContainerExceptionInterface
throws
NotFoundExceptionInterface
Return values
mixed|null

The result object or null if not found.

fetchAll()

Execute a SELECT query and fetch all results.

public fetchAll(string $query[, array<string|int, mixed> $bindVars = [] ]) : array<string|int, mixed>

Results are returned as an array of associative arrays or schema instances. Alteration is applied via AlterDocumentTrait.

Parameters
$query : string

The SQL query to execute.

$bindVars : array<string|int, mixed> = []

Optional bindings for the query.

Tags
throws
ContainerExceptionInterface
throws
NotFoundExceptionInterface
Return values
array<string|int, mixed>

An array of results.

fetchColumn()

Execute a query and return the value of a single column from the first row.

public fetchColumn(string $query[, array<string|int, mixed> $bindVars = [] ][, int $column = 0 ]) : mixed
Parameters
$query : string

The SQL query to execute.

$bindVars : array<string|int, mixed> = []

Optional bindings for the query.

$column : int = 0

Column index (0-based) to return from the first row.

Return values
mixed

The column value or 0 if the query fails.

fetchColumnArray()

Fetch a list of single-column results.

public fetchColumnArray(string $query[, array<string|int, mixed> $bindVars = [] ]) : array<int, string>
Parameters
$query : string

The SQL query to execute.

$bindVars : array<string|int, mixed> = []

Optional bindings for the query.

Return values
array<int, string>

flushPrivileges()

Flushes MySQL privileges.

public flushPrivileges() : bool

This reloads the grant tables to apply recent changes (like CREATE USER, GRANT, etc.).

Return values
bool

True on success, false otherwise.

getGrants()

Retrieves all GRANT statements for a given user.

public getGrants(string $username[, string $host = 'localhost' ]) : array<int, string>
Parameters
$username : string

The MySQL username.

$host : string = 'localhost'

The associated host (default: 'localhost').

Return values
array<int, string>

Array of GRANT statements (or empty if none/failure).

getPrivilegesSummary()

Returns a human-readable summary of privileges granted to a user.

public getPrivilegesSummary(string $username[, string $host = 'localhost' ]) : string

This method parses and formats the privileges returned by SHOW GRANTS into a simple multi-line string, with each line showing the scope (database.table or ALL) and the list of privileges.

Parameters
$username : string

The MySQL username.

$host : string = 'localhost'

The host associated with the user (default: 'localhost').

Tags
example
echo $model->getPrivilegesSummary('user1');
// Output:
// mydb.*: SELECT, INSERT
// mydb.products: SELECT
// ALL: USAGE
Return values
string

A formatted summary string.

grantAllPrivileges()

Grants all privileges on a database to a user.

public grantAllPrivileges(string $username, string $dbname, string $host) : bool
Parameters
$username : string

The user to grant privileges to.

$dbname : string

The database to grant access to.

$host : string

The user's host (usually 'localhost').

Return values
bool

True on success, false otherwise.

grantPrivilege()

Grants specific privileges on a database or table to a user.

public grantPrivilege(string $privileges, string $dbname, string $username[, string $host = 'localhost' ][, string|null $table = null ]) : bool
Parameters
$privileges : string

A comma-separated list of privileges (e.g. 'SELECT, INSERT').

$dbname : string

Database name.

$username : string

The MySQL user.

$host : string = 'localhost'

Host (default: 'localhost').

$table : string|null = null

Optional table name. If null, grants privileges on the entire database.

Tags
example
$model->grantPrivilege('SELECT, INSERT', 'mydb', 'user1');
$model->grantPrivilege('UPDATE', 'mydb', 'user1', 'localhost', 'products');
Return values
bool

True if the grant statement executed successfully.

hasAllPrivilegesOnDatabase()

Checks if the given user has ALL PRIVILEGES on a specific database.

public hasAllPrivilegesOnDatabase(string $username, string $database[, string $host = 'localhost' ]) : bool

This method parses the user's GRANT statements and looks for a "GRANT ALL PRIVILEGES ON database.* TO ..." entry.

Parameters
$username : string

The MySQL username to check.

$database : string

The database name to check privileges for.

$host : string = 'localhost'

The host part of the MySQL user (default: 'localhost').

Return values
bool

True if the user has ALL PRIVILEGES on the database, false otherwise.

hasAnyPrivilege()

Checks whether the user has at least one privilege on a given database or table.

public hasAnyPrivilege(string $username, string $dbname[, string|null $table = null ][, string $host = 'localhost' ]) : bool
Parameters
$username : string

The MySQL user.

$dbname : string

The database name.

$table : string|null = null

Optional table name. If null, checks privileges on the database.

$host : string = 'localhost'

The host (default: 'localhost').

Tags
example
if ($model->hasAnyPrivilege('user1', 'mydb')) {
    echo "User has privileges on the database.";
}
Return values
bool

True if the user has any privilege on the specified scope.

hasGlobalAllPrivileges()

Checks whether the given user has ALL PRIVILEGES globally (i.e., ON *.*).

public hasGlobalAllPrivileges(string $username[, string $host = 'localhost' ]) : bool
Parameters
$username : string

The MySQL username.

$host : string = 'localhost'

The associated host (default: 'localhost').

Return values
bool

True if the user has ALL PRIVILEGES globally.

hasPrivilege()

Checks whether a user has a specific privilege on a database or table.

public hasPrivilege(string $username, string $privilege, string $dbname[, string|null $table = null ][, string $host = 'localhost' ]) : bool
Parameters
$username : string

MySQL username.

$privilege : string

Privilege to check (e.g. SELECT, INSERT, ALL PRIVILEGES).

$dbname : string

Database name.

$table : string|null = null

Optional table name. If null, check DB-level privileges.

$host : string = 'localhost'

Host (default: 'localhost').

Return values
bool

True if the privilege is granted.

initializeDefaultFetchMode()

Set the default fetch mode on the statement.

public initializeDefaultFetchMode(PDOStatement $statement) : void

Uses FETCH_ASSOC by default or FETCH_CLASS (with optional FETCH_PROPS_LATE) if a schema class is defined and exists.

Parameters
$statement : PDOStatement

The PDO statement to configure.

initPDO()

Initialize the PDO instance from a config array or dependency injection container.

public initPDO([array<string|int, mixed> $init = [] ][, Container|null $container = null ]) : PDO|null
Parameters
$init : array<string|int, mixed> = []

Configuration array. Expects Param::PDO as key.

$container : Container|null = null

Optional DI container to resolve the PDO service.

Tags
throws
ContainerExceptionInterface
throws
NotFoundExceptionInterface
Return values
PDO|null

The resolved PDO instance or null.

isConnected()

Indicates if the PDO is connected.

public isConnected() : bool
Return values
bool

listDatabasesWithPrivileges()

Lists all databases on which a user has at least one privilege.

public listDatabasesWithPrivileges(string $username[, string $host = 'localhost' ]) : array<int, string>

This method analyzes the output of SHOW GRANTS and extracts database-level or table-level scopes from the privilege definitions. It returns a unique list of database names, including * if the user has global privileges.

Parameters
$username : string

The MySQL username.

$host : string = 'localhost'

The host associated with the user (default: 'localhost').

Tags
example
$databases = $model->listDatabasesWithPrivileges('user1');
// Result: ['mydb', 'test'] or ['*'] if global privileges
Return values
array<int, string>

List of database names (e.g. ['mydb', 'test', '*']).

listPrivileges()

Lists parsed privileges granted to a user.

public listPrivileges(string $username[, string $host = 'localhost' ]) : array<string, array<string|int, string>>
Parameters
$username : string

The MySQL user.

$host : string = 'localhost'

The associated host (default: 'localhost').

Tags
example
$list = $model->listPrivileges('user1');

foreach ( $list as $scope => $rights)
{
    echo "Privileges on {$scope}:\n - " . implode(', ', $rights) . PHP_EOL ;
}

// Privileges on mydb.*:
// - SELECT, INSERT, UPDATE

// Privileges on mydb.products:
// - SELECT

// Privileges on ALL:
// - USAGE
Return values
array<string, array<string|int, string>>

[ 'database.table' => [privileges...] ]

revokeAllPrivileges()

Revokes all privileges from a user at the global level (*.*).

public revokeAllPrivileges(string $username[, string $host = 'localhost' ]) : bool
Parameters
$username : string

The MySQL username.

$host : string = 'localhost'

The user's host (default: 'localhost').

Tags
example
$model->revokeAllPrivileges('user1');
Return values
bool

True on success, false otherwise.

revokePrivilege()

Revokes specific privileges from a user on a database or table.

public revokePrivilege(string $privileges, string $dbname, string $username[, string $host = 'localhost' ][, string|null $table = null ]) : bool
Parameters
$privileges : string

Comma-separated privileges (e.g. 'SELECT, INSERT').

$dbname : string

Database name.

$username : string

MySQL user.

$host : string = 'localhost'

Host (default: 'localhost').

$table : string|null = null

Optional table name (null = whole DB).

Tags
example
  1. Remove all modification rights:
$model->revokePrivilege('INSERT, UPDATE, DELETE', 'mydb', 'user1');
  1. Only remove SELECT from a specific table
$model->revokePrivilege('SELECT', 'mydb', 'user1', 'localhost', 'products');
Return values
bool

True if the statement was executed successfully.

revokePrivileges()

Revokes all privileges from a user on a specific database.

public revokePrivileges(string $username, string $host, string $dbname) : bool
Parameters
$username : string

The user to revoke privileges from.

$host : string

The user's host (usually 'localhost').

$dbname : string

The database to revoke access from.

Return values
bool

True on success, false otherwise.

assertHost()

Validates a MySQL host string.

protected assertHost(string $host) : void

A valid host string may contain:

  • letters (a–z, A–Z)
  • digits (0–9)
  • dots (.)
  • hyphens (-)
  • underscores (_) and percent signs (%) for wildcards
Parameters
$host : string

The host name or IP to validate (e.g., 'localhost', '127.0.0.1', '%.example.com').

Tags
throws
InvalidArgumentException

If the host string contains disallowed characters.

assertIdentifier()

Validates a MySQL identifier such as a database name, user name, or table name.

protected assertIdentifier(string $name) : void

A valid identifier consists of letters (a–z, A–Z), digits (0–9), and underscores (_). This ensures safe usage in SQL queries without risk of injection or syntax errors.

Parameters
$name : string

The identifier to validate.

Tags
throws
InvalidArgumentException

If the identifier contains invalid characters.


        
On this page

Search results