What is the name of the super administrator of mysql database?

The name of the super administrator of the MySQL database is "root". When MySQL is installed, a user named root will be created by default. This user has super permissions and can control the entire MySQL server. The root user has very high With permission, you can not only change your own password, but also the passwords of other users.

The operating system of this tutorial: Windows 10 system, mysql version 8.0, Dell G3 computer.

The super administrator name of the mysql database is "root"

When MySQL is installed, a user named root will be created by default, which has super permissions. Can control the entire MySQL server.

In the daily management and operation of MySQL, in order to prevent someone from maliciously using the root user to control the database, we usually create some users with appropriate permissions and use the root user as little or as little as possible to log in to the system. to ensure secure access to data.

Generally, the root super administrator has much greater permissions than ordinary users, so some operations require root permissions to run.

The root user has very high permissions and can not only change his own password, but also change the passwords of other users.

MySQL changes the root password

In MySQL, the root user has very high permissions, so the security of the root user password must be ensured. There are many ways to change the root user password. This section will introduce several commonly used methods to change the root user password.

Modify the user table of the MySQL database

Because all account information is stored in the user table, you can change the root user's password directly by modifying the user table.

After the root user logs in to the MySQL server, you can use the UPDATE statement to modify the authentication_string field of the user table of the MySQL database to modify the user's password.

1. The syntax format for using the UPDATA statement to modify the root user password is as follows:

UPDATE mysql.user set authentication_string = PASSWORD ("rootpwd) WHERE User = "root" and Host="localhost";

The new password must be encrypted using the PASSWORD() function. After executing the UPDATE statement, you need to execute the FLUSH PRIVILEGES statement to reload user permissions.

2. Use the SET statement to modify the password of the root user.

The SET PASSWORD statement can be used to reset the login password of other users or the password of the account you use. The syntax structure of using the SET statement to change the password is as follows:

SET PASSWORD = PASSWORD ("rootpwd");

MySQL root changes the password of an ordinary user

1. Use the SET statement to change the password of an ordinary user

In MySQL, only the root user can change the password by updating the MySQL database. After logging in to the MySQL server as the root user, you can use the SET statement to modify the ordinary user password. The syntax format is as follows:

SET PASSWORD FOR 'username'@'hostname' = PASSWORD ('newpwd');

Among them, the username parameter is the user name of an ordinary user, the hostname parameter is the host name of an ordinary user, and newpwd is the new password to be changed.

Note: The new password must be encrypted using the PASSWORD() function. If it is not encrypted using PASSWORD(), it will be executed successfully, but the user will not be able to log in.

2. If an ordinary user changes the password, the FOR clause can be omitted to change his own password. The syntax format is as follows:

SET PASSWORD = PASSWORD('newpwd');

Example 1

After the root user logs in to the MySQL server, use the SET statement to change the password of the testuser user to "newpwd". The SQL statement and running results are as follows:

mysql> SET PASSWORD FOR 'testuser'@'localhost' = PASSWORD("newpwd"); 
Query OK, 0 rows affected, 1 warning (0.01 sec)

It can be seen from the running results that the SET statement was successfully executed and the password of the testuser user was successfully set to "newpwd".

3. Use the UPDATE statement to modify the password of an ordinary user

After logging in to the MySQL server as the root user, you can use the UPDATE statement to modify the authentication_string field of the user table of the MySQL database to modify the password of an ordinary user. . The syntax of the UPDATA statement is as follows:

UPDATE MySQL.user SET authentication_string = PASSWORD("newpwd") WHERE User = "username" AND Host = "hostname";

Among them, the username parameter is the user name of an ordinary user, the hostname parameter is the host name of an ordinary user, and newpwd is the new password to be changed.

Note that after executing the UPDATE statement, you need to execute the FLUSH PRIVILEGES statement to reload user permissions.

Example 3

Use the root user to log in to the MySQL server, and then use the UPDATE statement to change the testuser user's password to "newpwd2". The SQL statement and running results are as follows:

mysql> UPDATE MySQL.user SET authentication_string = PASSWORD ("newpwd2") 
-> WHERE User = "testuser" AND Host = "localhost";
Query OK, 1 row affected, 1 warning (0.07 sec) 
Rows matched: 1 Changed: 1 Warnings: 1 
mysql> FLUSH PRIVILEGES; 
Query OK, 0 rows affected (0.03 sec)

It can be seen from the running results that the password was changed successfully. The password of testuser was changed to newpwd2. After reloading the permissions using FLUSH PRIVILEGES, you can log in to the testuser user with the new password.

Delete the default root user of MySQL

Requirement analysis:

The root password appears in many places, such as shared technical documents, emails, Screenshot.

The administrator account name root installed by MySQL by default is well known. In order to enhance security, you need to change a user name, such as to superuser, or with company characteristics. For example, xxx_admin.

Coping strategy:

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.

如果在创建的过程中，只给出了用户名，而没指定主机名，那么主机名默认为“%”，表示一组主机，即对所有主机开放权限。

注意问题:

1、视图

曾经用root帐号为DEFINER的视图,如果将root删除,将提示该视图无法使用,没有权限.所以要注意提前查看是否存在视图,存在的话,需要修改该视图的DEFINER属性.

修改视图,是瞬间就能完成的操作,除非该视图被其他sql语句占用,处于锁定的状态.

查看视图

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

修改视图(非root的暂不修改)

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

2、存储过程/函数

情况与视图类似

查看存储过程/视图

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

或者

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

修改存储例程,可直接修改mysql.proc

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

如果修改所有库

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

3、用root用户连接MySQL的脚本

此类问题比较好解决,可单独为脚本创建帐号用来执行脚本中指定的操作,该用户名可用script_,或者脚本名命名.权限够用就行,不要分配过多的权限.

4、方法:一个增加用户的脚本.(配合批量执行)

#!/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()

The above is the detailed content of What is the name of the super administrator of mysql database?. For more information, please follow other related articles on the PHP Chinese website!