ok so what would prevent users from using SQL tools to connect to the database
and browse data?
> As far as AD is concerned, I think Bruno Wolff described what to do best:
> 1) Configure PostgreSQL to authenticate via PAM (pam_ldap)
> 2) Create a table of appgroups & groupmembers that defines the
application groups and their members, respectively
> 3) Create views over the actual data that test for the appropriate
group membership.
> You can write your own function to simply the task:
> CREATE FUNCTION isMember(text, text) RETURNS bool AS '
SELECT true
FROM appgroups, groupmembers
WHERE appgroups.name = $1 AND
appgroups.appgroup = groupmembers.appgroup AND
groupmembers.userid = $2;
' LANGUAGE 'sql';
> 4) Now, if you only want people in the 'Administration' group to view
salaries:
CREATE VIEW v_salaries AS
SELECT *
FROM salaries
WHERE isMember('Administration', CURRENT_USER);
> Or you could do the join against the base table for row-level security:
CREATE VIEW v_salaries AS
SELECT *
FROM salaries, groupmembers
WHERE salaries.appgroup = groupmembers.appgroup AND
groupmembers.userid = CURRENT_USER;
> 5) REVOKE SELECT on salaries from the public and GRANT select on
v_salaries to the public.
HTH,
There is a security problem here if users are able to create their own
functions:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=3D02B372.B6A4EFB6%40mascari.com
HTH,
Mike Mascari
mascarm@mascari.com
--
http://mail.python.org/mailman/listinfo/python-list
