User Privilege Management Mysql/Mysqli/Maria DB

MYSQL/MARIA DB /MYSQLI USERS Privileges

STEP 1:

Go to your server directory

Open Prompt command

(Example my xampp server in D Drive)

Login to your Root Directory

D:\xampp\mysql\bin> mysql -h localhost -u root

After login you will be here

MySQL [(none)]>

Step 2:

We need to know how many Users

MySQL [(none)]> SELECT user From mysql.user;

MySQL [(none)]> SELECT host,user From mysql.user;

Step 3:

CREATE A USER

MySQL [(none)]>  CREATE USER ‘querypanel’@’localhost’ IDENTIFIED BY ‘querypane’;

FLUSH PRIVILEGES;  

For changes to take effect immediately flush the privileges

MySQL [(none)]>  GRANT ALL PRIVILEGES ON *.* TO querypanel@’localhost’ IDENTIFIED BY ‘querypanel’;

Here I have given to querypanel user all access.

Now move to root directory and login again via querypanel  new added user

Here login with new added user.

we can Insert, update and delete any user and database

Now I have deleted one database because w have all privileges

Step 4:  now I am going to create one user with limited access permission.

Ctrl+c move to directory. So need to login via root.

After that, creating a new user:

MySQL [(none)]> CREATE USER ‘Arick’@’localhost’ IDENTIFIED BY ‘arick’;

MySQL [(none)]> GRANT CREATE, SELECT,INSERT,UPDATE,DELETE ON delhi_metro.* TO Arick@’localhost’;

This user can access only one database delhi_metro.

Ctrl+c move to Arick (user)

Here We are Login With Arick(user)

D:\xampp\mysql\bin>mysql -h localhost -p  -u Arick

Password : arick

Permissions : CREATE,  SELECT, INSERT, UPDATE, DELETE

Here:  I have created a Table

MYSQL[ delhi_metro ]> CREATE TABLE North_metro(Metro_id INT NOT NULL PRIMaRY KEY, DESTINATION VARCHAR(50),STOPS VARCHAR(20),STATUS VARCHAR(20));

INSERT

MYSQL[ delhi_metro ] > INSERT INTO North_metro Values(’01’,’MANDI HOUSE’,’05’,’ACTIVE’);

SELECT

MYSQL[ delhi_metro ] > SELECT * FROM North_metro Order by Metro_id ASC;

UPDATE:

MYSQL[ delhi_metro ] > UPDATE north_metro SET DESTINATION=’RJ’ WHERE Metro_id=’1′;

DELETE QUERY:

MYSQL[ delhi_metro ] > DELETE FROM north_metro WHERE Metro_id=’22’;

Guys if you have any queries We love to share our Real Time Experience