[SQL] How to enforce server side security for deeply nested tables in SQL databases in an...

I have a SQL database where all user connections are controlled by the Usertable. Say I want to verify User owns a nested object on the server side like :

User-> Profile-> Land-> House-> Bedroom-> Lamp ( all connected with Foreign Keys)

Am I forced to perform table JOIN for each level like this ?

SELECT l.*
FROM lamps l
JOIN bedrooms b ON l.bedroomId = b.id
JOIN houses h ON b.houseId = h.id
JOIN lands ld ON h.landId = ld.id
JOIN profiles p ON ld.profileId = p.id
JOIN users u ON p.userId = u.id
WHERE u.id = :userId;


Or is there a more elegant way to do this ?

My goal with this is to prevent bad actors from performing requests while authenticated to access records they do not own ( my ids are serialized BIGINT for everything except user that has a uuid )

Currently my only server side validation is checking if a user is authenticated but not if whatever they are fetching belongs to them.

Continue reading...