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/yearning
cd /opt/yearning
wget https://github.com/cookieY/Yearning/releases/download/v3.1.1/Yearning-v3.1.1-linux-amd64.zip

1.3、修改配置

cd /opt/yearning
unzip Yearning-v3.1.1-linux-amd64.zip
cd Yearning
vim conf.toml

[Mysql]
Db = "Yearning"
Host = "your db ip"
Port = "3306"
Password = "Yearning@123"
User = "yearning"

[General]
SecretKey = "dbcjqheupqjsuwsm"
Hours = 4


[Oidc]
Enable = false
ClientId = "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 run

2、使用指定端口启动
./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/v1
kind: Deployment
metadata:
  labels:
    app: yearning
  name: yearning
  namespace: tools
spec:
  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: v1
kind: Service
metadata:
  name: yearning-svc
  namespace: tools
spec:
  ports:
  - name: http
    port: 8000
    protocol: TCP
    targetPort: 8000
  selector:
    app: yearning
  sessionAffinity: None
  type: ClusterIP
  
  
---

apiVersion: extensions/v1beta1
kind: Ingress
metadata:
  name: yearning-ing
  namespace: tools
spec:
  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、审核规则