1.MariaDB Database creation user
1.1 command
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
1.2 parameter
username:(jack) Created user name host:(192.168.13.34) Specify the host on which the user can log in ,
If available for local users localhost, If you want the user to be able to log in from any remote host , You can use wildcards % password:(jack)
It is recommended that the user name and password are not the same , The above is just for demonstration Login password of the user , The password can be empty , If it is blank, the user can log in to the server without a password .
1.3 Examples
# Create user ‘Ann’ MariaDB [(none)]> create user ann@localhost identified by 'ann' #
Create user ‘Steven’ MariaDB [(none)]> create user ann@localhost identified by 'ann' #
Create user ‘Jack’ MariaDB [mysql]> create user jack@'192.168.13.34' identified by
'jack';
2.MariaDB Database empowers users
2.1 command
GRANT privileges ON databasename.tablename TO 'username'@'host'
2.2 parameter
privileges: User's operation authority , as SELECT,INSERT,UPDATE etc. , If you want to grant permissions to, use the ALL. databasename: Database name
tablename: Table name , Available if you want to grant the user the appropriate operation permissions on all databases and tables * express , as *.*.
2.3 Examples
# to grant authorization Ann have db1 All permissions for the database , Allowed in localhost Sign in MariaDB [(none)]> grant select on db1.* to
ann@localhost; MariaDB [mysql]> flush privileges; #
to grant authorization Jack have db1 All permissions for the database , Allowed in 192.168.13.34 Sign in MariaDB [(none)]> grant all on db1.* to
jack@'192.168.13.34'; MariaDB [mysql]> flush privileges; #
to grant authorization Steven have db1 All permissions for the database , Allow login from any remote host , # Pay attention to your authorization %, When you create a user , It has to be %, To correspond , Otherwise, an error is reported . MariaDB
[(none)]> grant all on *.* to steven@'%'; MariaDB [mysql]> flush privileges;
3.MariaDB The command that the database creates the user and authorizes
3.1 to grant authorization apollo User owned db1 All permissions for the database
MariaDB [mysql]> grant all on *.* to john@'192.168.13.34' identified by
'john'; MariaDB [mysql]> flush privileges;
3.2 Grant access to Internet
MariaDB [mysql]> grant all privileges on *.* to username@'%' identified by
'password';
3.3 Grant permissions and be able to authorize
MariaDB [mysql]>grant all privileges on *.* to username@'hostname' identified
by 'password' with grant option; # The whole order is one sentence , Line breaks here are due to display problems .
Authorization part parameter value :
all privileges,all
select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file
4.MariaDB Database view user
4.1 MariaDB View current login user
# method 1 MariaDB [(none)]> select user(); # method 2 MariaDB [(none)]> select
current_user; # method 3 MariaDB [(none)]> select current_user();
4.2 MariaDB How to display all users in ?
MariaDB [(none)]> select User,Host,Password from mysql.user;
4.3 MariaDB Show all users ( No repetition )
MariaDB [mysql]> select distinct user from mysql.user;
5.MariaDB Database delete user
# 5.1 delete user 'jack' MariaDB [mysql]> delete from user where user='jack'; # 5.2
delete user 'steven' MariaDB [(none)]> delete from mysql.user where user='steven' and
host='%'; # 5.3 delete user 'john' MariaDB [(none)]> drop user 'john'@'192.168.13.34';
6.MariaDB Database revoking user rights
6.1 command
REVOKE privileges ON databasename.tablename FROM 'username'@'host';
6.2 parameter
privileges: User's operation authority , as SELECT,INSERT,UPDATE etc. , If you want to grant permissions to, use the ALL. databasename: Database name
tablename: Table name , Available if you want to grant the user the appropriate operation permissions on all databases and tables * express , as *.*.
6.3 Examples
# If you are authorizing users like this ： grant select on db1.user to jack@'%'; # Do the following sql sentence revoke
select on *.* from jack@'%'; # The user cannot cancel the db1 In the database user Tabular SELECT operation . #
If you are authorizing users like this ： grant select on *.* to jack@'%'; # Do the following sql sentence revoke select on
db1.user from jack@'%'; # The user cannot cancel the db1 In the database user Tabular SELECT operation .
6.4 View authorization information
MariaDB [(none)]> show grants for 'jack'@'192.168.13.34';
6.5 Undo user Jack All permissions
revoke ALL PRIVILEGES ON `db1`.* from 'jack'@'192.168.13.34'
Technology