Subject Re: [IBO] First stab at applying security
Author Martijn Tonies

"Geoff Worboys" <geoff@...> schreef in bericht
> > 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
> implementation.
> 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.

Don't forget to mention that USER is the way to check the current user
for InterBase.

> 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.

Adding a USER_NAME column to the table, fill it with a
BEFORE INSERT trigger and create a view like:

select * from table where user_name = USER (or CURRENT_USER)

> 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
> permission.

Personally, I prefer to call these 'groups'. And I would very much like
to see them appear in InterBase/Firebird.

Martijn Tonies
Upscene Productions

InterBase Workbench - The Developer Tool for InterBase

"Experience is what you get when you didn't get what you wanted"