How to grant a single database user permissions to manage multiple databases

Created:

2016-11-16 13:20:18 UTC

Modified:

2017-04-24 11:12:36 UTC

0

Was this article helpful?


Have more questions?

Submit a request

How to grant a single database user permissions to manage multiple databases

Applicable to:

  • Plesk for Linux
  • Plesk 12.0 for Linux

Question

How to grant a single database user permissions to manage multiple databases?

Answer

It is possible to create a user which can manage all databases on given subscription since Plesk 11.5 : go to Plesk > Domains > example.com > Databases > Database Users > db_username and select "Any" in "Database" drop-down list.

However, quick switching between databases in DB Webadmin (phpMyAdmin) will be not available, as phpMyAdmin is always opened for a specific database.

In versions prior to 11.5, permissions to manage additional databases should be granted manually.

Important: manually granted permissions will not be registered by Plesk, which means that user will not appear in control panel. It also means that such permissions will not be saved in a backup or migrated by Plesk.

To grant database user permissions to manage another database, use the following instructions:

MySQL : following SQL statements should be executed:

> GRANT ALL PRIVILEGES ON <newdb>.* TO 'olduser'@'%';

>FLUSH PRIVILEGES;

PostgreSQL : login to template1 database, grant all privileges on database to user and add user into a group with the same name as database:

# su - postgres

$ psql template1

template1=# grant all privileges on database newdb to olduser;GRANT

template1=# grant newdb to olduser;GRANT ROLE

template1=# \dg

List of roles Role name | Attributes | Member of

-----------+--------------+---------------------------------

... olduser | | {olddb,newdb}...*

MSSQL : open SQL Server Management Studio (free download from Microsoft website for SQL Server 2005 , 2008 and 2012 ). Connect to SQL server, in Object Explorer (left frame) expand Security → Logins , right-click on required database user and select Properties . In user properties, go to User Mapping , check new database and at the bottom select db_owner role and apply settings:

Have more questions? Submit a request
Please sign in to leave a comment.