How to grant all privileges to root user in MySQL 8.0

CREATE USER 'username'@'localhost' IDENTIFIED BY 'the_password';

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost' WITH GRANT OPTION;

CREATE USER 'username'@'%' IDENTIFIED BY 'the_password';

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

SHOW GRANTS FOR username;

Just my 2 cents on the subject. I was having the exact same issue with trying to connect from MySQL Workbench. I’m running a bitnami-mysql virtual machine to set up a local sandbox for development.

Bitnami’s tutorial said to run the ‘Grant All Privileges’ command:

/opt/bitnami/mysql/bin/mysql -u root -p -e "grant all privileges on *.* to 'root'@'%' identified by 'PASSWORD' with grant option";

This was clearly not working, I finally got it to work using Mike Lischke’s answer.

1. Dropping the user.
2. Create the user again.
3. Make sure you have the correct binding on your MySQL config file.
   In my case I’ve commented the line out since it’s just for a sandbox environment.

From Mysql Console:

select user, host from mysql.user;

drop user '{{ username }}'@'%';

CREATE USER '{{ username }}'@'%' IDENTIFIED BY '{{ password }}';
GRANT ALL PRIVILEGES ON *.* TO '{{ username }}'@'%' WITH GRANT OPTION;

Run this command:

FLUSH PRIVILEGES;

3. Make sure you have the correct binding on your MySQL config file.

bind-address=127.0.0.1

and comment it using a ‘#’:

#bind-address=127.0.0.1

For production environments you might want to use limit the network access (additional notes at the end).

Then restart your MySQL service.

Hope this helps someone having the same issue!

MySQL Config File The file could have different locations depending on your
Linux distribution and installation. On my system it was located at
'/etc/my.cnf'. Here are other suggested locations:

• /etc/mysql/mysql.conf.d
• /etc/mysql/my.cnf

You can also search for the config locations as shown in this website:
How to find locations of MySQL config files.

13.7.1.6 GRANT Statement

GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_or_role [, user_or_role] ... [WITH GRANT OPTION] [AS user [WITH ROLE DEFAULT | NONE | ALL | ALL EXCEPT role [, role ] ... | role [, role ] ... ] ]
}
GRANT PROXY ON user_or_role TO user_or_role [, user_or_role] ... [WITH GRANT OPTION]
GRANT role [, role] ... TO user_or_role [, user_or_role] ... [WITH ADMIN OPTION]object_type: { TABLE | FUNCTION | PROCEDURE
}priv_level: { * | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name}user_or_role: { user (see Section 6.2.4, “Specifying Account Names”) | role (see Section 6.2.5, “Specifying Role Names”)
}

GRANT General Overview

• GRANT cannot mix granting
  both privileges and roles in the same statement. A given
  GRANT statement must grant
  either privileges or roles.

• The ON clause distinguishes whether the
  statement grants privileges or roles:

  • With ON, the statement grants
    privileges.

  • Without ON, the statement grants
    roles.

  • It is permitted to assign both privileges and roles to
    an account, but you must use separate
    GRANT statements, each
    with syntax appropriate to what is to be granted.

Each account name uses the format described in
Section 6.2.4, “Specifying Account Names”. Each role name uses the
format described in Section 6.2.5, “Specifying Role Names”. For example:

GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
GRANT SELECT ON world.* TO 'role3';

The host name part of the account or role name, if omitted,
defaults to '%'.

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
ALTER USER 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

From the program,
GRANT responds with
Query OK, 0 rows affected when executed
successfully. To determine what privileges result from the
operation, use SHOW GRANTS. See
Section 13.7.7.21, “SHOW GRANTS Statement”.

Under some circumstances,
GRANT may be recorded in
server logs or on the client side in a history file such as
~/.mysql_history, which means that
cleartext passwords may be read by anyone having read access
to that information. For information about the conditions
under which this occurs for the server logs and how to
control it, see Section 6.1.2.3, “Passwords and Logging”. For
similar information about client-side logging, see
Section 4.5.1.3, “mysql Client Logging”.

Object Quoting Guidelines

To specify quoted values:

• Quote database, table, column, and routine names as
  identifiers.

