To delete a MySQL user account, use the following command: DROP USER Conclusion To delete a MySQL database, use the following command: DROP DATABSE testdb If you want to revoke or restore all privileges from a MySQL user over a database, use the revoke command as shown below: REVOKE ALL PRIVILEGES ON testdb.* FROM Step 7 – Delete MySQL Database and User Account Step 6 – Revoke Privileges from a MySQL User Account | GRANT SELECT, INSERT, DELETE ON `testdb1`.* TO | | GRANT ALL PRIVILEGES ON `testdb`.* TO | | GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO | | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO | Run the following command to display all granted privileges to testuser: SHOW GRANTS FOR should get the following output: +-a+ You can use the SHOW GRANTS command to display the privileges that you have granted to MySQL users. To grant all the privileges to testuser on testdb database, run the following command: GRANT ALL PRIVILEGES ON testdb.* TO grant all the privileges to testuser on all databases, run the following command: GRANT ALL PRIVILEGES ON *.* TO grant only SELECT, INSERT and DELETE privileges to testuser on testdb1 database, run the following command: GRANT SELECT, INSERT, DELETE ON testdb1.* TO Step 5 – Show Granted Privileges DROP: Allows users to delete a database and table.DELETE: This allows the user to delete rows from a table.CREATE: Allows users to create a database and table.UPDATE: Allows users to update table rows.SELECT: Allows users to read a database.INSERT: This allows the user to insert rows into a table.ALL PRIVILEGES: Used to grant all privileges to the user account.MySQL provides several types of user privileges that you can grant to a user. If you want to create a MySQL user and grant access from all remote hosts, run the following command: CREATE USER IDENTIFIED BY 'password' Step 4 – Grant Privileges to a MySQL User Account If you want to create a MySQL user and grant access from the remote machine with IP 192.168.10.100, run the following command: CREATE USER IDENTIFIED BY 'password' We have created a testuser for localhost, which means testuser will be able to connect to MySQL only from the localhost. Next, create a user named testuser for localhost and set a password using the following command: CREATE USER IDENTIFIED BY 'password' You should get all databases in the following output: +-+ Next, display all the databases using the following command: show databases Once you are connected, create a database named testdb and testdb1 using the following command: CREATE DATABASE testdb In this section, we will create a database and user in MySQL.įirst, connect to the MySQL shell using the following command: mysql Systemctl enable mysqld Step 3 – Create a Database and User in MySQL 8 Once the installation is completed, start the MySQL service and enable it to start at system reboot: systemctl start mysqld You can install it with the following command: dnf install mysql-server -y dnf update -y Step 2 – Install MySQL 8įirst, you will need to install MySQL 8 on your server. Once you are logged in to your CentOS 8 server, run the following command to update your base system with the latest available packages. Revoke Privileges from a MySQL User Account.Grant Privileges to a MySQL User Account.This post will show you how to create a user and grant permissions in MySQL 8. MySQL comes with a lot of options that allow you to grant specific permissions to databases based on user needs. This open-source database management system helps you to store, organize, and retrieve data. MySQL is one of the most popular database management systems around the world.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |