ENH: equivalent of `local_cc_dependency` override in bzlmod

### Description of the feature request:

Hi!

I help maintain the [conda-forge](https://conda-forge.org/) distribution, where we clash pretty fundamentally with bazel's philosophy of hermetic builds -- we absolutely cannot rebuild all dependencies in a single build, it _**MUST**_ be possible to leverage libraries we have already built for the respective platform (with a given version, ABI, etc.), whether they're built by CMake, autotools, meson or whatever. I can explain the reasons for this imperative requirement in more detail if desired, but it is utterly non-negotiable. I believe that conda-forge is not a "special" distribution in this regard - other distributions also have requirements to build things incrementally (both architecturally and practically), prefer shared libraries built once against a particular ABI, etc.

This has historically been extremely painful with bazel, to the point that many packaging efforts for packages natively built by bazel have either stalled completely (e.g. [tensorflow-addons](https://github.com/conda-forge/staged-recipes/pull/16888)), are buggy ([ray](https://github.com/conda-forge/ray-packages-feedstock/issues/90) vs. protobuf interaction), or are maintained with extreme effort by a handful of people who know how to hack a bazel build enough to inject local dependencies (e.g. tensorflow).

For tensorflow in particular, this works by [generating](https://github.com/conda-forge/tensorflow-feedstock/blob/main/recipe/gen-bazel-toolchain.sh) a custom [toolchain](https://github.com/conda-forge/tensorflow-feedstock/tree/main/recipe/custom_toolchain), with lots of manual [patching](https://github.com/conda-forge/tensorflow-feedstock/tree/main/recipe/patches), and so on. It is unworkable at scale.

I was very much looking forward to the bzlmod effort reducing the difficulty of doing this, but now that I'm taking a closer look, AFAICT all of the [documented](https://bazel.build/versions/6.0.0/build/bzlmod#module-bazel) overrides point to a repo, or at least need some bazel metadata (same for the given [examples](https://github.com/bazelbuild/examples/tree/main/bzlmod)) This is very likely still an improvement over the previous state of affairs, but still leaves us with manually having to write that metadata on the fly for every consuming library, which is similarly unrealistic at the scale of 100s-1000s of packages.

Based on having an existing library with headers, library artefacts (either shared or static), and potentially some binaries (like `protoc`), what would help us[^1], in ascending order of awesomeness:
* Provide a way to generate the required bazel-metadata on the fly
* Provide an equivalent of `local_cc_dependency` in the bzlmod world that allows pointing to a given prefix resp. path.
* It doesn't matter if we need to map each library/binary individually; though headers should only require one path.
* Provide a way to "use local build of this dependency from $PREFIX"; similar to of what tensorflow does with `TF_SYSTEM_LIBS` or protobuf with `-Dprotobuf__PROVIDER="package"`.
* Provide the ability to consume existing metadata from CMake / pkgconfig directly.

Note that this is not a new request, c.f. https://github.com/bazelbuild/bazel/issues/5252 or on SO ([1](https://stackoverflow.com/questions/75087118/bzlmod-dealing-with-external-dependencies-with-no-module-bazel), [2](https://stackoverflow.com/questions/37761469/how-to-add-external-header-files-during-bazel-tensorflow-build), ...). I've tried to look for related issues but didn't find anything for this point specifically.

[^1]: I only speak for myself; that said, I'm quite experienced in packaging, but I do not have the energy to deal with all the roadblocks that bazel builds put in my way. I think I can claim in good conscience that it would help many other people in conda-forge as well.

### Which category does this issue belong to?

Configurability, External Dependency, Local Execution

### What underlying problem are you trying to solve with this feature?

Distribute packages built by bazel, built against an existing (& well-controlled) set of prebuilt dependencies.

### Which operating system are you running Bazel on?

Linux, MacOS, Windows

### What is the output of `bazel info release`?

We'll generally build & use the last released bazel version

### If `bazel info release` returns `development version` or `(@non-git)`, tell us how you built Bazel.

_No response_

### What's the output of `git remote get-url origin; git rev-parse master; git rev-parse HEAD` ?

_No response_

### Have you found anything relevant by searching the web?

_No response_

### Any other information, logs, or outputs that you want to share?

_No response_