MySQL Permissions – How to Grant Permission to New User

MySQLpermissionsusers

I want to create new user and want to give them specific permission like inset, update and delete.
But how to create new user and give them permission i don't know.

Any help please.

Best Answer

It would be something like this :

GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password';

To see a list of the privileges that have been granted to a specific user:

select * from mysql.user where User='username';

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

Related Solutions

MySQL Grant Permission

'insert' permission should be all you need to do an insert:

create database stack;
create user dummy;
create table stack.t(v integer primary key);
grant insert on stack.* to dummy;

as dummy:

insert into stack.t values(1);
Query OK, 1 row affected (0.00 sec)

select * from stack.t;
ERROR 1142 (42000): SELECT command denied to user 'dummy'@'localhost' for table 't'

and the grant like this will even apply to tables created after the grant was made - it gives a database privilege

As you also want to select you will also need to grantselecton stack.* to dummy

Mysql – Give privilege to new created databases in MySQL

It is impossible to do this using permissions only .

The only way is to create a stored procedure as described here

If you want to avoid stored procedures, a workaround is: GRANT ALL PRIVILEGES ONtestuser_%. * TO 'testuser'@'%'; (as suggested here); however, this has the problem that the users must then be very careful in naming their databases.

For example if user aaa creates database bbb_xyz, it can then be accessed exclusively by user bbb but not by user aaa.

Best Answer

Related Solutions

MySQL Grant Permission

Mysql – Give privilege to new created databases in MySQL

Related Question