Managing Database User Accounts

Watch the video tutorial

When you work with a database in Plesk, Plesk accesses the database on behalf of a user account associated with the database. Therefore, every database should have at least one associated user account; otherwise, you will be unable to access it.

Any database user can be set as default for a certain database. Plesk will always access the database using this default user’s credentials even if there are other users associated with the database. If a database has several associated user accounts, and none of them are default, the first account from the list will be used.

Types of Database Users

There are two types of database user accounts in Plesk:

  • User accounts which have access to only one particular database. If you collaborate with other people on managing a website and wish to give them access to the database, you should create separate user accounts for them. Each of these accounts is used to access only one database. In this case, you first create a database and then user accounts.

  • Universal user accounts which have access to all databases.
    Universal users have access not only to all existing databases, but to all newly created databases as well.

    If you plan to install web apps on your site, it might be convenient to create one universal user account, so that all the apps can access their databases using this account. In this case, you first create a user account and then specify it when installing apps.

    Note

    A universal user acts only within one database server. If you use several database servers, create a separate universal user account for each server.

Operations with Database Users

You can create, update or remove a database user by going to Websites & Domains > Databases > User Management.

When creating a database user, you will be prompted to provide the user credentials for accessing the database and the name of the database that the specified user will access. A universal database user can be created by selecting Any for a Database.

Note

1. You can remove a default database user only by removing the database associated with this user. Alternatively, you can edit the user and clear the Make the user default for this database option, and then remove the user.

2. If a database user was created by an APS app, you can remove this user only by removing the respective app.

3. A database user password must be at least 5 characters long, must not contain the user name or any extended ASCII table characters.

Access Control

Starting from Plesk 12.0, you can allow or prohibit remote access to a database, or allow access only from the specified hosts. The access settings apply to individual database user accounts. For details, see the section Setting Up Custom Access Rules.

Database User Permissions

If you use MySQL/MariaDB or Microsoft SQL Server, you can manage permissions for database users through the Plesk UI, such as the permissions to perform operations on table structure and data. For MySQL/MariaDB these permissions are Select, Insert, Create, Drop and so on, and for Microsoft SQL Server these permissions are database-level roles such as db_ddladmin, db_datawriter, and so on.

To help you assign permissions, Plesk uses templates of permission sets called roles. On creation, each database user account is granted the default set of permissions. This set of permissions corresponds to the Read and Write role. Other supported roles are Read Only and Write Only. In addition, MySQL/MariaDB allows the Custom role with user-defined sets of privileges.

To modify the permissions of a MySQL/MariaDB database user:

  1. Go to Websites & Domains > Databases > User Management and click the database user name.

    By default, newly created database users have the Read and Write role. You can view and change the privileges included in this role.

  2. To allow read access or write access only, select the corresponding role (Read Only or Write Only).

  3. To add or remove privileges from the role already selected for the user, select or clear the corresponding checkboxes (Select, Insert, Update, and so on).

    Note that if you modify the set of privileges, the role becomes Custom.

To modify the permissions of a SQL Server database user:

  1. Go to Websites & Domains > Databases > User Management and click the database user name.

    By default, newly created database users have the Read and Write role.

  2. To allow read access or write access only, select the corresponding role (Read Only or Write Only).

    The default sets of permissions on Microsoft SQL Server are the following:

Permission Read and Write Read Only Write Only
db_backupoperator ➕ ➕ ➕
db_datareader ➕ ➕ ➖
db_datawriter ➕ ➖ ➕
db_ddladmin ➕ ➖ ➕

Note that the hosting provider can modify these permission sets.

Automatic Changes in User Roles

The hosting provider can add or remove permissions that are granted with different roles.

On MySQL/MariaDB, these modifications do not affect permissions of existing database users. The only thing that changes is their role in Plesk – it will change to Custom because their permissions no longer correspond to their previous role (Read and Write, Read Only, or Write Only). On Microsoft SQL Server, permissions (database-level roles) of existing users are changed in accordance with the changes made by the hosting provider.

The hosting provider can permanently deny some permissions for all MySQL/MariaDB database users, for example, the permission to delete objects. In this case, this permission is not displayed in Plesk. On Microsoft SQL Server, if the permission is excluded from all Plesk roles, it is denied for all users.