Dear MySQL users, MaxDB users and friends,

a new version of MaxDB 7.5.00 has been released recently: MaxDB 7.5.00.34. Two versions of MaxDB have the status of General Availability (GA). MaxDB 7.5.00 and MaxDB 7.6.00 can be used in production environments. Open Source users should use the latest release which is 7.6.00. Last weeks release of 7.5.00 Build 34 (7.5.00.34) is of interest for all 7.5.00 users. Check the Changelog if it shows “must-have” entries for you.

In this issue

This article is part of a MaxDB series on PlanetMySQL that teaches you how to use MaxDB. We hope that we can write about 40 articles in 2006: roughly one per week, published on wednesdays if time permits. All articles together will make a complete online class.

Todays lesson is on the user concept of MaxDB and the related topic of SQL schemata. The user concept of MaxDB seems to have caused a lot of confusion in the past. One reason might be that in different versions of MaxDB different system users are available and that in some cases default usernames overlap with the name of user classes. Therefore it is important that you know about the basic concepts and the terms used in the MaxDB documentation.

Closely related to the user concept is the topic of SQL schemata. A schemata is no more but a namespace for SQL objects. It would be hardly worth covering schemata if MaxDB would not implicitly create a schemata for every SQL user. This sounds odd if you do not have a long MaxDB background and if you do not know how it was handled before schemata have been introduced with MaxDB 7.6.00 .

Operating system level users are not discussed. On Unix, MaxDB will create a new operating system user “sdb” and a new operating system user group “sdba”. The new user will become the owner of the MaxDB software installation on the operating system level. This is done to implement extra security on the operating system level and to prevent other users from accidently destroying the software installation.

• Three different user types in MaxDB
• “root”: The Database System Administrator (SYSDBA user)
• “Administrator”: Database Manager operators (DBM operator)
• Managing DBM Operators
• Example: creating a start-stop user
• “SQL users”: Database users of the user classes DBA, RESOURCE and STANDARD
• Hands on: Managing two SQL users
• In the next issue

When talking about the user concept of MaxDB, I like to describe the three different user types as: root, administrator and SQL user. This comparison is far from beeing perfect. In fact, the comparison is even wrong in some ways. Nevertheless, the picture will be used. The terms root, administrator and SQL user are much easier to remember the official names for the three different user types. The official names of the different types of users are: Database System Administrator (SYSDBA user), Database Manager operators (DBM operator) and Database users.

Simplified and not 100% correct!

Usually the SYSDBA (“root”) is the first database user that gets created during the creation of a database instance. The SYSDBA belongs to the group of database users (“SQL users”) but has also all rights of a Database Manager operator (DBM operator, “Administrator”) plus some more. Default names of the SYSDBA are DBADMIN and DBA. Every database instance has only one SYSDBA.

A user of the user type Database Manager operator (DBM operator) is kind of an administrator. Only a DBM operator can log in to the DBM Clients (DBM GUI, DBMCLI, WebDBM – see also last week’s posting) and perform administrative tasks like backup and recovery. You can create as many DBM operators as you want. A DBM operator is not meant to perfrom SQL queries.

Performing SQL queries is the task of the Database users (“SQL users”). A user of the user type database users can log in to the SQL tools (SQL Studio, SQLCLI, WebSQL), use a programming interface (JDBC, ODBC, PHP, Perl, …) or the Loader to work with the SQL objects stored in the database. Database users cannot administrate the database instance, for example they cannot create a database backup.

Every MaxDB database instance has exactly one Database System Administrator (SYSDBA user). The SYSDBA is the first database user that you create when you create a database instance with the DBM GUI or the Installation Manager. As of database version MaxDB 7.6.00, the default value for the name of the database system administrator is DBADMIN. Typical default passwords are DBADMIN and SECRET. MaxDB 7.5.00 has used the name DBA and the password DBA as a default. It will be shown later that DBA has been a confusing default name. SAP installations using MaxDB might use different default names and passwords, search the manual for “SAP Standard Users for Databases”.