• Quote passwords as strings.

For string-quoting and identifier-quoting guidelines, see
Section 9.1.1, “String Literals”, and
Section 9.2, “Schema Object Names”.

mysql> CREATE DATABASE db;
Query OK, 1 row affected (0.01 sec)
mysql> CREATE TABLE db.t (c INT);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO db.t VALUES ROW(1);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER u;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT SELECT ON `d_`.* TO u;
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT INSERT ON `d%`.* TO u;
Query OK, 0 rows affected (0.00 sec)
mysql> EXIT

Bye

If we end the session and then log in again with the
client, this time as
, we see that this account has only the
privilege provided by the first matching grant, but not the
second:

$> mysql -uu -hlocalhost

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.35-tr Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
mysql> TABLE db.t;
+------+
| c |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> INSERT INTO db.t VALUES ROW(2);
ERROR 1142 (42000): INSERT command denied to user 'u'@'localhost' for table 't'

In privilege assignments, MySQL interprets occurrences of
unescaped _ and % SQL
wildcard characters in database names as literal characters
under these circumstances:

Account Names

GRANT ALL ON test.* TO ''@'localhost' ...;

SELECT Host, User FROM mysql.user WHERE User='';

DROP USER ''@'localhost';

Privileges Supported by MySQL

Table 13.11 Permissible Static Privileges for GRANT and REVOKE

A trigger is associated with a table. To create or drop a
trigger, you must have the
TRIGGER privilege for the
table, not the trigger.

MySQL account information is stored in the tables of the
mysql system schema. For additional
details, consult Section 6.2, “Access Control and Account Management”, which
discusses the mysql system schema and the
access control system extensively.

If the grant tables hold privilege rows that contain
mixed-case database or table names and the
lower_case_table_names system
variable is set to a nonzero value,
REVOKE cannot be used to revoke
these privileges. It is necessary in such cases to manipulate
the grant tables directly.
(GRANT does not create such
rows when
lower_case_table_names is
set, but such rows might have been created prior to setting
that variable. The
lower_case_table_names
setting can only be configured at server startup.)

Privileges can be granted at several levels, depending on the
syntax used for the ON clause. For
REVOKE, the same
ON syntax specifies which privileges to
remove.

For the global, database, table, and routine levels,
GRANT ALL
assigns only the privileges that exist at the level you are
granting. For example, GRANT ALL ON
db_name.* is a
database-level statement, so it does not grant any global-only
privileges such as FILE.
Granting ALL does not assign
the GRANT OPTION or
PROXY privilege.

GRANT SELECT, INSERT ON *.* TO u1;

The globally granted privileges apply to all databases,
tables, and columns, even though not granted at any of those
lower levels.

As of MySQL 8.0.16, it is possible to explicitly deny a
privilege granted at the global level by revoking it for
particular databases, if the
partial_revokes system
variable is enabled:

GRANT SELECT, INSERT, UPDATE ON *.* TO u1;
REVOKE INSERT, UPDATE ON db1.* FROM u1;

The result of the preceding statements is that
SELECT applies globally to all
tables, whereas INSERT and
UPDATE apply globally except to
tables in db1. Account access to
db1 is read only.

Details of the privilege-checking procedure are presented in
Section 6.2.7, “Access Control, Stage 2: Request Verification”.

MySQL does not automatically revoke any privileges
when you drop a database or table. However, if
you drop a routine, any routine-level privileges granted for
that routine are revoked.

Global Privileges

GRANT ALL ON *.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON *.* TO 'someuser'@'somehost';

Dynamic privileges are all global and can only be granted
globally.

Other privileges can be granted globally or at more specific
levels.

The effect of GRANT OPTION
granted at the global level differs for static and dynamic
privileges:

GRANT ALL at the global level grants all
static global privileges and all currently registered dynamic
privileges. A dynamic privilege registered subsequent to
execution of the GRANT statement is not
granted retroactively to any account.

Database Privileges

Database privileges apply to all objects in a given database.
To assign database-level privileges, use ON
db_name.* syntax:

GRANT ALL ON mydb.* TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.* TO 'someuser'@'somehost';

