Creating Databases and Users in MySQL

Historically I’ve been a Microsoft SQL Server guy but I’ve been doing quite a bit of query & analysis work on MySQL lately, though I’ve never performed any serious MySQL DBA. Well, the other day I was asked by a friend how to create a user and allow read/write access to a newly created database and this had to be done in SQL (i.e. no GUI tools or PHPMyAdmin).

Since I was starting from scratch I thought I’d put together a little script to create the database, add some data, create a user, prove that the user had write access and then tidy up after myself (always good to do!).

CREATE DATABASE ash_db;

USE ash_db;

CREATE TABLE ash_tbl (id INT NOT NULL,name CHAR(50) NOT NULL);

INSERT INTO ash_tbl (id,name) VALUES(1,'Ash');

SELECT * FROM ash_tbl;

CREATE USER 'ash_user' IDENTIFIED BY 'ash_pass';

GRANT ALL PRIVILEGES ON ash_db.* TO ash_user;

-- LOGIN AS ash_user THEN EXECUTE THIS...

INSERT INTO ash_tbl (id,name) VALUES(2,'Burton');

-- THEN LOG OUT AND COME BACK HERE

SELECT * FROM ash_tbl;

DROP TABLE ash_tbl;

DROP USER ash_user;

USE information_schema;

DROP DATABASE ash_db;