[Security] Automatic Scan for CWEs (additional to CVEs scans)

### Search before asking

- [X] I searched in the [issues](https://github.com/apache/pulsar/issues) and found nothing similar.

### Motivation

Since https://github.com/apache/pulsar/pull/10855 we are doing dependency scans for vulnerabilities (CVEs =Common Vulnerabilities and Exposures) on regular basis. That's really great!

Like always there is more one can do. We could also automatically scan for C**W**Es...

### Solution

Github allows for open source projects like pulsar, the free usage of `CodeQL`.
This tool tries to spot the C**W**Es (CommonWeaknesses) in Software written in different languages (and Hardware), see https://cwe.mitre.org/ and
https://codeql.github.com/codeql-query-help/codeql-cwe-coverage/

> If you haven’t heard of [CodeQL](https://codeql.github.com/), it’s GitHub’s static code analysis engine that treats code like data and makes it queryable. Then, using a growing library of open source queries corresponding to known security vulnerability patterns, `CodeQL` scans your code to identify any potential issues.

The usage seems to be the same as the CVE Scanner we use: simply triggered by github action and uploading the report in the end
For setup see: https://github.com/github/codeql-action

-> **Would be really interesting to give `CodeQL` a test run** on a small component of pulsar and see if it can catch anything valuable without too many false positives.

just as addition:
2021 CWE Top 25 Most Dangerous Software Weaknesses
https://cwe.mitre.org/top25/archive/2021/2021_cwe_top25.html

### Alternatives

Maybe a lighter alternative is to add the `find sec bug`-plugin to the already implemented `spotbugs` https://github.com/apache/pulsar/issues?q=enable+spotbugs+is%3Aclosed

This plugin also has some CWE coverage.
https://find-sec-bugs.github.io/

### Anything else?

_No response_

### Are you willing to submit a PR?

- [ ] I'm willing to submit a PR!

Start by reviewing the dependency-scanning setup introduced by pull request #10855 and the GitHub codeql-action documentation; no specific Pulsar files are named in the issue. Try CodeQL on a small Pulsar component, then compare the resulting CWE findings and false positives with the existing scan before defining the workflow changes and validation needed.

Written by the indexing model from the issue text.