You're correct. Most people who do their own scans either use a macro lens or a telephoto with extension tubes to create a macro lens of sorts. Then you need a negative holder such as this one: https://www.negative.supply/shop-all/basic-film-carrier-35. You need a light table or LED hot light, and a way to hold the camera and you're in business.
Personally, I've been scanning my own 35mm negatives for the past few years with both a Full-frame DSLR and an APS-C Mirrorless camera, and get much much better results than I'd been getting via my local film processing lab and their scanning.
Scanning negatives with a modern digital camera still retains a lot of the filmic quality that we've come to expect via shooting film. Obviously you lose the "resolution" and ability to blow those images up once scanned, but you can 100% still see the grain, color reproduction, and analog anomalies that we're used to by shooting film.
Yeah, I have recently switched to "scanning" my 6x6cm and 35mm negatives with a macro lens and digital camera. It's probably not as flat out perfect as a really good scanner, but the end results look amazing and fit my workflow for digital images as well.
I recently got a camera with "pixel shift" technology and using that the files have 96mb and really show off the grain. Plus it's just faster than scanning ever was. I am really very happy with the setup.
You don't "need" much tbh. My setup is a cardboard tube, a lens I found in a broken scanner and some tape. I'm using an old kindle as backlight since it doesn't have pixels = no need for diffusion.
If you don't use a planar lens it will never really look good.
What I do for my part is just use an enlarger to which I attach my digital camera with various tubes and adapters to get the distances right, and an enlarger lens. These lenses are infinitely cheaper than "regular" planar lenses and they're just made exactly for the job, and the whole assembly is rigid and aligned so it's easy to get the whole negative right.
Personally, I've been scanning my own 35mm negatives for the past few years with both a Full-frame DSLR and an APS-C Mirrorless camera, and get much much better results than I'd been getting via my local film processing lab and their scanning.
Scanning negatives with a modern digital camera still retains a lot of the filmic quality that we've come to expect via shooting film. Obviously you lose the "resolution" and ability to blow those images up once scanned, but you can 100% still see the grain, color reproduction, and analog anomalies that we're used to by shooting film.