Skip to main content

Users

All interactions with the database server are attributed to a particular user known to the system. Therefore, a user login name should be created upfront, much like you would have to on any computer system, Your user name determines the schemas you have permission to access and your role in managing them. Much like your computers file system, it is essential to restrict access to your valuable database content via grant and revoke privileges and/or roles.

To create, modify/alter or drop a user in the database you can use the following syntax:

user_stmt:
    CREATE USER user_name WITH [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'user_password' NAME 'full name' SCHEMA schema_name
   | ALTER USER user_name RENAME TO new_user_name
   | ALTER USER SET [ENCRYPTED | UNENCRYPTED] PASSWORD 'new_password' USING OLD PASSWORD 'user_password'
   | ALTER USER user_name WITH [ENCRYPTED | UNENCRYPTED] PASSWORD 'user_password'
   | ALTER USER user_name [ WITH [ENCRYPTED | UNENCRYPTED] PASSWORD 'user_password' ] SET SCHEMA schema_name
   | DROP USER user_name

The user name, password and default schema can be changed using the different alter user statements.

Note that for a user (including the administrator user: monetdb) to change its own password, the ALTER USER SET form has to be used where the current password is given. 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.

You may view the list of users in the database via query:

    SELECT * FROM sys.users;

The user name, password and database name are needed to authenticate yourself against tools such as mclient and all APIs, to gain access to the particular database. Once connected the current schema is the one as specified for the user.

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.