Hi All,

I am sitting here once again fighting a losing battle with
Firebird/SQL security and thought I would finally post some
thoughts on the subject.

This is not really an RFC, more like an RFI: request for ideas.
I am not really certain what would make the most sense to solve
this issue... I guess it is even possible that you have already
discussed ideas here that I have missed (hope not, I did look).


Background:

The systems I design do not want the users executing any DDL
(except grant/revoke during user maintenance). [I see that in
Feb' Adiano submitted a proposal (CORE-2310) for DDL triggers
that may finally provide a way to enforce this requirement (my
current work-around relies on the obscurity of closed source).]

Most tables in the system implement triggers that assume they
are reacting to normal user (interactive user) input, and
implement validation, auditing and automation accordingly...
however the triggers also want to allow such things as:

. automated import/transfer/creation of data for which it may
(sometimes) be assumed validation has already occured or
auditing is not required. For example it may simply be
that you do not want the "record created" timestamp updated
on this imported item, you may want to keep the existing
timestamp from data copied from another table/database.
[For audit purposes you dont want a normal user to be able
to change the timestamp on their normal inputs - but for
system controlled processing, initiated by that same user,
it may be ok.]

. setting simple boolean flags on a record to lock or unlock
it in response to some system event - BUT such locking
should be system controlled, and not available to the user.
The system event may have occured in response to a normal
user's actions (creation of an invoice or whatever).

It is possible to implement the above items via separate tables
that have different privileges to the main table... but this
adds significant complexity of an already complex database, and
adds overheads and complexity to using the tables (maintenance
of the foreign keys etc etc etc).

The problem can also be described in this way: I can give a
procedure/trigger more access than the user that executes it,
but I cannot (as far as I know) write a trigger that chooses to
do something different if it is the result of an update from a
procedure that is using raised privileges.

At the moment I fudge this via a hidden flags on the table;
when the flag is turned on the trigger/procedure assumes it is
in response to a system event. The problem with this is that
it relies on security by obscurity. It works because the
installations try very hard to prevent users from gaining
access to the database via any but this authorised application,
but it is not a good solution.



Possibilities:

1. I had originally thought to suggest that we make it so the
procedure can execute in the context of the procedure owner, or
of a specified user... but that is not a good idea. Most of
the triggers still want to know the true user that initiated
the update (for audittrail etc), so we want CURRENT_USER to
continue to reflect the connection user.


2. Another possibility (I believe the best suggestion of those
offered in this email) is to allow the procedure to execute as
a specific role. Assign/grant a role to a procedure (just one
until/unless we start to support roles that merge privileges)
and when executed the procedure would use that role. We then
allow that role (raised privilege) to be detected in SQL code.

The role context would be created when the procedure starts and
revert when procedure ends. This seems to have possibilities,
but looking at the definition of CURRENT_ROLE we could not
effect the value of that, we would need a new context value for
triggers to detect; perhaps something like EXECUTION_ROLE.

If the SQL role mechanism could be made to work it would also
offer the advantage of greatly simplifying the writing of
complex procedures. Give it the role you want it to have
rather than trying to read through all the code to determine
all the things you have to give it explicit access to.


3. I also looked at context variables... but like all UDFs
there is no security there, except that the SYSTEM variables
are read-only. If the database owner was allowed to grant a
procedure permission to create a new SYSTEM variable (and
remove it again when finished) then this would offers a way
in which an application could solve the problem for itself.


4. Having thought of using context variables another possible
solution to this problem would be to implement SQL security
for UDFs. If I could prevent normal users from executing a
particular udf, and allow specific procedures to execute it,
then I could write my own variation of the context variables
that would allow me to obtain the desired result.


5. For my own purposes I dont actually need the whole role
thing because, as indicated in the background, my procedures
are considered "system" procedures. All I need is a way to
indicate that a procedure should execute with the permissions
of the database owner, and a way for triggers etc to detect
that it has been called in such a context. So some specific
boolean on/off switch reported via the SYSTEM context variables
would satisfy my needs - it is just not as flexible as above.


I hope this is clear.

--
Geoff Worboys
Telesis Computing