[GENERAL] OT DBA type question - GRANT PRIVILEDGE
Do many of you implement fine grained security provisions at the DBMS level using GRANT PRIVILEGE ON object TO user/role? My background with DBMS security is mostly in CODASYL shallow network systems where user level access control to datasets and fields was mainly a matter for the application and / or operating system. Therefor the number of user ids known to the DBMS was minimal. The topic of RDBMS security has arisen in a discussion and, lacking evidence of my own, I am curious to discover just how frequently DBMS userids tied to specific individuals are used in production RDBMS based systems. I am also curious to know how often VIEWS are tied to individual user IDs known to the DBMS rather than to shared user IDs known only to an application through a configuration file. This is not really a PG specific question so if anyone wishes to reply privately rather than to the list that is fine with me. Thanks, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OT DBA type question - GRANT PRIVILEDGE
On 12/8/2011 3:46 PM, James B. Byrne wrote: Do many of you implement fine grained security provisions at the DBMS level using GRANT PRIVILEGE ON object TO user/role? My background with DBMS security is mostly in CODASYL shallow network systems where user level access control to datasets and fields was mainly a matter for the application and / or operating system. Therefor the number of user ids known to the DBMS was minimal. The topic of RDBMS security has arisen in a discussion and, lacking evidence of my own, I am curious to discover just how frequently DBMS userids tied to specific individuals are used in production RDBMS based systems. I am also curious to know how often VIEWS are tied to individual user IDs known to the DBMS rather than to shared user IDs known only to an application through a configuration file. This is not really a PG specific question so if anyone wishes to reply privately rather than to the list that is fine with me. Thanks, This is probably a bad example. Or maybe a good example of how not to do it. I have a generic user, with create user privileges. When users run my app (its a windows delphi app) they type in their username/password, I try to connect as them, and if it doesnt work I connect as the generic user, create them, and reconnect as them. I pretty much use the user to see who is connected, and for audit logging. I have the rights and stuff in a user table, and the delphi app does all the rights checking. On the website side, I always connect as a webuser, who has full read/write. Even though the website is read only. Err.. I do keep stats, so its mostly read only. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OT DBA type question - GRANT PRIVILEDGE
On 12/09/2011 05:46 AM, James B. Byrne wrote: The topic of RDBMS security has arisen in a discussion and, lacking evidence of my own, I am curious to discover just how frequently DBMS userids tied to specific individuals are used in production RDBMS based systems. I am also curious to know how often VIEWS are tied to individual user IDs known to the DBMS rather than to shared user IDs known only to an application through a configuration file. User IDs are often used to help secure multi-tenanted databases. I just wrote about this in response to another question, see: http://stackoverflow.com/questions/8432636/in-postgresql-are-partitions-or-multiple-databases-more-efficient/8439618#8439618 Individual user IDs are often useful the same way, via `SET ROLE' from an unpriveleged account a connection pool uses. Because of connection establishment overheads and the need to pool connections I'm generally reluctant to use setups where the app auths against the database with a given user ID and password directly. -- Craig Ringer
