IN THE US, OTHER COUNTRIES VARY: Typically black powder cannon that are muzzle loaded are not considered destructive devices. The things that get you a bit of freedom is having it be a "replica", and not being capable of firing fixed ammunition.
The bigger issue is the possession of black powder. When I was re-enacting we had two 6 pound full sized Napoleon field guns. If we were not doing historical and educational stuff, a federal explosives permit for low power explosives would have been required for amounts over one pound. Firing 10 ounces per shot when firing "blanks" we obviously used a lot more. The law allowed up to 50 pounds of powder per person, but limited common storage to 50 pounds in a single location. The place to get real answers is the ATF web site or the web sites of re-enactor cannon and artillery suppliers.
You can be legal with the feds and still get tripped up by state laws. It's sort of a mess.
While of no use for reenactments where the smoke and fire are part of the show, muzzle loading powder like pyrodex can be used provided the cannon is fabricated to accommodate the different burn characteristics of the alternate powder. I never used pyrodex, so have no feel for the material.
If you do pursue a cannon, be sure to use the correct grain size of powder for the bore size. 10 ounces of triple F down a 4 inch bore could readily blow the gun apart, while coarse F powder will just fizzle in a 1/2 inch bore. Learn from re-enacting sites how to safely load, fire, swab out, and reload a cannon before playing around, there are a number of ways you can screw up and injure yourself or others in this game.
We typically went through 44 pounds of powder per day in a large event, so had to restock the limber after each days event. By keeping the limber and additional powder in separate locations we remained in compliance with the exemption clause for needing an LEUP from the feds. Our group was mostly federal LE folks and folks in releted lines of work, so we were extra careful to stay squeaky legal as any complications on that front could ruin careers or clearances even if no penalties or verdicts ever occurred.
Cheers,
Stan