Access Road 0.7
Guide for the MySQL Server ACS
version 0.7.1


About the software

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.

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).


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 – 02 May 2012