Mysql deduce foreign key relationship for random queries

Question

I am an MySQL novice and am looking for the solution to the following problem:

I would like to create a CMS with cppcms which shall be capable to have modules. Since I want to reduce the chance of (accidental) access to private data, I want a module which handles data access and rights. Since this module is supposed to be unaware of data structures created by other modules I would like it to deduce the data owner through foreign key relations. My idea would be to search for a path (over foreign keys) which links a row to a user id.

Sum up: What I am trying to do

1. Taking a random query, determine the affected rows
2. for the affected rows determine a relationship/path (via foreign keys) to a user/userid (a column in an existing table)
3. return only the rows for which a relationship could be determined and a condition holds (e.g. the userid found in the related query matches a fixed user id, such as the user currently accessing the system)

(As far as I know foreign keys only enforce the existence of a key in another table, however the precondition I assume is, that every row is linked to a user over a path of foreign key relations)

My Problem/Question:

1. Is there an existing solution/Better approach to the problem? Prepared statements wont do the trick since I don't know all datastructures/queries in advance.

2. How do I get the foreign key relations? Is there another way besides "SHOW CREATE TABLE" and then parsing the result string?

3. How can I determine the rows that would be affected, without modifing them? I would like to filter this set afterwards by determining if I can link it to the current user (not the mysql user but system user).

Could I try executing the query, and then select the affect rows, and if I determine an access violation simply do a rollback? Problem with this: how to do the changes to the subset of rows for which it is legal (e.g. I attempt to change 5 rows, may only change 2, how to only change those 2). One idea was to search a way to create a temporary table with the result set; this solution has several drawbacks: foreign key relations are not possilbe for temporary tables, they are 'lost'.

P.S.: I am coding in c++, therfore I would prefer cpp-compatible library recommendations, however I am open to other suggestions. While googling I stumbled over doctrine and Iam currently researching it. P.P.S.: Database engine is InnoDB (has to because of the foreign keys)

UPDATE: Explanation Attempt of Part 2: I am trying to filter which collumns a user is allowed to see of tables. To do so I would like to find a connection in the database over foreign keys (By foreign keys I ensure that I can get to all data over joins, and they are a hint on which columns I have to join). Since I plan on a complexer system (e.g. forum) I don't want to join all data in a temporary table and run a user query on those. I would rather evaluate the userquery and check for the result if I can map it with a join to the users id. For example I could use this to enforce that an edit button is only enabled for the posts created by the user. (I know there are easier ways to do this, but I basically want to allow programmers to write their own queries without giving them the chance to edit or view data that they are not allowed to see. My assumption is that the programmer is not an evildoer but simply forgetting constraints, thus I want to enforce them in software).

Getting here would be pretty good, but I have a little more complex need.

First a basic example. Let's say its like facebook and all the friends of a person are allowed to see his pictures.

pictures = id **userid** file (bool)visibleForFriends album
friendship = **userid1** **userid2**
users = userid

What I want to happen is:

1. Programmer input "SELECT * FROM pictures WHERE album=2"
2. System gets all matching records (e.g. set of ids)
3. System sees foreign key userid, tries to match current userid against the pictures userid, adds all matching to the returned result part
4. System notices special column visibleForFriends
5. System tries to determin all Friends (SELECT userid1 FROM friendship WHERE userid2=currentUserID join (have to read up on joins) SELECT userid2 FROM friendship WHERE userid1 =currentUserID)
6. System adds all rows where visibleForFriends is true and pictures.userid=Result from 5.

While the Friendship part is some extra code (I think doable if igot started on the first bit), I still need to figure out how to automatically follow the foreign keys to see the connection. Ignoring the special Friendship case (special case), I would like the system to work on this as well:

pictures = id **albumid** file (bool)visibleForFriends album
albums = id **userid**
users = userid

Now the system should go pictures.albumid ==> albums.id -> albums.userid ==> users.userid.

I hope the examples clarified the question a bit. One problem is, that in point one from the example (programmer query input) I dont want to let "DELETE *" take effect on anything not owned by the user. So I have to filter which rows to actually delete.

Answer 1

In response to part of your answer (part 1), providing the Mysql user you access the database with has access rights to information_schema then you can use the following query to understand existing foreign key relations within a specific database:

SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM
    information_schema.KEY_COLUMN_USAGE
WHERE
    TABLE_SCHEMA = 'dbname' AND REFERENCED_COLUMN_NAME IS NOT NULL;

I am slightly confused by the part 2 and am unsure how to give an appropriate response to this section. I hope you find the above query helpful though in your project!

Answer 2

Is there an existing solution/Better approach to the problem?

Yes, I think so. You're describing a multi-tenant database. In a multi-tenant database in which the users share tables (also known as "shared everything"), each table should have a column for the user id. In effect, each row knows its owner.

This will vastly simplify your SQL, since you need no joins to determine who a row belongs to. it will probably speed up your SQL a lot, too.

This SO answer has a decent summary of the issues and alternatives.