Re: Trigger with current user

From: "Nicolas Kowalski" <Nicolas(dot)Kowalski(at)imag(dot)fr>

> - if the current user is declared as one of the lists maintainers
> ("SELECT maintainer FROM sys_mailing_lists;"), he(she) will be able to
> SELECT, INSERT, UPDATE & DELETE rows in the 'sys_mailing_members' table,
>
> - if not he(she) will only be able to do SELECT's on
> 'sys_mailing_members'.
>
>
> So, I thought using triggers. However, I am missing some elements :

Not quite the right approach (see below)

> - how can I get back the currently connected username ?

There is a magic value: select CURRENT_USER;

> - when using a "BEFORE" trigger, how can I cancel the
> INSERT/UPDATE/DELETE actions to be performed if the user connected does
> not match the access permissions ?

In your case I would look into the GRANT and REVOKE commands (in the SQL
reference). You can set up two user-groups and do something like:

GRANT ALL ON sys_mailing_members TO GROUP listadmins;
GRANT SELECT ON sys_mailing_members TO GROUP justusers;

You'll need to set up groups with CREATE GROUP and ALTER GROUP (see
"Database Users and Permissions" chapter of the docs)

You will need to resort to rules and triggers if you want more complex
controls (e.g. managers can change their team-member's diary but only
weekdays or similar)

HTH

- Richard Huxton