LDS doctrine on the subject is found in section 76 of the Doctrine and Covenants. Referring to the circumstances in which he received this revelation, Joseph Smith stated:
From sundry revelations which had been received, it was apparent that
many important points touching the salvation of man had been taken
from the Bible, or lost before it was compiled. It appeared
self-evident from what truths were left, that if God rewarded every
one according to the deeds done in the body the term ‘Heaven,’ as
intended for the Saints’ eternal home, must include more kingdoms than
one.
Upon seeking clarification from the Lord, this revelation was received, regarding the nature of the eternal destiny of mankind. It first speaks of the "sons of perdition," people whose choices have placed them outside of the Atonement.
31 Thus saith the Lord concerning all those who know my power, and
have been made partakers thereof, and suffered themselves through the
power of the devil to be overcome, and to deny the truth and defy my
power—
32 They are they who are the sons of perdition, of whom I say that it
had been better for them never to have been born;
33 For they are vessels of wrath, doomed to suffer the wrath of God,
with the devil and his angels in eternity;
34 Concerning whom I have said there is no forgiveness in this world
nor in the world to come—
35 Having denied the Holy Spirit after having received it, and having
denied the Only Begotten Son of the Father, having crucified him unto
themselves and put him to an open shame.
36 These are they who shall go away into the lake of fire and
brimstone, with the devil and his angels—
37 And the only ones on whom the second death shall have any power;
38 Yea, verily, the only ones who shall not be redeemed in the due
time of the Lord, after the sufferings of his wrath.
So the sons of perdition are people who "know my power and have been made partakers thereof" and then "deny the truth and defy my power," and they will be consigned to hell for eternity, with Satan and his fallen angels.
After this, it moves on to a more pleasant topic, the fate of those who have not committed unpardonable sins, and defines three "degrees of glory," as they're commonly known in LDS theology:
50 And again we bear record—for we saw and heard, and this is the
testimony of the gospel of Christ concerning them who shall come forth
in the resurrection of the just—
51 They are they who received the testimony of Jesus, and believed on
his name and were baptized after the manner of his burial, being
buried in the water in his name, and this according to the commandment
which he has given—
52 That by keeping the commandments they might be washed and cleansed
from all their sins, and receive the Holy Spirit by the laying on of
the hands of him who is ordained and sealed unto this power;
53 And who overcome by faith, and are sealed by the Holy Spirit of
promise, which the Father sheds forth upon all those who are just and
true.
...
58 Wherefore, as it is written, they are gods, even the sons of God—
59 Wherefore, all things are theirs, whether life or death, or things
present, or things to come, all are theirs and they are Christ’s, and
Christ is God’s.
60 And they shall overcome all things.
...
70 These are they whose bodies are celestial, whose glory is that of
the sun, even the glory of God, the highest of all, whose glory the
sun of the firmament is written of as being typical.
The reward of the most faithful is commonly known as "the Celestial kingdom," or also "exaltation," and is the idea that people unfamiliar with LDS theology are thinking of when they hear that Mormons believe they can become Gods in the afterlife.
After this, the revelation speaks of the the eternal rewards for people who have not lived a Celestial-grade life:
71 And again, we saw the terrestrial world, and behold and lo, these
are they who are of the terrestrial, whose glory differs from that of
the church of the Firstborn who have received the fulness of the
Father, even as that of the moon differs from the sun in the
firmament.
72 Behold, these are they who died without law;
73 And also they who are the spirits of men kept in prison, whom the
Son visited, and preached the gospel unto them, that they might be
judged according to men in the flesh;
74 Who received not the testimony of Jesus in the flesh, but
afterwards received it.
75 These are they who are honorable men of the earth, who were
blinded by the craftiness of men.
76 These are they who receive of his glory, but not of his fulness.
77 These are they who receive of the presence of the Son, but not of
the fulness of the Father.
78 Wherefore, they are bodies terrestrial, and not bodies celestial,
and differ in glory as the moon differs from the sun.
79 These are they who are not valiant in the testimony of Jesus;
wherefore, they obtain not the crown over the kingdom of our God.
Those who inherit "the Terrestrial kingdom" can be thought of as people who were "good, but not great," and thus their reward was not as great as those who were more valiant in their faith.
And finally, it speaks of "the Telestial kingdom," the lowest of the three degrees of glory:
81 And again, we saw the glory of the telestial, which glory is that
of the lesser, even as the glory of the stars differs from that of the
glory of the moon in the firmament.
82 These are they who received not the gospel of Christ, neither the
testimony of Jesus.
83 These are they who deny not the Holy Spirit.
84 These are they who are thrust down to hell.
85 These are they who shall not be redeemed from the devil until the
last resurrection, until the Lord, even Christ the Lamb, shall have
finished his work.
86 These are they who receive not of his fulness in the eternal
world, but of the Holy Spirit through the ministration of the
terrestrial;
...
99 For these are they who are of Paul, and of Apollos, and of Cephas.
100 These are they who say they are some of one and some of
another—some of Christ and some of John, and some of Moses, and some
of Elias, and some of Esaias, and some of Isaiah, and some of Enoch;
101 But received not the gospel, neither the testimony of Jesus,
neither the prophets, neither the everlasting covenant.
102 Last of all, these all are they who will not be gathered with the
saints, to be caught up unto the church of the Firstborn, and received
into the cloud.
103 These are they who are liars, and sorcerers, and adulterers, and
whoremongers, and whosoever loves and makes a lie.
104 These are they who suffer the wrath of God on earth.
105 These are they who suffer the vengeance of eternal fire.
106 These are they who are cast down to hell and suffer the wrath of
Almighty God, until the fulness of times, when Christ shall have
subdued all enemies under his feet, and shall have perfected his work;
...
111 For they shall be judged according to their works, and every man
shall receive according to his own works, his own dominion, in the
mansions which are prepared;
112 And they shall be servants of the Most High; but where God and
Christ dwell they cannot come, worlds without end.
People who have not lived righteous lives, and yet have not committed the unforgivable sin, will eventually be forgiven after they have suffered for their sins, and receive a small reward in heaven according to the quality of the lives they chose to live.
Section 76 is one of the longest chapters in the Doctrine and Covenants, and I've cut down the quotes a fair bit for brevity, but this covers the core of the LDS doctrine on the afterlife and the various different destinies available to mankind.
