Subject Re: [firebird-support] Grant privleges - HELP
Author Helen Borrie
At 04:19 AM 22/02/2007, you wrote:
>I have Firebird 1.5 and it seems to have been fine for the past year
>but something has happened to privleges that has me stumped.
>Using the Firebird ISQL tool that came with Firebird I revoked ALL
>privledges for a user to all tables in my DB and confirmed it had
>worked by looking at the privledges in IBEasy. It showed NO privleges
>for the user for all tables in the DB. I log onto the DB with IBEasy as
>the revoked user and it won't allow me to update a record but I can add
>and delete records. I thought perhaps it was IBEasy but using the ISQL
>tool I was able to connect as the user with NO privleges and delete
>records. This is a real killer since my application relies on the
>privleges to keep specific users from deleting from the DB. I'm pretty
>sure this worked in the past but in playing with the DB I've messed up
>Any help would be greatly appreciated. Thanks, Bob Ritter

Revoking specific or generalised privileges does not block that user
if it would get the same privileges via the PUBLIC user or from a
ROLE that it has been granted.

You really need to DESIGN your privileges schema on paper as a truth
table and deploy your system with every user and every privilege on
every object covered once and once only. The "right" way to do this
is to design one role for each set of privileges and not to grant
privileges (other than the appropriate role) to users at all.

By the way, adding and deleting records to and from
RDB$USER_PRIVILEGES is *not* the way to modify permissions. If this
is what you did then, yes, you will have screwed up the Access
Control List that is maintained in the database by the server in
response to properly committed GRANT, REVOKE and CREATE/DROP ROLE statements.

And remember, if you have pushed a GRANT, REVOKE or CREATE ROLE
statement from one tool, e.g. IBEasy, then that tool will see updates
to the RDB$USER_PRIVILEGES table reflecting what you have requested
in *that* transaction, even though it is uncommitted. At that point,
there will be changes to the ACL that are not visible yet, since they
will occur after the requests are committed.

Inspecting the privileges table from another tool (== a different
transaction) you won't see the uncommitted changes that you did in
IBEasy. Privileges for that user in a different transactions will be
just as they were before your IBEasy transaction began.