I only have a local root user for DBA pruposes, the rest will be DB specific roles (If I can do it properly).
Users have no privileges except CONNECT to the DB. All the privileges are granted to group roles. Users are assigned groups to have access to DB functionality which are all stored in functions. There is a "guests" group role for public access which is locked down to the basic data stuff, minimum required to display stuff on the website. Then there is "administrators" group role for the admin of the system. A user member of that group can then start creating new groups add assign them privileges (application + DB - they are synched) and then create new users and assign them groups. Everything is built so that the modularity of the application side can be respected, I just need to drop in a new application module, update the application privileges table, add the new functions and sync them together (M to M table mapping) and the system will now detect the new possibilities itself and allow an admin to add the new module privileges to already existing groups or create a new group for them. I'll test all that [EMAIL PROTECTED] stuff and see what happens and if it works properly and report back on it. Is there a place where I can see what features were suggested and where I could suggest my own? Thanks David On 2/21/07, Richard Huxton <dev@archonet.com> wrote:
David Legault wrote: > From the docs > > db_user_namespace (boolean) > It doesn't talk about this in the CREATE ROLE docs though so it's a bit > ambiguous and the note saying this is a temp measure means they are > thinking > of something better for the future I assume. Well, it's clearly not ideal, but it's probably a fair bit of work to have global AND database-specific users in a clean way, and there's just not been the demand for it. > I'm trying to build a web managed system for my apps (PHP) where I can > manage ROLES (users/groups) using an admin area of the site without having > to touch the DB directly like a DBA. This enables the customer to set > himself the access levels of the groups which are assigned to their > different users. Restricting roles to databases is what I want > ultimately. I > use the DB role system as the auth mecanism of the website too, so no > actual > DB super user has access to the DB and is stored in server PHP code. If the > server is compromised, there is still another layer before reaching the DB > data. Hmm - never gone quite that far myself. You've got to balance the prospects of someone gaining access to your PHP code versus the risks of handing out database passwords to all your users. Recently I've been using one user my app connects as, then use SET ROLE to switch to individual user-types or users. Not proof against hackers (except the most stupid), but it does prevent e.g. accidental changes to lookup tables. It'd be nice to have a LOCK option on the SET ROLE, but again, it depends on demand. Then, I have a separate user who owns the database and I use that user for admin tasks (backups etc). > If there was an option to force each new ROLE to have no connexion > privileges to any DB until I set one via GRANT would also be good. Else > I'll > have to revoke all DBs when I create it and then GRANT only the single > one I > want. Yep - REVOKE public from all databases and then anything after that will have to be a member of a group you've explicitly GRANTed. -- Richard Huxton Archonet Ltd
