yearning搭建及使用

数据库审计管理，是数据安全规范中不可或缺的一环，通过审计管理我们能够把控、追溯sql执行情况。yearning作为一款开源的数据库审计软件，是我们开发运维工作中经常打交道的一个“伙伴”。

yearning提供的核心功能就是sql查询和审计。

我们可以通过yearning来创建用户，设置权限，规定哪些用户可以查询哪个库，哪些用户可以修改哪个库，查询、修改操作需要经过那些人的审批后才能执行，执行完成的SQL修改语句可以进行回退，可以监控追溯到哪些人执行了哪些SQL。

一、搭建

1、二进制搭建

1.1、准备数据库

1. navicat工具创建数据库

2. mysql命令行创建

mysql> create database yearning;

3. 创建用户并授权

mysql> use mysql;mysql> create user 'yearning'@'%' identified by 'Yearning@123';mysql> grant all privielges on yearning.* to 'yearning'@'%';mysql> flush privileges;

1.2、下载

• 下载地址：https://github.com/cookieY/Yearning/releases

mkdir /opt/yearningcd /opt/yearningwget https://github.com/cookieY/Yearning/releases/download/v3.1.1/Yearning-v3.1.1-linux-amd64.zip

1.3、修改配置

cd /opt/yearningunzip Yearning-v3.1.1-linux-amd64.zipcd Yearningvim conf.toml[Mysql]Db = "Yearning"Host = "your db ip"Port = "3306"Password = "Yearning@123"User = "yearning"[General]SecretKey = "dbcjqheupqjsuwsm"Hours = 4[Oidc]Enable = falseClientId = "yearning"ClientSecret = "fefehelj23jlj22f3jfjdfd"Scope = "openid profile"AuthUrl = "https://keycloak.xxx.ca/auth/realms/master/protocol/openid-connect/auth"TokenUrl = "https://keycloak.xxx.ca/auth/realms/master/protocol/openid-connect/token"UserUrl = "https://keycloak.xxx.ca/auth/realms/master/protocol/openid-connect/userinfo"RedirectUrL = "http://127.0.0.1:8000/oidc/_token-login"UserNameKey = "preferred_username"RealNameKey = "name"EmailKey = "email"SessionKey = "session_state"

1.4、安装

$ ./Yearning install

1.5、启动

1、使用默认8000端口启动$ ./Yearning run2、使用指定端口启动./Yearning run --push “you ip:you port” --port “your port”3、放到后台运行$ nohup ./Yearning run --push “you ip:you port” --port “your port” >> /var/log/yearning.log 2>&1 &

1.6、web访问

• 如无法访问，请查看防火墙是否放行8000端口

• 地址：http://对外ip或域名:端口 http://localhost:8000

• 默认账号： admin / Yearning_admin

2、docker安装

docker run -it -d \  --name yearning \  --restart unless-stopped \  -p 8000:8000 \  -e MYSQL_ADDR=your db ip:3306 \  -e MYSQL_USER=yearning \  -e MYSQL_PASSWORD=Yearning@123 \  -e MYSQL_DB=yearning \  zhangsean/yearning:v3.1.1

3、k8s安装

deployment

apiVersion: apps/v1kind: Deploymentmetadata:  labels:    app: yearning  name: yearning  namespace: toolsspec:  replicas: 1  revisionHistoryLimit: 10  selector:    matchLabels:      app: yearning  strategy:    rollingUpdate:      maxSurge: 25%      maxUnavailable: 25%    type: RollingUpdate  template:    metadata:      labels:        app: yearning    spec:      containers:      - args:        - run        - -b        - 对外域名或ip:对外端口        env:        - name: MYSQL_ADDR          value: yearning数据库地址        - name: MYSQL_USER          value: yearning        - name: MYSQL_PASSWORD          value: Yearning@123        - name: MYSQL_DB          value: yearning        image: zhangsean/yearning:v3.1.1        imagePullPolicy: IfNotPresent        name: yearning        resources:          limits:            cpu: 800m            memory: 2Gi          requests:            cpu: 100m            memory: 256Mi      dnsPolicy: ClusterFirst      nodeSelector:        role: rools      tolerations:      - effect: NoExecute        key: role        operator: Equal        value: tools

svc和ingress

apiVersion: v1kind: Servicemetadata:  name: yearning-svc  namespace: toolsspec:  ports:  - name: http    port: 8000    protocol: TCP    targetPort: 8000  selector:    app: yearning  sessionAffinity: None  type: ClusterIP    ---apiVersion: extensions/v1beta1kind: Ingressmetadata:  name: yearning-ing  namespace: toolsspec:  rules:  - host: 域名或公网IP    http:      paths:      - backend:          serviceName: yearning-svc          servicePort: http        path: /

二、使用

1、基础配置

1.1、设置邮件或钉钉提醒

1.2、创建环境

1.3、创建审核流程

1.4、创建数据源

1.5、创建权限组

1.6、创建用户

• 审计人为否，则无权审核；为是，这为leader权限，可以审核SQL

1.7、授权

• 查询
• DDL
• DML

2、审核规则

来源地址：https://blog.csdn.net/weixin_45112997/article/details/128304557