The SYSDBA has the following properties:

• has all the server authorizations that a Database Manager operator (“Administrator”) can have
• has all the rights that a database user (“SQL user”) of the DBA database user class can have
• has owner rights over all users
• can create database users of any database user and Database Manager operators (DBM Operator)
• is the owner of a number of system tables

For beginners it is very convenient to work with the SYSDBA. The SYSDBA is the most priviledged user of a database instance. He can perform administrative tasks as well as running SQL queries. Beginners do not need to worry about the details of the user concept and authorizations. Of course this is also very dangerous in some ways. In production environments you should restrict the use of the SYSDBA (“root”) to a bare minimum, just like you do with the Unix operating system user root. The simple reason is paranoia. Time will come you will make a mistake and do some harm because you have been using a priviledged user for a operation that should have not been allowed to you. One day you might sit in front of your computer – like I did – and chat with your colleagues about the question what to do after you have run rf -rf . in / as root. Amazingly, the system continued running but of course I could not restart the computer any more with an empty disk.

You should never, ever forget the passwort of the SYSDBA. Of course there is no way to extract the password from the database files and you are unable to perform some actions if you do not know the password. Simply never forget the password. Maybe you can put a note with it on your monitor or use one of the default passwords… Is that true? Discuss it in the forum. We will give the answer to this excercise in the next lesson.

The SYSDBA has owner rights over all users. All users means that he has owner rights over all users of the user type Database user (“SQL users”) and all users of the user type DBM operator (“Administrators”). Beeing the owner of another user means that you manage the priviliges of another user. The owner of a user can grant more rights and revoke rights from the user he owns, but the owner can also delete the user. Additionally to managing existing users, the SYSDBA can create new users of all types.

Let’s see what the manual says about a DBM operator (“Administrator”): “Database Manager operators (DBM operators) can log on to the Database Manager database tool and use its administration functions. Database Manager operators are not database users and consequently cannot log on to the query tools (such as SQL Studio) or the Loader. When you create a database instance with the Database Manager or the Installation Manager, the respective tool creates a first Database Manager operator that has the required DBM operator properties and all server authorizations. The default value for the name of the Database Manager operator is DBM.”

Again, the installation wizards will create a user for you. If you followed the MaxDB series from the beginning and if you have done everything we described, then the Installation Manager that you used, has created a first DBM operator for you. The first DBM operator has been granted all server authorizations. The user has been given the user name DBM.

The default passwords that the installation wizards use are DBM and SECRET. In our series the Installation Manager was used to install MaxDB. This has caused a tiny pitfall: the Installation Manager does not use one of the before mentioned default passwords. The first database manager operator, the DBM user has been given the password that you have specified for the SYSDBA. We told you in the Installation lesson to use the user name DBADMIN and the password DBADMIN for the SYSDBA. Respectively, the first DBM user was given the password DBADMIN by the Installation Manager. You may check this now and try to log in to the DBM GUI and connect to your MAXDB1 database instance using the user name DBM and the password DBADMIN.

The manual continues to explain: “Database Manager operators can create, change or delete other Database Manager operators, if they have the respective server authorizations. If a Database Manager operator has the corresponding authorization, it can also display or change the DBM operator properties and server authorizations of another DBM operator.”

This means that you can have multiple DBM operators. DBM operators can be given certain server rights, certain server authorizations. Strictly speaking there is a small semantical difference between server rights and server authorizations, but we will ignore this difference for simplicity and use the terms interchargably. Every server right allows you to perform certain actions. One of the rights is the User management right. Every DBM operator that has this right can administrate the rights of all other DBM operators. Be careful, when you assign the User management right to someone. There is no hierarchie in the relationship of DBM operators. If you grant the User management right to a new DBM operator, the new DBM operator could revoke the User management right from the grantor.

