0470 Users and Privileges in MonetDB part2

We will start with a blank state. I will delete the users that were created in the Part 1.

DROP USER newUser;

DROP USER mnm;

Initial State

mclient -u monetdb -d voc #password "monetdb" I will create three users. I won't define their default schemes, so each of them will get a scheme named after them. We also have admin user "monetdb". CREATE USER a_user WITH PASSWORD '1' NAME 'a'; CREATE USER b_user WITH PASSWORD '1' NAME 'b'; CREATE USER c_user WITH PASSWORD '1' NAME 'c';

I will now login as "a_user" and I will create a table and a function. They will be created in the schema "a_user". mclient -u a_user -d voc    #password "1" CREATE TABLE a_user.a_table ( a_number INT PRIMARY KEY, a_letter CHAR ); INSERT INTO a_user.a_table ( a_number, a_letter ) VALUES ( 1, 'a' );

CREATE OR REPLACE FUNCTION a_user.a_Fx() RETURNS INTEGER BEGIN      RETURN 2; END;

I will create one table in the "b_user" schema. mclient -u b_user -d voc    #password "1"

CREATE TABLE b_user.b_table ( b_number INT, b_letter CHAR ); INSERT INTO b_user.b_table ( b_number, b_letter ) VALUES ( 1, 'b' );

Privileges vs Roles

Privilege is a permission to do one action (SELECT, UPDATE…). Role is a collection of such privileges.

Grant Privileges to a User

Grant and Revoke all Privileges on a Table

"b_user" can not use the table "a_table".

"a_user" will grant privilege to "b_user" to use table "a_table".  "FROM CURRENT_USER" is the default and can be omitted. That means that the privilege is given by the CURRENT_USER. Other option is that the privilege is given by a role. We'll see that later.

mclient -u a_user -d voc    #password "1" GRANT ALL ON a_user.a_table TO b_user FROM CURRENT_USER;

We will now log in as a b_user and we will try to use "a_table". mclient -u b_user -d voc    #password "1"

SELECT * FROM a_user.a_table;

"b_user" can not alter the table. Altering of a table is reserved for an owner of a table. "b_user" can only use DML stataments. In MonetDB, owner of a table is owner of a schema where some table is.

ALTER TABLE a_user.a_table RENAME COLUMN a_number TO zzz;

mclient -u a_user -d voc    #password "1" REVOKE ALL ON TABLE a_user.a_table FROM b_user;

This is how we can revoke privileges given to "b_user".

Grant and Revoke Separate Privileges

I will again give privileges to "b_user" for the table "a_table". GRANT SELECT ( a_number ), INSERT, UPDATE ( a_number, a_letter ) ON a_user.a_table TO b_user FROM CURRENT_USER; This time, instead of "ALL", I will give each privilege separately.  This time I will give him SELECT, INSERT and UPDATE privileges. Beside those we can give him privileges to DELETE, TRUNCATE, and REFERENCE. Any combination is allowed. Notice that for SELECT, UPDATE and REFERENCES we can limit our permission to a list of columns.

I will then login as a "b_user" and I will try SELECT and DELETE on "a_table". mclient -u b_user -d voc    #password "1" SELECT * FROM a_user.a_table;   –successful, but only 1 column SELECT a_number, a_letter FROM a_user.a_table;   –unsuccessful DELETE FROM a_user.a_table;               –unsuccessful

mclient -u a_user -d voc    #password "1" REVOKE ALL ON TABLE a_user.a_table FROM b_user; REVOKE SELECT ON TABLE a_user.a_table FROM b_user;

I will try to revoke privileges as a group. I will also try to revoke SELECT privilege for all of the columns. Both will succeed.

mclient -u b_user -d voc    #password "1" SELECT * FROM a_user.a_table;

Revoking didn't work. Privileges SELECT, SELECT ( column ), ALL, are all different privileges. They are given and revoked separately.

mclient -u a_user -d voc    #password "1" REVOKE SELECT ( a_number ), INSERT, UPDATE ( a_number, a_letter ) ON a_user.a_table FROM b_user;

Privileges must be defined in REVOKE in the same way as in GRANT.

mclient -u b_user -d voc    #password "1" SELECT * FROM a_user.a_table;

Now, it worked.

Grant and Revoke Reference Privileges

