MariaDB User Creation with CREATE USER - TeknoGenX

 

MariaDB User Creation with CREATE USER

This comprehensive guide explores the `CREATE USER` statement in MariaDB, detailing its functionality, options, and potential error handling. We'll cover everything from basic user creation to advanced authentication methods.

Understanding the CREATE USER Statement

The `CREATE USER` statement is fundamental for managing user accounts within your MariaDB database. It allows you to create new users, granting them access to your data with specific privileges. To execute this statement, you need either the global `CREATE USER` privilege or the `INSERT` privilege on the `mysql` database.

Key Features and Functionality

1. Account Creation:  Creates new MariaDB user accounts.

2. Privilege Management:  Initially, created users have no privileges. Separate `GRANT` statements are needed to assign permissions.

3. Error Handling: Returns `ERROR 1396 (HY000)` if the user or specified permissions already exist. It attempts to create all users, reporting only one error for those that failed.

4. Data Storage: Stores user information in `mysql.user` (until MariaDB 10.3, a table; from 10.4, a view) or `mysql.global_priv_table` (from MariaDB 10.4).

Error Code 1396 (HY000)

This error indicates a failure in the `CREATE USER`, `DROP USER`, `CREATE ROLE`, or `DROP ROLE` statements. It signifies that either the user already exists or there's a conflict with existing permissions.

Essential Clauses and Options

The `CREATE USER` statement offers several optional clauses to enhance its functionality:

1. OR REPLACE

This clause provides a shortcut for dropping an existing user before creating a new one:

```sql

CREATE OR REPLACE USER user_name@host ...;

```

This is equivalent to:

```sql

DROP USER IF EXISTS user_name@host;

CREATE USER user_name@host ...;

```

2. IF NOT EXISTS

This clause prevents errors if the user already exists. Instead of an error, it returns a warning:

```sql

CREATE USER IF NOT EXISTS user_name@host ...;

```

Authentication Methods

MariaDB offers various authentication methods for user accounts. Let's explore the most common options:

1. IDENTIFIED BY 'password'

This clause sets a password for the user. The password is stored after hashing using the `PASSWORD()` function.

```sql

CREATE USER foo@localhost IDENTIFIED BY 'mysecurepassword';

```

1. Important:  Omitting this clause allows connection without a password. A blank password is not a wildcard; the user must connect without providing any password.

2. IDENTIFIED BY PASSWORD 'password_hash'

This allows you to specify a pre-hashed password. Obtain the hash using `SELECT PASSWORD('your_password');` before using it in the `CREATE USER` statement.

```sql

CREATE USER bar@localhost IDENTIFIED BY PASSWORD '*prehashedpassword';

```

1. Supported Plugins:  `mysql_native_password` and `mysql_old_password`.

3. IDENTIFIED {VIA|WITH} authentication_plugin

This clause specifies a particular authentication plugin. `VIA` and `WITH` are interchangeable.

```sql

CREATE USER baz@localhost IDENTIFIED VIA pam;  -- Using PAM authentication

CREATE USER baz2@localhost IDENTIFIED VIA pam USING 'mariadb'; -- PAM with service name

```

1. Plugin Availability: Ensure the plugin is installed and active (check with `SHOW PLUGINS;`).

Account Names

Account names follow the format `user_name@host`.

Component	Description	Example
`user_name`	The username.	myuser
`@`	Separator	@
`host`	The host from which the user can connect. `%` means any host.	localhost, %

Example: Creating Users with Different Authentication Methods

Let's illustrate creating users with various authentication methods:

User Name	Host	Authentication Method	Command
simpleuser	localhost	Password ('mypass')	`CREATE USER simpleuser@localhost IDENTIFIED BY 'mypass';`
hasheduser	%	Pre-hashed password ('*hashvalue')	`CREATE USER hasheduser@% IDENTIFIED BY PASSWORD '*hashvalue';`
pamuser	localhost	PAM authentication	`CREATE USER pamuser@localhost IDENTIFIED VIA pam;`
pamuser_service	localhost	PAM authentication with service name ('myservice')	`CREATE USER pamuser_service@localhost IDENTIFIED VIA pam USING 'myservice';`

Enhancing MariaDB User Security: A Deep Dive into CREATE USER

This article explores the intricacies of the MariaDB `CREATE USER` statement, focusing on security enhancements through authentication plugins, password management, and TLS options. We'll cover various aspects to help you create secure and robust user accounts.

Authentication Plugins and Password Management

The `CREATE USER` statement offers multiple ways to manage user authentication, enhancing security and flexibility.

`IDENTIFIED BY` Clause

This clause sets a plain-text password, which MariaDB hashes before storage. It supports `mysql_native_password` and `mysql_old_password` plugins.

```sql

CREATE USER foo2@test IDENTIFIED BY 'mariadb';

```

Leaving this clause empty allows passwordless logins, but this is generally discouraged for security reasons.

`IDENTIFIED BY PASSWORD` Clause

This allows specifying a pre-hashed password, improving security by avoiding on-the-fly hashing.

```sql

SELECT PASSWORD('mariadb'); -- Get the hash

CREATE USER foo2@test IDENTIFIED BY PASSWORD '*54958E764CE10E50764C2EECBB71D01F08549980'; -- Use the hash

```

Again, only `mysql_native_password` and `mysql_old_password` are supported.

`IDENTIFIED VIA` or `IDENTIFIED WITH` Clause

This specifies a particular authentication plugin. `VIA` and `WITH` are interchangeable. You can add arguments using `USING` or `AS`.

```sql

CREATE USER foo2@test IDENTIFIED VIA pam USING 'mariadb';

```

This example uses the PAM plugin with the 'mariadb' service. The available plugins depend on your MariaDB installation.

