firebird/firebird-mirror
firebird/firebird-mirror
8
0
Fork 1
mirror of https://github.com/FirebirdSQL/firebird.git synced 2025-01-22 17:23:03 +01:00
Code Issues
master
firebird-mirror/doc/sql.extensions/README.mapping.html

321 lines
15 KiB
HTML
Raw Permalink Normal View History

Implemented CORE-3370: Resolve additional aspects of multiple security databases from services and cross-database requests POV (mapping names)
2014-04-04 17:57:18 +02:00
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8"/>
<title></title>
Misc docs
2020-04-13 13:20:53 +02:00
<meta name="generator" content="LibreOffice 6.3.4.2.0 (Linux)"/>
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
<meta name="author" content="irina "/>
<meta name="created" content="2014-03-25T00:00:00.010305100"/>
Misc docs
2020-04-13 13:20:53 +02:00
<meta name="changed" content="2020-04-13T14:19:46.849216419"/>
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
<style type="text/css">
@page { margin: 2.01cm }
p { margin-bottom: 0.2cm }
a:link { so-language: zxx }
</style>
</head>
Misc docs
2020-04-13 13:20:53 +02:00
<body lang="ru-RU" dir="ltr"><p lang="en-US" style="margin-bottom: 0cm">
<font size="4" style="font-size: 14pt">SQL Language Extension:
CREATE/ALTER/CREATE_OR_ALTER/DROP MAPPING</font></p>
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Implements
Implemented CORE-3370: Resolve additional aspects of multiple security databases from services and cross-database requests POV (mapping names)
2014-04-04 17:57:18 +02:00
capability to control mapping of security objects to and between
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
databases.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Author:</font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><span lang="en-US">Alex
Peshkoff &lt;<a href="mailto:peshkoff@mail.ru">peshkoff@mail.ru</a>&gt;</span></font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Preamble:</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Firebird
3 supports multiple security databases. This is great feature, but it
Implemented CORE-3370: Resolve additional aspects of multiple security databases from services and cross-database requests POV (mapping names)
2014-04-04 17:57:18 +02:00
raises some problems, missing in systems with single security
database. Clusters of databases, using same security database, are
efficiently separated and this is what we typically want to achieve
using different security databases. But in some cases we need
controlled limited interaction between such clusters. As an examples
can be provided EXECUTE STATEMENT ON EXTERNAL DATA SOURCE when some
data exchange between clusters is required and letting server-wide
SYSDBA access databases from other clusters using services. More or
less similar problems were already known in windows version of
firebird since v. 2.1 due to presence of trusted windows
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
authentication – we had 2 separate lists of users (in security
Implemented CORE-3370: Resolve additional aspects of multiple security databases from services and cross-database requests POV (mapping names)
2014-04-04 17:57:18 +02:00
database and OS) and sometimes it was needed to make them be related.
For example it appears to be good idea to automatically assign to
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
windows users from some group appropriate firebird role.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Single
Implemented CORE-3370: Resolve additional aspects of multiple security databases from services and cross-database requests POV (mapping names)
2014-04-04 17:57:18 +02:00
solution for all this problems is MAPPING login information, assigned
to user when it connected to firebird server, to internal security
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
objects in database – current_user and current_role. Mapping rule
contains 4 parts of information: </font>
</p>
<ul>
Misc docs
2020-04-13 13:20:53 +02:00
<li><p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">mapping
Implemented CORE-3370: Resolve additional aspects of multiple security databases from services and cross-database requests POV (mapping names)
2014-04-04 17:57:18 +02:00
scope (is mapping local for current database or affects all
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
databases in cluster, including security database),</font></p>
Misc docs
2020-04-13 13:20:53 +02:00
<li><p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">mapping
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
name (mappings are named like all the other objects in database), </font>
</p>
Misc docs
2020-04-13 13:20:53 +02:00
<li><p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">from
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
what we map </font>
</p>
Misc docs
2020-04-13 13:20:53 +02:00
<li><p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">to
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
what we map.</font></p>
</ul>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Here
it's necessary to mention that all versions of firebird had one
hardcoded global default rule – users authenticated in security
database are always mapped into any database one-to-one. This rule is
safe - if we have some security database it makes no use not to trust
itself. Therefore (and due to backward compatibility) this rule is
kept as is in firebird 3. What about mapping windows users to
current_user (which was enabled by default in 2.1 &amp; 2.5 when
trusted authentication enabled) in firebird 3 it must be done
explicitly. This is required for systems with multiple security
databases - not all of them need/use windows trusted authentication.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">'From'
part of mapping has 4 items:</font></p>
<ul>
Misc docs
2020-04-13 13:20:53 +02:00
<li><p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">authentication
Implemented CORE-3370: Resolve additional aspects of multiple security databases from services and cross-database requests POV (mapping names)
2014-04-04 17:57:18 +02:00
source (plugin name or result of mapping in other database or use of
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
serverwide authentication or any method),</font></p>
Misc docs
2020-04-13 13:20:53 +02:00
<li><p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">name
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
of database where authentication succeeded, </font>
</p>
Misc docs
2020-04-13 13:20:53 +02:00
<li><p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">name
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
from which mapping is performed,</font></p>
Misc docs
2020-04-13 13:20:53 +02:00
<li><p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">type
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
of that name (username, role, OS group – this depends upon plugin
which added that name during authentication).</font></p>
</ul>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Each
item may be ignored (any item is accepted) except type – it's
definitely bad idea to mix different types of security objects.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">'To'
part has 2 items:</font></p>
<ul>
Misc docs
2020-04-13 13:20:53 +02:00
<li><p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">name
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
to which mapping is performed,</font></p>
Misc docs
2020-04-13 13:20:53 +02:00
<li><p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">type
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
of that name (only USER/ROLE are accepted here).</font></p>
</ul>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Mappings
are defined using SQL (DDL) commands.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Syntax:</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-left: 1.17cm; margin-bottom: 0cm; page-break-before: auto; page-break-after: auto">
<font size="4" style="font-size: 14pt">{CREATE | ALTER | CREATE OR
ALTER} [GLOBAL] MAPPING name</font></p>
<p lang="en-US" style="margin-left: 2.18cm; margin-bottom: 0cm; page-break-before: auto; page-break-after: auto">
<font size="4" style="font-size: 14pt">USING {PLUGIN name [IN
database] | </font>
</p>
<p lang="en-US" style="margin-left: 4.06cm; margin-bottom: 0cm; page-break-before: auto; page-break-after: auto">
<font size="4" style="font-size: 14pt">ANY PLUGIN [IN database |
SERVERWIDE] | </font>
</p>
<p lang="en-US" style="margin-left: 4.06cm; margin-bottom: 0cm"><font size="4" style="font-size: 14pt">MAPPING
[IN database] | </font>
</p>
<p lang="en-US" style="margin-left: 4.06cm; margin-bottom: 0cm"><font size="4" style="font-size: 14pt">'*'
[IN database]}</font></p>
<p lang="en-US" style="margin-left: 2.23cm; margin-bottom: 0cm; page-break-before: auto; page-break-after: auto">
<font size="4" style="font-size: 14pt">FROM {ANY type | type name}</font></p>
<p lang="en-US" style="margin-left: 2.23cm; margin-bottom: 0cm"><font size="4" style="font-size: 14pt">TO
{USER | ROLE} [name]</font></p>
<p style="margin-left: 1.17cm; margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-left: 1.17cm; margin-bottom: 0cm"><font size="4" style="font-size: 14pt">DROP
[GLOBAL] MAPPING name</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Description:</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Each
mapping may be tagged as GLOBAL. Pay attention that global and local
maps with same name may exist and they are different objects!</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Create,
alter and create or alter commands use same set of options. Name of
mapping is used to identify it in former DDL commands. USING clause
has a most complicated set of options. One can provide explicit
plugin name, making it work only for given plugin, or make it use any
plugin (but not a result of previous mappings), or make it work only
with server-wide plugins, or make it work only with previous mapping
Implemented CORE-3370: Resolve additional aspects of multiple security databases from services and cross-database requests POV (mapping names)
2014-04-04 17:57:18 +02:00
results, or let it use any method using asterisk. In almost all cases
(except server-wide authentication which is not related with
databases) one can also provide name of database in which name from
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
which mapping is performed was “born”. FROM clause must set
required parameter – type of name from which mapping is done. When
mapping names from plugins type is defined by plugin, when previous
mapping results - type can be only user or role. One can provide
explicit name which will be taken into an account by this mapping or
use ANY keyword to work with any name of given type. In TO clause
USER or ROLE (to what mapping is done) must be specified, name is
optional - when it is not provided original name (from what mapping
is done) is used.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Samples:</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">All
sample are provided for CREATE command, use of ALTER is exactly the
same, use of DROP is obvious.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Enable
use of windows trusted authentication in all databases that use
current security database:</font></p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">CREATE
GLOBAL MAPPING TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY USER TO
USER;</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Enable
SYSDBA-like access for windows admins in current database:</font></p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">CREATE
Implemented CORE-3370: Resolve additional aspects of multiple security databases from services and cross-database requests POV (mapping names)
2014-04-04 17:57:18 +02:00
MAPPING WIN_ADMINS USING PLUGIN WIN_SSPI FROM Predefined_Group
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
DOMAIN_ANY_RID_ADMINS TO ROLE RDB$ADMIN;</font></p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">(there
is no group DOMAIN_ANY_RID_ADMINS in windows, but such name is added
by win_sspi plugin to provide exact backwards compatibility)</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Enable
Implemented CORE-3370: Resolve additional aspects of multiple security databases from services and cross-database requests POV (mapping names)
2014-04-04 17:57:18 +02:00
particular user from other database access current database with
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
other name:</font></p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">CREATE
Implemented CORE-3370: Resolve additional aspects of multiple security databases from services and cross-database requests POV (mapping names)
2014-04-04 17:57:18 +02:00
MAPPING FROM_RT USING PLUGIN SRP IN &quot;rt&quot; FROM USER U1 TO
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
USER U2;</font></p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">(providing
misc
2014-04-08 12:46:33 +02:00
database names/aliases in double quotes is important for operating
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
systems that have case-sensitive file names)</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Enable
misc
2014-04-08 12:46:33 +02:00
server's SYSDBA (from main security database) access current database
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
(assuming it has non-default security database):</font></p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">CREATE
Implemented CORE-3370: Resolve additional aspects of multiple security databases from services and cross-database requests POV (mapping names)
2014-04-04 17:57:18 +02:00
MAPPING DEF_SYSDBA USING PLUGIN SRP IN &quot;security.db&quot; FROM
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
USER SYSDBA TO USER;</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Force
people who logged in using legacy authentication plugin have not too
much rights:</font></p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">CREATE
Implemented CORE-3370: Resolve additional aspects of multiple security databases from services and cross-database requests POV (mapping names)
2014-04-04 17:57:18 +02:00
MAPPING LEGACY_2_GUEST USING PLUGIN legacy_auth FROM ANY USER TO USER
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
GUEST;</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Map
windows group to trusted firebird role:</font></p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">CREATE
Improved documentation
2018-12-07 18:17:39 +01:00
MAPPING WINGROUP1 USING PLUGIN WIN_SSPI FROM GROUP GROUP_NAME TO ROLE
ROLE_NAME;</font></p>
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Here
we expect that some windows users may belong to group GROUP_NAME. If
needed name of the group may be given in long form, i.e.
DOMAIN\GROUP.</font></p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p style="margin-bottom: 0cm"><br/>
</p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Notice:</font></p>
Misc docs
2020-04-13 13:20:53 +02:00
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><span lang="en-US">-
Global mapping works best if firebird 3 or higher version database is
used as security database. If you plan to use other database as
security one (using for example your own provider) please create in
it table RDB$AUTH_MAPPING with structure repeating one in firebird 3
database, public read access and SYSDBA-only write access.</span></font></p>
<p style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt"><span lang="en-US">-
</span></font><font size="4" style="font-size: 14pt"><span lang="en-US">Mappings
work only with information, coming from authentication plugins or
previously done mapping. Information present in DPB (particular SQL
role name) is not affected by mappings and can not be changed using
them.</span></font></p>
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
<p style="margin-bottom: 0cm"><br/>
</p>
Misc docs
2020-04-13 13:20:53 +02:00
<p style="margin-bottom: 0cm"><br/>
Improved documentation
2018-12-07 18:17:39 +01:00
</p>
Misc docs
2020-04-13 13:20:53 +02:00
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">Tip:</font></p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">It’s
Improved documentation
2018-12-07 18:17:39 +01:00
relatively easy to accidentally make a database remotely inaccessible
Misc docs
2020-04-13 13:20:53 +02:00
using CREATE MAPPING statement. For example: </font>
Improved documentation
2018-12-07 18:17:39 +01:00
</p>
Misc docs
2020-04-13 13:20:53 +02:00
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">CREATE
MAPPING BREAK_DB_1 USING * FROM ANY USER TO ROLE ROLE1;</font></p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">CREATE
MAPPING BREAK_DB_2 USING * FROM ANY USER TO ROLE ROLE2;</font></p>
<p lang="en-US" style="margin-bottom: 0cm"><font size="4" style="font-size: 14pt">This
Improved documentation
2018-12-07 18:17:39 +01:00
will disallow any user (including SYSDBA) to connect. Luckily
mappings are not processed when database is used in embedded mode,
i.e. in such a case one should attach to database using embedded
Misc docs
2020-04-13 13:20:53 +02:00
access and fix bad mappings.</font></p>
Improved documentation
2018-12-07 18:17:39 +01:00
<p style="margin-bottom: 0cm"><br/>
</p>
Document changes in win_sspi
2018-10-22 19:14:55 +02:00
<p style="margin-bottom: 0cm"><br/>
</p>
</body>
</html>
Copy Permalink