Database User Roles and Permissions

Plesk provides user roles for MySQL and Microsoft SQL Server database users. The roles act as templates that help to assign permissions to a database user. The following database user roles are supported: Read and Write (used by default), Read Only, and Write Only. Each role has a pre-defined set of privileges that are granted to a database user account with this role. You can edit sets of privileges that correspond to different roles.

Additionally, MySQL supports the Custom role that corresponds to a user-defined set of privileges. SQL Server users cannot modify sets of permissions that come with roles.

You can deny a permission and prohibit Plesk users from granting it.

For details on how to select roles for database users in Websites & Domains > Databases > User Management, see Managing Database User Accounts. Note that database users on administrator’s subscriptions can be given global privileges in addition to the rights on database tables.

MySQL Database User Roles

In Plesk with MySQL, users can select roles for database users as well as add or remove individual privileges.

The default sets of MySQL privileges for each role are listed below.

Privilege	Read and Write	Read Only	Write Only
Select	➕	➕	➖
Insert	➕	➖	➕
Update	➕	➖	➕
Delete	➕	➖	➕
Create	➕	➖	➕
Drop	➕	➖	➕
Alter	➕	➖	➕
Index	➕	➖	➕
Create Temporary Tables	➕	➖	➕
Lock Tables	➕	➖	➕
Create View	➕	➖	➕
Show View	➕	➕	➖

To modify the default sets of privileges, edit the panel.ini file and list the privilege names for each role that you want to modify.

Remember that Plesk customers can still select more privileges than you specified in panel.ini unless you prohibit certain privileges (see further on this page).

For example:

[databaseManagement]
features.roles.mysql.readWrite = Select,Update,Insert
features.roles.mysql.readOnly = Select
features.roles.mysql.writeOnly = Update

Note

Changing the set of privileges for a role (for example, the Read and Write role) does not affect the privileges of existing MySQL database users with this role. The role of such database users will automatically change to Custom.

How to Deny a Privilege for all Users

You might need to deny a certain privilege for all database users, for example, the Delete privilege. To deny a privilege, specify the list of MySQL privileges in the panel.ini file, and omit the privilege that you want to deny.

For example:

[databaseManagement]
features.privileges.mysql.dataAccess = Select, Insert, Update, Delete
features.privileges.mysql.structureAccess = Create, Drop, Alter, Index, Create Temporary Tables, Lock Tables, Create View, Show View

The specified privileges are displayed in the Plesk UI (Websites & Domains > Databases > User Management). Users will be able to grant or revoke only the specified privileges. If a privilege is not in this list, Plesk regards it as denied for a database user.

Note

A privilege becomes denied only after a Plesk user saves any changes in the database user settings (Websites & Domains > Databases > User Management > click a user name > click OK).

Microsoft SQL Server Database User Roles

In Plesk with Microsoft SQL Server, users can select roles (Read and Write, Read Only, Write Only), but cannot add or remove individual permissions (SQL Server database-level roles). Permissions are not displayed in the Plesk UI.

The default permissions on Microsoft SQL Server for each role are listed below:

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

To modify the default set of permissions for any role, edit the panel.ini file and list the permission names.

For example:

[databaseManagement]
features.roles.mssql.readWrite = db_datareader,db_backupoperator,db_ddladmin
features.roles.mssql.readOnly = db_datareader,db_backupoperator
features.roles.mssql.writeOnly = db_datawriter

Note

When you modify the set of permissions for a role, the permissions of existing database users are changed. All existing and newly created users with this role will have the permissions that you specified in panel.ini.

How to Recover Default Permissions for Microsoft SQL Server Users

Each database user role has its default set of permissions (SQL Server database-level user roles). To reset the permissions of existing database users to the default values (according to their role), run the following command:

%plesk_dir%bin\repair.exe --update-mssql-users-permissions [-database-server <name>] [-database-name <name>]

Note

If you modified the permissions for a role (for example, the Read and Write role) in panel.ini, the command --update-mssql-users-permissions will use the set of permissions that you specified, rather than the default set (for example, the default set for Read and Write is db_datareader,db_datawriter,db_backupoperator,db_ddladmin).