How do I give dealers access to only their records

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?

Re: How do I give dealers access to only their records

> 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'

Re: How do I give dealers access to only their records

peter wrote:
>
> > 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'

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?

Re: How do I give dealers access to only their records

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" wrote in message
news:465C6085.91C4D73B@thelogconnection.com...
> peter wrote:
>>
>> > 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'
>
> 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?

Re: How do I give dealers access to only their records

On May 29, 3:34 pm, "Matt White" wrote:
> "Gary Hasler" wrote in message
>
> news:465C6085.91C4D73B@thelogconnection.com...
>
> > peter wrote:
>
> >> > 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.htmlso 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?
>
> 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.

Re: How do I give dealers access to only their records

ZeldorBlat wrote:
....snip
> > > 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?
> >
> > 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.

A 'view'--what is that?

Re: How do I give dealers access to only their records

On May 30, 1:05 pm, Gary Hasler
wrote:
> ZeldorBlat wrote:
>
> ...snip
>
>
>
> > > > 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?
>
> > > 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.
>
> A 'view'--what is that?

More information is available in the manual:



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.

Re: How do I give dealers access to only their records

ZeldorBlat wrote:
>
> On May 30, 1:05 pm, Gary Hasler
> wrote:
> > ZeldorBlat wrote:
> >
> > ...snip
> >
> > > > > 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?
> >
> > > > 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.
> >
> > A 'view'--what is that?
>
> More information is available in the manual:
>
>
>
> 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).

Re: How do I give dealers access to only their records

Post removed (X-No-Archive: yes)