Skip to main content

Users

All interactions with the database server are attributed to a particular user known to the system. Therefore, a user-name should be created upfront, much like you would have to on any computer system, Your user name determines the database instances covered by database schemas you have permission to access and your role in managing them. Much like your computer environment, it is essential to restrict access to your valuable database content.

To create, modify and drop a user in the database you can use the following syntax (NB: the use of double and single quotes, i.e., id versus string value, at the various places.):

user_stmt:
    CREATE USER "user_name" WITH [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'user_password' NAME 'name' SCHEMA "schema_name"

The "user_name", 'user_password' and "schema_name" are needed to authenticate yourself against tools, such as mclient and all APIs, to gain access to the particular database schema.

Every user plays a certain role.  So, when a user is created, a role with the same name is automatically created as well.  The difference is that, the user has all corresponding permissions, e.g., the user has all permissions on the table it creates. However, the role with the same name remains empty until permissions have been granted to it explicitly.

The user name and password can be changed using the alter statement as well.

   | ALTER USER ident  WITH [ ENCRYPTED | UNENCRYPTED] PASSWORD string
   | ALTER USER ident  SET SCHEMA ident
   | ALTER USER ident  WITH [ENCRYPTED | UNENCRYPTED] PASSWORD SET SCHEMA ident
   | ALTER USER RENAME TO ident  
   | ALTER USER SET [ENCRYPTED | UNENCRYPTED] PASSWORD string USING OLD PASSWORD string

Note that for a user to change its own password, the second ALTER USER form has to be used where the current password is given. This also holds for the administrator user (default: monetdb). When the ENCRYPTED clause is given for PASSWORD, a hashed form of the password has to be supplied. The type of this hash depends on how the server was configured, but defaults to SHA512.