Subject [IB-Architect] Integrated security (Was: SQL names for user and role)
Author Aleksey Karyakin

Including integrated security option will require fulfilling two main tasks:
-- Mapping Directory Service (DS) security model to database-wide and
server-wide access control model.
-- Performing user authentication using some network security protocol (NTLM
or Kerberos for example).

Both of these tasks will likely require redesigning the IB/FB architecture:
-- designing new version of remote protocol that supports exchange of
authentication data and information which security package is used;
-- implementing usage of security protocols using generic APIs (SSPI or GSS
-- implementaion Directory Service access logic to retrieve users
information (using ldap might be preferrable);
-- extending login database (ISC4.GDB) to allow network users to me mapped
to local database user names/roles. This should be done in accordance with
possible new security enhancements such as server-wide and database-wide
roles and permissions, central database registration, etc;
-- implementing multiple possible user/role names for single network user
with consolidation of their permissions for ACL checking;

More thoughts on that below.

When establishing a connection with server, both client and server make
negotiations about which security options they would use. This includes
client's and server's preferences on security mode and supported
authentication protocols on both ends. Server may be configured to accept
only clients that provide integrated authentication for security reasons.
Client may choose to logon to domain first or alternatively provide clear
text username/password to server.

Usually, authentication (and not only it) is implemented through some
generic interface. On NT it is Security Service Provider Interface (SSPI)
that has been there since version 3.5 at least, on other platforms Generic
Security Services API (GSS API) which is RFC standard can be used. However,
I don't know if there are implementations of GSS suitable for IB/FB. In case
of using Kerberos authentication protocol interoperation between client and
server on different platforms can be achieved. However, there are many
organizations that use NT 4 compatible domains, so NTLM protocol should be
supported (in case of NT <-> NT) as well.

Using security services interface would also provide option of encrypting
and signing (PRIVACY and INTEGRITY options) traffic between client and
server as a almost "free" gift to integrated authentication because the same
crypto-algorithms are used in both cases.

Network login names (principals) are stored in some generic Directory
Service (e.g. ADSI or NDS) or in private security databases (e.g. SAM on NT
domain controllers). ADSI also has providers for accessing third-party or
legacy information (NT domains, NDS, etc) in unified manner. ADSI uses ldap
protocol to access it so client (i.e. database server in our case) on any
platform can easily read it if it has ldap implementation (e.g. OpenLDAP)
installed. After authenticating client, server accesses DS to look up list
of identities that the client has. In ADSI and NT (as well as in most other)
security models single user can participate in multiple groups (roles). So
effective permissions on database objects for authenticated user should be a
union of permissions of each identity he has. This is incompatible with
current IB/FB security model with single user name participating in single
role at a time.

Each network client's identity should be mapped to database user/role name
on database or server level. AFAIK, there is currently no general
distinction between role and user name in IB/FB except connection process
behavior so mapping to users or roles can be done the same way. Each network
client identity can be mapped to internal (currently 31 chars length) user
or role name in RDB$USER table of ISC4.GDB (or other equivalent method).
Then, that user/role name can be granted privileges on db objects as usual.
In that case ISC4. GDB should be extended to store network user identities
with different formats (for each DS provider), e.g. SALES\BOB (NT) or
bill@... (ADSI) along with security option (package) ID where that
user identity has come from. (Alternatively, only network SIDs can be stored
which are immutable during lifetime of network login ID (as actually MSSQL
does)). This hopefully means that in-database system data model doesn't need
to be changed and current RDBUSER_PRIVILEGES table should do well.

If this all is interesting to anyone, I would be glad to contribute on this
topic making further investigations, experiments and coding some features.

Aleksey Karyakin