I think when it first started being used in rap songs and street slang it was a way of African Americans taking something so oppressive and "owning" it...turning it around so it no longer had any power over us.
Unfortunately, I think that now, just like many phrases that originate in rap songs, popular culture has taken hold of it and I hear other people who are not African American using that word and it really gives me the shivers.
Negro and Colored were never in the same category as the "N" word. Those were just descriptions like African American or Black are today. When I teach about them in class I describe them as "old fashioned" terms that shouldn't be used today because they could offend people. I don't put them in the same category as the "bad N word".
"Oriental" is old fashioned and offensive these days as well. So is "mulatto" which was already mentioned.