Managing frequent database insertions without giving developers direct database access is crucial for maintaining security, data integrity, and operational efficiency. A Jenkins pipeline is a perfect solution for this. This blog explores how to set up a Jenkins pipeline to safely run regular database insertions, such as adding new skill names, with complete auditing and access control, without exposing database credentials or direct access to developers.

Use Case

Let’s say your application needs to frequently insert a new skill name into the skill table. Instead of letting developers run SQL queries manually (which risks data inconsistency and security), we automate this via Jenkins.

Key Challenges Without Automation:

• Developers require DB credentials
  
• Manual queries are error-prone
  
• Difficult to maintain audit logs

Pipeline Automation Benefits:

• Centralized and secure credential management
  
• Automatic auditing
  
• Easy-to-use interface for developers

Jenkins Pipeline Overview

Pipeline at a glance
 

Below is a simplified yet powerful Jenkins pipeline for this use case:

pipeline {

    agent none




    parameters {

        choice(name: 'AGENT_LABEL', choices: ['Agent1_Dev', 'Agent2_Prod'], description: 'Select Agent Label')

        text(name: 'SKILL_NAME', defaultValue: 'Scrum Master', description: 'Enter the skill name to be inserted')

    }




    stages {

        stage('Insert Skill into DB') {

            agent {

                label "${params.AGENT_LABEL}"

            }

            steps {

                script {

                    def props = readFile('/mnt/application.properties').readLines()

                    def propMap = [:]

                    props.each { line ->

                        if (line.contains("=")) {

                            def (key, value) = line.split('=', 2)

                            propMap[key.trim()] = value.trim()

                        }

                    }




                    def jdbcUrl = propMap['spring.datasource.url']

                    def dbUser = propMap['spring.datasource.username']

                    def dbPassword = propMap['spring.datasource.password']

                    def (dbHost, dbName) = parseJdbcUrl(jdbcUrl)




                    def skillName = params.SKILL_NAME.trim()

                    def userId = currentBuild.rawBuild.getCause(hudson.model.Cause$UserIdCause)?.userId

                    def triggeredBy = userId?.split('@')[0]?.split('\\.')?.collect { it.capitalize() }?.join(' ') ?: 'system'

                    def now = new Date().format("yyyy-MM-dd HH:mm:ss")

                    def auditFile = "/mnt/skill_insert_audit_log.csv"




                    writeFile file: '.my.cnf', text: """

                        [client]

                        user=${dbUser}

                        password=${dbPassword}

                        host=${dbHost}

                    """




                    sh 'chmod 600 .my.cnf'




                    try {

                        def query = """

                            INSERT INTO skill(

                                active, created_on, created_by, skill_name

                            ) VALUES (

                                true, now(), '${triggeredBy}', '${skillName}'

                            );

                        """




                        def exitCode = sh(

                            script: """mysql --defaults-file=.my.cnf ${dbName} -e \"${query}\" 2> mysql_error.log""",

                            returnStatus: true

                        )




                        def errorOutput = readFile('mysql_error.log').trim()




                        if (exitCode != 0) {

                            if (errorOutput.contains("Duplicate entry")) {

                                echo "Skill '${skillName}' already exists. Skipping insert."

                            } else {

                                error "MySQL Error: ${errorOutput}"

                            }

                        } else {

                            echo "Skill '${skillName}' inserted successfully by ${triggeredBy}."

                            def csvLine = "${now}, ${env.BUILD_NUMBER}, ${params.AGENT_LABEL}, ${skillName}, ${triggeredBy}\n"

                            writeFile file: 'temp_audit_log.csv', text: csvLine

                            sh "cat temp_audit_log.csv >> ${auditFile}"

                            sh "rm -f temp_audit_log.csv"

                        }

                    } finally {

                        sh 'rm -f .my.cnf'

                    }

                }

            }

        }

    }

}




@NonCPS

def parseJdbcUrl(jdbcUrl) {

    def matcher = jdbcUrl =~ /jdbc:mysql:\/\/(.*?):\d+\/(.*)/

    return [matcher[0][1], matcher[0][2]]

}

Advantages Over Direct DB Access

Feature	Jenkins Pipeline	Direct Developer DB Access
Security	Credentials hidden using .properties file	Credentials exposed
Audit Logging	Maintains a CSV log of all insertions	Manual effort needed
Controlled Access	Only whitelisted agents can run queries	All developers need access
Ease of Use	Devs just input skill name	Devs write and run SQL manually
️ Error Handling	Built-in duplicate check and logs	Error-prone and inconsistent

How It Works

️ Key Pipeline Actions:

• ✅ Reads DB credentials from /mnt/application.properties
• ✅ Parses JDBC URL to get DB host and name
• ✅ Inserts skill with metadata (creator, timestamp)
• ✅ Logs to /mnt/skill_insert_audit_log.csv

Jenkins UI to trigger skill insert job

Final Thoughts

This pipeline is a practical example of how to offload frequent DB operations from developers and run them safely in a controlled environment. It is extensible to any kind of query-based automation where auditability and security are required.

Key Takeaways:

• Improve database security by eliminating direct access
• Enable developers to trigger safe queries with one click
• Maintain a robust and searchable audit trail

Such Jenkins pipelines simplify operations, improve compliance, and protect your database integrity without adding burden to developers or database administrators.