Virtual Hosting Privileges
Hi all, This is my first post to this list so bear with me if I ramble on a bit. I have been reading as much as I can on this subject and am yet to feel confident about an answer so I thought I would throw it at this list and see what comes back. One thing I should mention up front is I am in no way a SQL power user, although, I have enough reference material that I can work my way through most problems. We run a small web hosting and design service and have been offering MySQL as part of the basic setup for quite a while without any problems. One of the things we offer it PHPMyAdmin which has server us well so far. Our clients seem to use it as a browsing tool more that anything, but there are a few that might be called 'Power Users that use it for more SQL intense purposes. (FYI, I am using the PMA user, who only has 'select' privileges to the mysql users table). Recently, I noticed something that caused me to reflect on the privileges that I currently have setup on the server, and was wondering if I could get some feedback and/or some light tutoring on the topic of privileges. It is perhaps prudent to start with some setup info: FreeBSD 6.2 - Not using jails. MySQL 4.1 PHP 4.4 - Soon to be upgraded to 5.x Apache 2.2 PHPMyAdmin 2.11.0 What I am looking for at the end of this discussion is if my current privileges are secure enough, and, give all users the maximum functionality within the security model. All mysql users are alos unix users. When I setup the accounts, I use scripts to setup the mysql user at the same time the UNIX users is setup. That having been said, Users can not log into a shell of the operating system, the unix user is setup to give ftp access, and access to thier back end admin panel. When a new mysql user is setup: 1. the user is assinged a password but is given no privileges in the mysql 'users' table, but is allowed to connect from localhost so thier scripts can run; sampleuser localhost XXX None 2. his/her blank database is setup, and that user is given access to is from localhost, although I have a few users that have requested, and were granted connections (via the 'hosts' table) remote access. sampleuser_database sampleuser localhost Select | Insert | Update | Delete | Create | Drop | Grant | References | Index | Alter I suspect that I want to remove the 'Grant' access from the privileges above. Here is where my confusion escelates If i remove it, can the user still add tables? I suspect that I should consider removing the 'Drop' privilege. If I do that will the user be able to drop tables that he/she created? i.e. ones that reside in thier own database? Hopefully, you all can see my confusion. At the end of the day, I gues the question is: What privileges can I grant each user that will only allow them to completely manange thier own database without allowing them access to others databases? or What priveleges should I grant users at setup time? Thanks a million, -Grant P.S. I will also keep reading. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Virtual Hosting Privileges
This one time, at band camp, Grant Peel [EMAIL PROTECTED] wrote: What priveleges should I grant users at setup time? Perhaps look at the GRANT option GRANT SELECT, UPDATE, DELETE TO [EMAIL PROTECTED] IDENTIFIELD BY 'password'; you can use GRANT * or any combination of privileges you like. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign keys on non-unique columns (problem)
Hi Yves, I have a problem with my foreign keys. I have the following two tables: CREATE TABLE keylist ( KeylistId INTEGER NOT NULL, UserId INTEGER NOT NULL, PRIMARY KEY (KeylistId, UserId)); CREATE TABLE user ( UserId INTEGER NOT NULL PRIMARY KEY, AdditionalKeylist INTEGER); A keylist stores multiple user IDs for each keylist ID. A user has a reference to one keylist to keep multiple additional keys. (My key is the same as a user ID.) Now I have added this foreign key constraint: ALTER TABLE user ADD FOREIGN KEY (AdditionalKeylist) REFERENCES keylist (KeylistId) ON DELETE SET NULL; This cannot work. The column in KEYLIST to which you are pointing should have a unique value, that means either a primary key or unique constraint. Given that the constraint on KEYLIST means that you can have multiple KEYLIST entries for each USERID value, how is a foreign key constraint supposed to be pointing to a single entry in KEYLIST? It cannot, unless you're referencing a unique (pair) value. What is it exactly that you want to store? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Virtual Hosting Privileges
Grant Peel wrote: Hi all, This is my first post to this list so bear with me if I ramble on a bit. I have been reading as much as I can on this subject and am yet to feel confident about an answer so I thought I would throw it at this list and see what comes back. One thing I should mention up front is I am in no way a SQL power user, although, I have enough reference material that I can work my way through most problems. We run a small web hosting and design service and have been offering MySQL as part of the basic setup for quite a while without any problems. One of the things we offer it PHPMyAdmin which has server us well so far. Our clients seem to use it as a browsing tool more that anything, but there are a few that might be called 'Power Users that use it for more SQL intense purposes. (FYI, I am using the PMA user, who only has 'select' privileges to the mysql users table). Recently, I noticed something that caused me to reflect on the privileges that I currently have setup on the server, and was wondering if I could get some feedback and/or some light tutoring on the topic of privileges. It is perhaps prudent to start with some setup info: FreeBSD 6.2 - Not using jails. MySQL 4.1 PHP 4.4 - Soon to be upgraded to 5.x Apache 2.2 PHPMyAdmin 2.11.0 What I am looking for at the end of this discussion is if my current privileges are secure enough, and, give all users the maximum functionality within the security model. All mysql users are alos unix users. When I setup the accounts, I use scripts to setup the mysql user at the same time the UNIX users is setup. That having been said, Users can not log into a shell of the operating system, the unix user is setup to give ftp access, and access to thier back end admin panel. When a new mysql user is setup: 1. the user is assinged a password but is given no privileges in the mysql 'users' table, but is allowed to connect from localhost so thier scripts can run; sampleuser localhost XXX None 2. his/her blank database is setup, and that user is given access to is from localhost, although I have a few users that have requested, and were granted connections (via the 'hosts' table) remote access. sampleuser_database sampleuser localhost Select | Insert | Update | Delete | Create | Drop | Grant | References | Index | Alter I suspect that I want to remove the 'Grant' access from the privileges above. Here is where my confusion escelates If i remove it, can the user still add tables? I suspect that I should consider removing the 'Drop' privilege. If I do that will the user be able to drop tables that he/she created? i.e. ones that reside in thier own database? Hopefully, you all can see my confusion. At the end of the day, I gues the question is: What privileges can I grant each user that will only allow them to completely manange thier own database without allowing them access to others databases? or What priveleges should I grant users at setup time? There is no way I'd have a setup like this with other users databases in the same mysql instance. I'd use openzv or similar to virtualise each account. Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign keys on non-unique columns (problem)
On 04.11.2007 21:10 CE(S)T, Martijn Tonies wrote: Now I have added this foreign key constraint: ALTER TABLE user ADD FOREIGN KEY (AdditionalKeylist) REFERENCES keylist (KeylistId) ON DELETE SET NULL; This cannot work. The column in KEYLIST to which you are pointing should have a unique value, that means either a primary key or unique constraint. I know, how I've written further down. Given that the constraint on KEYLIST means that you can have multiple KEYLIST entries for each USERID value, how is a foreign key constraint supposed to be pointing to a single entry in KEYLIST? It cannot, unless you're referencing a unique (pair) value. That's an interesting point. Actually, I'm not referencing a single row, but a single value which can occur multiple times. What is it exactly that you want to store? What I want to store is the reference on a key list ID that really exists. And as soon as the key list ID does not exist any more (i.e. because of the last occurence has been deleted), the reference on that value (not row!) shall be set to NULL. Regarding it this way, my hope to get this done with RDBMS means shrinks... (Sorry for double sending, I hit the Reply button first...) -- Yves Goergen LonelyPixel [EMAIL PROTECTED] Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SOLVED: Problem with *very* slow replication, FreeBSD 6.2
On Sat, 3 Nov 2007, bob b wrote: Good to hear that you found the problem. The only remaining puzzle is why the replica reported that it was up to date when it was several binlogs behind. Possibly the replica was always caught up with the last entry from the very slow link. Perhaps you should report this as a bug? The replication mechanism should be able to check the last binlog being written on the master and report that difference? Bob Bankay (from home) The reporting confusion is due to the fact that the seconds behind master figure is based on the relay logs and how long it will take to catch up. For example, I had replication shut down for 45 minutes wile feeding millions of writes into the master. On slave restart the binlog dump started, and it went fast. As the relay log grew so did seconds behind master. One the relay log was up to date, the seconds behind master was based on the execution rate and backlog. (Somthing like 12 minutes and counting down) So, a slave is down for 8hrs. It comes online and pulls the binlog in 120 seconds. The seconds behind master does not reflect 8hrs, but how many seconds (at current processing rate) before the slave finishes the relay logs. The seconds behind master value is really seconds until currency with the relay logs and should prolly be documented as such. It would be nice if there was a way for the slave to find the actual current master position and compare with the local state though. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SOLVED: Problem with *very* slow replication, FreeBSD 6.2
Christopher E. Brown wrote: On Sat, 3 Nov 2007, bob b wrote: So, a slave is down for 8hrs. It comes online and pulls the binlog in 120 seconds. The seconds behind master does not reflect 8hrs, but how many seconds (at current processing rate) before the slave finishes the relay logs. The seconds behind master value is really seconds until currency with the relay logs and should prolly be documented as such. This is incorrect. In most circumstances, it's basically the difference between the timestamp of the binlog event the SQL thread is currently processing, and the master's current timestamp (as fetched by the I/O thread). So it really is what it sounds like: the seconds behind the master. If it says 100, it means the slave is processing an event that took place 100 seconds ago on the master. You can read the source code in show_master_info() in sql/slave.cc. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
