sorry for my english.

i need to insert, select or delete a row from a table with various users,
but i need that:
* insert is allowed only when the column, say 'user', is set to the user who
is inserting
* select must return whatever the where clause match but discarding all the
results with the value of the column 'user' not equal to the user who is
selecting
* delete can only affect the rows with the column 'user' equal to the user
who is deleting

can this be done automagically in some way, without writing something like
this:
SELECT * FROM table WHERE user = user_name

in few words, can i grant privileges to select and delete only these rows
that match 'user' = current_user and insert only with 'user' =
current_user?

thanks in advance,


--
ut glaciem

On Tue, 04 Dec 2007 21:51:22 +0100, dissolvit wrote:
>
> sorry for my english.
>
> i need to insert, select or delete a row from a table with various users,
> but i need that:
> * insert is allowed only when the column, say 'user', is set to the user who
> is inserting
> * select must return whatever the where clause match but discarding all the
> results with the value of the column 'user' not equal to the user who is
> selecting
> * delete can only affect the rows with the column 'user' equal to the user
> who is deleting
>
> can this be done automagically in some way, without writing something like
> this:
> SELECT * FROM table WHERE user = user_name
>
> in few words, can i grant privileges to select and delete only these rows
> that match 'user' = current_user and insert only with 'user' =
> current_user?

Not terribly conveniently. The permissions don't get quite down to that
level of dynamism. You can kind sort of close to this with a limited set
of users by creating views over your real table that select out each
user ID and giving each user only SELECT and DELETE authorities into the
view. If you're prepopulating the user ID from outside, you can even
grant the user UPDATE through the view as well with reasonable safety,
if you omit the user ID column from the view selection section.

--
33. I won't require high-ranking female members of my organization to wear
a stainless-steel bustier. Morale is better with a more casual dress-code.
Similarly, outfits made entirely from black leather will be reserved for
formal occasions. --Peter Anspach's Evil Overlord List

Peter H. Coffin wrote:

> On Tue, 04 Dec 2007 21:51:22 +0100, dissolvit wrote:
[..]
>> in few words, can i grant privileges to select and delete only these rows
>> that match 'user' = current_user and insert only with 'user' =
>> current_user?
>
> Not terribly conveniently. The permissions don't get quite down to that
> level of dynamism.

that is what i want to ear.

> You can kind sort of close to this with a limited set
> of users by creating views over your real table that select out each
> user ID and giving each user only SELECT and DELETE authorities into the
> view. If you're prepopulating the user ID from outside, you can even
> grant the user UPDATE through the view as well with reasonable safety,
> if you omit the user ID column from the view selection section.
>

my problem comes from a school project.
and i'm not here to get the work done or as we say in italy 'la pappa
pronta'.

i have to create a system for some theathers, each theather has a manager,
and every manager must be able to modify only the data of the theathers he
own.

the solution is quite simple to me: i will create a database for each group
of theathers and i will grant all privileges to the user who manage the
group (a theather must have just one manager)

but the project said that for the superuser must be present a view with data
coming from all the theathers (for example all the shows of all the
theathers)

this way i have to create a view with some SELECT (one for each manager) and
using some simple UNION the work is done.

but this solution has a problem, if i add a new manager i have to add a
new "UNION SELECT ..." to the view.

well, adding a manager is not a frequent action, but i prefer some more
automated solution.

i thought even to put all the data of all the theathers, managers etc in a
single database, this way no problem with the views, but a manager can
modify everything, and this is bad (tm).

so one of the member of the project said that he found the solution, the
row-level permissions :-)

i said that it was a bad idea (because is or too much complicated or
unimplementable), but my solution is not so much better.

suggestion are appreciated.
thanks for the patience.

--
ut glaciem

dissolvit wrote:
> Peter H. Coffin wrote:
>
>> On Tue, 04 Dec 2007 21:51:22 +0100, dissolvit wrote:
> [..]
>>> in few words, can i grant privileges to select and delete only these rows
>>> that match 'user' = current_user and insert only with 'user' =
>>> current_user?
>> Not terribly conveniently. The permissions don't get quite down to that
>> level of dynamism.
>
> that is what i want to ear.
>
>> You can kind sort of close to this with a limited set
>> of users by creating views over your real table that select out each
>> user ID and giving each user only SELECT and DELETE authorities into the
>> view. If you're prepopulating the user ID from outside, you can even
>> grant the user UPDATE through the view as well with reasonable safety,
>> if you omit the user ID column from the view selection section.
>>
>
> my problem comes from a school project.
> and i'm not here to get the work done or as we say in italy 'la pappa
> pronta'.
>
> i have to create a system for some theathers, each theather has a manager,
> and every manager must be able to modify only the data of the theathers he
> own.
>
> the solution is quite simple to me: i will create a database for each group
> of theathers and i will grant all privileges to the user who manage the
> group (a theather must have just one manager)
>

Simple, but incorrect.

> but the project said that for the superuser must be present a view with data
> coming from all the theathers (for example all the shows of all the
> theathers)
>

