Linux: sql审计平台

TAGS: Linux

sql审计平台

yearning go开发
archery python开发

yearning

github: https://github.com/cookieY/Yearning

官方: https://next.yearning.io/

部署

docker-compose

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

docker-compose.yml

version: '3'

services:
    yearning:
        image: chaiyd/yearning:latest
        environment:
           MYSQL_USER: yearning
           MYSQL_PASSWORD: ukC2ZkcG_ZTeb
           MYSQL_ADDR: mysql
           MYSQL_DB: yearning
           SECRET_KEY: dbcjqheupqjsuwsm
           IS_DOCKER: is_docker
        ports:
           - 8000:8000
        # 首次使用请先初始化
        command: /bin/bash -c "./Yearning install && ./Yearning run"
        depends_on:
           - mysql
        restart: always

    mysql:
        image: mysql:5.7
        environment:
           MYSQL_ROOT_PASSWORD: ukC2ZkcG_ZTeb
           MYSQL_DATABASE: yearning
           MYSQL_USER: yearning
           MYSQL_PASSWORD: ukC2ZkcG_ZTeb
        command:
           - --character-set-server=utf8mb4
           - --collation-server=utf8mb4_general_ci
        volumes:
           - ./data/mysql:/var/lib/mysql

# 默认账号：admin，默认密码：Yearning_admin

安装包/源码仓库内docker目录下执行 docker-compose up -d 即可

kubernetes

默认账号/密码：admin/Yearning_admin

按docker-compose部署方式转化成k8s配置.

名称空间 infra
secrets或者configmap
deployment
service
ingress

1.创建数据库和用户

CREATE DATABASE `yearning` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
CREATE USER `yearning`@`%` IDENTIFIED BY 'yearning@!123';
GRANT ALL PRIVILEGES ON yearning.* TO 'yearning'@'%';

secret.sh 没用上

#使用文件挂载不需要执行以下SQL
#use yearning;
#INSERT INTO `core_accounts` (`username`,`password`,`department`,`real_name`,`email`,`is_recorder`) VALUES ('admin','pbkdf2_sha256$120000$LwSop65y17f4$AgXGkzmZLF7Lw3K1gToLkOBHqziBC0Wpf3bBIHoWA5Q=','DBA','超级管理员','',0);

#INSERT INTO `core_global_configurations` (`authorization`,`ldap`,`message`,`other`,`stmt`,`audit_role`,`board`) VALUES ('global','{"url":"","user":"","password":"","type":"(\u0026(objectClass=organizationalPerson)   (sAMAccountName=%s))","sc":"","ldaps":false,"map":"","test_user":"","test_password":""}','{"web_hook":"","host":"","port":25,"user":"","password":"","to_user":"","mail":false,"ding":false,"ssl":false,"push_type":false,"key":""}','{"limit":1000,"idc":["Aliyun","AWS"],"query":false,"register":false,"export":false,"ex_query_time":60}',0,'{"DMLAllowLimitSTMT":false,"DMLInsertColumns":false,"DMLMaxInsertRows":10,"DMLWhere":false,"DMLAllowInsertNull":false,"DMLOrder":false,"DMLSelect":false,"DMLInsertMustExplicitly":false,"DDLEnablePrimaryKey":false,"DDLCheckTableComment":false,"DDlCheckColumnComment":false,"DDLCheckColumnNullable":false,"DDLCheckColumnDefault":false,"DDLEnableAcrossDBRename":false,"DDLEnableAutoincrementInit":false,"DDLEnableAutoIncrement":false,"DDLEnableAutoincrementUnsigned":false,"DDLEnableDropTable":false,"DDLEnableDropDatabase":false,"DDLEnableNullIndexName":false,"DDLIndexNameSpec":false,"DDLMaxKeyParts":5,"DDLMaxKey":5,"DDLMaxCharLength":10,"MaxTableNameLen":10,"MaxAffectRows":1000,"MaxDDLAffectRows":0,"SupportCharset":"","SupportCollation":"","CheckIdentifier":false,"MustHaveColumns":"","DDLMultiToCommit":false,"DDLPrimaryKeyMust":false,"DDLAllowColumnType":false,"DDLImplicitTypeConversion":false,"DDLAllowPRINotInt":false,"DDLEnableForeignKey":false,"DDLTablePrefix":"","DDLColumnsMustHaveIndex":"","DDLAllowChangeColumnPosition":false,"DDLCheckFloatDouble":false,"IsOSC":false,"OSCExpr":"","OscSize":0,"AllowCreateView":false,"AllowCrateViewWithSelectStar":false,"AllowCreatePartition":false,"AllowSpecialType":false,"PRIRollBack":false}','');

