mysql-配置 - Mysql创建数据库时提示Error 1044

Question

使用非root用户登录时创建数据库会提示Error1044，使用root用户登录则能创建数据库。百度了一下似乎是权限问题，我该如何操作将我所登录的用户能创建数据库？

Mysql版本: 5.7.10 MySQL Community Server
使用系统是: Mac OS X

可用Terminal和Workbench

情况如下：

mysql> create database test1;
ERROR 1044 (42000): Access denied for user 'yisa'@'localhost' to database 'test1'

Answer 1

grant permissions on database objects to users
1. Grant ordinary data users have the right to query, insert, update, and delete all table data in the database.
grant select on testdb.* to common_user@'%'
grant insert on testdb.* to common_user@'%'
grant update on testdb.* to common_user@'%'
grant delete on testdb.* to common_user@'%'
or
grant select, insert, update, delete on testdb.* to common_user@'%'
cc

grant select, insert, update, delete on python.* to user@'%' IDENTIFIED BY '123456'

Allow users with account root and password 123456 to execute all permissions under all IP segments and under all libraries
GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY ' 123456' WITH GRANT OPTION;

2. Grant database developer, create tables, indexes, views, stored procedures, and functions. . . and other permissions.
grant permission to create, modify, and delete MySQL data table structures.
grant create on testdb.* to developer@'192.168.0.%';
grant alter on testdb.* to developer@'192.168.0.%';
grant drop on testdb.* to developer@'192.168.0.%';
grant permission to operate MySQL foreign keys.
grant references on testdb.* to developer@’192.168.0.%’;
grant permission to operate MySQL temporary tables.
grant create temporary tables on testdb.* to developer@’192.168.0.%’;
grant to operate MySQL index permissions.
grant index on testdb.* to developer@’192.168.0.%’;
grant permission to operate MySQL views and view view source code.
grant create view on testdb.* to developer@'192.168.0.%';
grant show view on testdb.* to developer@'192.168.0.%';
grant operate MySQL stored procedures , function permissions.
grant create routine on testdb.* to developer@'192.168.0.%'; — now, can show procedure status
grant alter routine on testdb.* to developer@'192.168.0.%'; — now, you can drop a procedure
grant execute on testdb.* to developer@'192.168.0.%';

3. grant ordinary DBA’s authority to manage a MySQL database.
grant all privileges on testdb to dba@’localhost’
Among them, the keyword “privileges” can be omitted.

4. Grant the senior DBA the authority to manage all databases in MySQL.
grant all on . to dba@’localhost’

5. MySQL grant permissions can be applied at multiple levels.

1. grant works on the entire MySQL server:

2. select on . to dba@localhost; — dba can query tables in all databases in MySQL.

grant all on . to dba@localhost; — dba can manage all databases in MySQL

1. grant works on a single database:

2. select on testdb.* to dba@localhost; — dba can query the tables in testdb.

3. grant works on a single data table:

4. select, insert, update, delete on testdb.orders to dba@localhost;

When authorizing multiple tables to a user, the above statement can be executed multiple times. For example:
grant select(user_id,username) on smp.users to mo_user@'%' identified by '123345′;
grant select on smp.mo_sms to mo_user@'%' identified by '123345′;

1. grant works on columns in the table:

2. select(id, se, rank) on testdb.apache_log to dba@localhost;

3. grant works on stored procedures and functions:

4. execute on procedure testdb.pr_add to ‘dba’@’localhost’

grant execute on function testdb.fn_add to ‘dba’@’localhost’

6. View MySQL user permissions
View the current user (self) permissions:
show grants;
View other MySQL user permissions:
show grants for zhangkh@localhost;

7. Revoke the permissions that have been granted to the MySQL user.
revoke has similar syntax to grant, just replace the keyword "to" with "from":
grant all on . to dba@localhost;
revoke all on . from dba@localhost;

8. Notes on MySQL grant and revoke user permissions

1. grant, revoke user permissions, the user must reconnect to the MySQL database for the permissions to take effect.

2. If you want authorized users to grant these permissions to other users, you need the option “grant option“

3. select on testdb.* to dba@localhost with grant option;

This feature is generally not used. In practice, database permissions are best managed uniformly by the DBA.
MySQL authorization table has 5 tables: user, db, host, tables_priv and columns_priv.
The contents of the authorization table serve the following purposes:
user table
The user table lists the users who can connect to the server and their passwords, and it specifies what global (superuser) permissions they have. Any permissions enabled on the user table are global permissions and apply to all databases. For example, if you enable DELETE permissions, users listed here can delete records from any table, so think carefully before you do this.
db table
db table lists the databases to which users have permission to access. The permissions specified here apply to all tables in a database.
Host table
The host table is used in combination with the db table to control the access rights of a specific host to the database at a better level, which may be better than using db alone. This table is not affected by GRANT and REVOKE statements, so you may find that you are not using it at all.

tables_priv table
tables_priv table specifies table-level permissions. A permission specified here applies to all columns of a table.
columns_priv table
columns_priv table specifies column-level permissions. The permissions specified here apply to specific columns of a table.
Note:
For GRANT USAGE ON, check the manual for the following introduction and examples:
mysql> GRANT USAGE ON . TO 'zhangkh'@'localhost';
an account There is a username zhangkh, but no password. This account is only used to connect from this machine. Permission not granted. With the USAGE permission in the GRANT statement, you can create an account without granting any permissions. It can set all global permissions to 'N'. It is assumed that you will grant specific permissions to this account later.

Answer 2

Log in as root and run the following command

grant all privileges on test1.* to yisa@localhost identified by 'password';

The password in the quotation marks can be replaced by something else, that is, the login password of the user yisa