Dear MySQL users, MaxDB users and friends,

Several co-workers have spoken to me on the length of the MaxDB series postings. I fully agree that most postings are too long for a blog. But we do have a very eager plan to publish a complete online class. This is causing long postings and we can’t make them much shorter. We try to structure the articles in a similar way like a web page to make reading and navigating easier. Every posting has a table of contents, is devided into sections and has a fixed structure. For offline reading, printing and as a reference we will soon publish a PDF document with all postings of the series.

The experiment to use the medium of a blog for a class will continue as long as the readers do not complain. But we will add a “read more” link to the fixed structure of every posting. That means, we will present only the beginning of a posting on PlanetMySQL and you have to click on a “read more” link for the full text which might get you to a different URL. The link will be added shortly after the “In this issue” section of the posting which shows the table of contents. However, this posting is short enough so that it will not use a “read more” link.

In this issue

This issue is a follow-up on the last issue MaxDB series: User concept, authorization and schemata. We continue to explain the user concept of MaxDB. MaxDB distinguishes between “Administrators” – as we called them – and “SQL users”. The group of “Administrators” (Database Manager operators, DBM operators) and the “root” user (Database System Administrator, the SYSDBA) have been discussed in depth. The three different classes of “SQL users” (Databases users) have been introduced: DBA, STANDARD, RESOURCE. It has been shown how to grant privileges to an SQL user and what an SQL schema is. Detailed hands-on examples that show how to create users and how to use the SQL statements GRANT and REVOKE are left as an exercise to the reader.

You already learned everything you need to know about the user concept. What follows is only sugar.

• User groups – CREATE USERGROUP
• Roles – CREATE ROLE
• Dinosaurs – DOMAIN, SYSINFO, PERMLIMIT, TEMPLIMIT
• In the next issue

User groups – CREATE USERGROUP

A user group is a tool to bundle related SQL objects, similar to a schema. The ownership of all SQL object that have been created by a certain member of a user group is given to the user group. All members of the user group are automatically owners of all SQL objects that have been created by any of the other group members. Privilieges are not granted to individual users of the group but to the group itself and by this to all its members. Users who are not members of the user group that own a certain object cannot be given access to objects owned by the group.

You should favour the concept of schemas over that of user groups. There’s no guarantee this feature will stay in MaxDB forever. Check the manual for details.

Roles – CREATE ROLE

A role is like a mask that you can wear for a certain time. So far, we have described the relationship of SQL users and permissions as something unflexible. A user (or a user group) has certain permissions over certain objects and can’t break out of this cage. With roles a user can break out of the cage.

Every SQL user of the user class DBA (including the SYSDBA) can define a role. A role can be granted permissions, very much like a user. Users can be granted permission to use a role. When users that have been granted access to a role change roles using SET ROLE during a database session, they will be given the permissions that have been assigned to the role. Using roles, a user can use different sets of permissions within one database session.

Be very careful with roles. One can compare roles to sudo/suexec and similar. This is a very mighty feature. You should only give selected and trustworthy users access to priviledged roles.

Dinosaurs – DOMAIN, SYSINFO, PERMLIMIT, TEMPLIMIT

All this is not enough for you? Well, you can scan the manual for details on the keywords DOMAIN, SYSINFO, PERMLIMIT and TEMPLIMIT. But be warned. These dinoaurs did already die out or might die out very soon. For example, you will find out that the DOMAIN user no longer exists and is only a schema in recent versions.

In the next issue

This has been the first group of series postings. Similar to what I did at the beginning we will now discuss internally how to improve and what to change. As usual, we appreciate any feedback.

In the next issue we will discuss some interesting details of the MaxDB SQL syntax. But we will limit it to a very high-level discussion. We found that most users are more interested in the administration of MaxDB and only few of you need a SQL course. Most MaxDB users do have experiences with other databases and just want to know what is different and hot.

Ulf Wendel for the MySQL MaxDB team