An identity is a symbolic representation of a person, object, or software. It can be a name, a code, or a number. Whenever we read this symbolic representation, we immediately know who/what it is.
Person
Computer
ERP program instance
BI program instance
Aung San
DESKTOP-9A3F2KJ
SAP\36
7892163
From the computer systems perspective, identity is a symbolic representation that can be authenticated and authorized to interact with a system.
Authentication is verifying of the identity.
Person "Aung San" has password "123".
Authorization is a list of things you're allowed to do.
"Aung San" can change and use any object in MonetDB schema "cars_schema".
When we register such identity into some computer system, then we have created an "Account".
For example, library has identity registered as an account.
If we provide our library card, we can be authenticated and then we will be authorized to borrow the book.
User "monetdb"
After the installation of MonetDB, we start with administrator user. His name is "monetdb" and password is "monetdb".
SELECT * FROM sys.auths;
In the system table "sys.auths", we have list of all of the users and roles. Here we can see that ID of the user "monetdb" is number 3.
We can see that this user is admin, because he is the owner of all the system schemas. => SELECT * FROM sys.schemas;
Create a User in MonetDB
We have already seen how to create a user. We need the user's identity ("username") and their authentication ("password"). We will define later what permissions this user has. Only administrators can create new users, so we will login as "monetdb" and we will create new user.
mclient -u monetdb -d voc –password is monetdb CREATE USER "User1" WITH PASSWORD '123' NAME 'Aung San';
"User1" is the username. "Aung San" is the full name of the user.
Auths table has a list of all of the users and roles: SELECT * FROM sys.auths;
We can also find new user in "SELECT * FROM sys.users;".
<= SELECT * FROM sys.schemas; We can also notice that a schema for this new user is created. It has the same name as a user. This is his default schema.
Role "User1" 15518, that is created automatically with the user, can not be given to other users.
It is not possible to create a user who doesn't have a password.
Preparing a Sample Table
While we are still logged in as administrator, I will create one table. CREATE TABLE schemaTest ( Number INT ); INSERT INTO schemaTest ( Number ) VALUES ( 1 ); SELECT * FROM sys.schemaTest;
GRANT SELECT ON sys.schemaTest TO "User1";
I will also grant the rights to "User1" to be able to read from the table "sys.SchemaTest".
Schemas for the New User
We will quit current session with "quit". Then we will log in as a new user: mclient -u "User1" -d voc –password is 123 SELECT CURRENT_SCHEMA;
If we check the default schema of a user, it will be "User1".
CREATE TABLE schemaTest ( Letter CHAR ); INSERT INTO schemaTest ( Letter ) VALUES ( 'A' ); SELECT * FROM "User1".schemaTest;
We already knew that the new table will be created within the default schema "User1".
I will now alter "User1". ALTER USER "User1" SCHEMA PATH '"sys","User1"';
I will also change the current schema. SET SCHEMA voc;
And then I will read from schemaTest; SELECT * FROM schemaTest;
We will get a table with the "1". That is the table from schema "sys". The reason is that a table is searched for in this order: 1) First search in the current schema. Current schema is "voc" and we will find nothing. 2) Then search in order "sys", "User1" ( SCHEMA PATH attribute ). We will find a table in a "sys" schema and we will return table with the number "1".
CREATE TABLE Test ( Test INT );
If the user "User1" tries now to create a table, MonetDB will try to create a table in the current schema "voc". This will fail because of privilages.
So, unqualified table name is always created in the current schema. Unqualified table in SELECT is searched in schemas in the order "voc" (current schema), "sys" (first schema in the SCHEMA PATH list), "User1" (second schema in the SCHEMA PATH list), and then the other schemas in the SCHEMA PATH list.
Deleting a User
We saw that when we create a user without specifying his schema, a new schema is created.
DROP USER "User1"; –It seems that we can not delete the user because of his schema.
DROP SCHEMA "User1"; — We can not delete schema because it is user's default schema.
ALTER USER "User1" SET SCHEMA sys; DROP SCHEMA "User1" CASCADE; DROP USER "User1"; * we have to cascade to delete dependent table "User1".schemaTest.
Solution is that first we change user's default schema. After that we can delete that schema. Then we can delete the user.
Creating a User with Attributes
mclient -u monetdb -d voc
I will quit the session, and then I will login as an administrator. Then I can create another user.
CREATE USER sali WITH PASSWORD '123' NAME 'Sali Noyan' SCHEMA sys SCHEMA PATH '"sys","voc"' MAX_MEMORY 1073741824 MAX_WORKERS 6 OPTIMIZER 'minimal_fast';
When creating a new user, we can define many attributes. We can define default schema and schema path. User's query can use maximally 1 GB of data, and can be processed with 6 CPU threads. Query will use "minimal fast" optimizer.
Default schema
Schema path
Maximal memory for queries
Maximal threads for query
Optimizer used
SCHEMA sys
SCHEMA PATH '"sys","voc"'
MAX_MEMORY 1073741824
MAX_WORKERS 6
OPTIMIZER 'minimal_fast'
We can read from the system table: SELECT * FROM sys.optimizers;
That query will give us a list of the possible optimizers. Optimizers are used to make our queries faster. We should mostly stick with the default optimizer "default_pipe".
Encrypted Password
When we create the user, his password will be encrypted and stored. MonetDB is, by default, using SHA-512 encryption.
If our password is "123" then we can go to the web site https://sha512.online, and there we can encrypt that password into SHA-512 encryption.
Instead of giving MonetDB the task of encrypting password, we can encrypt it ourself. We can, then, create a new user by using that already encrypted password.
If we are administrator, this is how we can create a new user by using encrypted password.
CREATE USER Test WITH ENCRYPTED PASSWORD '3c9909afec25354d551dae21590bb26e38d53f2173b8d3dc3eee4c047e7ab1c1eb8b85103e3be7ba613b31bb5c9c36214dc9f14a42fd7a2fdb84856bca5c44c2' NAME 'Test' SCHEMA sys;
After that, we will quit the session, and then we will log as a new user.
mclient -u test -d voc User will use original password "123". That password will then be encrypted and compared with the stored password.
I will now quit this session and then I will log as an administrator. After that I will delete "test" user.
mclient -u monetdb -d voc–password "monetdb"
DROP USER Test;
Altering Users
We can change the name of a user.
ALTER USER sali RENAME TO merik;
I will change "monetdb" password to "monetdb2" and back. This is for the users to change their own passwords.
ALTER USER SET PASSWORD 'monetdb2' USING OLD PASSWORD 'monetdb'; ALTER USER SET PASSWORD 'monetdb' USING OLD PASSWORD 'monetdb2';
ALTER USER merik WITH PASSWORD '456' SET SCHEMA voc SCHEMA PATH '"sys","voc"' MAX_MEMORY 1000 MAX_WORKERS 5;
<= This is how admin can change all of the attributes of a user. In this statement each subclause is optional. For example, statement bellow will change only the MAX_MEMORY and MAX_WORKERS. We will turn them of.
ALTER USER merik NO MAX_MEMORY NO MAX_WORKERS;
It seems that is not possible to change OPTIMIZER for a user.
We can now observe changes on the "merik" user.
SELECT * FROM sys.users WHERE name = 'merik';
ALTER with Encrypted Passwords
We can use encrypted version of a password in the ALTER statements.
This can be used by the user: -- ALTER USER SET ENCRYPTED PASSWORD '3c9909afec25354d551dae21590bb26e38d53f2173b8d3dc3eee4c047e7ab1c1eb8b85103e3be7ba613b31bb5c9c36214dc9f14a42fd7a2fdb84856bca5c44c2' USING OLD PASSWORD '456';
This is how admin would change the password of some other user, by using SHA-512. ALTER USER merik WITH ENCRYPTED PASSWORD '3c9909afec25354d551dae21590bb26e38d53f2173b8d3dc3eee4c047e7ab1c1eb8b85103e3be7ba613b31bb5c9c36214dc9f14a42fd7a2fdb84856bca5c44c2';
I will delete the user, "DROP USER merik;".
Roles
Role is a named set of privileges that can be granted to a user. Privileges are permissions to perform an action (e.g., SELECT, INSERT, EXECUTE). Instead of granting individual permissions, we assign a role, and the user inherits all the permissions associated with that role.
In chess, the rights to control each white piece are united under the role of "White".
The rights to control each black piece are united under the role of "Black".
In one match you can accept the role of White, but in another match you can be Black player. Each role can give different rights to the same chess player.
Creation of a New Role
When a new role is created it only has a name. We will see later how to add privileges to a role.
I will create a new user: CREATE USER newUser WITH PASSWORD '123' NAME 'newUser name' SCHEMA sys; New role with the id "15554" will appear in the sys.auths table. This role can not be given to other users; it is the personal property of the "newUser". This personal role will not appear in the sys.roles table.
SELECT * FROM sys.auths;
SELECT * FROM sys.roles;
This is how we create the real role. CREATE ROLE newRole; It will appear both in sys.auths and sys.roles.
These kinds of roles can be asign to users.
Default Role
Default role is automatically active for the user upon login and used to determine their default permissions.
In sys.users table we can find data about "newUser" user. SELECT * FROM sys.users WHERE name = 'newuser';
His default role is 15554. That is the personal role created together with that user. SELECT * FROM sys.auths WHERE name = 'newuser';
We can change the default role. I will set "newRole" as a default role for the "newUser".
ALTER USER newUser DEFAULT ROLE newRole;
We can read default role again. Role with ID 15557is "newRole". SELECT name, default_role FROM sys.users WHERE name = 'newuser';
We can assign a default role to a user when the user is created.
CREATE USER mnm WITH PASSWORD '123' NAME 'mnm' SCHEMA sys DEFAULT ROLE newRole; SELECT name, default_role FROM sys.users WHERE name = 'mnm';
Granting a Role
We are currently admin. We can find that out like this: SELECT CURRENT_USER, CURRENT_ROLE;
Admin is using his personal role. This is his default role.
Admin must first grant himself a right to use "newRole". "SET" will not work without that.
SET ROLE newRole;
I want to give admin the right to use newRole. GRANT newRole TO monetdb;
Admin now has the right to use "newRole". We will then make this role the current_role. SET ROLE newRole;
We can now test, what is the current role. SELECT CURRENT_USER, CURRENT_ROLE;
We have successfully changed current role for admin.
Each user, beside the default role, can have several other roles assigned. He can easily switch between them. For example, "SET ROLE monetdb;" will return original admin role to him.
We can find a list of granted roles in the system table: SELECT * FROM sys.user_role; User with ID 3 is admin. User with ID 15581 is "mnm".
When we have created the "mnm", we have set his default role to "newRole" ( 15557 ). That role is then automatically granted to him. We have also ALTER the user "newUser". We gave him default role "newRole" ( 15557 ), but in that case this role was not granted to him automatically. This is not good. If we alter the user default role, that role should be automatically granted to him. It is wrong that user, after he log in, have a default role that he was not granted. We can solve this problem by granting the role separately.
GRANT newRole TO newUser; SELECT * FROM sys.user_role;
Deleting a Role
We can easily delete the role. DROP ROLE newRole; SELECT * FROM sys.roles;
Admin is using his personal role. This is his default role.
We have successfully changed current role for admin.
