Hello!
I'd like to discuss some questions about SQL SECURITY feature and SQL
Standard (2013).
Adriano notes me about abcense SQL SECURITY clause for tables in SQL
Standard. Yes. It's true.
SQL SECURITY can be specified only for SQL routines (procedures and functions).
Moreover. If it's not specified it will be SQL SECURITY DEFINER by
default. Firebird historically has INVOKER by default and I guess we
sould not change it in Firebird 4.
What privileges will be used by accessing calculated field for example
from SQL Standard point of view? A lot of places say like this (column
reference)
"
i) If CR is contained, without an intervening <SQL routine spec> that
specifies SQL SECURITY
INVOKER, in an <SQL schema statement>, then the applicable privileges
of the <authorization identifier> that owns the containing schema
shall include SELECT on the column referenced
by CR.
ii) Otherwise, the current privileges shall include SELECT on the
column referenced by CR.
"
Other places means about the same.
In other words if something is not in SQL routine owner of the schema
must have a privilege to use it. I.e it's like a implicit SQL SECURITY
DEFINER inherited from schema. Only SQL routine with SQL SECURITY
INVOKER can change this behavior.
Firebird has no schema support but database. IMO it's not good idea to
inherid such privileges from database everytime. We have a possibility
to change it for tables and triggers now. But we have a different
defaults.
I propose:
1) Add SQL SECURITY clause to ALTER DATABASE <dbname> SET DEFAULT SQL
SECURITY {DEFINER|INVOKER}
2) Every object inherits SQL SECURITY if it's not specified explicitly
for this object. For example, we will check privileges of table owner
instead of CURRENT_USER if CREATE TABLE did not specify SQL SECURITY
clause but ALTER DATABASE has specified it in DEFINER.
Expected result:
By default we keep default behavior or Firebird. Existing applications
will work as usual.
To force database work in according to SQL Standard it's enought to run:
1) ALTER DATABASE <dbname> SET DEFAULT SQL SECURITY DEFINER;
2) Create database objects including tables without SQL SECURITY
clause or SQL routins with them but using only SQL Standard features.
3) Use database
But in the same time we can override permissions for tables, triggers,
etc. It extentds standard in more usual use case for Firebird.
--
Roman Simakov
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