"b_user" can reference table that belongs to "a_user". We didn't even have to grant rights. This is a bag. This shouldn't be allowed. GRANT and REVOKE statements are totally useless for REFERENCES privilege, because this privilege is always given.

ALTER TABLE b_user.b_table ADD CONSTRAINT ReferenceToA_Table FOREIGN KEY ( b_number ) REFERENCES a_user.a_table ( a_number );

Grant and Revoke Function Execution Rights

I will grant execution rights on a function to "b_user".

mclient -u a_user -d voc    #password "1" GRANT EXECUTE ON FUNCTION a_user.a_Fx() TO b_user;

"b_user" can now call a function. mclient -u b_user -d voc    #password "1"

SELECT a_user.a_Fx();

mclient -u a_user -d voc    #password "1" REVOKE EXECUTE ON FUNCTION a_user.a_Fx() FROM b_user;

I will revoke function execution rights.

This will also work for procedures.

-- GRANT EXECUTE ON PROCEDURE procTest() TO b_user; -- REVOKE EXECUTE ON PROCEDURE  procTest() FROM b_user;

Grant and Revoke COPY Rights

In this blog post "https://bizkapish.com/monetdb/0350-exporting-data-and-binary-files/" we can see how to export and import files on the client computer by using python. This right is automatically given to all of the users.

mclient -u monetdb -d voc    #password "monetdb" GRANT COPY INTO, COPY FROM TO a_user;

We have to be administrator to give someone rights to conduct import and export of data, if the file is on the server.

mclient -u a_user -d voc    #password "1" COPY SELECT * FROM a_user.a_table INTO '/home/fffovde/Desktop/a_table.csv';

Now, "a_user" can export (and import) table to CSV (from).

Grant to PUBLIC

There is a special role "PUBLIC". Everyone has this role.

GRANT SELECT ON a_user.a_table TO PUBLIC;

mclient -u c_user -d voc    #password "1" SELECT * FROM a_user.a_table;

"c_user" now has SELECT rights on the "a_table". Just like everyone else.

mclient -u a_user -d voc    #password "1" REVOKE SELECT ON a_user.a_table FROM PUBLIC;

I will the revoke the rights from the PUBLIC role.

WITH GRANT OPTION

Here is a visual representation of what will happen in the next example.

I will now grant grantable ( delegable ) right to the user "b_user".

GRANT SELECT ON a_user.a_table TO b_user WITH GRANT OPTION;

mclient -u b_user -d voc #password "1" SELECT * FROM a_user.a_table;

"b_user" received the right to SELECT from the "a_table". But he also received the right to give that right to someone else.

"b_user" can now give SELECT right to the user "c_user". GRANT SELECT ON a_user.a_table TO c_user WITH GRANT OPTION;

So, we have two rights, right to read and right to grant.

mclient -u a_user -d voc    #password "1" REVOKE GRANT OPTION FOR SELECT ON a_user.a_table FROM b_user; REVOKE                  SELECT ON a_user.a_table FROM b_user;

In MonetDB, it is not possible to revoke these two rights separately. Statement like these ones will remove all of the rights from "b_user".

mclient -u b_user -d voc    #password "1" GRANT SELECT ON a_user.a_table TO voc;

mclient -u c_user -d voc    #password "1" SELECT * FROM a_user.a_table; GRANT SELECT ON a_user.a_table TO voc WITH GRANT OPTION;

Interesting thing is that the user "c_user" will keep both of his rights. He can still read from the "a_table", and can further grant that right.

How to revoke these rights now? User "c" can revoke his rights from the "voc".

REVOKE GRANT OPTION FOR SELECT ON a_user.a_table FROM voc; But, what about "c_user", who can revoke his rights?

mclient -u b_user -d voc    #password "1" REVOKE GRANT OPTION FOR SELECT ON a_user.a_table FROM c_user;

"b_user" can not do it any more.

mclient -u a_user -d voc    #password "1" REVOKE GRANT OPTION FOR SELECT ON a_user.a_table FROM c_user;

"a_user" has to do it.

This means that "a_user" can revoke the rights on the "a_table" from anyone, anytime. As you can see, giving grantable rights can create a confusion where we don't know any more who has the rights and who hasn't. I think we should avoid using this option.

Grant Privileges to Roles

Grant Privileges to a User Through a Role

As admin I will create a role. mclient -u monetdb -d voc  #password "monetdb" CREATE ROLE Role1; GRANT SELECT ON a_user.a_table TO Role1; GRANT Role1 TO b_user;

