In short, the team to create a user with the same privileges as the root user, use the following command, which grants global privileges to the user Janet connecting via localhost: mysql> GRANT ALL ON *. * TO ‘janet’@’localhost’ WITH GRANT OPTION; The WITH GRANT OPTION clause allows users to grant their privileges to other users.
Table of Content
Introduction
MySQL was created by a Swedish company, MySQL AB, founded by Swedes David Axmark, and Allan Larsson, and Finland Swede Michael “Monty” Widenius. The original development of MySQL by Widenius and Axmark began in 1994. The first version of MySQL appeared on 23 May 1995.
MySQL is a relational database management system (RDBMS) developed by Oracle that is based on structured query language (SQL). A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or a place to hold vast amounts of information in a corporate network.
SQL Meaning
SQL stands for Structured Query Language. It is used for storing and managing data in a relational database management system (RDMS). SQL is a standard language for Relational Database Systems. It enables a user to create, read, update and delete relational databases and tables. MySQL’s name is a combination of “My,” the name of MySQL creator Michael Widenius’s daughter, and “SQL”. A flexible and powerful program, MySQL is the most popular open-source database system in the world.
Prerequisites
For learning MySQL, you don’t need anything except your interest which will only make your journey easy-going. MySQL is easy to learn and in practical terms also it is easy. Even a learner from non-programming can learn MySQL.
There are many online platforms available that help your journey of learning MySQL easily. Can give you practical useful insights. One of the highly recommended platforms for learning the same according to me is Edu4Sure, it gives you practical knowledge, and the trainers were are of containing experience of 12 years. I completed the journey of learning in just one week and was highly satisfied and happy. A proper certificate was issued at the end.
Granting Permission
To GRANT ALL privileges to a user, allowing that user full control over a specific database, use the following syntax: mysql> GRANT ALL PRIVILEGES ON database_name.* TO ‘username’@’localhost’; With that command, we’ve told MySQL to GRANT the PRIVILEGES of type ALL (thus everything of course). And the main thing is If the user account you are logged in as has SELECT privileges on the internal MySQL database, you can see the privileges granted to other user accounts. To show the privileges of other accounts, use the following format: SHOW GRANTS FOR ‘<user>’@'<host>’; The output will display the privileges of the provided account.
Creating a New User in MySQL
Create a new MySQL user account: mysql> CREATE USER ‘local_user’@’localhost’ IDENTIFIED BY ‘password’; This command will allow the user with username local_user to access the MySQL instance from the local machine (localhost) and prevent the user from accessing it directly from any other machine. In other words;
A MySQL shell loads. Use the ALTER USER command and change the authentication method to log into MySQL as root: ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘insert_password’; This command changes the password for the user root and sets the authentication method to mysql_native_password.
Step-by-Step Guide
1. At the command line, log in to MySQL as the root user
mysql -u root -p
2. Type the MySQL root password, and then press Enter
3. To create a database user, type the following command. Replace username with the user you want to create, and replace password with the user’s password
GRANT ALL PRIVILEGES ON. TO
The previous command grants the user all permissions on all databases. However, you can grant specific permissions to maintain precise control over database access. For example, to explicitly grant only the SELECT permission for the specified user, you would use the following command
GRANT SELECT ON. TO ‘username’@’localhost’;
To grant the user all permissions only on the database named dbname, you would use the following command:
GRANT ALL PRIVILEGES ON dbname.* TO ‘username’@’localhost’;
For more information about setting MySQL database permissions,
4. Type \q to exit the MySQL program.
5. To log in to MySQL as the user you just created, type the following command. Replace the username with the name of the user you created in step 3:
mysql -u username -p
6. Type the user’s password, and then press Enter.
7. To create a database, type the following command. Replace dbname with the name of the database that you want to create:
CREATE DATABASE dbname;
8. To work with the new database, type the following command. Replace dbname with the name of the database you created in step 7:
USE dbname;
9. You can now work with the database. For example, the following commands demonstrate how to create a basic table named example, and how to insert some data into it:
CREATE TABLE example ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );
INSERT INTO example ( id, name ) VALUES ( null, ‘Sample data’ );
Granting a User Permissions
To GRANT ALL privileges to a user, allowing that user full control over a specific database, use the following syntax: mysql> GRANT ALL PRIVILEGES ON database_name.* TO ‘username’@’localhost’;
To grant permissions for the user, switch to the Object Permissions tab. In the Objects block, select the database object on which you want to grant privileges. In the Available Privileges block, select the permissions to be assigned and click Save.
So to grant the minimum permissions to the user by using the utility tool permissions.cmd, complete the following:
1. Launch the Windows Explorer and browse to the utility tool directory
Agent_grant_perm_dir:
For a 64-bit agent, Agent_grant_perm_dir is Agent_home\TMAITM6_x64\scripts\KOQ\GrantPermission.
For a 32-bit agent, Agent_grant_perm_dir is Agent_home\TMAITM6\scripts\KOQ\GrantPermission.
Agent_home is the agent installation directory.
Attention: The utility tool permissions.cmd grants db_owner on all databases by default. To exclude certain databases, you must add the database names in the Agent_grant_perm_dir\exclude_database.txt file. The database names must be separated by the symbol alias @.
Tip: For example, if you want to exclude the databases MyDatabase1 and MyDatabase2, add the following entry in the exclude_database.txt file:
2. Double-click permissions .cmd to launch the utility tool.
3. Enter the intended parameter values when prompted:
Conclusion
In this blog, we have seen How to create a new user in SQL and Grant Permissions. From here, you could continue to explore and experiment with different permissions settings for your MySQL user, or you may want to learn more about some higher-level MySQL. And follow the tips and conditions to improve your skills and performance using MySQL & mainly you all follow our website for more information related to Coding. So Follow Publish Square for more blogs like this
