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]
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]
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]
RE: Row level security requirements, can I still use MySQL?
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]
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]
Re: Row level security requirements, can I still use MySQL?
Hi, I think Peter's suggesting you implement that security model in your application, rather than in the database server. Apart from probably not supporting such a security model, it's easier to audit user activity if you are controlling the security model. Cheers, Luke Venediger. On Thu, 18 Nov 2004 11:46:15 +0100, Jonas Ladenfors [EMAIL PROTECTED] wrote: 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] -- Get Firefox Browser! Reclaim the web. http://getfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row level security requirements, can I still use MySQL?
On Thu, 18 Nov 2004 10:45:37 +0100, Jonas Ladenfors wrote: 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. The only way to do that is client side encryption. Otherwise a sufficiently privileged user can still see the data. (Even if it is just by sniffing the network traffic or attaching a custom debugger to the running process.) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Row level security requirements, can I still use MySQL?
Well, This is a road I would like to avoid. We have several different applications fetching data from our MySQL system and constructing a application security layer is both a lot of work and have to be cusomized for some of the different applications. Some applications is not constructed by me and might not be applicable to this solution. Regards /Jonas -Original Message- From: Luke Venediger [mailto:[EMAIL PROTECTED] Sent: den 18 november 2004 12:35 To: Jonas Ladenfors Cc: Peter Lovatt; [EMAIL PROTECTED] Subject: Re: Row level security requirements, can I still use MySQL? Hi, I think Peter's suggesting you implement that security model in your application, rather than in the database server. Apart from probably not supporting such a security model, it's easier to audit user activity if you are controlling the security model. Cheers, Luke Venediger. On Thu, 18 Nov 2004 11:46:15 +0100, Jonas Ladenfors [EMAIL PROTECTED] wrote: 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
RE: Row level security requirements, can I still use MySQL?
Yup, you are absolutly correct but my application runs in a closed enviroment and our average users does not have sniffing/debugging knowledge. So this might be something I could live without. BUT this is something which touches on what I said before. That creating a good security system is a very complex thing. There is always something you forget;). It is therefore I would like to apply an already existing solution. This would hopefully minimize the potential bugs or security flaws. Anyway I was given a link by Mark Leith (thanks!) on Oracle row level access that seems interesting. Here it is (not MySQL but the mind-work might be interesting). http://www.securityfocus.com/infocus/1743 Regards /Jonas -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: den 18 november 2004 12:48 To: Jonas Ladenfors Cc: Mysql (E-mail) Subject: Re: Row level security requirements, can I still use MySQL? On Thu, 18 Nov 2004 10:45:37 +0100, Jonas Ladenfors wrote: 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. The only way to do that is client side encryption. Otherwise a sufficiently privileged user can still see the data. (Even if it is just by sniffing the network traffic or attaching a custom debugger to the running process.) Jochem -- 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]
Re: Row level security requirements, can I still use MySQL?
On Thu, 18 Nov 2004 13:07:11 +0100, Jonas Ladenfors wrote: Anyway I was given a link by Mark Leith (thanks!) on Oracle row level access that seems interesting. Here it is (not MySQL but the mind-work might be interesting). http://www.securityfocus.com/infocus/1743 It does not meet your initial requirement that there would be no root user with the ability to access the data. But if that is OK most databases have it one way or another, usually through a view + procedure. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Row level security requirements, can I still use MySQL?
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
Re: Row level security requirements, can I still use MySQL?
Hi! On Nov 18, Jonas Ladenfors wrote: 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. In MySQL there's no concept of root user. If you make sure that no user on the system has select privileges on the underlying tables, than nobody will be able to select from them. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Row level security requirements, can I still use MySQL?
Yeah, What i am referring to is rather the Global access options in MySQL. I have one root user with full global access for administration purposes. An option would be to remove the root user and let every user including the root user have access on table level instead of global access. But I fear that this would complicate administration. Each new table would need to be assign access to the root user. A better solution in my perspective is that the tables that need to be accessable on a user level should be marked as excluded from the global access option. But your suggestion of removing the root user would solve my problem but increase administration?. /jonas -Original Message- From: Sergei Golubchik [mailto:[EMAIL PROTECTED] Sent: den 18 november 2004 14:29 To: Jonas Ladenfors Cc: Mysql (E-mail) Subject: Re: Row level security requirements, can I still use MySQL? Hi! On Nov 18, Jonas Ladenfors wrote: 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. In MySQL there's no concept of root user. If you make sure that no user on the system has select privileges on the underlying tables, than nobody will be able to select from them. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- 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]
Re: Row level security requirements, can I still use MySQL?
At 03:45 AM 11/18/2004, you wrote: 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? How many users do you have? If fewer than 10, why not just create 10 different tables and put a different user password on each? How are they accessing this information? If it is through a program that you've written, then all you need to do is wrap an AND clause around each Where clause that gets constructed. Example: Select * from table1 where date = '2004-01-01' becomes Select * from table1 where (date = '2004-01-01') and useridcol = LoginId So you add ( ) around the existing where clause, and add AND useridcol = LoginId to the end of each of these WHERE clauses. Each of these user tables has a UserIdCol which can be an ENUM (or SET if more than 1 user can see it) and LoginId is the user id that was used to log into your program with. All of the database passwords are handled by your program and you don't give out any MySQL passwords to your users. This prevents the users from accessing the database unless it is through your program. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
