Re: [PHP] Database Administration
Thanks for the replies, they have been most enlightening. :)
Re: [PHP] Database Administration
On Fri, Sep 24, 2010 at 3:50 PM, Bob McConnell wrote: > From: tedd > >>At 2:36 PM -0400 9/24/10, Bastien Koert wrote: >>>On Fri, Sep 24, 2010 at 2:26 PM, tedd wrote: At 2:09 PM -0400 9/24/10, Bastien Koert wrote: > > �...@tedd, > > He wants not techie users to create new systems for their clients > when > they sign up. It involves creating a DB and he's wondering about > security for that. The main part of the app needs the least priv's > to > run (select, update, insert [,delete]) while the creating the DB > obviously takes more. The OP was asking how to best handle that > since > the he didn't want to give the main app DB user account more privs > than needed. Okay, what does creating "new systems for their clients" mean? What I want to know is specifically what these non-techie users > intend to do? Please don't answer that they want to set up accounts for their > clients because that is meaningless to me. That could mean anything. >>> > So, what specifically are these non-techie users going to do? >>> >>>Create a DB schema, create and populate tables. >> >> Creating a DB schema is not for non-techies -- you really need to >> know what you are doing to do this. >> >> But we all live with what we create. > > I suspect he actually means create a new table using a predefined > schema. But unfortunately, he doesn't appear to know enough about the > problem to be able to explain it. He is either in way over his depth, or > hasn't done a very good job of defining his requirements. > > Bob McConnell > The OP mentioned that each new client needed their own DB so that is how I took it. Having exactly the exact same tables in the structure with different names is just bad practice to. That just screams creating a multi-tenant DB. At work we do create each DB as a clone of a master table, but it is done manually and thankfully not that often. -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Database Administration
From: tedd >At 2:36 PM -0400 9/24/10, Bastien Koert wrote: >>On Fri, Sep 24, 2010 at 2:26 PM, tedd wrote: >>> At 2:09 PM -0400 9/24/10, Bastien Koert wrote: @tedd, He wants not techie users to create new systems for their clients when they sign up. It involves creating a DB and he's wondering about security for that. The main part of the app needs the least priv's to run (select, update, insert [,delete]) while the creating the DB obviously takes more. The OP was asking how to best handle that since the he didn't want to give the main app DB user account more privs than needed. >>> >>> Okay, what does creating "new systems for their clients" mean? >>> >>> What I want to know is specifically what these non-techie users intend to >>> do? >>> >>> Please don't answer that they want to set up accounts for their clients >>> because that is meaningless to me. That could mean anything. >>> >> > So, what specifically are these non-techie users going to do? >> >>Create a DB schema, create and populate tables. > > Creating a DB schema is not for non-techies -- you really need to > know what you are doing to do this. > > But we all live with what we create. I suspect he actually means create a new table using a predefined schema. But unfortunately, he doesn't appear to know enough about the problem to be able to explain it. He is either in way over his depth, or hasn't done a very good job of defining his requirements. Bob McConnell -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
At 2:36 PM -0400 9/24/10, Bastien Koert wrote: On Fri, Sep 24, 2010 at 2:26 PM, tedd wrote: At 2:09 PM -0400 9/24/10, Bastien Koert wrote: @tedd, He wants not techie users to create new systems for their clients when they sign up. It involves creating a DB and he's wondering about security for that. The main part of the app needs the least priv's to run (select, update, insert [,delete]) while the creating the DB obviously takes more. The OP was asking how to best handle that since the he didn't want to give the main app DB user account more privs than needed. Okay, what does creating "new systems for their clients" mean? What I want to know is specifically what these non-techie users intend to do? Please don't answer that they want to set up accounts for their clients because that is meaningless to me. That could mean anything. > So, what specifically are these non-techie users going to do? Create a DB schema, create and populate tables. Creating a DB schema is not for non-techies -- you really need to know what you are doing to do this. But we all live with what we create. Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
On Fri, Sep 24, 2010 at 2:26 PM, tedd wrote: > At 2:09 PM -0400 9/24/10, Bastien Koert wrote: >> >> @tedd, >> >> He wants not techie users to create new systems for their clients when >> they sign up. It involves creating a DB and he's wondering about >> security for that. The main part of the app needs the least priv's to >> run (select, update, insert [,delete]) while the creating the DB >> obviously takes more. The OP was asking how to best handle that since >> the he didn't want to give the main app DB user account more privs >> than needed. > > Okay, what does creating "new systems for their clients" mean? > > What I want to know is specifically what these non-techie users intend to > do? > > Please don't answer that they want to set up accounts for their clients > because that is meaningless to me. That could mean anything. > > So, what specifically are these non-techie users going to do? > > Cheers, > > tedd > > > -- > --- > http://sperling.com/ > Create a DB schema, create and populate tables. -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
At 2:09 PM -0400 9/24/10, Bastien Koert wrote: @tedd, He wants not techie users to create new systems for their clients when they sign up. It involves creating a DB and he's wondering about security for that. The main part of the app needs the least priv's to run (select, update, insert [,delete]) while the creating the DB obviously takes more. The OP was asking how to best handle that since the he didn't want to give the main app DB user account more privs than needed. Okay, what does creating "new systems for their clients" mean? What I want to know is specifically what these non-techie users intend to do? Please don't answer that they want to set up accounts for their clients because that is meaningless to me. That could mean anything. So, what specifically are these non-techie users going to do? Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
On Fri, Sep 24, 2010 at 2:05 PM, tedd wrote: > At 11:19 AM +0100 9/24/10, Tom Barrett wrote: >> >> On 22 September 2010 21:40, Bastien Koert wrote: >> >>> Not at all. What I would suggest is that you create a separate mysql >>> user that is used exclusively by the script to do the create stuff. >>> The regular application user account should not have those privileges >>> at all. >>> >> >> I'm not actually that familiar with DB admin to that extent. I have either >> app users with lock+crud on specific databases, or root. As a an aside, >> would you know if there is a level of permissions for a user between app >> and >> root that would be 'sensibly secure' (it will be MySQL 5)? >> >> >>> Another option, if immediate response is not required, is to save this >>> data into the system for a cron script with another user account to >>> run. >>> >> >> This was sort of my first instinct. I ponder writing a small daemon/cron >> that queries a database table (client list) and does all the 'build' bits. >> The main issue with cron is that the users would want a fairly immediate >> response. Seconds is acceptable, but a 5 minute cron might be too slow. >> >> >>> Is there a reason for you not to place all the data in one DB and just >>> separate them out based on user id, to ensure they only see their own >>> data >>> >> >> For legal reasons. Each client must have separate data. I need to be able >> to >> box up all the client data (containing multiple app instances) and be 100% >> sure that I am giving them all their data and nobody else's. >> >> On 23 September 2010 18:04, tedd wrote: >> >>> No, but from what you've said, I don't think the end user must have >>> privileges and the ability to create a database and tables. It sounds >>> more >>> like allowing the user to set up his own admin for acceptable users -- >>> there's a big difference. >>> >>> So, what you need to define is what the client and his users want to do. >>> From that, we can determine what they need. >> >> >> Depending on what you mean by 'the client', all the client side things are >> fine :) >> The web front-end I am working on here is for internal use only. To allow >> non-technical people to set up clients and their apps. >> >> The more I look into this, the more I am leaning towards some shell >> scripts >> for client management, invoking them by cron. Then if an immediate >> response >> is needed someone technical will have to manually run the cron job. It >> looks >> like the law of diminishing returns for me to build something really >> usable. > > The more I hear, the more confused I get. > > I still don't understand what your client is going to do? > > Cheers, > > tedd > > -- > --- > http://sperling.com/ > > -- > PHP General Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > @tedd, He wants not techie users to create new systems for their clients when they sign up. It involves creating a DB and he's wondering about security for that. The main part of the app needs the least priv's to run (select, update, insert [,delete]) while the creating the DB obviously takes more. The OP was asking how to best handle that since the he didn't want to give the main app DB user account more privs than needed. -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
At 11:19 AM +0100 9/24/10, Tom Barrett wrote: On 22 September 2010 21:40, Bastien Koert wrote: Not at all. What I would suggest is that you create a separate mysql user that is used exclusively by the script to do the create stuff. The regular application user account should not have those privileges at all. I'm not actually that familiar with DB admin to that extent. I have either app users with lock+crud on specific databases, or root. As a an aside, would you know if there is a level of permissions for a user between app and root that would be 'sensibly secure' (it will be MySQL 5)? Another option, if immediate response is not required, is to save this data into the system for a cron script with another user account to run. This was sort of my first instinct. I ponder writing a small daemon/cron that queries a database table (client list) and does all the 'build' bits. The main issue with cron is that the users would want a fairly immediate response. Seconds is acceptable, but a 5 minute cron might be too slow. Is there a reason for you not to place all the data in one DB and just separate them out based on user id, to ensure they only see their own data For legal reasons. Each client must have separate data. I need to be able to box up all the client data (containing multiple app instances) and be 100% sure that I am giving them all their data and nobody else's. On 23 September 2010 18:04, tedd wrote: No, but from what you've said, I don't think the end user must have privileges and the ability to create a database and tables. It sounds more like allowing the user to set up his own admin for acceptable users -- there's a big difference. So, what you need to define is what the client and his users want to do. From that, we can determine what they need. Depending on what you mean by 'the client', all the client side things are fine :) The web front-end I am working on here is for internal use only. To allow non-technical people to set up clients and their apps. The more I look into this, the more I am leaning towards some shell scripts for client management, invoking them by cron. Then if an immediate response is needed someone technical will have to manually run the cron job. It looks like the law of diminishing returns for me to build something really usable. The more I hear, the more confused I get. I still don't understand what your client is going to do? Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
On Fri, Sep 24, 2010 at 6:19 AM, Tom Barrett wrote: [snip] > I'm not actually that familiar with DB admin to that extent. I have either > app users with lock+crud on specific databases, or root. As a an aside, > would you know if there is a level of permissions for a user between app and > root that would be 'sensibly secure' (it will be MySQL 5)? It depends on the app, but phrases like 'sensibly secure' raise caution flags for me. I tend to go with the principle of least privilege. Where I currently work, the admin functions for a web application are usually on an intranet site that is completely separate from the public site. Because of this, I have a different database user for each site. In this case, these are database-only logins unrelated in any way to the actual machine account used by the web servers. On our newer development, nearly all table access is managed strictly through stored procedures (we use SQL Server, but the same would work for MySQL if you were so inclined), and each database user is only granted execute permission on the specific procedures necessary for that role. The only time we grant access directly to a table is in cases where we just can't get a procedure to do what we need efficiently or effectively. And, in those cases where I do need to grant access to a table, I grant permission to only the columns/operations necessary for that user. If I encountered a case where I needed to allow a user to make schema changes as you mentioned in your original post, I would create a totally separate account -- again with no more permission than necessary for its intended task. Depending on the needs of the application, I'd decide whether that account was used by the web server or via a script scheduled to execute at intervals as several others have suggested in this thread. I've not tried this, but you could probably write the logic needed to create the database objects into a stored procedure. Then, you might only need to grant permission to that procedure and not grant permission to CREATE/ALTER anything. That would pretty well guarantee that the only objects created are the ones you intended. Andrew -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
On 22 September 2010 21:40, Bastien Koert wrote: > Not at all. What I would suggest is that you create a separate mysql > user that is used exclusively by the script to do the create stuff. > The regular application user account should not have those privileges > at all. > I'm not actually that familiar with DB admin to that extent. I have either app users with lock+crud on specific databases, or root. As a an aside, would you know if there is a level of permissions for a user between app and root that would be 'sensibly secure' (it will be MySQL 5)? > Another option, if immediate response is not required, is to save this > data into the system for a cron script with another user account to > run. > This was sort of my first instinct. I ponder writing a small daemon/cron that queries a database table (client list) and does all the 'build' bits. The main issue with cron is that the users would want a fairly immediate response. Seconds is acceptable, but a 5 minute cron might be too slow. > Is there a reason for you not to place all the data in one DB and just > separate them out based on user id, to ensure they only see their own > data > For legal reasons. Each client must have separate data. I need to be able to box up all the client data (containing multiple app instances) and be 100% sure that I am giving them all their data and nobody else's. On 23 September 2010 18:04, tedd wrote: > No, but from what you've said, I don't think the end user must have > privileges and the ability to create a database and tables. It sounds more > like allowing the user to set up his own admin for acceptable users -- > there's a big difference. > > So, what you need to define is what the client and his users want to do. > From that, we can determine what they need. Depending on what you mean by 'the client', all the client side things are fine :) The web front-end I am working on here is for internal use only. To allow non-technical people to set up clients and their apps. The more I look into this, the more I am leaning towards some shell scripts for client management, invoking them by cron. Then if an immediate response is needed someone technical will have to manually run the cron job. It looks like the law of diminishing returns for me to build something really usable.
Re: [PHP] Database Administration
At 9:35 PM +0100 9/22/10, Tom Barrett wrote: Hmm.. I am familiar with PMA. I would for the purpose of this project consider it too technical for the target user base. The point is to create a GUI layer that would manage these things. For example, the 'add client' screen would ask for four things; name, description, username and password. Then behind the scenes a database would be created, the user created, permissions granted and a pre-configure set of tables built (and populated). My reservations come from security issues (which, as an aside, are also discussed about PMA), allowing a normal user account CREATE and GRANT on the database. Maybe I'm being too fuddy-duddy cautious. Tom: No, but from what you've said, I don't think the end user must have privileges and the ability to create a database and tables. It sounds more like allowing the user to set up his own admin for acceptable users -- there's a big difference. So, what you need to define is what the client and his users want to do. From that, we can determine what they need. Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
On Wed, Sep 22, 2010 at 4:35 PM, Tom Barrett wrote: > Hmm.. > > I am familiar with PMA. I would for the purpose of this project consider it > too technical for the target user base. The point is to create a GUI layer > that would manage these things. > > For example, the 'add client' screen would ask for four things; name, > description, username and password. Then behind the scenes a database would > be created, the user created, permissions granted and a pre-configure set of > tables built (and populated). > > My reservations come from security issues (which, as an aside, are also > discussed about PMA), allowing a normal user account CREATE and GRANT on the > database. > > Maybe I'm being too fuddy-duddy cautious. > Not at all. What I would suggest is that you create a separate mysql user that is used exclusively by the script to do the create stuff. The regular application user account should not have those privileges at all. Another option, if immediate response is not required, is to save this data into the system for a cron script with another user account to run. Is there a reason for you not to place all the data in one DB and just separate them out based on user id, to ensure they only see their own data? -- Bastien Cat, the other other white meat -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
Hmm.. I am familiar with PMA. I would for the purpose of this project consider it too technical for the target user base. The point is to create a GUI layer that would manage these things. For example, the 'add client' screen would ask for four things; name, description, username and password. Then behind the scenes a database would be created, the user created, permissions granted and a pre-configure set of tables built (and populated). My reservations come from security issues (which, as an aside, are also discussed about PMA), allowing a normal user account CREATE and GRANT on the database. Maybe I'm being too fuddy-duddy cautious.
Re: [PHP] Database Administration
At 10:48 AM +0100 9/21/10, Tom Barrett wrote: Hi I need to build a custom client management app, which will build and manage a database per client. This means that on top of the usual sql crud, it needs to be able to create databases, add/edit/delete database users, create tables. Is there a way for me to do this nicely as PHP solution? am I better off incorporating non PHP pieces into this (e.g. shell)? or should I leave the admin tasks (e.g. database creation) as a 'normal' administrative task (commandline/webmin/watever)? I'm not sure as to what you need, but for me I do all my database creation in phpMyAdmin. From there I populate with php. Cheers, tedd -- --- http://sperling.com/ -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Database Administration
[snip] I need to build a custom client management app, which will build and manage a database per client. This means that on top of the usual sql crud, it needs to be able to create databases, add/edit/delete database users, create tables. Is there a way for me to do this nicely as PHP solution? am I better off incorporating non PHP pieces into this (e.g. shell)? or should I leave the admin tasks (e.g. database creation) as a 'normal' administrative task (commandline/webmin/watever)? [/snip] Have you thought about using phpMyAdmin? http://www.phpmyadmin.net/home_page/index.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Database Administration
Possible. Google phpmyadmin I believe it is completely written in PHP and does complete database administration (has some weaknesses on stored procedures though in my view) Jangita | +254 76 918383 | MSN & Y!: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -Original Message- From: Tom Barrett [mailto:t...@miramedia.co.uk] Sent: 21 September 2010 11:48 AM To: PHP General List Subject: [PHP] Database Administration Hi I need to build a custom client management app, which will build and manage a database per client. This means that on top of the usual sql crud, it needs to be able to create databases, add/edit/delete database users, create tables. Is there a way for me to do this nicely as PHP solution? am I better off incorporating non PHP pieces into this (e.g. shell)? or should I leave the admin tasks (e.g. database creation) as a 'normal' administrative task (commandline/webmin/watever)? -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database Administration
On 21 September 2010 11:48, Tom Barrett wrote: > Hi > > I need to build a custom client management app, which will build and manage > a database per client. This means that on top of the usual sql crud, it > needs to be able to create databases, add/edit/delete database users, create > tables. > > Is there a way for me to do this nicely as PHP solution? am I better off > incorporating non PHP pieces into this (e.g. shell)? or should I leave the > admin tasks (e.g. database creation) as a 'normal' administrative task > (commandline/webmin/watever)? > Seeing as all the "extra" stuff you need is just plain sql commands, I don't see the problem as such. You just need to make sure access to the tool is done right (i.e. a user that can create/destroy databases needs to be aware of this and you need to restrict the access to those specific persons). Regards Peter -- WWW: http://plphp.dk / http://plind.dk LinkedIn: http://www.linkedin.com/in/plind BeWelcome/Couchsurfing: Fake51 Twitter: http://twitter.com/kafe15 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database administration framework
The DB ist mysql. No, you didn't understand me clearly. I need a framework, toolkit, class call it how you want, with wich I develop an application that administrates the data's in the database. Something like Smarty but more specific for what I need. The ideea is the following: I have 20 tables that have data's in them. And these table data's must be edited with this admin tool. There are very few joins but there are references between the tables. You could try MySQL TableEditor: http://www.phpguru.org/static/TableEditor.html It's a (big) one file class which is good for editing the tables, though it's not phpMyAdmin; you can't "administer" the database. -- Richard Heyes 0844 801 1072 http://www.websupportsolutions.co.uk Knowledge Base and HelpDesk software -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Database administration framework
The DB ist mysql. No, you didn't understand me clearly. I need a framework, toolkit, class call it how you want, with wich I develop an application that administrates the data's in the database. Something like Smarty but more specific for what I need. The ideea is the following: I have 20 tables that have data's in them. And these table data's must be edited with this admin tool. There are very few joins but there are references between the tables. Regards, Andy. > -Original Message- > From: Chris Boget [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 26, 2007 3:29 PM > To: Andy; php-general@lists.php.net > Subject: Re: [PHP] Database administration framework > > > I just need a framework for administrating tables in a > database. These > > are simple add/edit/remove operations from tables. > > Can you suggest a framework for this kind of job? Cause there are a > > lot of tables and I hope I can find a nice tool to work with. > > What database are you working with? phpMyAdmin is probably > the most ubiquitous when working with MySQL databases. > > thnx, > Chris > > > -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Database administration framework
> Hi, > > I just need a framework for administrating tables in a database. These are > simple add/edit/remove operations from tables. > > Can you suggest a framework for this kind of job? Cause there are a lot of > tables and I hope I can find a nice tool to work with. > Look at Qcodo (http://www.qcodo.com). Edward -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Database administration framework
I just need a framework for administrating tables in a database. These are simple add/edit/remove operations from tables. Can you suggest a framework for this kind of job? Cause there are a lot of tables and I hope I can find a nice tool to work with. What database are you working with? phpMyAdmin is probably the most ubiquitous when working with MySQL databases. thnx, Chris -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