I will then log as the "b_user". He will be able to read from the "a_user.a_table". mclient -u b_user -d voc    #password "1" SET ROLE Role1; SELECT * FROM a_user.a_table;

We saw how to grant rights to the role, how to grant role to the user, and how user can use role with "SET ROLE" statement.

The Problem: Anyone Can Delete a Role

mclient -u c_user -d voc    #password "1" DROP ROLE Role1;

Any user, including "c_user", can delete a role.

Just as anyone can reference any table (we saw that earlier), anyone can delete the role. This is also a bug, database shouldn't work like this.

Create Another Administrator

The administrator privileges are contained in the predefined role "sysadmin". Anyone who take that role will have administrative rights.

mclient -u monetdb -d voc    #password "monetdb" GRANT sysadmin TO c_user;

We will then jump to be "c_user". mclient -u c_user -d voc    #password "1"

"c_user" will try to create a new user. CREATE USER d_user WITH PASSWORD '1' NAME 'd';	He will fail.
He first has to start using this new role. SET ROLE sysadmin;	Now it is working.

If we try to create a new role, we will fail. CREATE ROLE ZZZ;
We are failing because default clause is "WITH ADMIN CURRENT_USER". We have to change this to "CURRENT_ROLE" (sysadmin role). CREATE ROLE ZZZ WITH ADMIN CURRENT_ROLE;

This is where we can see the difference between "WITH ADMIN CURRENT_USER" and "WITH ADMIN CURRENT_ROLE".

We can see in the sys.auths table that "c_user" created "d_user", because he has "sysadmin" role. The role "zzz" was created by the "sysadmin" role.  SELECT * FROM sys.user_role; SELECT * FROM sys.auths WHERE id IN ( 2, 15601, 15709, 15710 );

Grant a Role

We are still "c_user" and we will grant a role to "a_user". GRANT ZZZ TO a_user;	We will fail.
Just like previously, we have to grant a role as a CURRENT_ROLE. GRANT ZZZ TO a_user FROM CURRENT_ROLE;	Now it works.

The "a_user" will be now able to use "ZZZ" role, but he will not be able to grant that role further to the "b_user". I want to change that.

I will revoke "ZZZ" from "a_user". REVOKE ZZZ FROM a_user FROM CURRENT_ROLE;	Then, I will grant him the same role, but with ADMIN rights for that role. GRANT ZZZ TO a_user WITH ADMIN OPTION FROM CURRENT_ROLE;
We'll now jump to be "a_user". mclient -u a_user -d voc    #password "1" SET ROLE ZZZ; GRANT ZZZ TO b_user;	This works. Now "b_user" also has "ZZZ" role assigned.

Role Hierarchy

I will create two roles. Then I will create a hierarchy between them.

mclient -u monetdb -d voc    CREATE ROLE ParentRole; CREATE ROLE ChildRole;

I will now grant "ChildRole" to "ParentRole", with ADMIN OPTION. GRANT ChildRole TO ParentRole WITH ADMIN OPTION; This is how we create hierarchy. "ParentRole" can delegate "ChildRole".

I will then give some privilege to the ChildRole. Just so we can test it.

GRANT SELECT ON b_user.b_table TO ChildRole;

In the next step, I will grant only the ParentRole to the "c_user".

GRANT ParentRole TO c_user;

We haven't granted ChildRole to the "c_user" but he will still be able to use it by utilizing the hierarchy. mclient -u c_user -d voc    #password "1" SET ROLE ParentRole; GRANT ChildRole TO c_user FROM CURRENT_ROLE; SET ROLE ChildRole; SELECT * FROM b_user.b_table;

So, "c_user" will SET the role "ParentRole", and then he will use "WITH ADMIN OPTION" right to delegate "ChildRole" from the "ParentRole" to himself. After the grant, he can use SET ROLE ChildRole to get access to the table "b_user.b_table".

System Tables

Sistem table "sys.privilege_codes" contains code for all of the possible combinations of SELECT, UPDATE, INSERT, DELETE, TRUNCATE, GRANT. SELECT * FROM sys.privilege_codes ORDER BY privilege_code_id;

In the table "sys.privileges" we can find the list of the given privileges. On the image we can see that "b_user" gaved "c_user" SELECT grantable rights on the "a_table". SELECT * FROM sys.privileges WHERE obj_id = 15658;