如何给mariadb数据创建用户并且授权

说明：MariaDB的默认用户是存放在mysql库的user表中，当然也可以通过对mysql.user表的增删改查来新增用户，删除用户，修改密码和权限

创建用户

(jlive)[crashcourse]>CREATE USER a

Query OK, 0 rows affected (0.00 sec)

MariaDB的用户是由'用户名'@'主机'组成， 如果是简写的用户则主机默认为%,即可来源于所以的主机

或者

(jlive)[mysql]>INSERT INTO user(User, Host, Password) VALUES('foo', '%', Password('hello'))

Query OK, 1 row affected, 4 warnings (0.00 sec)

(jlive)[mysql]>SELECT User, Host, Password FROM user WHERE User = 'foo'

+------+------+-------------------------------------------+

| User | Host | Password |

+------+------+-------------------------------------------+

| foo | %| *6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119 |

+------+------+-------------------------------------------+

1 row in set (0.00 sec)

创建备份用户

(jlive)[crashcourse]>CREATE USER 'backupuser'@'localhost' IDENTIFIED BY 'password'

Query OK, 0 rows affected (0.00 sec)

(jlive)[crashcourse]>GRANT SELECT,SHOW VIEW,LOCK TABLES,RELOAD,REPLICATION CLIENT ON *.* TO 'backupuser'@'localhost'

Query OK, 0 rows affected (0.15 sec)

(jlive)[crashcourse]>FLUSH PRIVILEGES

Query OK, 0 rows affected (0.00 sec)

重命名用户

(jlive)[crashcourse]>RENAME USER a TO A

Query OK, 0 rows affected (0.00 sec)

或者

(jlive)[mysql]>UPDATE user SET User = 'FOO' WHERE User = 'foo'

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

(jlive)[mysql]>SELECT User, Host, Password FROM user WHERE User = 'FOO'

+------+------+-------------------------------------------+

| User | Host | Password |

+------+------+-------------------------------------------+

| FOO | %| *6B4F89A54E2D27ECD7E8DA05B4AB8FD9D1D8B119 |

+------+------+-------------------------------------------+

1 row in set (0.00 sec)

查看用户权限

(jlive)[crashcourse]>SHOW GRANTS FOR A

+-------------------------------+

| Grants for A@%|

+-------------------------------+

| GRANT USAGE ON *.* TO 'A'@'%' |

+-------------------------------+

1 row in set (0.00 sec)

单一的SHOW GRANTS不接用户时可以查看登录用户本身的权限

修改用户权限

(jlive)[crashcourse]>GRANT SELECT ON crashcourse.* TO A

Query OK, 0 rows affected (0.00 sec)

特殊用户可以赋予最大权限，还可顺便修改密码

GRANT ALL PRIVILEGES ON *.* TO foo IDENTIFIED BY 'password' WITH GRANT OPTION；

(jlive)[crashcourse]>SHOW GRANTS FOR A

+--------------------------------------------+

| Grants for A@% |

+--------------------------------------------+

| GRANT USAGE ON *.* TO 'A'@'%' |

| GRANT SELECT ON `crashcourse`.* TO 'A'@'%' |

+--------------------------------------------+

2 rows in set (0.01 sec)

解除用户权限

(jlive)[crashcourse]>REVOKE SELECT ON crashcourse.* FROM A

Query OK, 0 rows affected (0.00 sec)

为用户设置密码

(jlive)[crashcourse]>SET PASSWORD FOR A = Password('hello')

Query OK, 0 rows affected (0.00 sec)

SET PASSWORD = Password('hello')#不接用户时则是修改登录用户的密码

或者

(jlive)[mysql]>UPDATE user SET Password = Password('test') WHERE User = 'FOO'

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

删除用户

(jlive)[crashcourse]>DROP USER A

Query OK, 0 rows affected (0.00 sec)

或者

(jlive)[mysql]>DELETE FROM user WHERE User = 'FOO'

Query OK, 1 row affected (0.00 sec)

因为权限问题。导致看不到日志。

1、首先需要找到设置。

2、其次在设置中找到mariadb并点击。

3、在mariadb找到权限设置，打开权限就可以了。

1.安装mariadb-server

2.运行mysql_install_db

正常运行完成后可以看到如下：

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !

To do so, start the server, then issue the following commands:

'/usr/bin/mysqladmin' -u root password 'new-password'

'/usr/bin/mysqladmin' -u root -h localhost password 'new-password'

Alternatively you can run:

'/usr/bin/mysql_secure_installation'

which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.

See the MariaDB Knowledgebase at http://mariadb.com/kb or the

MySQL manual for more instructions.

You can start the MariaDB daemon with:

cd '/usr' /usr/bin/mysqld_safe --datadir='/var/lib/mysql'

You can test the MariaDB daemon with mysql-test-run.pl

cd '/usr/mysql-test' perl mysql-test-run.pl

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.

You can find additional information about the MySQL part at:

http://dev.mysql.com

Consider joining MariaDB's strong and vibrant community:

https://mariadb.org/get-involved/

3. 给mysql用户配置权限

4. 启动mariadb

5. 运行mysql_secure_installation

6. 更改字符集

7. 最后查看字符集

完成。

---