If you use ON * syntax (rather than
ON *.*), privileges are assigned at the
database level for the default database. An error occurs if
there is no default database.

The CREATE,
DROP,
EVENT,
GRANT OPTION,
LOCK TABLES, and
REFERENCES privileges can be
specified at the database level. Table or routine privileges
also can be specified at the database level, in which case
they apply to all tables or routines in the database.

MySQL stores database privileges in the
mysql.db system table.

Table Privileges

Table privileges apply to all columns in a given table. To
assign table-level privileges, use ON
db_name.tbl_name syntax:

GRANT ALL ON mydb.mytbl TO 'someuser'@'somehost';
GRANT SELECT, INSERT ON mydb.mytbl TO 'someuser'@'somehost';

If you specify tbl_name rather than
db_name.tbl_name, the statement
applies to tbl_name in the default
database. An error occurs if there is no default database.

The permissible priv_type values at
the table level are ALTER,
CREATE VIEW,
CREATE,
DELETE,
DROP,
GRANT OPTION,
INDEX,
INSERT,
REFERENCES,
SELECT,
SHOW VIEW,
TRIGGER, and
UPDATE.

Table-level privileges apply to base tables and views. They do
not apply to tables created with CREATE
TEMPORARY TABLE, even if the table names match. For
information about TEMPORARY table
privileges, see Section 13.1.20.2, “CREATE TEMPORARY TABLE Statement”.

MySQL stores table privileges in the
mysql.tables_priv system table.

Column Privileges

GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost';

The permissible priv_type values
for a column (that is, when you use a
column_list clause) are
INSERT,
REFERENCES,
SELECT, and
UPDATE.

MySQL stores column privileges in the
mysql.columns_priv system table.

Stored Routine Privileges

The ALTER ROUTINE,
CREATE ROUTINE,
EXECUTE, and
GRANT OPTION privileges apply
to stored routines (procedures and functions). They can be
granted at the global and database levels. Except for
CREATE ROUTINE, these
privileges can be granted at the routine level for individual
routines.

GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost';
GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';

The permissible priv_type values at
the routine level are ALTER
ROUTINE, EXECUTE, and
GRANT OPTION.
CREATE ROUTINE is not a
routine-level privilege because you must have the privilege at
the global or database level to create a routine in the first
place.

MySQL stores routine-level privileges in the
mysql.procs_priv system table.

Proxy User Privileges

GRANT PROXY ON 'localuser'@'localhost' TO 'externaluser'@'somehost';

When PROXY is granted, it must
be the only privilege named in the
GRANT statement, and the only
permitted WITH option is WITH
GRANT OPTION.

MySQL stores proxy privileges in the
mysql.proxies_priv system table.

Granting Roles

GRANT syntax without an
ON clause grants roles rather than
individual privileges. A role is a named collection of
privileges; see Section 6.2.10, “Using Roles”. For example:

GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';

Granting a role does not automatically cause the role to be
active. For information about role activation and
inactivation, see Activating Roles.

These privileges are required to grant roles:

It is possible to create circular references with
GRANT. For example:

CREATE USER 'u1', 'u2';
CREATE ROLE 'r1', 'r2';
GRANT 'u1' TO 'u1'; -- simple loop: u1 => u1
GRANT 'r1' TO 'r1'; -- simple loop: r1 => r1
GRANT 'r2' TO 'u2';
GRANT 'u2' TO 'r2'; -- mixed user/role loop: u2 => r2 => u2

The AS Clause and Privilege Restrictions

• AS is supported only for granting
  global privileges (ON *.*).

• AS is not supported for
  PROXY grants.

CREATE USER u1;
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO u1;
REVOKE INSERT, UPDATE ON schema1.* FROM u1;
REVOKE SELECT ON schema2.* FROM u1;

Also create a role r1 that lifts some of
the privilege restrictions and grant the role to
u1:

CREATE ROLE r1;
GRANT INSERT ON schema1.* TO r1;
GRANT SELECT ON schema2.* TO r1;
GRANT r1 TO u1;

