The super administrator name 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 permissions and can not only change his own password, but also change the passwords of other users.
The operating environment of this tutorial: windows7 system, java8 version, DELL G3 computer.
The super administrator name of mysql database is "root".
When MySQL is installed, a user named root will be created by default. This user has super privileges and 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.
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 directly Change the root user's password 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.
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.
Use the SET statement to modify the password of the root user
The SET PASSWORD statement can be used to reset the login of other users Password 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");
Using 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.
If an ordinary user changes the password, you can omit the FOR clause to change your 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".
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 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.
Requirement analysis:
The root password appears in many places, such as sharing Technical documents, emails, screenshots.
The default administrator account name for MySQL installation is root, which is well known. In order to enhance security, you need to change a user name, such as superuser, or have Company-specific. 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';
删除默认的root用户.
drop user root@'127.0.0.1'; drop user root@'localhost'; drop user root@'::1';
用户账号：
格式为 user_name'@'host_name。
这里的user_name是用户名，host_name为主机名，即用户连接 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';
2、用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()
【相关推荐：mysql视频教程】
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!