It is a distribution. The easiest, cleanest way to think of it is as a linear functional $\mathscr{H}\to\mathbb{R}$ on the Hilbert space $\mathscr{H}$ of functions $\mathbb{R}^N\to\mathbb{R}$ that are $\mathbf{L}^2(\mathbb{R}^N)$. Input a function $f\in\mathbf{L}^2(\mathbb{R}^N)$, and DiracDelta spits out $f(0)$. It's a manifestly linear operator.
Historically it was intuitively motivated by the need for a function $\delta:\mathbb{R}^N\to\mathbb{R}$, in the everyday sense, with precisely the properties you state. It's also motivated by the Riesz Representation Theorem: a Hilbert space is complete, so every continuous linear functional can be represented as an inner product in the space: for every continuous linear functional $f:\mathbf{L}^2(\mathbb{R}^N)$ there exists a unique function $f_0:\mathbb{R}^N\to\mathbb{R}$ in the everyday sense such that:
$$\langle f_0,\,g\rangle = \int_{\mathbb{R}^N} f_0(U)\,g(U)\,\mathrm{d} U\tag{1}$$
In finite dimensional vector spaces over $\mathbb{R}$ kitted with inner product, all linear functionals are also continuous. Thus, for example, the one forms make up all the linear functionals that there are: there are no linear functionals that cannot be written as the action $X\to\omega(X)$ of a one form $\omega$ on the vectors in a finite dimensional Hilbert space. In physicist's speak: all linear functionals can be written as vectors with their indices lowered by contraction with the metric tensor $g$.
In infinite dimensional spaces, the notion of continuous linear functional is strictly more precise than simply linear functional: there are always linear functionals which are not continuous and indeed DiracDelta is one. See, for example, my answer here (as well as the MathOverflow threads linked in it) on rigged Hilbert spaces for more information. So we can discuss distributions through this notion of rigged Hilbert space: we kit the Hilbert space with a stronger topology than simply the Hilbert norm topology so that the new space's topology is precisely strong enough, but no stronger, to deem all linear functionals to be continuous with respect to this newer, stronger topology. We speak of the "topological dual space" of all continuous linear functionals to a Hilbert space as distinct from the larger, "algebraic dual" of linear, but not needfully continuous functionals. It is the former that a Hilbert space, by definition, is equivalent to, not the latter. The Riesz Representation Theorem in this context is then the assertion that this definition of topological self duality is the same as the Hilbert space definition as a complete, inner product space.
So there is no everyday function with the properties you state precisely because the notion of linear functional is strictly more general than the notion of continuous linear functional in infinite dimension Hilbert space.
We can also think of distributions as sequences of everyday functions. This is the approach you allude to in the thinking of DiracDelta as a a sequence of ever peakier Gaussians. This is the approach that M. J. Lighthill, "An Introduction to Fourier Analysis and Generalised Functions" uses. It's a cute little book: rigorous, highly readable but quite off-beat (it uses nomenclature "good functions" for the Schwartz functions that I have not seen elsewhere, for example) and a little dated. But it is still a valid conception of the notion of distribution.
