Home > Archive > PHP SQL > May 2007 > How do I give dealers access to only their records
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How do I give dealers access to only their records
|
|
| Gary Hasler 2007-05-29, 7:02 pm |
| Newbie warning--I'm ok with PHP but my SQL knowledge is limited.
I want to allow our dealers access to our database of price quotes, but
only to their own records. The table has a field "dealer", so for
example dealer "Joe_Blow" should only be able to read records where that
field has that value.
Currently I use MS Excel to do a query thru ODBC. I can easily set up a
new MySQL user for each dealer with our web site host's "control panel"
tools, but cannot set the above mentioned restriction. I could write my
own custom PHP interface, but I'm sure this wheel has already been
invented, and I would rather not add more pages to our web site. Can
someone point me in the right direction?
| |
|
| > Newbie warning--I'm ok with PHP but my SQL knowledge is limited.
> I want to allow our dealers access to our database of price quotes, but
> only to their own records. The table has a field "dealer", so for
> example dealer "Joe_Blow" should only be able to read records where that
> field has that value.
>
> Currently I use MS Excel to do a query thru ODBC. I can easily set up a
> new MySQL user for each dealer with our web site host's "control panel"
> tools, but cannot set the above mentioned restriction. I could write my
> own custom PHP interface, but I'm sure this wheel has already been
> invented, and I would rather not add more pages to our web site. Can
> someone point me in the right direction?
You simply need to use a where clause in your sql:-
http://dev.mysql.com/doc/refman/5.0/en/select.html so on the end of your sql
you would have something like WHERE `dealer` = 'Joe_Blow'
| |
| Gary Hasler 2007-05-29, 7:02 pm |
| peter wrote:
>
>
> You simply need to use a where clause in your sql:-
>
> http://dev.mysql.com/doc/refman/5.0/en/select.html so on the end of your sql
> you would have something like WHERE `dealer` = 'Joe_Blow'
Yes, that is what I would do if I were creating a PHP page to show them
the data. What I would greatly prefer is they can simply retrieve data
directly into Excel, say to make a mailing list.
I guess basically I'm asking: is there any way to restrict a mySql user;
or to set a restriction on the table, beyond the built in mySql
restrictions of SELECT, INSERT, etc.
For example: for user Joe_Blow, the restriction "WHERE `dealer` =
'Joe_Blow'" is added to every query? My question is evolving...am I
looking for something like a "middle-man" between the mySql server and
the client?
| |
| Matt White 2007-05-29, 7:02 pm |
| If I understand what you are saying, the answer is no. You cannot make a
table in which certain fields are open only to certain people.
"Gary Hasler" <garyhasler@thelogconnection.com> wrote in message
news:465C6085.91C4D73B@thelogconnection.com...
> peter wrote:
>
> Yes, that is what I would do if I were creating a PHP page to show them
> the data. What I would greatly prefer is they can simply retrieve data
> directly into Excel, say to make a mailing list.
>
> I guess basically I'm asking: is there any way to restrict a mySql user;
> or to set a restriction on the table, beyond the built in mySql
> restrictions of SELECT, INSERT, etc.
>
> For example: for user Joe_Blow, the restriction "WHERE `dealer` =
> 'Joe_Blow'" is added to every query? My question is evolving...am I
> looking for something like a "middle-man" between the mySql server and
> the client?
| |
| ZeldorBlat 2007-05-30, 10:01 pm |
| On May 29, 3:34 pm, "Matt White" <mgw...@msn.com> wrote:
> "Gary Hasler" <garyhas...@thelogconnection.com> wrote in message
>
> news:465C6085.91C4D73B@thelogconnection.com...
>
>
>
>
>
>
>
>
>
> If I understand what you are saying, the answer is no. You cannot make a
> table in which certain fields are open only to certain people.
>
(Top-posting fixed)
No, but you can create a view that includes an appropriate where
clause and give specific users select permission on the view.
| |
| Gary Hasler 2007-05-30, 10:01 pm |
| ZeldorBlat wrote:
....snip
>
> (Top-posting fixed)
>
> No, but you can create a view that includes an appropriate where
> clause and give specific users select permission on the view.
A 'view'--what is that?
| |
| ZeldorBlat 2007-05-30, 10:01 pm |
| On May 30, 1:05 pm, Gary Hasler <garyhas...@thelogconnection.com>
wrote:
> ZeldorBlat wrote:
>
> ...snip
>
>
>
>
>
>
>
>
> A 'view'--what is that?
More information is available in the manual:
<http://dev.mysql.com/doc/refman/5.0/en/views.html>
But the short answer is that they are saved queries that can be used
just like tables. So you might create a view called "JoesQuotes" that
looks like this:
select *
from quotes
where dealer = 'Joe_Blow'
And then grant Joe's user account select permissions on the view (but
not on the quotes table itself). Then, from Excel, Joe can select
from JoesQuotes and be restricted to his records.
| |
| Gary Hasler 2007-05-30, 10:01 pm |
| ZeldorBlat wrote:
>
> On May 30, 1:05 pm, Gary Hasler <garyhas...@thelogconnection.com>
> wrote:
>
> More information is available in the manual:
>
> <http://dev.mysql.com/doc/refman/5.0/en/views.html>
>
> But the short answer is that they are saved queries that can be used
> just like tables. So you might create a view called "JoesQuotes" that
> looks like this:
>
> select *
> from quotes
> where dealer = 'Joe_Blow'
>
> And then grant Joe's user account select permissions on the view (but
> not on the quotes table itself). Then, from Excel, Joe can select
> from JoesQuotes and be restricted to his records.
Right on!..this sounds like EXACTLY what I need...I mean exactly!
Guess I need to upgrade my tables to Mysql 5 (should have done it long
ago really).
| |
|
| On 30 May 2007 10:19:57 -0700, ZeldorBlat wrote...
>
>On May 30, 1:05 pm, Gary Hasler <garyhas...@thelogconnection.com>
>wrote:
>
>More information is available in the manual:
>
><http://dev.mysql.com/doc/refman/5.0/en/views.html>
>
>But the short answer is that they are saved queries that can be used
>just like tables. So you might create a view called "JoesQuotes" that
>looks like this:
>
>select *
>from quotes
>where dealer = 'Joe_Blow'
>
>And then grant Joe's user account select permissions on the view (but
>not on the quotes table itself). Then, from Excel, Joe can select
>from JoesQuotes and be restricted to his records.
>
Views are a great way to package up a query into a simple request. Very handy
for many purposes.
Tom
--
Internet Access $9.95 / mo. or $79.95 / yr.
http://newsguy.com/allinone.htm
|
|
|
|
|