Multiple Authentication Plugins

You can specify multiple authentication methods as alternatives. MariaDB tries them sequentially until one succeeds.

```sql

CREATE USER safe@'%' IDENTIFIED VIA ed25519 USING PASSWORD('secret') OR unix_socket;

```

This allows authentication via `ed25519` with a password or via a Unix socket.

Default Authentication Plugin

If no plugin is specified, MariaDB defaults to `mysql_native_password`.

Secure Connections with TLS

MariaDB allows encrypting data in transit using TLS (formerly SSL) to protect against eavesdropping. The `CREATE USER` statement offers several options to enforce TLS usage for specific accounts.

TLS Options Summary

Option	Description	Implies	Can be combined with
`REQUIRE NONE`	TLS is not required but allowed.	None	All
`REQUIRE SSL`	TLS is required; X509 certificate not required.	None	None
`REQUIRE X509`	TLS and a valid X509 certificate are required.	`REQUIRE SSL`	None
`REQUIRE ISSUER`	TLS, a valid X509 certificate, and a specific Certificate Authority are required.	`REQUIRE X509`	`SUBJECT`, `CIPHER`
`REQUIRE SUBJECT`	TLS, a valid X509 certificate, and a specific certificate subject are required.	`REQUIRE X509`	`ISSUER`, `CIPHER`
`REQUIRE CIPHER`	TLS, a specific cipher method is required; X509 certificate not required.	`REQUIRE SSL`	`ISSUER`, `SUBJECT`

Example Usage

This example creates a user requiring a specific subject, issuer, and cipher:

```sql

CREATE USER 'alice'@'%'

REQUIRE SUBJECT '/CN=alice/O=My Dom, Inc./C=US/ST=Oregon/L=Portland'

AND ISSUER '/C=FI/ST=Somewhere/L=City/ O=Some Company/CN=Peter Parker/emailAddress=p.parker@marvel.com'

AND CIPHER 'SHA-DES-CBC3-EDH-RSA';

```

Remember to configure both client and server for TLS if these options are used.

Conclusion

Resource Limit Options in MariaDB

MariaDB allows you to set per-account limits for various server resources, enhancing security and resource management. This article details how to configure these limits, manage account names, and understand the implications of different host name configurations.

Setting Resource Limits

You can control resource usage per account using the `CREATE USER` statement with specific resource limit options. These limits help prevent resource exhaustion by individual users or applications. If a limit is set to 0, there's no limit for that resource for that user.

Limit Type	Description
`MAX_QUERIES_PER_HOUR`	Number of statements (including updates) the account can issue per hour.
`MAX_UPDATES_PER_HOUR`	Number of update statements (excluding SELECT statements) the account can issue per hour.
`MAX_CONNECTIONS_PER_HOUR`	Number of connections the account can initiate per hour.
`MAX_USER_CONNECTIONS`	Number of simultaneous connections allowed from the same account.  0 uses `max_connections`; if `max_connections` is 0, there's no limit.
`MAX_STATEMENT_TIME`	Timeout (in seconds) for statements executed by the user.

Here's an example of creating a user with resource limits:

```sql

CREATE USER 'someone'@'localhost' WITH

MAX_USER_CONNECTIONS 10

MAX_QUERIES_PER_HOUR 200;

```

Resource tracking is done per account (`'user'@'server'`), not per username or connection. You can reset these counts for all users using `FLUSH USER_RESOURCES`, `FLUSH PRIVILEGES`, or `mariadb-admin reload`. These limits are stored in the `mysql.user` table (or `mysql.global_priv_table` from MariaDB 10.4) in columns like `max_questions`, `max_updates`, `max_connections`, and `max_user_connections`.

Account Names and Host Name Components

Account names consist of a username and a hostname component, specified as `'user_name'@'host_name'`. Both components can be quoted using single quotes (`'...'`), double quotes (`"..."`), or backticks (`...`). Quoting is mandatory when using special or wildcard characters.

Host Name Details

1. Default Host: If omitted, the host name defaults to `%` (wildcard, allowing connections from any host).

2. Wildcard Characters:  `%` matches any sequence of characters, and `_` matches a single character. Escape wildcard characters with a backslash (`\`).

3. Case-Insensitivity: Host name matching is case-insensitive.

4. Localhost: `'localhost'` restricts connections to local clients only (via UNIX-domain sockets on Linux;  `127.0.0.1` is not considered local).

5. Netmasks: Use `'base_ip/netmask'` to specify a range of IP addresses (IPv4 only). An IP address `ip_addr` is allowed if `ip_addr & netmask = base_ip`. For example, `'247.150.130.0/255.255.255.0'` allows connections from 247.150.130.0 to 247.150.130.255.

6. Length Limits:  Before MariaDB 10.6, host names were limited to 60 characters; from MariaDB 10.6 onwards, the limit is 255 characters.

7. Wildcard Considerations:  Using `%` might not grant access in all situations (e.g., due to pre-existing anonymous users).

Example: Creating Users with Different Host Name Specifications

User Name	Host Name	Description
`'user1'`	`'localhost'`	Allows connections only from the local machine.
`'user2'`	`'192.168.1.%'`	Allows connections from any host within the 192.168.1.0/24 subnet.
`'user3'`	`'%'`	Allows connections from any host (use with caution).
`'user4'`	`'example.com'`	Allows connections only from the `example.com` domain.

Conclusion

Effectively managing resource limits is crucial for maintaining a secure and performant MariaDB server. By understanding account naming conventions and utilizing the resource limit options within the `CREATE USER` statement, you can fine-tune access control and prevent resource exhaustion. Remember to carefully consider the implications of wildcard characters in host name specifications.