就像在主题中一样，我可以以root用户身份在Maria DB中创建和删除表，但不能以普通用户身份删除表。仅在连接Tabel引擎时才会发生。
作为根：
MariaDB [(none)]> GRANT ALL PRIVILEGES ON test_database.* To 'user'@'localhost' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW GRANTS FOR user@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD '*EE22D94139EAEE5486C30FBC352B12340EEF82F5' |
| GRANT ALL PRIVILEGES ON `test_database`.* TO 'user'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
以普通用户身份从root帐户登录：
# mysql -u user -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20120
Server version: 10.1.9-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [test_database]> CREATE TABLE example (id INT, data VARCHAR(100));
Query OK, 0 rows affected (0.61 sec)
MariaDB [test_database]> DROP TABLE example;
Query OK, 0 rows affected (0.03 sec)
MariaDB [test_database]> CREATE TABLE odbc_test ENGINE=CONNECT TABLE_TYPE=ODBC tabname='sample_table' CONNECTION='DSN=mssql_test;UID=test_user;PWD=password';
Query OK, 0 rows affected (0.03 sec)
MariaDB [test_database]> DROP TABLE odbc_test;
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)
MariaDB [test_database]> SHOW GRANTS;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user@localhost |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD '*EE22D94139EAEE5486C30FBC352B12340EEF82F5' |
| GRANT ALL PRIVILEGES ON `test_database`.* TO 'user'@'localhost' |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [test_database]> select current_user;
+-----------------+
| current_user |
+-----------------+
| user@localhost |
+-----------------+
1 row in set (0.04 sec)
用户必须使用engine = connect删除表的哪种特权？
更新1。使用以下链接检查csv文件：
https://mariadb.com/kb/en/mariadb/connect-csv-and-fmt-table-types/
它以root身份工作，但是作为具有所有特权的用户，我什至无法创建表。
MariaDB [test_database]> create table people (name char(12) not null, birth date not null date_format='DD/MM/YY', children smallint(2) not null) engine=CONNECT table_type=CSV file_name='test.csv' header=1 sep_char=';' quoted=1;
ERROR 1045 (28000): Access denied for user 'user'@'%' (using password: YES)
问题解决了
GRANT FILE ON *.* TO 'user'@'%';
最佳答案
那解决了我的问题:-)
GRANT FILE ON *.* TO 'user'@'%';