mysql数据库权限校验
前言
迁移mysql数据库后，校验权限也是个问题，一个库大几十个账号，不可能去一个个校验的，所以就搞了个校验的小脚本，通过对比两个库的所有账号的md5进行比对。
1、代码如下
代码
package main
import (
"context"
"crypto/md5"
"database/sql"
"encoding/hex"
"fmt"
"log"
"os"
"os/signal"
"strings"
"time"
_ "github.com/go-sql-driver/mysql"
)
type DBConfig struct {
User string
Password string
Host string
Port string
Name string
}
type Database struct {
pool *sql.DB
}
func NewDatabase(config DBConfig) (*Database, error) {
dsn := fmt.Sprintf("%s:%s@tcp(%s:%s)/%s?timeout=5s",
config.User, config.Password, config.Host, config.Port, config.Name)
pool, err := sql.Open("mysql", dsn)
if err != nil {
return nil, fmt.Errorf("unable to use data source name: %w", err)
}
pool.SetConnMaxLifetime(0)
pool.SetMaxIdleConns(3)
pool.SetMaxOpenConns(3)
return &Database{pool: pool}, nil
}
func (db *Database) Close() {
db.pool.Close()
}
func (db *Database) Ping(ctx context.Context) error {
ctx, cancel := context.WithTimeout(ctx, 1*time.Second)
defer cancel()
return db.pool.PingContext(ctx)
}
func (db *Database) QueryGetAllUsers(ctx context.Context) []string {
ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
defer cancel()
rows, err := db.pool.QueryContext(ctx, "SELECT CONCAT('''', user, '''', '@', '''', host, '''') AS user_grant FROM mysql.user;")
if err != nil {
fmt.Errorf("unable to execute search query: %w", err)
}
defer rows.Close()
var all_users []string
for rows.Next() {
var userGrant string
if err := rows.Scan(&userGrant); err != nil {
fmt.Errorf("error scanning row: %w", err)
}
userGrantstr := fmt.Sprintf("SHOW GRANTS FOR %s;", userGrant)
all_users = append(all_users, userGrantstr)
}
return all_users
}
func (db *Database) QueryGetAllUsersGrant(ctx context.Context, grantUsersSql []string) map[string][]string {
ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
defer cancel()
grantUsersSqlmap := make(map[string][]string)
addgrantUsersSqlmap := func(key string, value string) {
if _, exists := grantUsersSqlmap[key]; exists {
grantUsersSqlmap[key] = append(grantUsersSqlmap[key], value)
} else {
grantUsersSqlmap[key] = []string{value}
}
}
for _, v := range grantUsersSql {
rows, _ := db.pool.QueryContext(ctx, v)
for rows.Next() {
var userGrant string
if err := rows.Scan(&userGrant); err != nil {
fmt.Errorf("error scanning row: %w", err)
}
addgrantUsersSqlmap(v, userGrant)
}
}
return grantUsersSqlmap
}
func SetupSignalHandler(stop context.CancelFunc) {
appSignal := make(chan os.Signal, 3)
signal.Notify(appSignal, os.Interrupt)
go func() {
<-appSignal
stop()
}()
}
func contains(sql_slice []string, sql string) bool {
for i := 0; i < len(sql_slice); i++ {
if sql == sql_slice[i] {
return true
}
}
return false
}
func calculateMD5(data []string) string {
combined := strings.Join(data, ",")
hash := md5.New()
hash.Write([]byte(combined))
return hex.EncodeToString(hash.Sum(nil))
}
func generateUserGrantMd5(grant_map map[string][]string) map[string]string {
grantUsersSqlmapMd5 := make(map[string]string)
for key, value := range grant_map {
res := calculateMD5(value)
grantUsersSqlmapMd5[key] = res
}
return grantUsersSqlmapMd5
}
func DbM(ctx context.Context, dbconf DBConfig) ([]string, map[string][]string, map[string]string) {
new_db_config := dbconf
db, err := NewDatabase(new_db_config)
if err != nil {
log.Fatal(err)
}
defer db.Close()
if err := db.Ping(ctx); err != nil {
log.Fatal(err)
}
grant_sql_slice := db.QueryGetAllUsers(ctx)
grant_sql_map := db.QueryGetAllUsersGrant(ctx, grant_sql_slice)
guam := generateUserGrantMd5(grant_sql_map)
return grant_sql_slice, grant_sql_map, guam
}
func checkDataBaseGrant(ctx context.Context) {
// 新db
grant_sql_slice, grant_sql_map, guam := DbM(ctx, DBConfig{
User: "xxxxxx",
Password: "xxxxxx",
Host: "xxxxxx",
Port: "3306",
Name: "xxxxxx",
})
// 老db
grant_sql2_slice, grant_sql2_map, guam2 := DbM(ctx, DBConfig{
User: "xxxxxx",
Password: "xxxxxx",
Host: "xxxxxx",
Port: "3306",
Name: "xxxxxx",
})
// 老db里面有，新db里面没有的表
for i := 0; i < len(grant_sql2_slice); i++ {
res := contains(grant_sql_slice, grant_sql2_slice[i])
if res == false {
fmt.Printf("在老db：%s 不在新db\n", grant_sql2_slice[i])
}
}
// 判断对应表的权限的md5是否一样
for key, _ := range guam {
if guam[key] != guam2[key] {
fmt.Printf("%s权限不一致\n", key)
fmt.Printf("新db权限:\n%s\n", grant_sql_map[key])
fmt.Printf("老db权限:\n%s\n", grant_sql2_map[key])
fmt.Println("====================================================================")
}
}
}
func main() {
ctx, stop := context.WithCancel(context.Background())
defer stop()
SetupSignalHandler(stop)
checkDataBaseGrant(ctx)
}
2、使用说明
有两个这个配置，一个新库，一个老库，别的无需修改，然后可以直接go run 执行，go的版本是1.21。
本文阅读量 次