For the background, there are two layers -- the 2 pattern tables are 256 8x8 tile images each using 2 bpp color. The name tables are what is actually displayed. Each tile in the background is an index into one of the pattern tables. (Determined by a global selection bit).
However, in some cartridges, the pattern tables are mapped to RAM. This lets the game simulate drawing into the framebuffer directly. Some games use this for drawing variable width fonts, for example. Battletoads does some cool parallax effects. It turns out that the pattern tables have just enough bits to fill the whole screen with a monochrome image, so you could treat it as a framebuffer (it requires timing changes to midframe, though). I created a demo for that http://forums.nesdev.com/viewtopic.php?f=22&t=14884
The hardware itself doesn't even contain a framebuffer; calculating palette effects and compositing the background and sprite layers into a final image is done by the hardware in realtime, 1 pixel at a time, and fed to the video output pin.
However, many game cartridges actually had RAM to store their graphic tiles in, rather than a set group of tiles in ROM. Graphics would be stored within the program code and transferred byte-by-byte into the PPU's (Picture Processing Unit, aka the GPU's) memory space. Legend of Zelda worked this way, along with a lot of other well-known games. Look at Elite for an example of the NES emulating a framebuffer using that method to render 3D polygons: https://www.youtube.com/watch?v=zoBIOi00sEI
[0] The background has 4 screens worth of area in a 2x2 screen grid, and increments addresses during rendering to simulate a toroidal memory space, allowing for smooth scrolling. This is a (perhaps overly) simplified explanation; things are slightly more complicated.
On the NES, the PPU would render a bunch of scanlines, and then during the vertical blanking interval, would trigger an interrupt on the CPU telling it to make whatever changes it needs to the graphical memory through a particular port. However, the CPU is still running while the PPU is rendering things, so it's possible to modify the graphics memory during rendering to pull some interesting tricks.
On the SNES, you could do the same (but now faster, with more memory, with Mode 7, with variable size sprites iirc) but on top of that, you could use external coprocessors as part of the cartridge to extend the existing hardware, like the Super FX or Cx4 to name a couple. With these, you could draw actual realtime 3D graphics to the screen.
So in a sense, on the SNES, you could provide a chip that would have direct access to the renderer. It was just never directly controlled from code on the main processor.