And now you know why.

> this way i have to create a view with some SELECT (one for each manager) and
> using some simple UNION the work is done.
>
> but this solution has a problem, if i add a new manager i have to add a
> new "UNION SELECT ..." to the view.
>

Now you understand part of the problem.

> well, adding a manager is not a frequent action, but i prefer some more
> automated solution.
>
> i thought even to put all the data of all the theathers, managers etc in a
> single database, this way no problem with the views, but a manager can
> modify everything, and this is bad (tm).
>
> so one of the member of the project said that he found the solution, the
> row-level permissions :-)
>

SQL doesn't have row-level permissions like this.

> i said that it was a bad idea (because is or too much complicated or
> unimplementable), but my solution is not so much better.
>
> suggestion are appreciated.
> thanks for the patience.
>

This is a very common situation. What if you have a bulletin board
(forum) with thousands of users? You want each user to be able to edit
his/her own messages, and an administrator to be able to edit all
messages. Are you going to create thousands of databases, tables or views?

Or, in your case, what if you have thousands of theaters around the world?

In a typical web application, you have one user who accesses the
database - this user is your PHP script file. To limit what a user can
update or delete, you use the WHERE clause.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:

> dissolvit wrote:
>> Peter H. Coffin wrote:
>>
>>> On Tue, 04 Dec 2007 21:51:22 +0100, dissolvit wrote:
[..]
>
> SQL doesn't have row-level permissions like this.

god thanks.

>> i said that it was a bad idea (because is or too much complicated or
>> unimplementable), but my solution is not so much better.
>>
>> suggestion are appreciated.
>> thanks for the patience.
>>
>
> This is a very common situation. What if you have a bulletin board
> (forum) with thousands of users? You want each user to be able to edit
> his/her own messages, and an administrator to be able to edit all
> messages. Are you going to create thousands of databases, tables or
> views?
>
> Or, in your case, what if you have thousands of theaters around the world?

that's absolutely right.

> In a typical web application, you have one user who accesses the
> database - this user is your PHP script file. To limit what a user can
> update or delete, you use the WHERE clause.
>
>

yes, this is what i usually do (creating a user with the needed permissions
and using PHP or something else to control the access), but the teacher
said that is better if every manager of a group of theathers has the
possibility to login to the database for managing everything with a SQL
shell (naturally without being allowed to touch the data of the others
managers).

and seen that i'm not a so-much-skilled database designer i thought that i
was missing something (DB functionality or techniques or so) to solve the
problem.

BTW thanks for your answer, you and Peter H. Coffin have been very useful to
me

and sorry again for my english :-)

--
ut glaciem

dissolvit wrote:
> Jerry Stuckle wrote:
>
>> dissolvit wrote:
>>> Peter H. Coffin wrote:
>>>
>>>> On Tue, 04 Dec 2007 21:51:22 +0100, dissolvit wrote:
> [..]
>> SQL doesn't have row-level permissions like this.
>
> god thanks.
>
>>> i said that it was a bad idea (because is or too much complicated or
>>> unimplementable), but my solution is not so much better.
>>>
>>> suggestion are appreciated.
>>> thanks for the patience.
>>>
>> This is a very common situation. What if you have a bulletin board
>> (forum) with thousands of users? You want each user to be able to edit
>> his/her own messages, and an administrator to be able to edit all
>> messages. Are you going to create thousands of databases, tables or
>> views?
>>
>> Or, in your case, what if you have thousands of theaters around the world?
>
> that's absolutely right.
>
>> In a typical web application, you have one user who accesses the
>> database - this user is your PHP script file. To limit what a user can
>> update or delete, you use the WHERE clause.
>>
>>
>
> yes, this is what i usually do (creating a user with the needed permissions
> and using PHP or something else to control the access), but the teacher
> said that is better if every manager of a group of theathers has the
> possibility to login to the database for managing everything with a SQL
> shell (naturally without being allowed to touch the data of the others
> managers).
>
> and seen that i'm not a so-much-skilled database designer i thought that i
> was missing something (DB functionality or techniques or so) to solve the
> problem.
>
> BTW thanks for your answer, you and Peter H. Coffin have been very useful to
> me
>
> and sorry again for my english :-)
>

First of all, don't worry about your English. It's fine :-)

And while I understand your instructor's thoughts, I don't agree with
them. How many theater managers are even going to know SQL? Much less
be able to modify their tables without screwing things up (i.e. DELETE
FROM mytable).

Personally, I don't give my users direct access to the database for this
very reason - they screw things up then expect me to fix it (for free,
of course!).

But if that's the case, then your best shot is to create a view for each
user and give users access to the appropriate view.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

>i need to insert, select or delete a row from a table with various users,
>but i need that:
>* insert is allowed only when the column, say 'user', is set to the user who
>is inserting
>* select must return whatever the where clause match but discarding all the
>results with the value of the column 'user' not equal to the user who is
>selecting
>* delete can only affect the rows with the column 'user' equal to the user
>who is deleting
>
>can this be done automagically in some way, without writing something like
>this:
>SELECT * FROM table WHERE user = user_name
>
>in few words, can i grant privileges to select and delete only these rows
>that match 'user' = current_user and insert only with 'user' =
>current_user?

