automatically filter records based on a userlogin name??

[Matt]

Hi everyone, still pretty new to MySQL. I was
wondering if there is a way to automatically filter
records based on a mysql userlogin name??

I have serveral databases that I want to combine
in order to manage the databases more efficiently.

- I'm currently using MySQL 4.1.12 and I'm currently testing 5.0.14
- I have a databases for each user account.
- Each database has the same schema and entity relation model.
- I have to provide raw access to the database via various connections (odbc, mysql clients)

Example, when the user logs in, via any applicaition/odbc connection/
mysql utiltiy, MySQL server sets a variable DatabaseAccount=$loginusername,
where DatabaseAccount is a table column in all the tables. The server
only returns records that match the DatabaseAccount name.

I think this would be a namespace but I did search for "mysql database namespace"
in google and got a bunch of .Net stuff back.

Thanks for your time,
Matt

[Markus Popp]

Hi,

you can do this with a stored procedure. With 'select user()' you can query
the user who's logged in. You can e.g. set up a reference table, which user
can select which set of data and set up a query with a prepared statement,
where you build the parts together.

Maybe this sounds a bit complicated, if you need some more detailed help -
this could be a nice example that I could write about in my weblog ;-).

Markus

[Matt]

Markus Popp wrote:

Hi,

you can do this with a stored procedure. With 'select user()' you can query
the user who's logged in. You can e.g. set up a reference table, which user
can select which set of data and set up a query with a prepared statement,
where you build the parts together.

Maybe this sounds a bit complicated, if you need some more detailed help -
this could be a nice example that I could write about in my weblog ;-).

Markus

I suppose we would have to setup a Trigger to detects SELECT, INSERT
UPDATE and DELETE statements and run the Stored Procedure that further filters
the returned results, if any. It would be cool if such a feature where
built into the database core.

I look forward to reading your blog Markus. I think it would be a neat
little write up.

Thanks,
Matt

[Matt]

Matt <no email> wrote:

Markus Popp wrote:

Hi,

you can do this with a stored procedure. With 'select user()' you can query
the user who's logged in. You can e.g. set up a reference table, which user
can select which set of data and set up a query with a prepared statement,
where you build the parts together.

Maybe this sounds a bit complicated, if you need some more detailed help -
this could be a nice example that I could write about in my weblog ;-).

Markus

I suppose we would have to setup a Trigger to detects SELECT, INSERT
UPDATE and DELETE statements and run the Stored Procedure that further filters
the returned results, if any. It would be cool if such a feature where
built into the database core.

I look forward to reading your blog Markus. I think it would be a neat
little write up.

Thanks,
Matt

Scratch that, triggers don't support SELECT statements.

[Markus Popp]

Here's another solution - it uses a view that limits access of rows to a
specific user that is logged in:

http://db4free.blogspot.com/2005/10/...-in-views.html

I hope that helps ;-).

Markus

[Matt]

Markus Popp wrote:

Here's another solution - it uses a view that limits access of rows to a
specific user that is logged in:

http://db4free.blogspot.com/2005/10/...-in-views.html

I hope that helps ;-).

Markus

Thats a great work around, but that requires the user be educated in
selecting those views. Also when the database grows and the schema
changes then you get into managing views. This also limits users
to use views and not any custom SELECTs with their data.

Maybe this is out of the current relm of MySQL's capabilities.

It would be nice to have syntax to create filters to allow an admin
to set an automated filter to a database(s) or table(s). This
could be handy for ISPs who offer forum services. They can consolodate
all the forums into one database instead of having one database for each
forum.

I'm just thinking the syntax could be:

