CODEWITHSUNDEEP
mysqlsqlpython-3.xpymysql
Arnold
Arnold

Reputation: 191

use column names from inner query select the columns to display in outer query

My scenario is as follows( in MySQL) I have a table say table 1, which has 2 columns:

userID, column_acess

Table 2 which has a list of columns say col1,col2,col3, etc.

Now What I would like to do is use pymySQL to query table 1 for the columns a particular userID is allowed to acesss, by inspecting the column, acess field ( which will contain a comma seperated list of columns in tabl2), and use that result in another sql query ( which works on table2) to actually get the data from the respective columns a user is allowed to acess.

So essentially I would like something like:

Select (Select column_acess from tabl1 where user_ID='123') from table2

So inner query should return the list of columns say col1, col2, which would be used to select the columns in the outer query in table2

How do I do that in mySQL?

Upvotes: 0

Views: 1364

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

MySQL supports the granting of column-level privileges to users, using the standard grant statement.

I would suggest that you start with the documentation on this subject.

An alternative to using grant for columns is to create views for different user types. This is, in fact, the more general solution, because the views can filter rows as well as columns. The idea is that the underlying tables are not directly accessible. The views are, so all access needs to go through the views.

Upvotes: 0

Xenon
Xenon

Reputation: 304

I strongly encourage you too read this post. You should either first store columns in variable or use dynamic sql query. Use SELECT result as COLUMN name in other SELECT

BTW your schema is not even in 1 NF since you don’t have atomic values in table 1. You should avoid that.

Upvotes: 0

Related Questions