Hi yes that is a loophole
you could encrypt the passwords using a password held in the software so SELECT DECODE(g.datapasword ,"system pass supplied by software") FROM `User` u, `Groups` g, `Groups_user_link` l WHERE u.userID = "users ID here" AND u.userID = l.userID AND l.groupID = g.groupID someone with root access to the database and access to the software sourcecode would still be able to gain access, but it is an extra layer of security. Peter > -----Original Message----- > From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] > Sent: 18 November 2004 12:36 > To: 'Peter Lovatt' > Subject: RE: Row level security requirements, can I still use MySQL? > > > Ok, This is a nice solution but it still lacks a thing I would like to > avoid. Any user with "global" access for example root would have > access to > the groups table and could easily se which password was allocated to each > group and in that way bypass the security system? Or have I misunderstod > your solution. > > In any case, thanks alot for wasting som "brain-time" on this ;) > > Regards > /Jonas > > -----Original Message----- > From: Peter Lovatt [mailto:[EMAIL PROTECTED] > Sent: den 18 november 2004 12:13 > To: Jonas Ladenfors > Subject: RE: Row level security requirements, can I still use MySQL? > > > The groups system would work in software > > I would suggest > > in mysql > > create tables > > `User` > userID > username > password > > > `Groups` > groupID > data_password > > > `Groups_user_link` > > userID > groupID > > In software > > log user in > select data (group) to view > > SELECT g.datapasword > FROM `User` u, `Groups` g, `Groups_user_link` l > WHERE > u.userID = "users ID here" > AND u.userID = l.userID > AND l.groupID = g.groupID > > which will return the password for the data group > > the software will then retrieve the data using the password > > HTH > > Peter > > > > > > > > > > > > > > > > > > > -----Original Message----- > > From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] > > Sent: 18 November 2004 10:46 > > To: 'Peter Lovatt'; [EMAIL PROTECTED] > > Subject: RE: Row level security requirements, can I still use MySQL? > > > > > > Ok, jupp if I could use groups each group could have a shared > key. How do > > you create groups and then add users to them in MySQL? Are you > refering to > > the Linux systems user and groups? This idea should work but I am not > > familiar with how groups work in mysql. I need to be able to > > audit logs on a > > per user level, is this possible in this solution? > > > > /Jonas > > > > Hi > > > > I use a system based on linux securuty model > > > > create groups - this will define access to the data, so you > need to group > > the data - and encrypt data the group can access using the password > > belonging to that group. > > > > make users members of any number of groups, as required. > > > > Users can then access any data they are untitled to, but > cannot read data > > encrytped with a password they do not have access to. > > > > You will need to use software (php, C++, asp, whatever) to manage the > > user/group system. > > > > > > HTH > > > > Peter > > > > > > > > > > > -----Original Message----- > > > From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] > > > Sent: 18 November 2004 10:19 > > > To: 'Peter Lovatt'; 'Mysql (E-mail)' > > > Subject: RE: Row level security requirements, can I still use MySQL? > > > > > > > > > Yeah you are correct locking is something else I actually meant was > > > restricted access. > > > > > > If I understand you correctly I would then encrypt all > > information in the > > > table I was interested in restricting access to. But if two or > > more users > > > was to share a row in the table they would need a shared key? and then > > > several user collaborations would result in a lot of different > > > keys. I have > > > actually been thinking about this solution earlier, my problem > > with it is > > > where to store the different keys that are needed. Forcing the user to > > > manually keep track of 5 - 10 keys is to much to hope for sadly;) > > > > > > What I have been thinking about is some low-level way where you as an > > > administrator can control users and groups and place > > restrictions on each > > > row by tagging the row in some way? Or the user could tag his rows in > > > someway. > > > > > > Is this how other RDBMS enforce access restrictions? > > > > > > Regards > > > /Jonas > > > > > > -----Original Message----- > > > From: Peter Lovatt [mailto:[EMAIL PROTECTED] > > > Sent: den 18 november 2004 11:03 > > > To: Jonas Ladenfors; Mysql (E-mail) > > > Subject: RE: Row level security requirements, can I still use MySQL? > > > > > > > > > Hi > > > > > > What about encrypting the data using a password that is > specific to the > > > user. That way only those that know the password for that row can > > > access it. > > > > > > Locking is really to stop two users editing the same record > at the same > > > time, rather than controlling access. > > > > > > HTH > > > > > > Peter > > > > > > > > > > > > > -----Original Message----- > > > > From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] > > > > Sent: 18 November 2004 09:46 > > > > To: Mysql (E-mail) > > > > Subject: Row level security requirements, can I still use MySQL? > > > > > > > > > > > > Hello, I am in the position where I need row level user > > access, this is > > > > crucial in my current project. I know this has been discussed > > > > before and the > > > > answer has been "use views when they become availble". But views > > > > would still > > > > allow the "root" user access to the complete table, wouldnt it? I > > > > would like > > > > to lock rows to certain user and not let anyone else see them, > > > > not even the > > > > root user. > > > > > > > > I have been thinking about using heap tables or trying to > supply each > > > > user/group with their own dynamically created tables. But I > > > always come to > > > > the conclusion that I am hacking away at something I do not fully > > > > understand > > > > and that I cannot guaranty that the end result will have the > > security I > > > > claim. > > > > > > > > Is this possible in MySQL? > > > > Does anyone know if it cab be performed with other RDBMS? > > > > > > > > Regards > > > > /Jonas > > > > > > > > > > > > > > > > > > > > > > > > -- > > > > MySQL General Mailing List > > > > For list archives: http://lists.mysql.com/mysql > > > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