CREATE FILTER <filter name> ON <database name>.<table name> TO
{(USER <user name> [{, <user name>}...]) | ALL USERS} [EXCEPT USER <user name> [{, <user name>}...]
WHERE COLUMN = <column name>

You can apply the above syntax to a DROP and ALTER so that it can be edited or
removed.

This filter would automatically apply to all SELECT, INSERT, UPDATE and DELETE
statements. So when a user does an INSERT, and the user is listed in the filter
definition, then filter knows to insert the username into defined COLUMN. This
even could expand to groups, that is if mysql supports groups. When the user
does a SELECT, MySQL returns whatever the user specified in the SELECT statement
with additional filtering done to it.

Thanks Markus for your blog entry.

Matt

[Markus Popp]

> Thats a great work around, but that requires the user be educated in

selecting those views.

From the user's sight, the views work just like tables - the user doesn't
even have to know that it's a view and not a table. You can make all kinds
of queries on the views, you can join views, make all kinds of alterations -
just as you can with tables.

There's another blog entry from Arjen Lentz that also relates to this
topic - it shows how to add a constraint to the view that makes it safe that
users can't add records that are not related to the corresponding data:

http://www.livejournal.com/users/arjen_lentz/49881.html

I agree that it's a little harder to manage if you have multiple tables that
are managed this way, because you need a view for every table. But on the
other hand, using filters (which are currently unavailable in MySQL) would
probably not be less effort.

Markus

[Gordon Burditt]

>Thats a great work around, but that requires the user be educated in

selecting those views. Also when the database grows and the schema
changes then you get into managing views. This also limits users
to use views and not any custom SELECTs with their data.
It seems to me that denying user access to the base table and giving
them access to the view will educate the user pretty quickly to use
the view. It appears that MySQL is capable of this (unless I'm
reading the doc wrong), and that the user doesn't have to know it's
a view. He just needs to treat it like a table. And he can make up
his own custom SELECTs.
Maybe this is out of the current relm of MySQL's capabilities.

It would be nice to have syntax to create filters to allow an admin
to set an automated filter to a database(s) or table(s). This
could be handy for ISPs who offer forum services. They can consolodate
all the forums into one database instead of having one database for each
forum.

I'm just thinking the syntax could be:

CREATE FILTER <filter name> ON <database name>.<table name> TO
{(USER <user name> [{, <user name>}...]) | ALL USERS} [EXCEPT USER <user
name> [{, <user name>}...]
WHERE COLUMN = <column name>

You can apply the above syntax to a DROP and ALTER so that it can be edited or
removed.

This filter would automatically apply to all SELECT, INSERT, UPDATE and DELETE
statements. So when a user does an INSERT, and the user is listed in the filter
definition, then filter knows to insert the username into defined COLUMN. This
even could expand to groups, that is if mysql supports groups. When the user
does a SELECT, MySQL returns whatever the user specified in the SELECT statement
with additional filtering done to it.

I don't particularly like hard-tying filters to user names. This
goes especially when user() returns names with possibly
dynamically-allocated IP addresses with who knows what ISP-assigned
reverse DNS entries which are subject to change at any time, or are
perhaps on a round-robin. I usually assign names so that if the
left side of the @ is the same, the administrative entity with that
login is the same, but others may not do that.

It seems to limit the usefulness a lot. It would be more useful,
IMHO, if some kind of procedure could written for determing who
gets to see what. Just going by user could use a limiting clause
of WHERE user() = user, or perhaps chop user() to the part to the
left of the @, then match it. You could also use a limiting clause
with an implicit join to implement groups, where a user is a member
of one or more groups and members of a particular group can see a
given record (group membership being determined by another table).
No, I haven't worked out a syntax for how you could specify this
stuff.

Gordon L. Burditt

[Markus Popp]

I think, views are the perfect solutions for this. Triggers disqualify,
because they can't handle SELECTs. I also thought of stored procedures, but
they have the disadvantage that you can only perform certain queries and
therefore they would not be as flexible as views.

where a user is a member
of one or more groups and members of a particular group can see a
given record (group membership being determined by another table).

That's right - it's also possible to create lookup tables to group users
together, or to allow multiple users to access a certain row ;-). That would
make the definition of the view a little bit more complex (but not really
hard), but the system would principally remain the same.

Markus

[Matt]

Gordon Burditt wrote:

Thats a great work around, but that requires the user be educated in
selecting those views. Also when the database grows and the schema
changes then you get into managing views. This also limits users
to use views and not any custom SELECTs with their data.

It seems to me that denying user access to the base table and giving
them access to the view will educate the user pretty quickly to use
the view. It appears that MySQL is capable of this (unless I'm
reading the doc wrong), and that the user doesn't have to know it's
a view. He just needs to treat it like a table. And he can make up
his own custom SELECTs.

Maybe this is out of the current relm of MySQL's capabilities.

It would be nice to have syntax to create filters to allow an admin
to set an automated filter to a database(s) or table(s). This
could be handy for ISPs who offer forum services. They can consolodate
all the forums into one database instead of having one database for each
forum.

I'm just thinking the syntax could be:

CREATE FILTER <filter name> ON <database name>.<table name> TO
{(USER <user name> [{, <user name>}...]) | ALL USERS} [EXCEPT USER <user
name> [{, <user name>}...]
WHERE COLUMN = <column name>

You can apply the above syntax to a DROP and ALTER so that it can be edited or
removed.

This filter would automatically apply to all SELECT, INSERT, UPDATE and DELETE
statements. So when a user does an INSERT, and the user is listed in the filter
definition, then filter knows to insert the username into defined COLUMN. This
even could expand to groups, that is if mysql supports groups. When the user
does a SELECT, MySQL returns whatever the user specified in the SELECT statement
with additional filtering done to it.

I don't particularly like hard-tying filters to user names. This
goes especially when user() returns names with possibly
dynamically-allocated IP addresses with who knows what ISP-assigned
reverse DNS entries which are subject to change at any time, or are
perhaps on a round-robin. I usually assign names so that if the
left side of the @ is the same, the administrative entity with that
login is the same, but others may not do that.

It seems to limit the usefulness a lot. It would be more useful,
IMHO, if some kind of procedure could written for determing who
gets to see what. Just going by user could use a limiting clause
of WHERE user() = user, or perhaps chop user() to the part to the
left of the @, then match it. You could also use a limiting clause
with an implicit join to implement groups, where a user is a member
of one or more groups and members of a particular group can see a
given record (group membership being determined by another table).
No, I haven't worked out a syntax for how you could specify this
stuff.

Gordon L. Burditt

The only reason I only used users as an example is because I don't think MySQL
natively supports user groups, at least, not that I know of. I'm still new to MySQL.
I agree, its better to have Roles/Groups where a user can belong to one or many groups.

Correct me if I'm wrong, but isn't a limiting clause the same thing as setting some
sort of inherent filter?

Matt

[Matt]

Markus Popp wrote:

I think, views are the perfect solutions for this. Triggers disqualify,
because they can't handle SELECTs. I also thought of stored procedures, but
they have the disadvantage that you can only perform certain queries and
therefore they would not be as flexible as views.

where a user is a member
of one or more groups and members of a particular group can see a
given record (group membership being determined by another table).

That's right - it's also possible to create lookup tables to group users
together, or to allow multiple users to access a certain row ;-). That would
make the definition of the view a little bit more complex (but not really
hard), but the system would principally remain the same.

Markus

If a user connects via ODBC or some MySQL library will the users see the
views as tables?

My only thing is that if an application is expecting a certain table name
but views then the app will crash. In order to do this with views
you have to rename the table name and then name the view the original
table name -OR- start going through the application changing the code to
look at the view rather than the table. I assume you can't have views
with the same name as the table??

If your application base grows then you end up managing views rather than
databases.

Matt

[Gordon Burditt]

>>>Thats a great work around, but that requires the user be educated in

selecting those views. Also when the database grows and the schema
changes then you get into managing views. This also limits users
to use views and not any custom SELECTs with their data.
It seems to me that denying user access to the base table and giving
them access to the view will educate the user pretty quickly to use
the view. It appears that MySQL is capable of this (unless I'm
reading the doc wrong), and that the user doesn't have to know it's
a view. He just needs to treat it like a table. And he can make up
his own custom SELECTs.

Maybe this is out of the current relm of MySQL's capabilities.

It would be nice to have syntax to create filters to allow an admin
to set an automated filter to a database(s) or table(s). This
could be handy for ISPs who offer forum services. They can consolodate
all the forums into one database instead of having one database for each
forum.

I'm just thinking the syntax could be:

CREATE FILTER <filter name> ON <database name>.<table name> TO
{(USER <user name> [{, <user name>}...]) | ALL USERS} [EXCEPT USER <user
name> [{, <user name>}...]
WHERE COLUMN = <column name>

You can apply the above syntax to a DROP and ALTER so that it can be edited or
removed.

This filter would automatically apply to all SELECT, INSERT, UPDATE and DELETE
statements. So when a user does an INSERT, and the user is listed inthe filterdefinition, then filter knows to insert the username into definedCOLUMN. Thiseven could expand to groups, that is if mysql supports groups. When the user
does a SELECT, MySQL returns whatever the user specified in the SELECTstatementwith additional filtering done to it.

I don't particularly like hard-tying filters to user names. This
goes especially when user() returns names with possibly
dynamically-allocated IP addresses with who knows what ISP-assigned
reverse DNS entries which are subject to change at any time, or are
perhaps on a round-robin. I usually assign names so that if the
left side of the @ is the same, the administrative entity with that
login is the same, but others may not do that.

It seems to limit the usefulness a lot. It would be more useful,
IMHO, if some kind of procedure could written for determing who
gets to see what. Just going by user could use a limiting clause
of WHERE user() = user, or perhaps chop user() to the part to the
left of the @, then match it. You could also use a limiting clause
with an implicit join to implement groups, where a user is a member
of one or more groups and members of a particular group can see a
given record (group membership being determined by another table).
No, I haven't worked out a syntax for how you could specify this
stuff.

Gordon L. Burditt

The only reason I only used users as an example is because I don't think MySQL
natively supports user groups, at least, not that I know of. I'm still
new to MySQL.

But I don't *WANT* filters to be limited to something that "MySQL
natively supports". I want to be able to use any tables I've got
to make the determination. These tables might define user groups,
or allow me to check the user's credit limit, or give me the average
income in the user's ZIP code, or whatever, assuming that information
is in tables somewhere and I can get it with a big join. It appears
that views are capable of doing that.

Oh, yes, you don't have to key only off of what current_user()
returns, you might key off of time-of-day or the host the user
connects from (from user()) or his charset or collation. It could
also be based on the contents of the record being fetched itself
(e.g. nobody can see plaintext passwords of employees, and you might
manage to avoid even storing employee plaintext passwords in the
first place).

I agree, its better to have Roles/Groups where a user can belong to one
or many groups.

Correct me if I'm wrong, but isn't a limiting clause the same thing as
setting some
sort of inherent filter?

Since neither of these terms have a formal definition, it's hard
to be specific. The example of a filter was based only on users
specifically listed in the syntax. I'm trying to cover the case
of limits based on something other than the user. A filter isn't
inherently limited to that, although the example given was.

Gordon L. Burditt