VIVEK PANDHARKAR Uncategorized MYSQL Database Permissions to User for all Databases on server

MYSQL Database Permissions to User for all Databases on server

Categories:

Create a Database Using cPanel API

  1. Log into your server via SSH.
  2. Run the following command to create the database:uapi --user=exampl3 Mysql create_database name=db_nameNOTE: Be sure to replace exampl3 with your actual cPanel username and db_name with the actual name you would like to give to the database.
  3. Run the following command to create the database user:uapi --user=exampl3 Mysql create_user name=db_user password=P@s$w0rd123!NOTE: Be sure to replace exampl3 with your actual cPanel username, db_user with the actual name you would like to give to the database user, and P@s$w0rd123! to the actual password you would like to assign to this user.
  4. Assign the database user privileges to access the database by running the following command:uapi --user=exampl3 Mysql set_privileges_on_database user=db_user database=db_name privileges=ALLNOTE: Be sure to replace exampl3 with your actual cPanel username, db_user with your actual database user, and db_name with the actual name of the database.

uapi Mysql set_privileges_on_database user=rgvpiqgy_democrm database=rgvpiqgy_crmt_demo_1010 privileges=ALL

Get all the Database names in the database in comma separated:

SELECT GROUP_CONCAT(SCHEMA_NAME) as databases
FROM SCHEMATA where SCHEMA_NAME NOT IN (‘information_schema’)

The dbmaptool Script


Overview

The /usr/local/cpanel/bin/dbmaptool script grants ownership of databases and database users to a cPanel account. Note:

This script does not map a virtual user to a database.

Run the script

To run this script on the command line, use the following format:

/usr/local/cpanel/bin/dbmaptool [user] [options]

Options

Use the following options with this script:

OptionDescriptionExample
userRequired.
The cPanel username to grant database ownership to.
username
--dbsA comma-separated list of the database names that the account will own.--dbs 'db1, db2'
--dbusersA comma-separated list of the database users that the account will own.--dbusers 'virt1, virt2'
--typeThe database type:
mysql — MySQL® database.pg — PostgreSQL® database.
--type 'mysql'
--helpDisplay the script’s help documentation.--help

Example

To grant ownership of the db1 and db2 MySQL databases and the virt1 and virt2 database users to the username cPanel account, run the following command:

/usr/local/cpanel/bin/dbmaptool username --type 'mysql' --dbs 'db1, db2' --dbusers 'virt1, virt2'

If the script succeeds, it will not return output.

Introduction

MySQL is a database application for Linux and part of the popular LAMP stack (Linux, Apache, MySQL, PHP). A MySQL installation includes options of managing through a root user or specific user accounts.

For security reasons, it is generally better to create and handle data as specific users.

In this tutorial, learn how to create MySQL user accounts and managing their permissions and privileges.

tutorial on mysql create user and grant privileges

Prerequisites

  • A Linux server with MySQL or MariaDB installed and running
  • Access to the MySQL root user credentials
  • Access to a terminal window/command-line (Ctrl-Alt-T / Ctrl-Alt-F2)

How to Create New MySQL User

1. Before you can create a new MySQL user, you need to open a terminal window and launch the MySQL shell as the root user. To do so, enter the following command:

sudo mysql –u root –p

2. Type in the root password for this account and press Enter.

entering root password on MySQL

The prompt should change to show that you are in the mysql> shell.

3. Next, create a new MySQL user with:

CREATE USER 'username' IDENTIFIED BY 'password';

Replace username and password with a username and password of your choice.

Alternatively, you can set up a user by specifying the machine hosting the database.

  • If you are working on the machine with MySQL, use username@localhost to define the user.
  • If you are connecting remotely, use username@ip_address, and replace ip_address with the actual address of the remote system hosting MySQL.

Therefore, the command will be:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

or

CREATE USER 'username'@'ip_address' IDENTIFIED BY 'password';

You can also create a user that can connect from any machine with the command:

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Note: Make sure you use a strong and complex password, especially if you are setting up a user who can connect from any machine.


How to Grant Permissions in MySQL

Before logging in with a new account, make sure you have set the permissions for the user.

Permissions are actions that the user is allowed to perform in the database. Depending on how much authority you want your user to have, you can grant them one, several or all of the following privileges:

  • All Privileges: The user account has full access to the database
  • Insert: The user can insert rows into tables
  • Delete: The user can remove rows from tables
  • Create: The user can create entirely new tables and databases
  • Drop: The user can drop (remove) entire tables and databases
  • Select: The user gets access to the select command, to read the information in the databases
  • Update: The user can update table rows
  • Grant Option: The user can modify other user account privileges

The basic syntax used to grant privileges to a user account is:

GRANT permission_type ON database.table TO 'username'@'localhost';

For example, to grant insert privileges to a MySQL user you would run the command:

GRANT INSERT ON *.* TO 'username'@'localhost';

You can replace the privilege level according to your needs. Run the command for each privilege you wish to grant.

If you want to limit the user’s access to a specific database, name that database before the dot. Likewise, you can restrict a user’s access to a particular table by naming it after the dot, as in the command below:

GRANT INSERT *database_name.table_name* TO 'username'@'localhost';

MySQL User Management

This section will help you list the privileges held by a user account, take privileges away from a user, and completely delete a user account. It will also show you how to log out of the root MySQL user account, and log back in under the account you’ve just created.

How to List MySQL User Account-Privileges

To display all the current privileges held by a user:

SHOW GRANTS username;

Note: Refer to our article to learn how to list MySQL database users.


How to Grant All Privileges on a Database in MySQL

To grant all privileges to MySQL User on all databases use the command:

GRANT ALL PRIVILEGES ON *.* TO 'database_user'@'localhost';

However, you can also grant all privileges to a user account on a specific database with the following command:

GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost';

To grant all privileges to a user account over a specific table from a database type:

GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';

Revoke Privileges MySQL User Account

To take back privileges from a specific user, use the REVOKE command. It works similar to the GRANT command, its basic syntax being:

REVOKE permission_type ON database.table TO 'username'@'localhost';

Remove an Entire User Account

To delete a MySQL user account use the command:

DROP USER 'username'@'localhost';

Conclusion

You should now be able to create, modify, delete users and grant permissions in a MySQL database.

To improve security and limit accidental damage it is better to use a regular user instead of a root user in a production environment. You can also secure your database by limiting users only to the privileges required for their jobs.

After creating MySQL users and granting the privileges, our suggestion is to check out our article on how to allow remote connections to MySQL database.