Updatable View limited by current user

Hello,

I would like to have an updatable view that only shows the records in a
table that have been created by the current user. By restricting regular
users to this view (rather than the underlying table) they could only look
at and modify their own data; more privileged users could look at anyone's
data.

Something like this seems to work:

CREATE
SQL SECURITY INVOKER
VIEW `sessionviewbyuser`
AS select `session`.`sessionid` AS `sessionid`,
`session`.`Folder` AS `Folder`,
`session`.`User` AS `User`,
`session`.`Path` AS `Path`,
`session`.`IP` AS `IP`
from `session`
where (`session`.`User` = SUBSTRING_INDEX(USER(),'@',1);

Is this a reasonable approach? Are there any potential gotchas? Are their
better ways to achieve this?

With many thanks for any suggestions/pointers,

Greg.

--
Gregory Jefferis, PhD
Division of Neurobiology
MRC Laboratory of Molecular Biology,
Hills Road,
Cambridge, CB2 0QH, UK.

http://www2.mrc-lmb.cam.ac.uk/group-leaders/h-to-m/g-jefferi s
http://www.neuroscience.cam.ac.uk/directory/profile.php?gsxe j2
http://flybrain.stanford.edu




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org