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|
|Create Temporary Tables||➕||➖||➕|
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
panel.ini unless you prohibit certain privileges (see
further on this page).
[databaseManagement] features.roles.mysql.readWrite = Select,Update,Insert features.roles.mysql.readOnly = Select features.roles.mysql.writeOnly = Update
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.
[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.
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|
To modify the default set of permissions for any role, edit the panel.ini file and list the permission names.
[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
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
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>]
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