You might be able to do this with a MySQL VIEW. (This requires MySQL 5
at least). And it's a little quirky.

Create a view with the query:
SELECT * FROM table where user = user()
Have the view run as creator, not as invoker, and have the creator
be an administrative user who can access all rows of that table.
Do not let the other users access the table directly. Grant
privileges on the view (not the underlying table) to all users who
can manage the table. Each user looking at the contents of the
view will only see his own stuff. Defining the view WITH CHECK
OPTION prevents inserts of rows "owned" by other users. You can't
UPDATE or DELETE rows you can't see in the view.

Disadvantages of this scheme: it ties together the MySQL user ID
with the IDs stored in the table, which might be more oriented
towards the application. On the other hand, if you're really got
several managers with their own MySQL logins and they are typing
raw SQL into a command-line interface, it works at constraining
what they can change.

If this is a web-based application, there will probably be *one*
MySQL user for the application, web users have their own kind of
IDs created by the application, and the application (e.g. written
in PHP) can control sticking "WHERE user = '$applicationUserID'"
in appropriate queries to prevent users from messing with other
people's stuff. You don't need a view for this.

On 4 dic, 17:51, dissolvit <ludo.men...@aciem.org> wrote:
> sorry for my english.
>
> i need to insert, select or delete a row from a table with various users,
> but i need that:
> * insert is allowed only when the column, say 'user', is set to the user who
> is inserting
> * select must return whatever the where clause match but discarding all the
> results with the value of the column 'user' not equal to the user who is
> selecting
> * delete can only affect the rows with the column 'user' equal to the user
> who is deleting
>
> can this be done automagically in some way, without writing something like
> this:
> SELECT * FROM table WHERE user = user_name
>
> in few words, can i grant privileges to select and delete only these rows
> that match 'user' = current_user and insert only with 'user' =
> current_user?
>
> thanks in advance,
>
> --
> ut glaciem
The usual procedure is what called an "abstraction layer". This is, to
build on top of the database with some kind of application that
enforces some type of rules.
This is what is called "bussiness rules". As so, imagine that you can
add a row to the "sales" db, but you are currently out of stock for a
specific product. Well, the db would have no problem adding the row,
but violating a simple bussiness rule: "you cannot sell what you don't
have".
So, how to get arround this using mysql? STORED PROCEDURES COME TO THE
RESCUE!
The table to be updates must be only visible and editable to the
designer, not to the user.
You can create a stored procedure (sp_) that takes 4 parameters (one
for output), let's say AddSomething(ThingToAdd, User,
Password,Result) (Just saying the user is poor security). Well, this
procedure has all the select statements inside to make sure that the
"ThingToAdd" can actualy be added, and it should return an OK or FAIL
on Result. Also, the procedure will run with the CREATOR'S previleges,
so even if the user can't see or touch a table, the procedure can.
I won't go into writing sp's, as it's quite easy by just reading the
manual.
BTW, in web applications that takes sql parameters from user input,
beware of "SQL INJECT" attacs.
SP's can be a great approach on securing and pseudo-access control to
data. Check it out!
Regards
Carlos Troncoso Phillips

Carlos Troncoso <schmickcl@gmail.com> wrote in
news:678f154d-f6ee-4e14-ae49-a779e57ad376@d61g2000hsa.googlegroups.com:


> SP's can be a great approach on securing and pseudo-access control to
> data. Check it out!

Nice post... I've just learned about SP's and I can't see where in my web
applications I would have any use for them, but you're opening a good door.

Thanks

On 6 dic, 15:43, Good Man <he...@letsgo.com> wrote:
> Carlos Troncoso <schmic...@gmail.com> wrote innews:678f154d-f6ee-4e14-ae49-a779e57ad376@d61g2000hsa.googlegroups.com:
>
> > SP's can be a great approach on securing and pseudo-access control to
> > data. Check it out!
>
> Nice post... I've just learned about SP's and I can't see where in my web
> applications I would have any use for them, but you're opening a good door.
>
> Thanks

Well, I can see where U can use sp in your web app..
Lets say instead of actualy creating real mysql users, you just create
a table with users.
That table can have a certain access level witch can be queried.
So, with all the above done... instead of doing a:
Insert into foo (c1, c2,c3...) Values (webparam1, webparam2,
webparam3...);

you could create a very simple procedure that would do the insert
INSIDE the sp and also isolating the table.

OK, now you want to "Run" your procedure insted of the insert. Just
CALL MyInsertProcedure(webparam1,webparam2, webparam3, result);
All of the user checking stuff can be implemented in the sp.

Once you get the hang of it, you might even start using only SPs in
your web apps.

You'll get to it.
Luck!
Carlos Troncoso Phillips