Scribd
Upload
6 views10 pages

Creating User

The document provides instructions on creating and managing MySQL users, including syntax for creating users, granting and revoking privileges, and applying changes. It includes examples for creating users with specific permissions and demonstrates how to check user accounts and modify user passwords. Additionally, it outlines steps for switching users and deleting user accounts.

Uploaded by

Nafisa s
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views10 pages

Creating User

The document provides instructions on creating and managing MySQL users, including syntax for creating users, granting and revoking privileges, and applying changes. It includes examples for creating users with specific permissions and demonstrates how to check user accounts and modify user passwords. Additionally, it outlines steps for switching users and deleting user accounts.

Uploaded by

Nafisa s
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Creating User

MySQL USer

 A MySQL user is an account that allows a person or application to connect to


the MySQL server.
Each user has:
 A username
 A host from where they can connect (e.g., localhost)
 Privileges controlling what they can do
Syntax to Create a MySQL User

 CREATE USER 'username'@'host' IDENTIFIED BY 'password’;

 Example

 CREATE USER ‘jon'@'localhost' IDENTIFIED BY 'mypassword123';


Create a user who can log in from any
machine
 CREATE USER 'trainer'@'%' IDENTIFIED BY 'trainer@123’;

 Create a user for a specific remote IP

 CREATE USER 'student'@'[Link]' IDENTIFIED BY 'stud@123';


Grant Privileges
 A new user has no permissions.
 To give permissions, use:
 GRANT privileges ON [Link] TO 'username'@'host’;

 Give full permissions on one database


 GRANT ALL PRIVILEGES ON mydata.* TO 'nafisa'@'localhost’;

 Give only SELECT and INSERT


 GRANT SELECT, INSERT ON mydata.* TO 'trainer'@’%’;

 Allow only reading one table


 GRANT SELECT ON [Link] TO 'student'@'[Link]';
 Apply changes (FLUSH PRIVILEGES)
 FLUSH PRIVILEGES;

 Check all users


 SELECT User, Host FROM [Link];

 Change user password


 ALTER USER 'nafisa'@'localhost' IDENTIFIED BY 'newpass123’;
 Delete a user
 DROP USER 'trainer'@'%';
Complete example
 Step 1 – Create user
 CREATE USER 'techguru'@'localhost' IDENTIFIED BY 'guru@123’;

 Step 2 – Grant permissions


 GRANT SELECT, INSERT, UPDATE ON mydata.* TO 'techguru'@'localhost’;

 Step 3 – Apply privileges


 FLUSH PRIVILEGES;
 FLUSH PRIVILEGES in MySQL reloads the privilege tables from the mysql
system database into memory.

 Step 4 – Test login


 mysql -u techguru -p
Revoke previleges

 REVOKE privilege_list ON [Link] FROM 'username'@'host’;

 1: Revoke SELECT privilege


 REVOKE SELECT ON school.* FROM 'testuser'@'localhost’;

 Revoke multiple privileges


 REVOKE INSERT, UPDATE ON [Link] FROM 'testuser'@'localhost’;

 Revoke ALL privileges


 REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'testuser'@'localhost';
Steps to switch user

 cmd
 cd C:\Program Files\MySQL\MySQL Server 8.0\bin
 mysql -u root –p
 CREATE USER 'abc'@'localhost' IDENTIFIED BY 'xyz';
 GRANT SELECT ON [Link] TO 'abc'@'localhost';
 FLUSH PRIVILEGES;
 quit;
 mysql -u abc -p
 SELECT User, Host FROM [Link] WHERE User = 'abc';
 DROP USER IF EXISTS 'abc'@'%';
 DROP USER IF EXISTS 'abc'@'[Link]';
 DROP USER IF EXISTS 'abc'@'::1';

You might also like