Subject Re: [IBO] First stab at applying security
Author Geoff Worboys
> I need more control over the Member role though. I want Member to be
> able to insert and select anything but only update the members own
> entries. The member login name is saved with each record. I would
> like as much of this to be controlled by IB as possible rather than
> by the client app. These rights are currently for a single table
> only.

SQL security can be controlled to the table, and even to particular
columns. But it cannot be controlled to the row. The only way of
enforcing row level security is via views, triggers and via client

In triggers you can check USER (CURRENT_USER and CURRENT_ROLE is
availabe in new Firebird release), and so determine whether you want
to allow the current user/role to be allowed to perform some action on
the particular row.

You can effect row level select security by setting up views that only
select records according to the current user/role. Dont give the user
table access, give them access to the view. Alternatively you can
build some sort of enforced filtering into the client software.

You may already know this...

One common misunderstanding is the way roles work. You can make a user
a member of multiple roles, but they can only login under only ONE
role at a time. While logged in under that role, they can do whatever
that role permits BUT they cannot do anything else - even though they
may also be a member of a different role that does have the necessary

Or to put it another way. Its not like WindowsNT/2K based security,
where membership of multiple groups is cumulative. When the user logs
in they give a role, indicating who they want to be right now. To
perform action requiring different access they must log out and log
back in with the necessary role.

(I hope that is clear.)


Geoff Worboys
Telesis Computing