时间:2020-10-19来源:www.pcxitongcheng.com作者:电脑系统城
SELECT COUNT(*)
FROM information_schema.tables t1
LEFT OUTER JOIN information_schema.table_constraints t2
ON t1.table_schema = t2.table_schema
AND t1.table_name = t2.table_name
AND t2.constraint_name IN ( 'PRIMARY' )
WHERE t2.table_name IS NULL
AND t1.table_schema NOT IN ( 'information_schema', 'myawr', 'mysql',
'performance_schema',
'slowlog', 'sys', 'test' )
AND t1.table_type = 'BASE TABLE'
mysql> GRANT SELECT ON information_schema.TABLES TO test@'%';
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `moitor_without_primarykey`()
BEGIN
SELECT COUNT(*)
FROM information_schema.tables t1
LEFT OUTER JOIN information_schema.table_constraints t2
ON t1.table_schema = t2.table_schema
AND t1.table_name = t2.table_name
AND t2.constraint_name IN ( 'PRIMARY' )
WHERE t2.table_name IS NULL
AND t1.table_schema NOT IN ( 'information_schema', 'myawr', 'mysql',
'performance_schema',
'slowlog', 'sys', 'test' )
AND t1.table_type = 'BASE TABLE';
END //
DELIMITER ;
mysql> GRANT EXECUTE ON PROCEDURE moitor_without_primarykey TO 'test'@'%';
Query OK, 0 rows affected (0.02 sec)
mysql> select current_user();
+----------------+
| current_user() |
+----------------+
| test@% |
+----------------+
1 row in set (0.00 sec)
mysql> call moitor_without_primarykey;
+----------+
| COUNT(*) |
+----------+
| 6 |
+----------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> show grants for test@'%';
+-------------------------------------------------------------------------------+
| Grants for test@% |
+-------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `test`@`%` |
| GRANT EXECUTE ON PROCEDURE `zabbix`.`moitor_without_primarykey` TO `test`@`%` |
+-------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
2023-03-19
Centos 7.9安装MySQL8.0.32的详细教程2023-03-19
一步步教你利用Mysql存储过程造百万级数据2023-03-19
利用Mysql定时+存储过程创建临时表统计数据的过程问题描述:MYSQL version 5.6.8command 表结构 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 CREATE TABLE command ( ID INT NOT NULL, NAME VARCHAR(16), D...
2023-03-19
一、Galera Cluster 二、基础环境搭建 三、加入配置参数启动集群 四、 测试 五、ProxySql...
2023-03-17