Обсуждение: 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. Byrne mailto:ByrneJB@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
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
On 12/09/2011 05:46 AM, James B. Byrne wrote:
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
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
On Thu, December 8, 2011 20:23, Craig Ringer wrote: On Thu, December 8, 2011 17:28, Andy Colson wrote: These are the only replies I received. Am I to conclude that most of the people on the list do not use GRANT PRIVILEGE to implement anything more than the minimal authorization scheme required to obtain access for anyone? Nil reports, as in: "Generally, we do not use PostgreSQL's GRANT PRIVILEGE to implement detailed user ACLs", would be most welcome. If instead you are using GRANT PRIVILEGE and have not responded, a simple "We (often, occasionally, seldom) use detailed GRANT PRIVILEGE based user ACLs" is sufficient. I really want to get a sense of how prevalent using GRANT PRIVILEGE, beyond the minimum required, is. And this seems like a very good place to discover it. Again, please reply off-list if you wish. -- *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@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
On Fri, December 9, 2011 12:13, James B. Byrne wrote: Just to clarify the question. What I am asking basically comes down to if separate user ids are added for most individuals that access the database or not. -- *** E-Mail is NOT a SECURE channel *** James B. Byrne mailto:ByrneJB@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
On Fri, Dec 9, 2011 at 10:43 AM, James B. Byrne <byrnejb@harte-lyne.ca> wrote: > > On Fri, December 9, 2011 12:13, James B. Byrne wrote: > > Just to clarify the question. What I am asking basically > comes down to if separate user ids are added for most > individuals that access the database or not. Most of the time I create a role, grant / revoke privileges there, and then grant that role to individuals as needed. I almost never grant / revoke privileges to an individual
On Fri, Dec 09, 2011 at 12:13:59PM -0500, James B. Byrne wrote: > I really want to get a sense of how prevalent using GRANT > PRIVILEGE, beyond the minimum required, is. And this > seems like a very good place to discover it. Again, please > reply off-list if you wish. I used it this year in a system that was designed to mimic a complicated Oracle mutli-user set up. I used a number of schemas, the search_path, and a lot of GRANTs to make everything work reliably in the cases where there was shared data across the users. It seemed to work for me. A -- Andrew Sullivan ajs@crankycanuck.ca
On Fri, Dec 09, 2011 at 12:13:59PM -0500, James B. Byrne wrote: > I really want to get a sense of how prevalent using GRANT > PRIVILEGE, beyond the minimum required, is. And this > seems like a very good place to discover it. Again, please > reply off-list if you wish. I used it this year in a system that was designed to mimic a complicated Oracle mutli-user set up. I used a number of schemas, the search_path, and a lot of GRANTs to make everything work reliably in the cases where there was shared data across the users. It seemed to work for me. A -- Andrew Sullivan ajs@anvilwalrusden.com