The following server authorizations exist. Check the table carfully and ask your self why we said that the picture “SQL user” vs. “Administrator” is not perfect.

Before we dive into the details of the Database users (“SQL users”) and schemata, we will take the opportunity to create a new DBM operator and assign some rights to the user. Managing DBM Operators in the DBM GUI is very easy. Open the DBM GUI and connect to your MAXDB1 database. Select Configuration – DBM Operator … from the menu list of the current database instance or use the window menu Instance – Configuration – DBM Operator… to open a popup window for user management. That’s it, it should not require a manual to learn how to use it.

The more experience you get and the more MaxDB installation you have to administrate, the more likely that you will use the text based DBMCLI and scripts for administration. Don’t be much afraid of using the DBMCLI. Remember that DBMCLI and DBM GUI are clients of the DBM Server. The DBM Server writes a protocol file with all DBM commands that the clients send to the server. If you’re looking for a specific DBM command, it can be helpful to use the DBM GUI first and then to reverse engineer the DBM commands you need for the DBMCLI based on the DBM Server protocol file. We described this in our last posting.

Let’s start with a DBM command that shows a list of all existing DBM operators, similar to the list in the popup windows of the DBM GUI: user_getall. In addition to the user list we want to check the properties of one of the DBM operators using the DBM command user_get <user_name>. To excecute any of the two commands, you need the DBInfoRead server authorization. Log in to the DBMCLI using the SYSDBA user which has the username DBADMIN and the password DBADMIN if you did not overrule any of the defaults that we suggested in the class. The SYSDBA has all server authorizations that a DBM operator can have. This includes the required DBInfoRead server authorization.

$ /opt/sdb/programs/bin/dbmcli -u DBADMIN,DBADMIN -d MAXDB1
dbmcli on MAXDB1> user_getall
OK
dbm
dbadmin

---
dbmcli on MAXDB1> user_get dbm
OK
SERVERRIGHTS=DBInfoRead,SystemCmd,ExecLoad,UserMgm,DBFileRead,Backup,InstallMgm,LoadSysTab,ParamCheckWrite,ParamFull,ParamRead,DBS
tart,DBStop,Recovery,AccessSQL,AccessUtility,SharedMemoryMgm,SchedulerMgm,Scheduling,EvtDispMgm,EvtDisp
GUIRIGHTS=
SECONDPASSWORD=NO
DISABLED=NO
COMMENT=
USERTYPE=DBM

--

Create a new DBM operator called “DBM_STARTSTOP” with the password “STARTSTOP_PASSWORD” using the DBM command user_create. We will give this user the right to start and stop the MAXDB1 database instance in the following.

Quit the DBMCLI session and log in to the DBMCLI again using the newly created user. Try to execute user_getlist. The DBM server will not allow you to execute the command, because the newly created user has not been given any server right. The user DBM_STARTSTOP does not have the server authorization DBInfoRead which is needed to execute the user_getlist command. MaxDB bails at you.

dbmcli on MAXDB1>user_create DBM_STARTSTOP,STARTSTOP_PASSWORD
OK

---
dbmcli on MAXDB1> quit
OK

---
$ /opt/sdb/programs/bin/dbmcli -u DBM_STARTSTOP,STARTSTOP_PASSWORD -d MAXDB1
dbmcli on MAXDB1> user_getall
ERR
-24937,ERR_MISSRIGHT: no server rights for this command

---
dbmcli on MAXDB1> quit
OK 

---

You can assign the “DBStart” and “DBStop” to the user DBM_STARTSTOP by help of the command user_put. Use Glossary – DBM Command to navigate to the overview of all DBM commands and check the syntax of user_put. Please open the manual page now, you should learn how to look up the syntax and how to “read” the manual. The manual says the following.

Use

You can change the DBM operator properties and server authorizations of DBM operators 
and of the database system administrator.

