Database User Roles and Permissions Print

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.

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.

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:

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).