There isn’t really a clear answer to this in official materials. However, the text about the Draconic Prophecy doesn’t suggest that its existence is any kind of a secret—while any piece of what the Prophecy actually says is often closely-guarded by those that know it, that it exists, that it’s really important to the dragons, etc., does not seem to be. It certainly doesn’t seem to be common knowledge, but that seems to be more because it’s just not important to most people than because it’s particularly difficult to learn about.
I’m going to go through the official Eberron materials for the 5th edition and for the “v.3.5 revised edition” of D&D—that is, the latest one, and the one for which Eberron was originally described, and which has by far the most content. That means I’m skipping over 4e, but 4e only had a couple of Eberron books, and, to put it mildly, did not take great care with its lore continuity—and much of what it had to say on that subject was quietly ignored by 5e. I’m also not covering Keith Baker’s blog—which is covered by Sarge’s fine answer—because that’s not an official source: Keith Baker is the inventor of Eberron, and is in charge of it, but ultimately the actual published Eberron is the work of a considerable team, not just him.
D&D 5e
Eberron: Rising from the Last War is the only Eberron book published for 5e, and it discusses the Draconic Prophecy in a number of places. Most relevantly to this question, we have
In general, “few”
[…] Few humans or even elves have the time or resources to unravel [the Prophecy’s] secrets. […]
(Eberron: Rising from the Last War, “The Draconic Prophecy,” pg. 15)
Few, but not none. Unfortunately, spoilers, you won’t find really a better answer than “few.” Worse, this is “actively studying,” rather than “being generally aware of,” which is more what the question is after.
One House Ghallanda enclave (of many)
Baron Yoren [of House Ghallanda] and his daughter Chervina have greatly expanded the house’s presence even in remote areas such as the edge of the Demon Wastes, guided by their study of the Draconic Prophecy.
(Eberron: Rising from the Last War, “House Ghallanda,” pg. 44)
This is a very interesting tidbit kind of just thrown onto the end of the description of House Ghallanda.
If a Ghallanda enclave is big on this, presumably the higher-ups in the House—other barons, anyone above the barons—are generally aware of it, as a general part of keeping at least vaguely aware of what everyone is working on. There’s no suggestion that Yoren and Chervina are keeping this a secret.
Note, however, that House Ghallanda has a lot of enclaves, “which are more numerous than those of any other dragonmarked house,” (ibid.), so it’s relatively to imagine Yoren’s getting lost in the mix a little bit.
Chasing after the Prophecy is a lot of work
In order for the Draconic Prophecy to work as a patron, one or more of the characters needs to have access to words from the Prophecy. Your group might adventure to seek out places where the Prophecy is written: mountainsides and cavern walls, ancient text and crumbling ruins, or the patterns of moons, stars, and the Ring of Siberys (best interpreted at an observatory). A dragonmarked character might gain insight into the Prophecy from the marks on their skin. Alternatively, a character might hear words from the Prophecy in dreams or visions, or surfacing from some deeply buried memory of a traumatic event.
(Eberron: Rising from the Last War, “The Draconic Prophecy as Group Patron,” pg. 84)
Nothing here really suggests how common any of these activities might be, but it does give a sense of what is involved in studying the Draconic Prophecy. Still, “general awareness that it is a thing” is much different from “actively studying,” so only so helpful.
Flamewind the sphinx and Morgrave University
Flamewind: The sphinx Flamewind (described in the “Immortal Being” section) isn’t officially affiliated with [Morgrave University], but she lives there and spends much of her time in its libraries and museums. As a scholar of the Draconic Prophecy, Flamewind often poses strange questions and sends adventurers on obscure missions.
(Eberron: Rising from the Last War, “Morgrave University, Allies,” pg. 98)
It’s a safe bet that pretty much everyone in Morgrave University has at least heard of the Draconic Prophecies, if a gynosphinx prowls through its libraries and museums asking random people weird questions. “What’s up with the sphinx?” “Oh yeah, she studies some dragonny prophecies, apparently pretty important.” Such a famous and notable resident of Morgrave University is likely to be a figure that people in academia more broadly are aware of. And if it’s something well known in academia, then it’s something known to people whose job it is to know things (spymasters, etc.).
Draconic Prophecy as the subject of rumor
Though Vvaraak is long dead, rumors say that her hidden lair holds secrets tied to the Draconic Prophecy and the mysteries of druidic magic.
(Eberron: Rising from the Last War, “Shadow Marches, Cities and Sites: Vvaraak’s Cave,” pg. 124)
If you can possibly hear a rumor that talks about the Draconic Prophecy, that means enough people have at least heard of the Draconic Prophecy for that relevant detail to be included in the rumor. Though I’d take this one with a grain of salt, since it’s a bit of a throwaway line for the sake of a little plot hook offered to DMs. Vvaraak does get mentioned elsewhere with respect to the Prophecy and druidry, though nothing relevant to this question.
D&D “v.3.5 revised edition”
The original description of Eberron is found in Eberron Campaign Setting, a supplement for the “v.3.5 revised edition” of D&D, and various additional 3.5e Eberron supplements significantly fleshed out the setting. There are some things in Eberron: Rising from the Last War that conflict with their original descriptions in 3.5e books, but much of it remains the same—and there is just so much more Eberron content in 3.5e than there is in 5e—so it is often helpful to look back to these older sources for more information.
Eberron Campaign Setting
The introduction of the Draconic Prophecy is in a sidebar on page 130 of Eberron Campaign Setting, cleverly titled “The Draconic Prophecy.” It includes a few statements that are useful here:
The draconic Prophecy is as complex and unfathomable as the dragons themselves. A few among the lesser races learn and study snippets of the huge and constantly accumulating text, but only the dragons have the time and perspective to see the Prophecy for what it truly is.
(Eberron Campaign Setting, “The Draconic Prophecy,” pg. 130)
Once again, “few.”
The book’s index doesn’t indicate any other pages mentioning the Draconic Prophecy, but that isn’t strictly true. At the very least, the chapter on organizations says “The Chamber was formed by younger Argonnessen dragons who believed that the Prophecy demanded their participation in the world beyond their continent,” (Eberron Campaign Setting, pg. 229). There may be other mentions. But it’s pretty unlikely that there’s anything major left in this book, that would answer this question.
Notably, the book also says
a few institutes of learning have attempted to launch expeditions into Argonnessen from Seren or Totem Beach, though only one professor (from Morgrave University) has returned after negotiating with the Seren chiefs.
(Eberron Campaign Setting, pg. 220)
While this doesn’t speak to the Prophecy, per se, it does strongly suggest that even educational and research organizations have extremely limited visibility into what the dragons are up to, and that is where almost all of the research into the Prophecy comes from. Still, the initial description said “A few […] study snippets of the [Prophecy],” so there must be some.
Magic of Eberron
Magic of Eberron includes a new mechanic, “prophetic favor,” which can be accessed using the Dragon Prophesier feat, expanded upon with a number of follow-up feats (Prophecy’s Artifex, Explorer, Hero, Shaper, Shepherd, and Slayer) interacts with a new spell, glipse the Prophecy, and even had a dedicated prestige class, the dragon prophet. This last has a lengthier description that’s of note here:
The dragon prophet is a member of one of the “lesser races” who shares the dragons’ ambitious goal of understanding the complex and convoluted draconic Prophecy. Dragon prophets wander the world seeking signs of the Prophecy in everything, hoping to make sense of new revelations and pass them on to their draconic mentors back in Argonnessen, home of the dragons. […]
[…] The dragons learned long ago that many sorcerers and wizards among the lesser races possessed a hunger for knowledge that would make them excellent researchers.
(Magic of Eberron, pg. 63)
None of this directly talks about how many dragon prophets there are, but there are some hints. Dragon Prophesier is not a terribly difficult feat to take, but in D&D 3.5e, there are many, many feats available. The competition for any given feat opening is intense, and a more-obscure feat like Dragon Prophesier is an uncommon choice. The dragon prophet isn’t so difficult to enter, either, at least mechanically; it basically requires you to have 5 levels in another spellcasting class, and the Dragon Prophesier feat. A 6th-level character is pretty notable in 3.5e Eberron—that’s war-hero status, or equivalent, but still, they just had a really big war; there are a number of heroes out there. But it also requires being personally recruited by a dragon—that’s a pretty big deal.
Player’s Guide to Eberron
Player’s Guide to Eberron is kind of a weird book, not primarily written by Keith Baker, and in an unusual format for the edition. Some of its statements have a questionable fit in the broader descriptions of Eberron. That said, it does have this to say:
What is the Prophecy? No humanoid knows. It might be a transcription of the words of the progenitor dragons, spoken before the birth of the world and written through all creation. It might be the utterances of Chronepsis, the dragon god of fate, giving clues to the future to those with ears to hear. It might simply be the expression of patterns in nature that point to the inevitable unfolding of those patterns in the future. Even the dragons do not know for sure, but each dragon has a theory—a personal understanding of what the Prophecy is and what its significance might be, for dragonkind and the world.
(Player’s Guide to Eberron)
Explorer’s Handbook
Another odd book, Explorer’s Handbook is mostly to do with travel around Eberron—what modes of transportation are available, how long different routes take, and so on. There are a fair few details about what’s going on at each of the exotic destinations, but nonetheless, an unusual structure.
It also contains a prestige class, the cataclysm mage, which has a strong claim to coolest class name in the history of D&D. Its description says
Misunderstood by the common races and persecuted by the dragons, cataclysm mages are uncommon among even the most erudite circles. Intense arcane study and an obsession with ancient history, lost empires, and the Prophecy are keys to becoming a cataclysm mage. […]
Cataclysm mages study the titanic magic of past civilizations. As they grow in power, they gradually uncover the secrets of each past age and its cataclysm: the Age of Monsters, the Age of Giants, and the Age of Demons, in that order. No cataclysm mage has yet progressed to study the end of the Age of Dragons, and it is said that the dragons of Argonnessen mark those who attempt it for swift and brutal assassination. […]
Personal Prophecy: At 1st level and every three levels thereafter, a cataclysm mage receives and creates a personal prophecy. After he fulfills is prophecy, fate smiles on him; [description of mechanical benefit] […]
Dragonmark Secret (Ex): When you reach 10th level, your study of the Age of Dragons and arcane magic has yielded great knowledge at terrible risk. At the beginning of each day, you can choose to manifest one least, lesser, or greater dragonmark for an entire day. […]
As powerful as this ability is, it carries with it great risk. Many dragons already resent that the dragonmarks appear on lesser races, yet take solace in the fact that they alone (as far as they are concerned) can interpret the infinitely complex meanings of these marks and their impact on the great Prophecy. The dragons put great store in the number and location of every individual dragonmark. A cataclysm mage who masters the dragonmark secret can throw their many-faceted calculations into chaos at the slightest whim. To protect both the Prophecy and the fabric of fate itself, the dragons decree that those who carry the dragonmark secret must die swiftly and with no hope of resurrection. […]
NPC Reactions
Most citizens of the Five Nations are only dimly aware of the existence of cataclysm mages. Those who have knowledge of the order tend to look on it with a great deal of suspicion. Decades of subtle dragon propaganda have seeded the universities and arcane colleges with doubts, questions, and fear concerning the mages’ activities. Focused as always on the past and the future to the neglect of the present, the Masters of Argent have failed to mount an effective campaign to counter this slander.
(Explorer’s Handbook, pg. 58-62)
Note the interesting statement that “No cataclysm mage has yet progressed to study the end of the Age of Dragons,” the 10th-level feature of the class. This is consistent with 3.5e Eberron as a whole—as a prestige class, you can’t take your first levels in cataclysm mage, but must multiclass into it once you meet its requirements, which is (usually) impossible before 6th level. That makes 10th-level cataclysm mages 15th level overall, and mortal characters of that level just don’t exist in Eberron; the highest-level mortal, humanoid NPCs in the setting cap out at 12th or 13th, maybe 14th. However, the “Masters of Argent”—a group of cataclysm mages who have mastered the cataclysm of silver, as part of their 9th-level class features, seems to exist. These characters must be 14th level, and thus extremely powerful relative to the overall society. Explorer’s Handbook doesn’t make it clear how many Masters of Argent there are, but there could not be many.
Conclusion
It seems to me that the learned and erudite have likely at least heard of the concept of the Draconic Prophecy. People who are in the business of knowing—either academia, or espionage—almost certainly do. Monarchs probably do, or at the very least have people on staff who do and are able to inform them should the need ever arise. The one mention of a “rumor” about it in Eberron: Rising from the Last War may even indicate that the name “Draconic Prophecy,” at least, has some kind of meaning even among common folk. At any rate, we can be sure that the existence of something called the Draconic Prophecy is no great secret in Eberron. What it actually means, on the other hand, is basically completely unknown—even the dragons are just guessing. Almost everyone else can’t even guess.