Caution

See Conventions for User Names and Passwords. 
The database system automatically converts passwords for 
DBM operators into upper-case letters. [Note of the editor: possible pitfall]

Prerequisites

·  You have the server authorization UserMgm.
·  If you want to change the password of the database system administrator, 
   the database instance must be in the ONLINE operational state.

Syntax

user_put <user_name> <property_definition>
<property_definition> :: = <property_spec> | <property_spec_list>
<property_spec> :: = <property>=<value>
<property_spec_list> :: = <property>=<value_list>
<value_list> :: = <property>=<value,value,...>
[...]

The manual continues to explain that propery can be any of: PASSWORD, COMMENT, SECONDPASSWORD, DISABLED and SERVERRIGHTS. The idea behind SECONDPASSWORD is that you do not need to give the support your original password but you can temporarily assign a user a second password that you can be give to the support engineers for the time of their work. Obviously, SERVERRIGHTS is the property needed to assign server rights.

A list of possible values for SERVERRIGTS has been given above. If you want to grant not only ony server right to a user using one user_put call, you can specify a list of values. The list of rights has to be seperated by commas. Every server right in the list must be preceded by a plus (+) or a minus sign (-). Putting a plus in front of the right means that you want to grant the right, a minus sign in front of the right means that you want to revoke it. This gives us:

$ /opt/sdb/programs/bin/dbmcli -u DBADMIN,DBADMIN -d MAXDB1
dbmcli on MAXDB1>user_put DBM_STARTSTOP SERVERRIGHTS=+DBStart,+DBStop
OK

---
dbmcli on MAXDB1>user_get DBM_STARTSTOP
OK
SERVERRIGHTS=DBStart,DBStop
GUIRIGHTS=
SECONDPASSWORD=NO
DISABLED=NO
COMMENT=
USERTYPE=

---
dbmcli on MAXDB1>user_getrights DBM_STARTSTOP SERVERRIGHTS
OK
DBInfoRead              -       Request status data
SystemCmd               -       Execute operating system commands
ExecLoad                -       Execute the LOAD program
UserMgm                 -       User management
DBFileRead              -       Database file access (read only)
Backup                  -       Saving backups
InstallMgm              -       Installation management
LoadSysTab              -       Load the system tables
ParamCheckWrite         -       Parameter access (checked write)
ParamFull               -       Parameter access (read and write)
ParamRead               -       Parameter access (read only)
DBStart                 +       Start database instance
DBStop                  +       Stop database instance
Recovery                -       Restoring backups
AccessSQL               -       Access to SQL session
AccessUtility           -       Access to utility session
SharedMemoryMgm         -       Shared memory management
SchedulerMgm            -       Scheduler management
Scheduling              -       Scheduler use
EvtDispMgm              -       Event Dispatcher management
EvtDisp                 -       Event Dispatcher use

Quit the DBMCLI and log in to the DBM GUI using the user DBM_STARTSTOP. Ask yourself why you are getting error messages, read the error messages carefully and you will find out. When you are done with your testing, use user_delete to drop the DBM operator DBM_STARTSTOP.

That’s been a lot of “ugly”, “difficult to understand” command line “hacking”. Always remember that you do not need to know about any of these commands, if you are using the DBM GUI. The wizards of the DBM GUI will hide all this from you. Especially for beginners it is highly recommended to get started with the DBM GUI before you start writing DBMCLI scripts. If you want to start writing shell scripts right now, recall the help output of DBMCLI and the following options on input, output and protocol files:

$ /opt/sdb/programs/bin/dbmcli -h
[...]
      -i    inputfile         (Default stdin)
      -ic   inputfile         (Default stdin)
      -o    outputfile        (Default stdout)
      -t    protocolfile      (writes subsequent protocol)
[...]

