MySQL Forums
Forum List  »  Views

How to create a View that hides the tables behind
Posted by: Christian Esborg
Date: March 23, 2017 07:05AM

Hi all

I have already posted this in the forum: Database Design & Modelling. Maybe it fits better here. My problem is described shortly below:

I am working on a logging system, that logs calls between radios. Things like CDRs: who called who at what time, and which organization do they belong to, are logged into the database.

The db contains data for different organizations. As of now each organization has their own database, which works fine also security wise.
Now the database has to meet a new requirement, only one database for several organizations.

To meet this requirement, I want to implement a Role Based Access Control system, and had hoped that 'views' could help me in doing this.
One of Roles could be to access data from only 1 specific organization. The corresponding view 'role0_view' should select only organization=1 records from the 'call' table behind.

Call table
From, To, Organization
222, 211, 1
777, 711, 2

For each role in the system, and I want to create a database user, which is only granted SELECT rights on view, but hides the table behind.

role0_view view
CREATE VIEW role0_view
SELECT * FROM <mydb>.call WHERE Organization=1

For User0 I am granting privileges like this:
revoke all privileges, grant option from User0;
grant select on <mydb>.role0_view to User0 identified by '<passw>';

But running the query below as User0, produces an error:
SELECT * FROM <mydb>.role0_view
Error Code: 1356. View '<mydb>.role0_view' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 0.000 sec

Unfortunately I have to grant access to the 'call' table also, to get rid of the error. This is not acceptable because 'User0' then can access all organizations data again.

Are there any smart workarounds/solutions for this?

Maybe I need to emphasize that I want the MySql server to handle the security issues, so that 'organization 0' cannot access 'organization 1' data.

What I want is this:
SELECT * FROM <mydb>.role0_view
returning 1 row (222, 211, 1), and at the same time
SELECT * FROM <mydb>.call
to return something like:
SELECT command denied to user 'User0'@'localhost' for table 'call'

Not this:
SELECT * FROM <mydb>.role0_view
returning 1 row (222, 211, 1),SELECT * FROM <mydb>.call
SELECT * FROM <mydb>.call
returns 2 rows at the moment (222, 211, 1), (777, 711, 2)

BTW MySQL server used is the community version 5.6.

Hope you can help solving this issue, or point me in the right direction.

Thanks in advance ;-)

BR Christian

Options: ReplyQuote

Written By
How to create a View that hides the tables behind
March 23, 2017 07:05AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.