4

I want to create a new user in MySql. I do not want that new user to do much with my existing databases [I just want to grant Select privilege to him], but he can do anything and everything with a new database which he creates.

Firstly, is there a way to grant permission as per the database owner? If it is possible, then that is the ideal thing I am looking for. And if not, then how do I restrict a particular user from accessing [only Select privilege] some specific database only, allowing him to do anything he wants with the remaining ones?

Anuj
  • 1,203
  • 3
  • 13
  • 20

4 Answers4

17

From the MySQL grant documentation:

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT SELECT ON *.* TO 'jeffrey'@'localhost';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';

The first command creates the user. The second grants select on all databases and tables. The third command grants all access to all tables in db1.

Is there anything else specific you are looking to do?

Drew
  • 24,851
  • 10
  • 43
  • 78
HeatfanJohn
  • 7,143
  • 2
  • 35
  • 41
  • Ya I read this, but I am unable to get what I plan to do. Say I already have 2 databases present, namely db1 and db2. I want the new user to just have only select privilege to these 2 db's but he should be able to create a new database of his own and should be able to do whatever he wants with that database. – Anuj Jul 02 '12 at 20:27
  • Instead I want something like: GRANT ALL ON *.* TO 'jeffrey'@'localhost'; GRANT SELECT ON db1.* TO 'jeffrey'@'localhost'; But then this doesn't work. I get all permission for even db1. – Anuj Jul 02 '12 at 20:35
  • Yes, I see your issue. I tried a test giving a user the create privilege and I was able to create a new database using that user ID, however the user ID didn't have any privileges on the database that was created. Need to do more research. – HeatfanJohn Jul 02 '12 at 21:16
  • Ya and moreover, the create privilege also enables the user to create tables in the existing database, which again I do not want. – Anuj Jul 02 '12 at 21:32
3

To provide a specific user with a permission, you can use this framework:

GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’; 
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables.

Once you have finalized the permissions that you want to set up for your new users, always be sure to reload all the privileges.

 FLUSH PRIVILEGES;

For more about permission you can read this article https://www.digitalocean.com/community/articles/how-to-create-a-new-user-and-grant-permissions-in-mysql

For the list of permissions, see the MySQL Manual page Privileges Provided by MySQL.

Drew
  • 24,851
  • 10
  • 43
  • 78
1

Open mysql command prompt.

To create a new user when host is localhost then use this command

CREATE user 'test_user'@'localhost' identified by 'some_password';

for any host use %, like this

CREATE user 'test_user'@'%' identified by 'some_password';

Once the user is created, you need to Grant some access. Use following command for this.

GRANT SELECT,INSERT,UPDATE
ON database_name.table_name
TO 'test_user'@'localhost';

After successful execution of above query, test_user can select, insert and update in table_name (name of table) of database_name (name of database).

Anil Chahal
  • 2,544
  • 2
  • 22
  • 19
-2

grant privilege is given in data base like this

grant privilege on object to user

object is any data base table or relation and user might be the whom the privilege is provided to him.

Example

grant select,insert,update,on object name to user name

grant select on employee to john with grant option;

revoke delete on employee from john.
I.G. Pascual
  • 5,818
  • 5
  • 42
  • 58
Ebisa
  • 1