Usually you do not care where the database stores user informations. One situation when you do care is, when you remove database files or when your harddisk gets corrupted and database files are affected from the corruption. Removing database files without beeing 100% sure that you can remove them without doing any harm is like shooting yourself in your feets. It hurts and nobody will feel sorry for you. In the worst case, even the development support will not be able to help you. Never forget the SYSDBA password, never remove any database files unless you know what you are doing.

The user profile container is such a file that you should not modify or delete. The user profile container is the place where MaxDB stores the user informations of all DBM operators and the SYSDBA user. If the DBM operator and SYSDBA user informations would be stored in the database catalog, it would not be possible to connect to the DBM server if the database is in OFFLINE mode or corrupted. The user profile container does not store informations about the “SQL users”. “SQL users” are keept inside the database in the database catalog.

Two copies of the user profile container exist. The original file is <indep_data>/config/<dbname>.upc and a copy exists in a <rundirectory>/dbm.upc. We demonstrated in the last lesson how to find the actual values of <indep_data> and <rundirectory>. MaxDB versions prior to 7.6.00 might use a different place to store the copy of the user profile container.

$ ls -la /var/opt/sdb/data/wrk/MAXDB1/dbm.upc
-rw-rw----  1 sdb sdba 3072 2006-03-08 12:48 /var/opt/sdb/data/wrk/MAXDB1/dbm.upc
$ ls -la /var/opt/sdb/data/config/MAXDB1.upc
-rw-rw----  1 sdb sdba 3072 2006-03-08 12:48 /var/opt/sdb/data/config/MAXDB1.upc
$ diff /var/opt/sdb/data/wrk/MAXDB1/dbm.upc /var/opt/sdb/data/config/MAXDB1.upc

As you can see, the files belong to the operating system user “sdb” and the operating system user group “sdba”. This is important to protect the files from users who should not know about the database operators and it is required to ensure that the DBM server can read and write the two files. When you look into the file, you’ll see that all contents are encrypted except the user names. This is helpful for debugging.

The database messages -24950 ERR_USRFAIL, -24951 ERR_USRREAD, -24952 ERR_USRSAVE can indicate problems with the user profile container. Most likely -24950 ERR_USRFAIL was caused by you, because you have used a wrong user name but it could also be a corrupted user profile container, which is likely the case for -24951 ERR_USRREAD and -24952 ERR_USRSAVE. If you get any of these messages start to debug the case:

1. Ensure the user exists, use DBMCLI and user_get|user_getall or DBM GUI to verify this
2. Ensure the user in question is a DBM operator or the SYSDBA, the user profile does not contain contain “SQL users”
3. Check the operating system level file permissions for both *.upc files, they must be as shown above
4. Use grep to ensure the that the user is contained in both *.upc files

If all fails and one of your *.upc files is missing or corrupted, you might still able to rebuild it from the second one. When you loose the copy <rundirectory>/dbm.upc, then the DBM server will re-create it based on the original in <indep_data>/config/<dbname>.upc . When you loose the original file <indep_data>/config/<dbname>.upc, it is slighly more complicated. Open a DBM session using DBMCLI or DBM GUI. You can still log in to the database, because MaxDB can check your credentials against the copy <rundirectory>/dbm.upc. Switch the database in online mode and reload the system tables with load_systab . This will cause the original file to be recreated. Only the SYSDBA and DOMAIN user will be recorded in the file. Not perfect, but you’re “up again”. Loosing both *.upc-files is a very bad idea. Simply put the two directories on two different disks, keep your operating system backup up to date and never play with your database internal files and you will hardly ever deal with this problem.

The counterpart to the DBM operators (“Administrators”), who are allowed to use the DBM GUI/DBMCLI/WebDBM for database administration, are the Database users (“SQL Users”). Database users can only log in to the SQL tools like SQL Studio and SQLCLI. Additionally they can use any other SQL clients like the Loader or a programming interface to connect to the database.