#INSERT INTO `core_graineds` (`username`,`group`) VALUES ('admin','["admin"]');
#admin密码Yearning_admin

conf='
addr=xxs.us-west-2.rds.amazonaws.com
user=yearning
pass=yearning@!123
data=yearning
sk=JHcrCBRuCWey2dI6
'

#kubectl create cm conf-tmp --from-env-file=<(echo "$conf") --dry-run=client -oyaml
#kubectl -n infra create secret  generic yearning-conf --from-env-file=<(echo "$conf") --dry-run=client -oyaml
kubectl -n infra create secret  generic yearning-conf --from-env-file=<(echo "$conf")
kubectl -n infra get secret yearning-conf -oyaml

2.configmap.yaml

---
apiVersion: v1
data:
  conf.toml: |
    [Mysql]
    Db = "yearning"
    Host = "xxxs.us-west-2.rds.amazonaws.com"
    Port = "3306"
    User = "yearning"
    Password = "yearning@!123"


    [General] #数据库加解密key，只可更改一次
    SecretKey = "JHcrCBRuCWey2dI6"
    Hours = 4
    Lang = "zh_CN" #en_US,zh_CN 中文界面

    [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"
kind: ConfigMap
metadata:
  creationTimestamp: null
  name: yearning-conf
  namespace: infra

3.service.yaml

---
apiVersion: v1
kind: Service
metadata:
  labels:
    app: yearning
  name: yearning
  namespace: infra
spec:
  ports:
    - port: 80
      name: http
      protocol: TCP
      targetPort: 8000
  selector: 
    app: yearning
  type: ClusterIP

4.deployment.yaml

---
apiVersion: apps/v1
kind: Deployment
metadata:
  labels:
    app: yearning
  name: yearning
  namespace: infra
spec: 
  replicas: 2
  selector:
    matchLabels:
      app: yearning
  template:
    metadata:
      labels:
        app: yearning
    spec:
      containers:
      - image: yeelabs/yearning:v3.1.8
        name: yearning
        imagePullPolicy: IfNotPresent
        env:
          - name: TZ
            value: Asia/Shanghai
          #- name: MYSQL_ADDR
          #  valueFrom:
          #    secretKeyRef: # 存储的变量信息
          #      name: yearning-conf
          #      key: addr
          #- name: MYSQL_USER
          #  valueFrom:
          #    secretKeyRef:
          #      name: yearning-conf
          #      key: user
          #- name: MYSQL_PASSWORD
          #  valueFrom:
          #    secretKeyRef:
          #      name: yearning-conf
          #      key: pass
          #- name: MYSQL_DB
          #  valueFrom:
          #    secretKeyRef:
          #      name: yearning-conf
          #      key: data
          #- name: SECRET_KEY
          #  valueFrom:
          #    secretKeyRef:
          #      name: yearning-conf
          #      key: sk
          #- name: IS_DOCKER
          #  value: is_docker
        command: ["sh"]
        args: ["-c", "/opt/Yearning install && /opt/Yearning run"]
        ports:
          - containerPort: 8000
            name: web
            protocol: TCP
        readinessProbe:
          httpGet:
            path: /
            port: web
            scheme: HTTP
          initialDelaySeconds: 25
          periodSeconds: 2
        livenessProbe:
          httpGet:
            path: /
            port: web
            scheme: HTTP
          initialDelaySeconds: 30
          periodSeconds: 2
        resources:
          requests:
            cpu: 200m
            memory: 256Mi
          limits:
            cpu: 1
            memory: 2Gi
        volumeMounts:
        - mountPath: /opt/conf.toml
          name: config
          subPath: conf.toml
      volumes:
      - configMap:
          defaultMode: 420
          name: yearning-conf
        name: config

5.ingress.yaml

---
apiVersion: networking.k8s.io/v1
kind: Ingress
metadata:
  annotations:
    nginx.ingress.kubernetes.io/proxy-body-size: 100M
    nginx.ingress.kubernetes.io/proxy-connect-timeout: "600"
    nginx.ingress.kubernetes.io/proxy-read-timeout: "600"
    nginx.ingress.kubernetes.io/proxy-send-timeout: "600"
    nginx.ingress.kubernetes.io/server-snippet: |
        location ~* /api/actuator {
            deny all;
        }
    nginx.ingress.kubernetes.io/configuration-snippet: |
        add_header Strict-Transport-Security 'max-age=31536000; includeSubDomains;' always;
    nginx.ingress.kubernetes.io/whitelist-source-range: 124.xxxxxxx, 125.xxxxx
  name: ingress-infra-cms
  namespace: infra
spec:
  ingressClassName: nginx-dev
  rules:
  - host: yearning-aws.xxx.xxx
    http:
      paths:
      - backend:
          service:
            name: yearning
            port:
              number: 80
        path: /
        pathType: ImplementationSpecific

管理

创建流程: staging\production不同环境的审批流程
添加数据源指定流程
添加权限组关联数据源
添加用户关联权限组

工单使用

审批流：

开发人员 -> DBA审核并执行
开发人员 -> 开发主管审批 -> DBA审核并执行

创建工单

工单申请，指定数据库，写好sql，右键SQL检查，左侧点提交
等待审批

工单审批

首页可看到当前等待审批工单数
打开我的工单，详情，右键SQL检查，右上方点同意点一次就好，不用重复点击

archery

github: https://github.com/hhyo/Archery

kubernetes

官方参考: https://github.com/hhyo/Archery/wiki/k8s

0.准备

外置数据库

CREATE DATABASE `archery` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
CREATE USER `archery`@`%` IDENTIFIED BY 'archery_)123';
GRANT ALL PRIVILEGES ON archery.* TO 'archery'@'%';

1.创建名称空间infra kubectl create ns archery

2.定制vaules.yaml

#查看原values.yaml文件内容
helm show values archery --repo https://douban.github.io/charts/

ingress:
  enabled: true # 启用 ingress
  className: "nginx"
  paths:
    - /
  servicePort: 9123
  hosts:
    - my-redis.example.com # 你的ingress 域名

redis:
  embedded: true
  # 如果启动true， 修改template/deployment.yaml中redis service地址，
  # 也可以将 embedded 改成 false, 这里直接写连接串
  # url: "redis://127.0.0.1:6379/0"
  # dingdingUrl: "redis://127.0.0.1:6379/1"
# subcharts mysql
mysql:
  embedded: false
  # 这里 mysql 也是类似的
  url: "mysql://archery:archery_)[email protected]:3306/archery"

#解决port为空启动失败问题
goinception:
  embedded: true
  initEnv:
    - name: BACKUP_PORT
      value: "3306"

4.安装

#测试
helm upgrade --install archery archery \
     --repo https://douban.github.io/charts \
     --timeout 600s \
     --debug \
     --dry-run \
     --namespace archery `#--create-namespace` \
     -f values-rl.yaml

#部署1-如果使用外置redis
helm upgrade --install archery archery \
     --repo https://douban.github.io/charts \
     --namespace archery `#--create-namespace` \
     -f values-rl.yaml

#部署2-如果使用自带redis
#helm pull 下载文件修改template/deployment.yaml 中 redis地址
helm upgrade --install archery . \
     --namespace archery `#--create-namespace` \
     -f values-rl.yaml

5.创建ingress访问网站

6.删除 archery

删除 chart helm -n archery uninstall archery

删除 namespace kubectl delete ns archery