Re: 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