Three different classes of database users exist: DBA, RESOURCE and STANDARD. A user class implies certain rights. The rights are not bound to any specific SQL objects. The most powerful “SQL user” is a user of the class DBA (database administrator). Note that the term administrator in DBA (database administrator) refers only to the SQL world. It is not about managing the database system/database software. Unfortunatelly in some older versions of MaxDB the default user name of the SYSDBA was DBA! A user with the name DBA is not necessarily the same as a “SQL user” of the user class DBA. When you talk someone about “the DBA”, make sure you talk about the same things.

A database user of the user class DBA is the most priviledged “SQL user”. A DBA can create other database users of the user classes RESOURCE and STANDARD. He can create all kind of database object (SQL objects) and grant of all or part of privileges for these database objects to other database users.

Next in row from most privileged to least privileged users comes a database user of the user class RESOURCE. A RESOURCE user can define data and database procedures. Data means tables, views, … and so on. The user can grant privileges of these database objects to other users. But a RESOURCE user cannot create any users.

If your are a STANDARD user, you are restricted to accessing data and database procedures that were defined by other database users and for which the user has been given privileges. All you can create are views, synonyms and temporary tables. You cannot create users, you cannot create base tables nor can you grant privileges for any of the database objects you can access to other users. This means, you’re basically restricted to work with the existing database objects.

The SYSDBA is the only user who can create database users of all classes. The SYSDBA can create DBA, RESOURCE and STANDARD users. Only the SYSDBA can create a DBA user. An existing DBA cannot create another DBA. Naturally, DBM operators (“Administrators”) also cannot manage any of the “SQL users”, including the DBA.

So, unlike to the world of DBM operators (“Administrators”) there is a clear hierarchy in managing “SQL users”. Among the DBM operators a user only needs the server right UserMgm to manage any of the DBM operators. This is not the case among the database users (“SQL users”). User management rights are bound to the user class. In order to manage a database user, you must have the owner rights of the user. The SYSDBA has owner rights on all “SQL users”. A DBA owns all users he created.

1. SYSDBA —- manages — > DBA
2. DBA — manages and grants privileges for his objects to —> his RESOURCE and STANDARD users
3. RESOURCE — grants privileges for his objects to —> all other users
4. STANDARD — is at the end of the food chain –>

SQL users have more properties than “Administrators” and it it likely that you will more frequently adjust SQL users properties than the properties of a DBM operator or the SYSDBA.

Managing SQL users with the DBM GUI is very easy. Log in to the DBM GUI and get connected to the MAXDB1 database instance . This time, use the DBM operator with the username DBM and the password DBADMIN. As usual, we assume that no changes to accounts and passwords have been and you are using exactly the setup that was created earlier in the class. Do not log in with the SYSDBA. If you do, we can’t demonstrate what we plan to. Select Configuration – Database User… in the menu list of the database instance in the lower left corner of the window or use the menu Instance – Configuration – Database User… to open a dialog for SQL user management. If you did not use the SYSDBA user, a authentification dialog appears first. The reason is that a DBM operator is not allowed to manage SQL users, there is a clear line between both worlds. Only a DBA (database administrator) and the SYSDBA can manage SQL users. As there is no DBA account you could use to authenticate, use the SYSDBA (username: DBADMIN, password: DBADMIN) to log in to the SQL user management dialog. Using the GUI dialog is pretty much like using the GUI dialog for DBM operators. It’s hardly possible not to understand how to use the GUI.

The only “surprise” is hidden behind the scenes. SQL users are managed using SQL commands. As a DBA cannot log in to the DBM GUI, he and you should get familiar with the correspondending SQL commands.

• CREATE USER, DROP USER
• ALTER USER, RENAME USER, GRANT USER
• ALTER PASSWORD
• GRANT, REVOKE