• The GRANT statement here
  has no AS clause, so the privileges
  granted are exactly those specified:

  mysql> CREATE USER u2;
  mysql> GRANT SELECT, INSERT, UPDATE ON *.* TO u2;
  mysql> SHOW GRANTS FOR u2;
  +-------------------------------------------------+
  | Grants for u2@% |
  +-------------------------------------------------+
  | GRANT SELECT, INSERT, UPDATE ON *.* TO `u2`@`%` |
  +-------------------------------------------------+

• The GRANT statement here
  has an AS clause, so the privileges
  granted are those specified but with the restrictions from
  u1 applied:

  mysql> CREATE USER u3;
  mysql> GRANT SELECT, INSERT, UPDATE ON *.* TO u3 AS u1;
  mysql> SHOW GRANTS FOR u3;
  +----------------------------------------------------+
  | Grants for u3@% |
  +----------------------------------------------------+
  | GRANT SELECT, INSERT, UPDATE ON *.* TO `u3`@`%` |
  | REVOKE INSERT, UPDATE ON `schema1`.* FROM `u3`@`%` |
  | REVOKE SELECT ON `schema2`.* FROM `u3`@`%` |
  +----------------------------------------------------+

  As mentioned previously, the AS clause
  can only add privilege restrictions; it cannot escalate
  privileges. Thus, although u1 has the
  DELETE privilege, that is
  not included in the privileges granted because the
  statement does not specify granting
  DELETE.

• The AS clause for the
  GRANT statement here makes
  the role r1 active for
  u1. That role lifts some of the
  restrictions on u1. Consequently, the
  privileges granted have some restrictions, but not so many
  as for the previous GRANT
  statement:

  mysql> CREATE USER u4;
  mysql> GRANT SELECT, INSERT, UPDATE ON *.* TO u4 AS u1 WITH ROLE r1;
  mysql> SHOW GRANTS FOR u4;
  +-------------------------------------------------+
  | Grants for u4@% |
  +-------------------------------------------------+
  | GRANT SELECT, INSERT, UPDATE ON *.* TO `u4`@`%` |
  | REVOKE UPDATE ON `schema1`.* FROM `u4`@`%` |
  +-------------------------------------------------+

Other Account Characteristics

To grant the GRANT OPTION
privilege to an account without otherwise changing its
privileges, do this:

GRANT USAGE ON *.* TO 'someuser'@'somehost' WITH GRANT OPTION;

For additional information about security risks associated
with particular privileges, see
Section 6.2.2, “Privileges Provided by MySQL”.

MySQL and Standard SQL Versions of GRANT

The biggest differences between the MySQL and standard SQL
versions of GRANT are:

• Standard SQL does not have global or database-level
  privileges, nor does it support all the privilege types
  that MySQL supports.

• MySQL does not support the standard SQL
  UNDER privilege.

• In MySQL, it is possible to have the
  INSERT privilege for only
  some of the columns in a table. In this case, you can
  still execute INSERT
  statements on the table, provided that you insert values
  only for those columns for which you have the
  INSERT privilege. The
  omitted columns are set to their implicit default values
  if strict SQL mode is not enabled. In strict mode, the
  statement is rejected if any of the omitted columns have
  no default value. (Standard SQL requires you to have the
  INSERT privilege on all
  columns.) For information about strict SQL mode and
  implicit default values, see Section 5.1.11, “Server SQL Modes”,
  and Section 11.6, “Data Type Default Values”.

Using “sudo su” Command

Using the “adduser” Command to Add a Root User

It requires the “password” through which you access the root privileges.

The “visudo” command assists in modifying the sudoers file in the Nano Editor. To access the root privilege of “roger”, execute the below script:

After executing the command, it navigates to the “/etc/sudoers.tmp” file to perform modifications via Nano Editor:

$ roger ALL= (ALL:ALL) ALL

After modifications, save the file via “Ctrl+S” and exit the Nano Editor through the “Ctrl+X”.

Using “sudo -i”

Using “sudo -s”

Using the su Command

su

Other helpful code samples for allowing a user to run root commands on Linux CentOS

su root

Using the “sudo su –” command

Using the Sudo Command

visudo

Verify User Has Privileges

Adding User to the Correct Group

usermod -aG wheel username

Creating a New User with Root Privileges

useradd -ou 0 -g 0 username