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.
|
|
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
first rule is correct for the 3 first forms of rights, which are
those this tutorial covers. It has to be adapted for some of the
3 other cases. The second rule is correct for the 5 last forms of
rights, but not for the first one. The third rule is correct for
the 6 forms of rights the MySQL Server handles.
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.
Access
Road does not simulate the privilege tables (user db tables_priv
columns_priv procs_priv). The overall simulation of the MySQL
Cluster distributed
privileges is exactly like the simulation of a single MySQL
Server. The simpliest and efficient simulation of a MySQL Cluster
is then to model it as one MySQL Server. The second way is of
course to model each SQL node with its local components and
privileges tables. Views and stored procedures are not copied on
all the MySQL nodes of a cluster by the installation script
'ndb_dist_priv.sql', but they may be copied manually. This is
easy to simulate in Access Road, like the copying by the basic
programs. If the privilege tables are modeled by Access Road on
each MySQL node, then modeling the NDB API and ClusterJ
applications would be considered, because they have direct access
on the local privilege tables.
|
|
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).
|
|
About
the design of MySQL Server access controls
There is a powerful and
well-known free software called MySQL Workbench®.
It is able to design the access controls through administrative
and technical roles. Above all, it can inject the resulting
design, as right statements, into an instance of MySQL Server.
For
instance, it is quite easy to design, with MySQL Workbench:
a set of 3 separate database
administrative roles to attribute to the IT technical staff, the
good rights for the good task, at the global and base levels;
a set of 10 separate technical
roles to attribute to the 10 sets of applicative transactions
that have to access to the bases of the MySQL Server;
from a structure of MySQL bases
designed to well match to this set of MySQL technical roles, the
access controls are efficient, easy to design, to configure and
to maintain.
For simple and stable
needs of access controls, MySQL
Workbench is quite good. It is efficient for
managing the administrative roles. However, this tool has some
important limitations each time the MySQL Server contains complex
or unstable bases, or with fine-grained needs of access control.
It
does not cover the full range of the MySQL access control
features:
it cannot tell if a right
statement for 'jerry@local%' overrides or not a right statement
for 'jerry@%'; this means it does not allow to design a
differential naming space into the right statements, as we will
see Access Road help to do;
it cannot handle the rights at the
levels under the bases, like on a table, a stored procedure, a
view or a column; this means, for instance, it is not possible
to design a stored procedure, running under a specific secure
account, to provide a common high-security-level code to all the
applications accessing the MySQL Server.
Furthermore, MySQL Workbench cannot
use flexible strategies of access control, while Access Road is
able to design them.
There are some things to
remember first, about what you can and cannot do with the MySQL
Server:
You
cannot explicitly specify that a given user should be
denied access. That is, you cannot explicitly match a user and
then refuse the connection. This means it is not possible for
Access Road to match on an enabled conditional ACL without
rights. However, it is possible to set a flexible strategy to
attribute a 'small' unused right, like TRIGGER, to a small range
of accounts, defined in Access Road as an accounts-for-hosts
group, so that this range will be connected to the server, but
in practice without workable rights.
On the other hand, you can enable
access in the MySQL Server to all hosts on your network, except
one given host. The
wildcard characters “%” and “_” can be used in the hosts
group names. This is the way to enable access to all hosts. This
tutorial does not covered this feature, while Access Road is
able to do it.
There
are also some MySQL best practices to know:
The rights
statements that are assigned on a global basis are applied no
matter what the default database is. For example, with a global
DELETE right on the root 'MySQL Server', you can delete rows
from any table in any database on the server! It is wise to
grant global rights only to people who need them, such as
database administrators. For other users, you should use rights
statement at more specific levels only, for particular
databases, tables, columns or routines.
To keep rights for
temporary and nontemporary tables separate, a common workaround
for this situation is to create a database dedicated to the use
of temporary tables. Then for that database, a user can be
granted the CREATE TEMPORARY TABLE right, along with any other
rights required for temporary table operations done by that
user.
When you modify the
account's rights, it is a good idea to verify that the changes
set up rights the way you want. To check the rights for a given
account, use the SHOW GRANT statement. For example, to determine
the rights that are granted to an account with user name and
host name values of bob
and pc84.example.com,
use this statement: SHOW GRANTS FOR
'bob'@'pc84.example.com'.
With
this last MySQL best practicee, a SHOW GRANTS statement cannot
show all the applicable rights of 'bob', since for instance the
'bob@%.exemple.com' right statements are not listed. On the
contrary, the Access Road beamer displays all the ACL of 'bob' in
one list, and more, it is a sorted list in the order of
importance!
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. 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'.
|
|