Search Our Database

Introduction

In Plesk, databases are managed on behalf of user accounts associated with them. Every database must have at least one user account to access it, and any database user can be designated as the default user. The default user credentials are used by Plesk to access the database, even if other users are associated with the database. If no default user is specified, Plesk uses the first user account listed.

This guide explains the types of database users in Plesk, how to manage them, and the permissions that can be assigned to database users. Whether you’re managing a single database or multiple databases across several servers, proper user management is critical for ensuring security and access control.

Types of Database Users

Plesk offers two types of database user accounts:

1. User Accounts for Individual Databases:
   These user accounts have access to only one specific database. If multiple people are collaborating on managing the website, you can create separate user accounts for them. Each account is associated with a single database, ensuring restricted access to the relevant data.
2. Universal User Accounts for All Databases:
   Universal users have access to all databases within a database server. This type of account is especially useful if you plan to install multiple web applications, as it allows a single user to manage all database connections. Universal users can be granted access to all existing and newly created databases. However, note that a universal user only operates within a single database server. If you are using multiple database servers, you’ll need to create separate universal accounts for each.

Operations with Database Users

Database users can be created, updated, or removed through the Plesk interface. Here’s how:

1. Go to Websites & Domains > Databases > User Management.
2. When creating a new user, provide the user credentials and specify the database they will access. For a universal user, select Any for the database field.

Important Notes:

• A default database user can only be removed by deleting the associated database, or by clearing the Make the user default for this database option.
• If a database user is created by an APS app, it can only be removed by uninstalling the app.
• Passwords for database users must be at least 5 characters long and cannot contain the username or extended ASCII characters.

Access Control for Database Users

From Plesk version 12.0 and later, you can control remote access for each database user. You can either allow or deny remote access entirely or limit access to specific hosts. These settings provide additional control over how and from where a database can be accessed, enhancing security.

Database User Permissions

Plesk allows granular control over database user permissions for MySQL and Microsoft SQL Server. These permissions govern what actions a user can perform on the database, such as modifying data or altering table structures.

MySQL Database Permissions

In MySQL, user permissions include actions like Select, Insert, Create, and Drop. To simplify assigning permissions, Plesk uses predefined permission sets called roles:

• Read and Write: Grants both read and write permissions by default.
• Read Only: Grants read permissions without the ability to modify data.
• Write Only: Grants the ability to modify data without read access.
• Custom: Allows user-defined sets of privileges for more specific control.

You can modify a MySQL user’s permissions by navigating to Websites & Domains > Databases > User Management, selecting the user, and adjusting the role or privileges as needed.

Microsoft SQL Server Permissions

For Microsoft SQL Server, permissions are managed using database-level roles such as db_backupoperator, db_datareader, db_datawriter, and db_ddladmin. Plesk defines three default roles:

• Read and Write: Allows full access to read and write data, as well as perform backup and administrative operations.
• Read Only: Grants only read permissions.
• Write Only: Allows modifications but no read access.

To change permissions for a Microsoft SQL Server user, navigate to Websites & Domains > Databases > User Management and select the relevant user.

Default SQL Server Permissions:

Note: Hosting providers can modify these permission sets and deny certain privileges across all users.

Automatic Changes in User Roles

Hosting providers may modify permissions associated with different roles. These changes affect database users differently depending on the database server:

• MySQL: If permissions change, existing users retain their old permissions, but their role in Plesk changes to Custom if it no longer matches the original role (Read and Write, Read Only, or Write Only).
• Microsoft SQL Server: Permissions for existing users are updated automatically according to the changes made by the hosting provider.

Hosting providers can also permanently deny specific permissions for all MySQL users, such as the ability to delete objects. If a permission is denied for all users, it will not appear in Plesk. Similarly, if permissions are removed from all SQL Server roles, they are denied for all users.

Conclusion

Managing database users in Plesk involves creating, modifying, and removing user accounts, assigning appropriate permissions, and controlling access. By understanding the types of database users, how to set permissions, and the impact of hosting provider modifications, administrators can ensure secure and efficient database management.

Should you have any inquiries about the guidelines, please feel free to open a ticket through your portal account or contact us at support@ipserverone.com. We’ll be happy to assist you further.

Source : www.plesk.com