Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Master user account management in MariaDB Server. This section covers creating, modifying, and revoking user privileges to ensure secure and controlled access to your databases.
Manage roles in MariaDB Server for streamlined user access control. This section explains how to create, assign, and manage roles to simplify privilege management and enhance security.
Catalogs are a user account management feature. This section explains their role in organizing database objects and controlling access permissions.
The status variable is incremented when someone tries to access something they do not have rights to.
This happens in the following cases:
When accessing a database, table, or column that the user does not have rights to access. The error is sent to the client.
When a login fails because of the wrong user/password, etc. The error is printed to the general log.
When the require_secure_transport option is enabled on the server, and the user has not used a secure transport. The error is sent to the client.
When connecting to a MariaDB server configured for , one has to provide the catalog to connect to. There are several ways to do this:
All new native MariaDB clients will support the --catalog option:
New and old clients can use the 'catalog_name.database_name' syntax to connect:
This will connect the user to the 'mine' catalog and the database 'test'.
Note that one consequence of this is that one should not have a database that contains '.' in
the name. If such a database exists, one can still connect to it by using the --catalog= option or prefixing the database with the catalog, like in def.data.base.name.
One will also be able to configure the MariaDB server to automatically choose catalogs depending on the port or IP they are using to connect to the server. This is done by adding the following to the catalog specific my.cnf file, residing in the catalog directory:
initializes the MariaDB data directory and creates the in the database.
When used with the --catalog options it will initialize MariaDB server to use catalogs.
The will automatically discover if catalogs are used or not.
Note that one cannot change a 'normal server' to a server with catalogs or a server with catalogs to a 'normal server'. In the future we will add tools that will allow one to easily move an existing server inside a catalog or move an server inside a catalog to a standalone server.
When using a MariaDB Server with support, all status information is collected for the whole server, per catalog and per session.
shows the status for the whole server. Note that only the super user in the 'def' catalog has privileges for the above statement.
Both commands show the status for the current catalog.
The reason that GLOBAL shows catalog status is that because catalogs are 'multi-tenant', a
catalog user should not be able to see the status from other users (for most things).
Shows the status for the current connection.
The main "new thing" is that catalogs enable SAS providers to see the status for a single tenant (catalog user). This makes it much easier to find 'bad neighbors' (tenants that cause problems for other tenants) so that they can be moved to other servers.
When the MariaDB server is not configured for catalogs, the following commands are equivalent:
Users try to change to a database/schema they do not have access to. A warning is written to the error log if log_warnings > 1.
Users try to use a SHOW command to access an object they do not have rights to see. The error is sent to the client.
Users access something that requires global access, like "CREATE SERVER". The error is sent to the client.
Login failures can be found in the general log. Errors that are sent to the client can be found by using the SQL Error Log Plugin. The plugin captures all errors sent to the client. Starting from MariaDB 10.11.5, it can also optionally capture all warnings sent to the client.
This page is licensed: CC BY-SA / Gnu FDL
defWhen connecting to a server not configured for catalogs, one can still use mariadb --catalog=def or mariadb def.datbase_name.
To check if a server supports catalogs:
0 means that the server is not configured for catalogs.
This page is licensed: CC BY-SA / Gnu FDL
mariadb --catalog=mine testmariadb mine.test[[mariadbd]]
--port=#
--connect-ip=To initialize a server with 4 catalogs (the def catalog, that holds the catalog root user (CRU) is automatically created):
The above will create a directory /my/data and the 4 directories under it, one for each catalog.
One can create a new catalog with CREATE CATALOG catalog_name
When adding more catalogs to an existing server, mariadb_install_db will start the mariadb client to execute the needed commands on the running server. This is why one has to supply user and password to mariadb_install_db.
One benefit of using mariadb_install_db is that it's possible to create many catalogs with one command.
This page is licensed: CC BY-SA / Gnu FDL
This page is licensed: CC BY-SA / Gnu FDL
SHOW SERVER STATUS;SHOW GLOBAL STATUS;
SHOW CATALOG STATUS;SHOW [SESSION] STATUS;This page is licensed: CC BY-SA / Gnu FDL
SHOW CREATE CATALOG catalog_nameDefine external catalogs for data integration. This statement configures connections to remote storage systems, allowing query access to external data sources.
Creates a catalog and the mysql, sys and performance_schema schemas inside the catalog.
CREATE CATALOG can only be performed by a user in the def catalog with the CATALOG privilege.
Note that no users are created.
The catalog name is limited to 64 characters. All characters must be in the basic ASCII set: (A-Z, a-z, -, _) This limitations is to be able to run catalogs with engines like InnoDB which has limited space in their internal data dictionary.
CREATE CATALOG works even if the catalog directory already exists (as long as there is no mysql sub directory). This is to allow a database administrator to pre-create the catalog directory and mount it to disk volume and optionally add a inside the catalog directory.
The directory will not be recognized as a catalog or show up in until the mysql sub directory is created by or .
can be used to create multiple catalogs with a default root user in one go.
This page is licensed: CC BY-SA / Gnu FDL
USE CATALOG catalog_nameChanges to another catalog. Can only be done by a super user in the 'def' catalog. Changing catalog will update catalog status and reset all session status.
A tenant (a user in any other catalog than 'def') cannot change to another catalog.
However tenants can execute USE CATALOG current_catalog. This is to allow the
user to import SQL scripts that use USE CATALOG....
. Changing database.
This page is licensed: CC BY-SA / Gnu FDL
{% hint style="info" %} Important: The PUBLIC role is created implicitly by GRANT statements and its creation is not logged, distinguishing it from standard system principals. {% endhint %}
MariaDB automatically creates several users and roles for administrative and internal server functions.
These user accounts are created by the mariadb-install-db script during the initial server setup.
SELECT @@catalogs;
+------------+
| @@catalogs |
+------------+
| 0 |
+------------+
USE catalog foo;
ERROR 4193 (HY000): MariaDB is not configured to support catalogsmariadb_install_db --catalogs="cat1 cat2 cat3" --datadir=/my/data/mariadb_install_db --catalogs="cat4 cat5 cat6" --datadir=/my/data --catalog-user=monty --catalog-passwordSHOW GLOBAL STATUS
SHOW SERVER STATUS
SHOW CATALOG STATUSSHOW CREATE CATALOG def;
+---------+-------------------------------------------------------------------------------------------------------+
| Catalog | Create Catalog |
+---------+-------------------------------------------------------------------------------------------------------+
| def | CREATE CATALOG `def` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci COMMENT 'default catalog' |
+---------+-------------------------------------------------------------------------------------------------------+CREATE CATALOG [IF NOT EXISTS] catalog_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'comment'root@localhostCreation
Created automatically by mariadb-install-db.
Purpose
Serves as the primary administrative account for initial server setup and management.
Management
It is highly recommended to secure this account immediately after installation. Standard security practices include setting a strong password, renaming the account, or removing it entirely in favor of other named administrative accounts.
Creation
Created automatically by mariadb-install-db.
Purpose
A mandatory system user required for internal server operations, such as executing scheduled events.
Management
This user account is essential for server functionality and is protected;
it cannot be dropped.
These roles are built into the server and have special behaviors.
The PUBLIC role is a special, built-in concept that represents every user on the server.
The PUBLIC role is created implicitly by the server the first time a GRANT ... TO PUBLIC statement is executed. It is not created with CREATE ROLE.
It provides a convenient way to grant privileges server-wide without having to grant them to each user individually. Privileges granted to PUBLIC are inherited by all existing and future users.
Because the PUBLIC role is created implicitly, its creation is not written to the audit log or binary log as a standard DDL event. This is a critical detail for security auditing.
The PUBLIC role cannot be explicitly dropped with DROP ROLE.
To audit the privileges that apply to all users, you must check the grants for PUBLIC directly.
-- Grant a privilege to all users
GRANT SELECT ON my_app.reports TO PUBLIC;
-- View privileges granted to PUBLIC
SHOW GRANTS FOR PUBLIC;
-- Revoke a privilege from all users
REVOKE SELECT ON my_app.reports FROM PUBLIC;Account locking permits privileged administrators to lock/unlock user accounts. No new client connections will be permitted if an account is locked (existing connections are not affected).
User accounts can be locked at creation, with the CREATE USER statement, or modified after creation with the ALTER USER statement. For example:
or
The server will return an ER_ACCOUNT_HAS_BEEN_LOCKED error when locked users attempt to connect:
The ALTER USER statement is used to unlock a user:
The SHOW CREATE USER statement will show whether the account is locked:
as well as querying the mysql.global_priv table:
This query against the mysql.global_priv table will return all accounts which have "account_locked": true within the Priv json column:
Example Output:
Account Locking and Password Expiry video tutorial
This page is licensed: CC BY-SA / Gnu FDL
Password expiry permits administrators to expire user passwords, either manually or automatically.
There are two system variables which affect password expiry: default_password_lifetime, which determines the amount of time between requiring the user to change their password. 0, the default, means automatic password expiry is not active.
The second variable, disconnect_on_expired_password determines whether a client is permitted to connect if their password has expired, or whether they are permitted to connect in sandbox mode, able to perform a limited subset of queries related to resetting the password, in particular SET PASSWORD and SET.
Besides automatic password expiry, as determined by , password expiry times can be set on an individual user basis, overriding the global using the or statements, for example:
Limits can be disabled by use of the NEVER keyword, for example:
A manually set limit can be restored the system default by use of DEFAULT, for example:
Note that the limit is defined as the number of days since the last password change. And the last password change is the value of CURRENT_TIMESTAMP when the password was changed last. If the @@secure_timestamp variable is set to NO (which is its default value) any user can modify the session timestamp arbitrarily, in particular, they can pretend that the password was changed at some point in time far in the future, effectively disabling any password expiration limit.
To prevent it you need to make sure the @@secure_timestamp is set or to audit password expiration limits regularly.
The statement will display information about the password expiry status of the user. Unlike MySQL, it will not display if the user is unlocked, or if the password expiry is set to default.
MariaDB starting with
From , the stores password expiry and password error information. An unprivileged user can access their own user data from the table.
The following query can also be used to check when the current passwords expire for all users:
The --connect-expired-password option notifies the server that the client is prepared to handle expired password sandbox mode (even if the --batch option was specified).
video tutorial
This page is licensed: CC BY-SA / Gnu FDL
DROP CATALOG catalog_nameDeletes a catalog.
Limitations:
DROP CATALOG can only be performed by a super user in the 'def' catalog.
The current catalog cannot be dropped.
The 'def' catalog cannot be dropped.
When dropping a catalog, all databases and files within that catalog will be deleted.
This page is licensed: CC BY-SA / Gnu FDL
CREATE USER [...]
[lock_option] [password_option]
ALTER USER [...]
[lock_option] [password_option] CREATE USER 'lorin'@'localhost' ACCOUNT LOCK;ALTER USER 'marijn'@'localhost' ACCOUNT LOCK;mariadb -ulorin
ERROR 4151 (HY000): Access denied, this account is lockedALTER USER 'lorin'@'localhost' ACCOUNT UNLOCK;SHOW CREATE USER 'marijn'@'localhost';
+-----------------------------------------------+
| CREATE USER for marijn@localhost |
+-----------------------------------------------+
| CREATE USER 'marijn'@'localhost' ACCOUNT LOCK |
+-----------------------------------------------+SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv))
FROM mysql.global_priv
WHERE user='marijn';
+--------------------------------------------------------------------------------------+
| CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) |
+--------------------------------------------------------------------------------------+
| marijn@localhost => {
"access": 0,
"plugin": "mysql_native_password",
"authentication_string": "",
"account_locked": true,
"password_last_changed": 1558017158
} |
+--------------------------------------------------------------------------------------+SELECT CONCAT(user, '@', host) AS 'Locked Accounts' FROM mysql.global_priv WHERE Priv like '%account_locked":true%';+------------------------+
| Locked Accounts |
+------------------------+
| mariadb.sys@localhost |
| bart.simpson@localhost |
+------------------------+
2 rows in set (0.000 sec)CREATE CATALOG cat1;
USE CATALOG cat1;
CREATE USER root@localhost;
GRANT ALL PRIVILEGES ON *.* TO root@localhost;
CREATE DATABASE test;SHOW CATALOGS lists the catalogs on the MariaDB server host.
The LIKE clause, if present on its own, indicates which catalog names to match. The WHERE and LIKE clauses can be given to select rows using more general conditions, as discussed in .
You see only use SHOW CATALOGS have the . Only users of the 'def' schema can have this privilege.
If the server was started with the option, you cannot use this statement unless you have the .
The list of results returned by SHOW CATALOGS is based on directories in the data directory, which is how MariaDB implements catalogs. It only list directories that have a mysql directory.
The also contains catalog information.
This page is licensed: CC BY-SA / Gnu FDL
CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY;ALTER USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY;CREATE USER 'monty'@'localhost' PASSWORD EXPIRE NEVER;ALTER USER 'monty'@'localhost' PASSWORD EXPIRE NEVER;CREATE USER 'monty'@'localhost' PASSWORD EXPIRE DEFAULT;ALTER USER 'monty'@'localhost' PASSWORD EXPIRE DEFAULT;CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY;
CREATE USER 'konstantin'@'localhost' PASSWORD EXPIRE NEVER;
CREATE USER 'amse'@'localhost' PASSWORD EXPIRE DEFAULT;
SHOW CREATE USER 'monty'@'localhost';
+------------------------------------------------------------------+
| CREATE USER for monty@localhost |
+------------------------------------------------------------------+
| CREATE USER 'monty'@'localhost' PASSWORD EXPIRE INTERVAL 120 DAY |
+------------------------------------------------------------------+
SHOW CREATE USER 'konstantin'@'localhost';
+------------------------------------------------------------+
| CREATE USER for konstantin@localhost |
+------------------------------------------------------------+
| CREATE USER 'konstantin'@'localhost' PASSWORD EXPIRE NEVER |
+------------------------------------------------------------+
SHOW CREATE USER 'amse'@'localhost';
+--------------------------------+
| CREATE USER for amse@localhost |
+--------------------------------+
| CREATE USER 'amse'@'localhost' |
+--------------------------------+WITH password_expiration_info AS (
SELECT User, Host,
IF(
IFNULL(JSON_EXTRACT(Priv, '$.password_lifetime'), -1) = -1,
@@global.default_password_lifetime,
JSON_EXTRACT(Priv, '$.password_lifetime')
) AS password_lifetime,
JSON_EXTRACT(Priv, '$.password_last_changed') AS password_last_changed
FROM mysql.global_priv
)
SELECT pei.User, pei.Host,
pei.password_lifetime,
FROM_UNIXTIME(pei.password_last_changed) AS password_last_changed_datetime,
FROM_UNIXTIME(
pei.password_last_changed +
(pei.password_lifetime * 60 * 60 * 24)
) AS password_expiration_datetime
FROM password_expiration_info pei
WHERE pei.password_lifetime != 0
AND pei.password_last_changed IS NOT NULL
UNION
SELECT pei.User, pei.Host,
pei.password_lifetime,
FROM_UNIXTIME(pei.password_last_changed) AS password_last_changed_datetime,
0 AS password_expiration_datetime
FROM password_expiration_info pei
WHERE pei.password_lifetime = 0
OR pei.password_last_changed IS NULL;SHOW CATALOGS
[LIKE 'pattern' | WHERE expr]+---------+--------------------+
| Catalog | Comment |
+---------+--------------------+
| c1 | This is catalog c1 |
| cat2 | |
| def | default catalog |
+---------+--------------------+SHOW CATALOGS LIKE 'c%';
+--------------+--------------------+
| Catalog (c%) | Comment |
+--------------+--------------------+
| c1 | This is catalog c1 |
| cat2 | |
+--------------+--------------------+A role bundles a number of privileges together. It assists larger organizations where, typically, a number of users would have the same privileges, and, previously, the only way to change the privileges for a group of users was by changing each user's privileges individually.
Alternatively, multiple external users could have been assigned the same user, and there would have been no way to see which actual user was responsible for which action.
With roles, managing this is easy. For example, there could be a number of users assigned to a journalist role, with identical privileges. Changing the privileges for all the journalists is a matter of simply changing the role's privileges, while the individual user is still linked with any changes that take place.
Roles are created with the CREATE ROLE statement, and dropped with the DROP ROLE statement. Roles are then assigned to a user with an extension to the statement, while privileges are assigned to a role in the regular way with . Similarly, the statement can be used to both revoke a role from a user, or revoke a privilege from a role.
Once a user has connected, he can obtain all privileges associated with a role by setting a role with the statement. The function returns the currently set role for the session, if any.
Only roles granted directly to a user can be set, roles granted to other roles cannot. Instead the privileges granted to a role, which is, in turn, granted to another role (grantee), will be immediately available to any user who sets this second grantee role.
The statement allows one to set a default role for a user. A default role is automatically enabled when a user connects (an implicit SET ROLE statement is executed immediately after a connection is established).
Roles were implemented as a GSoC 2013 project by Vicentiu Ciorbaru.
Information about roles and who they've been granted to can be found in the as well as the .
The shows the enabled roles for the current session.
Creating a role and granting a privilege:
Note, that hulda has no SHOW DATABASES privilege, even though she was granted the journalist role. She needs to set the role first:
Roles can be granted to roles:
But one does not need to set a role granted to a role. For example, hulda will automatically get all writer privileges when she sets the journalist role:
When a user sets a role, he, in a sense, has two identities with two associated sets of privileges.
But a view (or a stored routine) can have only one definer. So, when a view (or a stored routine) is created with the SQL SECURITY DEFINER, one can specify whether the definer should be CURRENT_USER (and the view will have none of the privileges of the user's role) or CURRENT_ROLE (in this case, the view will use role's privileges, but none of the user's privileges). As a result, sometimes one can create a view that is impossible to use.
by Peter Gulutzan
This page is licensed: CC BY-SA / Gnu FDL
MariaDB 10.4 introduced a number of changes to the authentication process, intended to make things easier and more intuitive. Those changes aren't available in earlier versions of MariaDB.
For Windows, see Authentication Plugin - GSSAPI.
There are four new main features in 10.4 relating to authentication:
It is possible to use more than one for each user account. For example, this can be useful to slowly migrate users to the more secure authentication plugin over time, while allowing the old authentication plugin as an alternative for the transitional period.
The root@localhost user account created by is created with the ability to use two .
First, it is configured to try to use the authentication plugin. This allows the root@localhost user to log in without a password via the local Unix socket file defined by the
As a result of the above changes, the open-for-everyone all-powerful root account is finally gone. And installation scripts will no longer demand that you “PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER!”, because the root account is securely created automatically.
Two all-powerful accounts are created by default — root and the OS user that owns the data directory, typically mysql. They are created as:
Using unix_socket means that if you are the system root user, you can log in as root@locahost without a password. This technique was pioneered by Otto Kekäläinen in Debian MariaDB packages and has been successfully since as early as .
It is based on a simple fact that asking the system root for a password adds no extra security — root has full access to all the data files and all process memory anyway. But not asking for a password means there is no root password to forget (no need for the numerous tutorials on “how to reset MariaDB root password”). And if you want to script some tedious database work, there is no need to store the root password in plain text for the script to use (no need for debian-sys-maint user).
Still, some users may wish to log in as MariaDB root without using sudo. Hence the old authentication method — conventional MariaDB password — is still available. By default, it is disabled (“invalid” is not a valid password hash), but one can set the password with a usual statement. And still retain the password-less access via sudo.
If you install MariaDB locally (say from a tarball), you will not want to use sudo to be able to log in. This is why MariaDB creates a second all-powerful user with the same name as a system user that owns the data directory. In local (not system-wide) installations, this will be the user who installed MariaDB — they automatically get convenient password-less root-like access, because they can access all the data files anyway.
Even if MariaDB is installed system-wide, you may not want to run your database maintenance scripts as system root — now you can run them as system mysql user. And you will know that they will never destroy your entire system, even if you make a typo in a shell script.
However, seasoned MariaDB DBAs who are used to the old ways do need to make some changes. See the examples below for common tasks.
After installing MariaDB system-wide, the first thing you’ve got used to doing is logging in to the unprotected root account and protecting it, that is, setting the root password:
This is not only unnecessary now, but it will also simply not work — there is no unprotected root account. To log in as the root user, use
Note that it implies you are connecting via the unix socket, not tcp. If you happen to have protocol=tcp in a system-wide /etc/my.cnf file, use sudo mariadb --protocol=socket.
After installing MariaDB locally, you’ve also used it to connect to the unprotected root account using mariadb -uroot. This will not work either; simply use mariadb without specifying a username.
If you've forgotten your root password, no problem — you can still connect using sudo and change the password. And if you've also removed unix_socket authentication, to restore access, do as follows:
restart MariaDB with --skip-grant-tables
log in to the unprotected server
run (note, before 10.4, this would’ve been the last step, not anymore). This disables --skip-grant-tables and allows you to change the stored authentication method
run FOR root@localhost to change the root password.
To view inside privilege tables, the old mysql.user table still exists. You can select from it as before, although you cannot update it anymore. It doesn’t show alternative authentication plugins, and this was one of the reasons for switching to the mysql.global_priv table — complex authentication rules did not fit into the rigid structure of a relational table. You can select from the new table, for example:
If you don't want the root@localhost user account created by to use authentication by default, then there are a few ways to revert to the previous authentication method for this user account.
One way to revert to the previous authentication method for the root@localhost user account is to execute with a special option. If is executed while --auth-root-authentication-method=normal is specified, then it will create the default user accounts using the default behavior of and before.
This means that the root@localhost user account will use authentication by default. There are some other differences as well. See for more information.
For example, the option can be set on the command-line while running :
The option can also be set in an in an supported by . For example:
If the option is set in an and if is executed, then will read this option from the , and it will automatically set this option.
If you have already installed MariaDB, and if the root@localhost user account is already using authentication, then you can revert to the old authentication method for the user account by executing the following:
This page is licensed: CC BY-SA / Gnu FDL
CREATE ROLE journalist;
GRANT SHOW DATABASES ON *.* TO journalist;
GRANT journalist TO hulda;SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| NULL |
+--------------+
SET ROLE journalist;
SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| journalist |
+--------------+
SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| ... |
| information_schema |
| mysql |
| performance_schema |
| test |
| ... |
+--------------------+
SET ROLE NONE;CREATE ROLE writer;
GRANT SELECT ON data.* TO writer;
GRANT writer TO journalist;SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| NULL |
+--------------+
SHOW TABLES FROM data;
Empty set (0.01 sec)
SET ROLE journalist;
SELECT CURRENT_ROLE;
+--------------+
| CURRENT_ROLE |
+--------------+
| journalist |
+--------------+
SHOW TABLES FROM data;
+------------------------------+
| Tables_in_data |
+------------------------------+
| set1 |
| ... |
+------------------------------+CREATE ROLE r1;
GRANT ALL ON db1.* TO r1;
GRANT r1 TO foo@localhost;
GRANT ALL ON db.* TO foo@localhost;
SELECT CURRENT_USER
+---------------+
| current_user |
+---------------+
| foo@localhost |
+---------------+
SET ROLE r1;
CREATE TABLE db1.t1 (i INT);
CREATE VIEW db.v1 AS SELECT * FROM db1.t1;
SHOW CREATE VIEW db.v1;
+------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v1 | CREATE ALGORITHM=UNDEFINED DEFINER=`foo`@`localhost` SQL SECURITY DEFINER VIEW `db`.`v1` AS SELECT `db1`.`t1`.`i` AS `i` from `db1`.`t1` | utf8 | utf8_general_ci |
+------+------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
CREATE DEFINER=CURRENT_ROLE VIEW db.v2 AS SELECT * FROM db1.t1;
SHOW CREATE VIEW db.b2;
+------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View | character_set_client | collation_connection |
+------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v2 | CREATE ALGORITHM=UNDEFINED DEFINER=`r1` SQL SECURITY DEFINER VIEW `db`.`v2` AS select `db1`.`t1`.`a` AS `a` from `db1`.`t1` | utf8 | utf8_general_ci |
+------+-----------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+rootSecond, if authentication fails with the unix_socket authentication plugin, then it is configured to try to use the mysql_native_password authentication plugin. However, an invalid password is initially set, so in order to authenticate this way, a password must be set with SET PASSWORD.
However, just using the unix_socket authentication plugin may be fine for many users, and it is very secure. You may want to try going without password authentication to see how well it works for you. Remember, the best way to keep your password safe is not to have one!
All user accounts, passwords, and global privileges are now stored in the mysql.global_priv table. The mysql.user table still exists and has exactly the same set of columns as before, but it’s now a view that references the mysql.global_priv table. Tools that analyze the mysql.user table should continue to work as before. From , the dedicated mariadb.sys user is created as the definer of this view. Previously, root was the definer, which resulted in privilege problems when this username was changed.
adds support for User Password Expiry, which is not active by default.
CREATE USER root@localhost IDENTIFIED VIA unix_socket
OR mysql_native_password USING 'invalid'
CREATE USER mysql@localhost IDENTIFIED VIA unix_socket
OR mysql_native_password USING 'invalid'$ sudo dnf install MariaDB-server
$ mariadb -uroot
...
MariaDB> set password = password("XH4VmT3_jt");$ sudo dnf install MariaDB-server
$ sudo mariadbSELECT CONCAT(user, '@', host, ' => ', json_detailed(priv)) FROM mysql.global_priv;mariadb-install-db --user=mysql --datadir=/var/lib/mysql --auth-root-authentication-method=normal[mysql_install_db]
auth_root_authentication_method=normalALTER USER root@localhost IDENTIFIED VIA mysql_native_password
USING PASSWORD("verysecret")MariaDB catalogs will be a multi-tenancy feature where a single instance MariaDB server handles multiple independent tenants (customers), who have their own users, schemas etc. See MDEV-31542 "Add multi-tenancy catalogs to MariaDB" for details.
For hosting providers, a common solution, to drive down cost, is to have one MariaDB server support several different customers by creating one named schema for each of them.
This has however a lot of limitations:
The user cannot have exactly the same schema(s) on the cloud as they have on premise.
The user cannot use multiple schemas.
The user cannot take a backup of all their data (not even with ). This is because the ‘mysql’ schema, which includes users, stored procedures etc. cannot be copied as its data is shared among all server users.
The user cannot access the or .
The other option is to have a MariaDB container for each tenant. The problem with this is that, because of the memory needed per container, one can only have a very limited number of tenants per computer.
The suggested solution is to solve all of the above and thus create a better multi-tenant database is to add support for catalogs to MariaDB.
The following picture shows the change:
By each user having their own catalog, they will get very close to the same user experience as if they would have the MariaDB server for themselves.
Catalogs make it possible for hosting providers to have 10-100x more 'not that active' database users on a server compared to having a container or MariaDB server per customer (which limits a 192G server to about 100 customers with a 1G InnoDB buffer each).
Each user is assigned one catalog. The user can specify their catalog in their my.cnf file or as an argument to clients or when connecting to MariaDB server.
Users can of all their tables (including the ‘mysql’ database) and apply it on their own on premise MariaDB or to another ‘MariaDB catalog’ to duplicate their setup.
Each catalog has its own privilege system. This allows a MariaDB admin to create users independently in their catalog to users in any other catalog. This also implies that the catalog has to be part of the connect information as otherwise the server does not know which user table to use.
For the end user, the MariaDB server will act as a normal a standalone server, with the following differences:
When connecting to the server, a normal user must specify the catalog. If the connector software does not support catalogs, then the catalog should be specified in the database string. If the catalog is not specified, the 'def' catalog is assumed.
and can be configured to only be used with the catalog directory or a directory in it.
command is only for the 'catalog root users'
Replication (MASTER and SLAVE commands) are only for 'catalog root users'
The 'def' catalog is reserved to store permissions for 'catalog root users', which can access any catalog. * These are meant for admin users that need to do tasks like shutdown, upgrade, create/drop catalogs, managing primaries and replicas etc.
Only the ‘catalog root user’ can change to another catalog with ‘set catalog catalog_name’.
A normal user can do ‘set catalog current-catalog’. This will be needed to be able to execute a that includes this command.
MariaDB server will be able to run either on 'original mode', where the data layout is exactly as it was before, or on 'catalog' mode, with a new data layout:
When running with --use-catalogs, it will create the following new data structure:
data_directory/
engine system data files
system files
replication files
The disk structure when not using catalogs is:
data_directory/
engine system data files
system files
replication files
The above shows:
There is a 'mariadb' catalog that stores admin users that can access all catalogs, shutdown servers, create new catalogs etc. The 'system root' user uses this when connecting.
Each catalog has their own users, privilege tables, databases, error log and general logs
The MariaDB server will automatically start in catalog mode if it notices the new directory structure.
;
CREATE CATALOG
ALTER CATALOG
Client changes:
Add --catalog option to all standard MariaDB clients
Add support for looping over all existing catalogs to:
Changes to :
Allow one to create multiple catalogs at once: -–catalogs=”catalog1,catalog2”
Init MariaDB with catalog support: —use-catalogs
Changes to mariadb (mysql client):
Add support for 'USE CATALOG xxx’' (and later 'use database xxx').
Changes to mysql-test-run:
Add support of running tests with catalogs (normal tests are run without catalogs)
Changes to MariaDB server (See ):
Add support for 'catalog' in the connection string. For old clients, the user can specify the catalog as part of the database. If catalog is not specified, the 'def' catalog (like now) is assumed.
Add CATALOG() function that returns the current catalog.
Add ‘USE CATALOG xxx’
Add 'USE DATABASE xxx'
Notes:
The storage handler calls will probably not be changed. The storage engine will get the catalog name as part of the database name (catalog/database).
We don't need a 'catalog' column for tables in the 'mysql' schema (like mysql.proc) as these are stored per catalog.
Instead of sending a catalog string to function, use a pointer to the global catalog object. Do the same later for databases. This allows use to precompute things like 'filename' for catalogs and databases and we don't have to do this for every table open. It also allows us to later support logging information at a catalog and database level.
Don't take a MDL lock for the catalog for each table. The metadata lock for the catalog will be taken when a user logs in or changes catalog.
Add system variables ‘current_catalog’ and ‘current_database’ and allow users to change these.
Add support for ‘catalog ports’ that are connected to catalog. This allows users to connect to a specific catalog from any client software.
Database names cannot contain ‘.’ when connecting from clients without the new catalog connect option.
One cannot refer to other catalogs in triggers, stored procedures, events etc. This is because a transaction cannot span catalogs.
Only the catalog root user can use mariadb-backup. This is a normal restriction as one has to be system root to be able to use mariadb-backup.
Events are global (to save resources). Catalog users can enable/disable events for their catalog.
Support usage statistics per catalog and whole server (the last for the ‘catalog root user’). This allows the DBA to see the number of queries, type of queries etc. Some ‘system’ and ‘global innodb’ statistics will only be shown globally (number of open files, number of sync calls etc).
Support a my.cnf file in each catalog directory to handle catalog (customer) unique defaults.
Add quotas per catalog for tables and temporary files.
Add more support to limit users from overusing resources (cpu, tables, databases, number of connections etc)
Allow users to manage their own replication stream (maybe?).
Allow users to have different options for the S3 engine
More things will be added later.
Appendix
Legacy Connector Support
SQLALchemy test:
The following tests ensured that inside the server (mysql_change_db), the “catalog/test” was picked up as the database.
PHP PDO test:
PHP mysqli test:
Nodejs test:
(need to map out a few other connectors here to make sure it’s supported well in this form).
Ref:
As shared hosting services have a naming scheme from user/schema to database name in MariaDB, to provide a migration to the new catalog layout, the following steps will be required:
Use to dump the original data
On the new server execute:
mariadb –catalog catalog_name < dump_file
This is needed as InnoDB needs to know where the new files are located.
Create a migration tool set / procedure that does the following
Execute for all tables in a catalog.
Take a copy of the catalog directory
Copy the data to a new catalog directory to the new server
Run on each InnoDB table
Note that for partitioned tables the process will be a bit more complex, see above link.
This procedure will be a bit easier after an in-the-works patch for InnoDB related to IMPORT will be pushed. (Should happen before we start on the catalog project)
Drizzle’s default catalog was called "local". MariaDB’s default will be called ‘def’, as this is what we already have as the default catalog in information_schema, in current connectors and other places.
CONNECT engine will need testing against catalogs and maybe a small code change to support them. It could also be a way to join from one catalog to another.
Add multi-tenancy catalogs to MariaDB
This page is licensed: CC BY-SA / Gnu FDL
One can check if a server supports catalogs with:
1 means that the server is configured for catalogs.
This page is licensed: CC BY-SA / Gnu FDL
MariaDB [def.test]> select catalog();
+-----------+
| catalog() |
+-----------+
| def |
+-----------+SELECT @@catalogs;
+------------+
| @@catalogs |
+------------+
| 1 |
+------------+After logging in, a normal user can only see the objects (databases, tables, users etc) from their database. They cannot access other catalogs or change catalogs.
A normal user cannot change the active catalog with a command. They need to logout from the current catalog and login to another.
Errors from background task (like write error) will be logged into the system error log, not the catalog error log.
SHOW STATUS will show status data for the whole server, not only for the active catalog.
The server will handle legacy applications by extending the default database in the connection to contain the catalog in the form “catalog/database”. See Appendix for details.
Tables that are only read from the ‘def.mysql’ schema:
help_* tables
time_zone* tables
(replication state)
(innodb internal)
(federated)
(innodb internal)
(udf)
general.log
error.log
mariadb/
mysql/
privilege tables
catalog1
general.log
error.log
mysql/
privilege tables
database1/
tables for database1
database2/
tables for database2
catalog2/
general.log
error.log
mysql/
privilege tables
database1/
tables for database1
database2/
tables for database2
general.log
error.log
mysql/
privilege tables
database1/
tables for database1
database2/
tables for database2
SHOW CREATE CATALOG catalog_name;
SELECT CATALOG();
Create a global CATALOG object to hold all information related to the catalog.
Add the current catalog to the 'thd' object.
Add catalog argument to all functions that take 'database' as an argument.
Add SHOW CATALOGS and information_schema.catalogs
Move all relevant global variables (users, privileges, mdl-locks(?), open log files) to be stored in the CATALOG structure.
Add 'catalog privilege', for ‘catalog super users’ to allow them to access data in any catalog.
Add support for accessing tables with 'catalog.schema.table' (needed for catalog super users).
For normal users, only show processes for the current catalog in 'show processlist'.
Add loops over all catalogs for information schema for the 'catalog root user'.
Update performance schema to take catalogs into account.
Work with external connectors to get them to support connecting with a catalog.
Check/update all storage engines to ensure they work also with catalogs.
Support 'drop catalog'. (This is in Stage 2 as there may be some issues to drop already active CATALOG objects)
Add optional catalog support to the S3 engine
More things will be added later.
In [1]: from sqlalchemy.engine import make_url
In [2]: u = make_url('mariadb+mariadbconnector://app_user:Password123!@127.0.0.1:3306/catalog/company')
In [3]: u.database
Out[3]: 'catalog/company'$ php -r '$db = new \PDO("mysql:host=localhost;user=dan;dbname=catalog/test;charset=utf8mb4;unix_socket=/tmp/build-mariadb-server-10.4.sock");'php -r '$dbcon = mysqli_connect("localhost","dan","nopass","catalog/test",3306,"/tmp/build-mariadb-server-10.4.sock");'var mysql = require('mysql')
var con = mysql.createConnection({
socketPath: "/tmp/build-mariadb-server-10.4.sock",
user: "dan",
password: "yourpassword",
database: "catalog/test",
})
con.connect(function(err) {
if (err) throw err;
console.log("Connected!");
})