How to delete root user in mysql

Mysql method to delete the root user: 1. Use the "CREATE USER" statement to create a user with the same permissions as the root user; 2. Use the "drop user" statement to delete the root user, with the syntax "DROP USER root user account ".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

Delete MySQL’s default root user

Requirements analysis:

• The root password is in multiple It has appeared in places, such as shared technical documents, emails, and screenshots.

• The administrator account name root installed by MySQL by default is well known. In order to enhance security, a user name needs to be changed, for example Change to superuser, or one with company characteristics. For example, xxx_admin.

Countermeasures:

• First create a user with the same permissions as the root user .

GRANT ALL PRIVILEGES ON *.* TO 'x_admin'@'127.0.0.1' IDENTIFIED BY 'xxxx';

• Delete the default root user.

drop user root@'127.0.0.1';
drop user root@'localhost';
drop user root@'::1';

User account:

The format is user_name'@'host_name.

The user_name here is the user name, and host_name is the host name, which is the name of the host used by the user to connect to MySQL.

If during the creation process, only the user name is given but no host name is specified, the host name defaults to "%", which means a group of hosts, that is, permissions are open to all hosts.

Attention:

1. View

The view that once used the root account as DEFINER will be prompted if root is deleted. The view cannot be used and has no permissions. Therefore, pay attention to check in advance whether the view exists. If it exists, you need to modify the DEFINER attribute of the view.

Modifying the view can be completed in an instant, unless the view is used by other sql The statement is occupied and is in a locked state.

View view

select TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION, DEFINER from information_schema.VIEWS;

Modify the view (non-root ones will not be modified temporarily)

ALTER DEFINER=`x_admin`@`127.0.0.1` SQL SECURITY DEFINER VIEW v_name AS...

2. Stored procedure/function

The situation is similar to that of views

View stored procedures/views

select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,DEFINER from information_schema.ROUTINES;

or

select db,name,type,definer from mysql.proc;

Modify stored routines, you can directly modify mysql.proc

update mysql.proc set definer='x_admin@127.0.0.1'where db='db_name';

If you modify all libraries

update mysql.proc set definer='x_admin@127.0.0.1';

2. Use the root user to connect to the MySQL script

This kind of problem is easier to solve. You can create a separate account for the script to perform the operations specified in the script. , the user name can be named script_, or the script name. The permissions are enough, do not assign too many permissions.

4. Method: a script to add users. (Cooperate with batch execution)

#!/usr/bin/python
#-*- coding: UTF-8 -*-
# ########################################################################
# This program
# Version: 2.0.0 (2012-10-10)
# Authors: lianjie.ning@qunar.com
# History:
# ########################################################################

import os
import socket
import subprocess
import sys
import traceback
from ConfigParser import ConfigParser

class Finger(object):
   'finger.py'

   def __init__ (self):
       print '---- %s, %s' % (socket.gethostname(), self.__doc__)

   def load_config (self, file="finger.ini"):
       if not os.path.exists(file):
           print file,"is not exists, but is created, please fix it"
           temp_ini = '''[conn_db]
login_pwd =
exec_sql =
'''
           open(file, 'w').write(temp_ini)
           os.chmod(file, 0600)
           sys.exit()
       config = ConfigParser()
       config.read(file)
       if config.has_section('conn_db') is True:
           if config.has_option('conn_db', 'login_pwd') is True:
               login_pwd = config.get('conn_db', 'login_pwd')
           if config.has_option('conn_db', 'exec_sql') is True:
               exec_sql = config.get('conn_db', 'exec_sql')
           return (login_pwd, exec_sql)

   def grant_user(self, login_pwd, exec_sql):
       if os.path.exists('/usr/local/bin/mysql'):
           mysql = '/usr/local/bin/mysql'
       elif os.path.exists('/usr/bin/mysql'):
           mysql = '/usr/bin/mysql'
       elif os.path.exists('/bin/mysql'):
           mysql = '/bin/mysql'
       else:
           print "command not fount of mysql"
           sys.exit()

       user = 'xxxx'
       conn_port = [3306,3307,3308,3309,3310]
       for i in conn_port:
           ss = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
           address = ('127.0.0.1', int(i))
           status = ss.connect_ex(address)
           ss.settimeout(3)
           ss.close()
           if status == 0:
               conn_mysql  = '%s -u%s -p%s -h127.0.0.1 -P%d -N -s -e"%s"' % (mysql, user, login_pwd, i, exec_sql)
               p = subprocess.call(conn_mysql, shell=True, stdout=open("/dev/null"))
               if p == 0:
                   print "---- checking port: %s is NORMAL" % i
               else:
                   print "---- checking prot: %s is ERROR" % i

if __name__ == '__main__':
   try:
       process = Finger()
       (login_pwd, exec_sql) = process.load_config()
       process.grant_user(login_pwd, exec_sql)
   except Exception, e:
       print str(e)
       traceback.print_exc()
       sys.exit()

【Related recommendations: mysql video tutorial】

The above is the detailed content of How to delete root user in mysql. For more information, please follow other related articles on the PHP Chinese website!