Access
Road 0.7 Guide for the MySQL Server ACS
|
About
the s oftware
This is the user guide
for an ACS add-on of Access Road. The Access Road software is an
universal simulator of access controls, to improve the design and
audit of IT security. Access Road 0.7 includes the simulation of
MySQL Server®
software in a complementary add-on called 'MySQL Server®
ACS add-on for Access Road'.
The
'MySQL
Server®
ACS
add-on for Access Road' is a free, libre and open source
software, licensed
under the GNU AGPL (Affero General Public
License version 3).
All the source code and all the algorithms are original. The
simulation of MySQL
Server®
is
based only on the public description and the use of this
software, without copy of the source code or algorithms of the
simulated software. This program is distributed in the hope that
it will be useful, but WITHOUT ANY WARRANTY; without even the
implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR
PURPOSE, including warranty about the rights simulation.
The
MySQL Server®
access controls are described
by its editor at
http://dev.mysql.com/doc/refman/5.6/en/request-access.html and
the near pages.
The
owner and the publisher of the 'MySQL
Server®
ACS
add-on for Access Road' is the company ACCBEE,
35bis, Rue des Renoullières, 93220 Saint-Denis, FRANCE,
EU.
|
|
About
this guide
The Access Road documentation includes a second tutorial named
'two-hours tutorial for designing access controls'. It is
recommended to read first this document, because it introduces
the simulation of MySQL
Server®.
It shows how to create an ACS for simulating this software. It
demonstrates the capacities of Access Road through the example of
the MySQL Server AclEntry. More generally, it is recommended, to
save time, to read first the 3 basic tutorials for Access Road.
This guide discusses
the following issues about the simulation of MySQL Server:
the
AclEntry,
the
host and its accounts,
the
pattern-name containers, as groups and bases sets,
the
mixed AclEntry,
the
MySQL Server oddities.
For the software
developer, the reference documentation is the extended Javadoc
documentation for Access Road. It includes a full description of
this ACS add-on.
This
present documentation is published under a FDL
license.
|
|
How
the MySQL Server ACS handles the AclEntries
The management of access controls by the MySQL server ACS may be
summarized by 6 cases. The simulation of the MySQL server access
control can take the following forms:
the
ACL source is a proxy
having
no proper rights, using the rights of a proxied account as user;
setting
rights of the user to the pair (user@host), through GRANT
statements without
wildcard into the names,
at the global (like this first ACL), database, table, column and
routine levels, and this is simulated by a conditional
ACL for an account;
setting
rights of the user to the pair (user@host with wildcard) through
GRANT statements with a wildcard
'_' or '%' only into the host name,
at the global, database, table, column and routine levels, and
this is simulated by a non-conditional
ACL for an 'accounts-for-hosts' group,
like the first displayed ACL into the explorer: '<G>::
anonymous§@%:: >> MySQL server:: || grant;
the
three previous cases when the ACL target is a bases set under
the node '|pattern-name_bases sets|', like the first example
'BASES O%' having the base 'BASE One' as connected database;
setting
rights of the user to the pair (user@host) through two direct
INSERT statements into the MySQL server tables 'mysql.db' and
'mysql.host', at the database level, and this is simulated by
the virtual mixed ACL that results from two
non-conditional ACL for an account and a 'host' group on
the same database;
the
previous case for setting rights to the pair (user@host with
wildcard) with two non-conditional ACL for an account
and a 'hosts group' on the
same database.
The second tutorial
for Access Road presents the 3 first forms of access control. The
virtual mixed AclEntry is described in a following section.
The USAGE right in
MySQL has some ambiguity. Its meaning varies following the
context. At the global level on the root 'MySQL server', it means
'no rights at this level but rights are possible at another
levels', while at the other levels, it means 'no rights at this
parent level'. Access Road comments this right to recall this
specificity.
|
|
Introducing
the MySQL host and its accounts
Under
the standard groups tree in the explorer, the group '|simple
hosts|' contains all the hosts the MySQL server knows. Those
hosts have simply the type 'host'. At the creation of a MySQL
Server ACS, '|localhost|' is the single known host. By
convention, the local host is
the operating system on which the MySQL Server runs.
Any new MySQL Server host has to be the image of an ACS in the
information system. For instance, let's say there is a Linux
Ubuntu ACS named 'IO:: two:: bun2:: '.
It
is possible to declare such a host in a MySQL Server ACS, as a
new GroupID having the type 'host'. The GroupID name
'IO:two:bun2' has
to be used to allow the ACS add-on to match it to the good ACS in
the information system. The
new GroupID is added as member of the standard group '|simple
hosts|'. In a MySQL Server ACS, a host has then a name that
fulfills with a precise form, because the name is an image of the
ACS name.
Following the same logic, a
MySQL UserID may be added as member of the host 'IO:two:bun2'
only if it is the image of an UserID in the associated ACS. For
instance, to create the MySQL Server ACS UserID 'games' with
'IO:two:bun2' as
first host, it is mandatory to have an UserID 'games' in the ACS
'bun2'. In this case, the association manages the collisions of
names among the UserIDs. For instance, there is only one account
'jerry' under a given ACS, but 'jerry' may be an UserID into
several ACS. This is why the MySQL Server ACS addon allows to
create an account 'jerry(2)' with 'IO:two:bun2' as first host.
The general form
'jerry(any_character)' defines a so-called pattern-name account.
It may seem strange to declare two
accounts for the same person 'jerry' into two host servers. From
an administration point of view, it is time-consuming and MySQL
has not taken this way. The MySQL account is associated to a host
or a group of hosts, using MySQL right statements like:
GRANT ALL ON db1.* TO
'jerry'@'localhost'.
On
the other hand, Access
Road simulates the MySQL Server and all the objects it handles,
but not the organization.
Access Road is a support for security analysis. It has to model
that, say, the account 'jerry' under the host '192.168.1.2' is
NOT the account 'jerry' into '192.168.1.1', since MySQL Server
may attribute distinct rights to them. Even if the MySQL Server
is configured to use the same password for the two 'jerry',
offering or not the same rights from different hosts is an
organizational issue. It remains true that the two accounts are
not technically the same 'jerry'. Considering other design
constraints like the mixed ACL, it is not efficient to model a
MySQL pair (user, host) as one single Access Road UserID. From
the software point of view, these 'jerry' are indeed different
accounts having separate first hosts, and sometimes separate
rights.
|
|
The
pattern-name containers in the ACS tree window
The AclEntry uses the wildcard in the most complex cases. The
MySQL
Server ACS handles the wildcard characters
'%', '_' and '§' in a container name. The name defines then a
pattern for selecting the elements of the container. This way of
naming is applied to some group names and some directory names.
The character '%' replaces in MySQL Server any group of
characters (and even no character). The character '_' replaces
exactly one character in MySQL Server. On the other hand, the
character '§' is specific to the ACS add-on. There are three
types of pattern-name containers, as described following:
a
group of hosts has the ACS object type 'pattern-name
hosts group', and this
simulates a MySQL specification, like for the GroupID '%local%'
to get all the hosts having 'host' into their names,
a
directory of bases has the ACS object type 'pattern-name
bases set', and this simulates a MySQL specification, like
for the Directory 'BASE O%' to get all the databases having
'BASE O' at the beginning of their names.
a
group of accounts has the ACS object type '
pattern-name
accounts-for-hosts group',
and this is NOT a MySQL specification, for which the group name
'xxxx§@yyyy' defines a group of accounts that are selected if
(1) the account name
has exactly one of the forms 'xxxx' or 'xxxx(z)', and (2) the
account
is member of the hosts group 'yyyy' (there, 'yyyy' may be a
simple host like 'localhost' or a pattern-name hosts group like
'%local%').
This
last container is necessary for example to attribute one ACL to
all the accounts 'jerry', 'jerry(2)' and 'jerry(z)' in all the
current hosts. Such an ACL must have, as right
user, a
pattern-name
accounts-for-hosts group. The name of this group is 'jerry§@%'.
The ACS add-on sets its members as being 'jerry' and all the
'jerry(z)' accounts, excluding 'jerryOne' or 'jerry1'. The use of
'§' is proper to the MySQL Server ACS addon.
This
ACS add-on models only the pattern-names having one wildcard.
This is commented in the next section about the true MySQL Server
behavior.
|
|
The
virtual mixed AclEntry
The
mixed AclEntries are at the database level. The
term 'mixed ACL' is not from the MySQL Server documentation, but
it is useful for having a clear description. Mixed ACL are used
for simulating in Access Road the MySQL Server 'host'
table
of privileges. While this 'host' table is rarely used by the
MySQL administrators (perhaps because it is complex), it is an
important feature.
A
mixed AclEntry is called virtual because it is never into the ACL
list of a Resource. On the contrary, it is created through an
internal processing of the current AclEntries on a database. Only
a MySQL database may have mixed AclEntries. They are selected by
the ACS addon. The main feature of an applicable
mixed ACL is to be the last matched ACL on the database.
The
MySQL Server rule is:
EXCLUSIVE OR (non-conditional user
ACL on database AND non-conditional host (or hosts group) ACL on
database).
This means that each of these two
real ACL do not deliver their
rights directly to their right users, as for the other ACL. A
full view never shows an access path having the form 'right
user/non-conditional ACL for this right user/database'. A
mixed ACL results then from the combination of two
non-conditional ACL. The mixed ACL is produced by the ACS addon,
not directly by the user, and it may appear in the full views. It
appears as a special key in the primary rights map of the
database.
The
resulting rights of the virtual ACL are the common rights
of
the two implied non-conditional ACL.
The user's database-specific mixed ACL rights are computed as the
intersection (not
the
union!)
of the two non-conditional ACL rights lists. More precisely, if a
non-conditional user ACL have no rights, it is not used to build
up a mixed ACL. On the other hand, a
non-conditional host (or hosts group) ACL having no rights may
product an applicable mixed ACL with no rights.
If
all the AclEntries for
the database-related rights (INSERT, UPDATE and so on)
are
at the database level, such a no-rights mixed ACL implies no
rights for the pair (any right user from the host, resource). In
this configuration, the mixed ACL is like a denying right for all
the users of this host.
When
an access path finds a virtual ACL, the 'See why' text of the
full view is completed by the comment: 'database
mixed ACL through '
with the name of the host (or hosts group).
|
|
The
MySQL Server oddities
The oddities of a
simulated software is a classical section. An oddity appears when
there is a lack of consistency among the software concepts, or
into the behavior of the software. Our point of view is not to
criticize a great software, but to learn and to simulate it
precisely. Simulating is of course a powerful way to detect
oddities, and we are happy to bring this information to the
Access Road users.
Sometimes, false
information is published in varied supports about the access
controls of a software. When we see them as common errors, they
are listed in this section.
The
oddities and false information in the MySQL environment are
extended to those beyond the MySQL Server, from usual tools like
MySQL Workbench from Oracle, the editor of MySQL. They are listed
hereinafter:
Even
a well-known MySQL book may deliver false information about
MySQL privileges. It is written: “MySQL was designed to
make it easy to grant privileges but not to deny privileges.
From the MySQL point of view, you deny a privilege by never
granting it at the first place (…) If you want to allow
'raymond' to connect from any host except insecure.example.com,
you have to either block that host at the network level or add a
record with a bogus password to the user table for
raymond@insecure.example.com”. Unfortunately, the sentence
“you deny a privilege by never granting it at the first place”
seems to be wrong. A GRANT statement for
'raymond@insecure.example.com' with an unworkable privilege
(like SHOW VIEW while there is no view) is sorted before a GRANT
statement for 'raymond@%.example.com' with broad privileges. By
this way to forbid 'insecure.example.com' precisely to
'raymond', the MySQL rights are all defined into the privileges
tables, without bogus password. This is a better design
strategy, for instance to help auditing. This feature may also
be viewed as a MySQL oddity that has mistaken some experts. It
recalls us how important is the sorting of privileges, then the
sorting of Access Road ACL, to design the MySQL access controls.
The
wildcards '_' and '%' are used in the hosts and databases names
to define sets of hosts or databases, like '192.168.1.%'. The
tests of the MySQL server has shown that its behavior may be
very far from the general rule which tells the most-specific
name is sorted first, in a
list of hosts or in a list of databases. For instance,
the host name '%%' is sorted before the host name '_ocalhos_'.
On the other hand, the sorting
rules of wildcard names are different for databases and for
hosts. For instance, for the host names, 'localhos_' is
before 'localhost%', but for the database names, 'on%' is before
'on_'. The MySQL documentation does not describe how the MySQL
server handles the names like '192.%.1.%' or '192.16_.1._', that
is when there is more than one wildcard in the name. This is
why, to limit the risk of wrong simulations, Access Road does
not accept hosts and databases names including more than one
wildcard, and it defines different algorithms for the hosts and
the databases sorting. Access Road applies the rules which
have been detected by tests on the MySQL server
5.1.41-3ubuntu12.10. The good news is very few MySQL server
instances define such complex names for hosts or databases. One
may consider that all the MySQL server versions should use the
same algorithms for the sorting of host and database names. This
is credible but without warranty. Of course, this point is
beyond the responsibility of the Access Road editor.
At
http://dev.mysql.com/doc/refman/5.6/en/request-access.html,
the MySQL documentation explains that, “for
database-related requests, if there is a matching 'db'
table row and its Host
column is not blank, that row
defines the user's database-specific privileges”.
The privileges come from the first matching 'db' table row. Our
tests show that this description is incomplete. The
first matching host is selected before the first matching base.
For instance, when the user connection is local and the request
is on the base 'one', the MySQL server selects the privileges of
the base 'o_e' for the host 'localhost', while there is also a
row in the 'db' table for the privileges of the base 'one' for
the host 'localho__'. This means the matching of 'localhost' is
detected before the matching of 'one', which is then neglected.
In this case, the
choice of Access Road is to follow the true behavior of the
MySQL Server, version 5.1.41-3ubuntu12.10. Again,
one may consider that all the MySQL server versions should use
the same algorithms for the sorting of host and database names.
This is credible but without guarantee. The MySQL documentation
explains also that, “for
database-related requests, (...) if the matching 'db'
table
row's Host
column
is blank, it signifies that the host
table
enumerates which hosts should be permitted access to the
database”.
There, with the combination of the 'db' and 'host' tables, since
the base matching is searched first, the reader may think that
the first matching base should be selected before the first
matching host. Again, our tests demonstrate that
the
MySQL Server selects first the matching host. For instance, the
base 'on%' for the host 'localhost' is selected before the base
'one' for the host 'localho%'. The information from the
documentation is quite confusing. Access Road simulates the true
behavior of the MySQL Server, and this feature is provided as
mixed AclEntries.
It
is not possible to delete at the global level the basic right
USAGE for a pair (user, host). For instance, the mysql command
'REVOKE USAGE ON * FROM 'jerry'@'__';' does not delete the pair
(jerry, '__'). Such a basic right is added by the MySQL server
at the global level when a right is granted to the pair at any
level (global level, base level, table level,...). Since these
global rights cannot be deleted, they remain into the privileges
tables for ever. The good news is this global right statement
delivers no right until there is another right at another level.
The
'ALL' right stands for “all privileges available at a given
parent level (except GRANT
OPTION )”, but
MySQL does not handle fully this meaning. For instance, revoking
'ALL' is not a short way to revoke several current rights. On
the other hand, as 'ALL' is defined as the upper level right of
most of the MySQL ACS rights, Access Road manages the true
meaning of 'ALL'.
|
|
®All
trademarks are property of their respective holders. Copyright
ACCBEE – 22 February 2012
|
|