After all this theoretical background information, it is time for excercises and examples. We will create two SQL users and grant them access to to two tables. Log in to the SQL Studio and get connected to the example database MAXDB1. Ask yourself which is the only user account that you can use to log in to any of the SQL tools at the moment. Correct, the only user that can be used is the SYSDBA. Use the CREATE USER statement to create a new database user of the user class DBA with the username MYDBA and the password MYDBA.

<create_user_statement> ::=
  CREATE USER <user_name> PASSWORD <password>
    [<user_mode>]
    [TIMEOUT <unsigned_integer>]
    [COSTWARNING <unsigned_integer>]
    [COSTLIMIT <unsigned_integer>]
    [[NOT] EXCLUSIVE]
    [DEFAULTCODE ASCII]
    [DEFAULTCODE UNICODE]
    [<connect_mode>]
| CREATE USER <user_name> PASSWORD <password> LIKE <source_user>
| CREATE USER <user_name> PASSWORD <password> USERGROUP <usergroup_name>

<user_mode> ::= DBA | RESOURCE | STANDARD
<connect_mode> ::= ENABLE CONNECT | DISABLE CONNECT
<source_user> ::= <user_name>

Use the system table USERS to check if the user has been created and what default values have been used my MaxDB for user properties like TIMEOUT which have not been specified by you. A complete list of all system tables is contained in the manual, see also Glossary – System Table. You can compare the system tables of MaxDB to a superset of the SHOW command and the INFORMATION SCHEMA in MySQL.

CREATE USER MYDBA PASSWORD MYDBA DBA
//
SELECT * FROM USERS ORDER BY USERMODE, OWNER, USERNAME

Open a second SQL Studio (Session – New SQL Studio). Log in to the second SQL Studio, using the newly created MYDBA and create a table called “MOVIES” with two columns. Give the first column the name ID and the data type INTEGER. Use the name TITLE and the data type VARCHAR(30) for the second column. Insert one record into the table.

CREATE TABLE MOVIES (ID INT, TITLE VARCHAR(30))
//
INSERT INTO MOVIES (ID, TITLE) VALUES ('1', 'Wargames')

Go back to the original SQL Studio running the SQL session of the SYSDBA. Execute exactly the same CREATE TABLE statement in the SQL session of the SYSDBA. Run SELECT * FROM MOVIES.

Why does the CREATE TABLE not fail and where is the record? Try the same SELECT statement to in the SQL session of MYDBA. The record is there and AUTO_COMMIT is turned on, so transactions and a missing COMMIT are not the cause. The solution to the riddle is, that the user MYDBA and the user DBADMIN are using two different namespaces. MYDBA is using a namespace called MYDBA and DBADMIN is using a namespace called DBADMIN. The correct SQL term is not namespace, but schema (recall: MaxDB schema = MySQL CREATE DATABASE = MySQL CREATE SCHEMA for recent versions).. That’s all what a schema is about: a schema is a namespace for SQL objects like tables. MaxDB 7.6.00 implicitly creates a schema for every user. The name of the schema is identical to the name of the user. When the user opens a SQL session in SQL Studio, the current schema is set to the his schema. Check it with the help of the system table SCHEMAS:

SELECT * FROM SCHEMAS
//
SELECT CURRENT_SCHEMA FROM DUAL

What happened with the two tables is that MYDBA has created a table MYDBA.MOVIES and DBADMIN has created a table DBADMIN.MOVIES. <schema_name>.<object_name> is called a fully qualified name. If you leave out the <schema_name>, MaxDB will use the current schema. The current schema is a property of your SQL session. You can set the current schema using SET CURRENT_SCHEMA = <schema_name> . When DBADMIN tries to access the table MYDBA.MOVIES, he will be told that MYDBA.MOVIES does not exist.

Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
 Base table not found;-4004 POS(15) Unknown table name:MOVIES
SELECT * FROM MYDBA.MOVIES

The reason is that even the user DBADMIN (the SYSDBA in this case) does not have the permissions to access the SQL objects of other users. If you do not know the “trick” of using SELECT * FROM SCHEMAS to get a list of all schemas, MaxDB does not even tell you that the schema MYDBA exists, unless you have been granted access to the schema. Only the owner of a SQL object – be it a schema or a table – can grant access to the object to other users. Even DBADMIN (the SYSDBA in this case), “root” as we called him before, has no way to workaround this. That one reason why we said at the beginning the comparisons we made are not perfect.

The user MYDBA is the ower of the table MYDBA.MOVIES and he is the owner of the schema MYDBA. He can grant any other “SQL user” access to the schema or the table within it.

GRANT ALL ON MYDBA.MOVIES TO DBADMIN

Once this statement is committed, DBADMIN can access the table MYDBA.MOVIES. DBADMIN still cannot access any other tables of the schema MYDBA, but he can access the table MOVIES.

Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
SELECT * FROM MYDBA.MOVIES
Statement successfully executed.
Execution Time:  20:34:21.543 - 20:34:21.562  (00.019 sec)

In the example the user DBADMIN has been granted “ALL” permissions on the table MYDBA.MOVIES. Of course, there are much more fine-grained alternatives to “ALL”. The syntax diagram of the SQL statement GRANT shows the options. You can grant access to schema or to invidual tables. If you want to give someone permissions to create new SQL objects inside a schema or to drop existing object, you give him the CREATEIN resp. DROPIN schema privileges. On the level of individual SQL object, for example tables, you can give someone the right to alter the table (ALTER), to remove records (DELETE), add an index to the table (INDEX), add new records (INSERT), add referential constraints (REFERENCES), allow him to read records from the table (SELECT), give a combined right for reading and updating (SELUPD) or just give him the allowance to update existing records of the table (UPDATE).

<grant_statement> ::= GRANT <priv_spec>,... TO <grantee>,... [WITH GRANT OPTION]
| GRANT <schema_priv_spec> TO <grantee>,...
| GRANT EXECUTE ON <dbproc_name> TO <grantee>,...
| GRANT EXECUTE ON <dbfunction_name> TO <grantee>,...
| GRANT SELECT ON <sequence_name> TO <grantee>,... [WITH GRANT OPTION]

<grantee> ::= <user_name> | <usergroup_name> | <role_name> | PUBLIC


priv_spec> ::= ALL [PRIV[ILEGES]] ON [TABLE] <table_name>,...
| <privilege>,... ON [TABLE] <table_name>,...
| <role_name>

<privilege> ::=
  ALTER
| DELETE
| INDEX
| INSERT
| REFERENCES [(<column_name>,...)]
| SELECT [(<column_name>,...)]
| SELUPD [(<column_name>,...)]
| UPDATE [(<column_name>,...)] 

<schema_priv_spec> ::= <schema_privilege> ON <schema_name>,...
<schema_privilege> ::= CREATEIN | DROPIN

The counterpart to the GRANT statement is REVOKE.

Please appologize that we are late with this weeks posting. Again, we missed the magic wednesday. And we are forced to shift the remaining details on SQL users to the next issue. We are forced, because of a few days off and other commitments. Be sure, we continue to explain the concept and details of SQL users in the next lesson. Of course we encourage you to read the manual and find out yourself about the missing details.

Although we can’t give you all details today, you are already armed with the necessary information to setup the users in your MaxDB installation. Always keep in mind that MaxDB 7.6.00 makes a clear cut between “SQL users” and “Administrators” – the only exception is the SYSDBA, kind of “root”. Be warned that in MaxDB 7.5.00 you will find some more and different users. However, like with MySQL, we ask you to use the latest production versions of the database: 7.6.00 for MaxDB, 5.0 for MySQL.

Use the opportunity to comment to this article on the MaxDB forum. Never hesitate to ask questions, the user concept is not always easy to understand. Talk to you next wednesday … or thursday!

Ulf Wendel for the MySQL MaxDB team