Project

General

Profile

Forums » Appion and Leginon Web tools »

SOLVED: Can't remove users from a project?

Added by Patrick Goetz about 6 years ago

Hi -

Everything is in the subject. We've been experimenting with how best to compartmentalize raw data, as several research groups will be using our microscopes, and some of these are very worried about security. In any case, in the process of doing this we noticed that it appears to be impossible to remove users from a project. If I navigate to the Project DB tile, click on a project (that I own, but I'm also a myamiweb administrator), check the radio button next to the user I want to delete, and then click the [del] button, the only thing that happens is the check mark is removed next to the user's name.


Replies (6)

RE: Can't remove users from a project? - Added by Anchi Cheng about 6 years ago

I tried it on my system and it works fine. Please check delete privilege of the projectdb.projectowners table.

And try with this query (php format here) in mysql interface once you have $userid and $projectid figured out from the same table.

     $q = "DELETE FROM projectowners " 
        ."WHERE `REF|leginondata|UserData|user` = ".$userId." " 
        ."and `REF|projects|project`= ".$projectId." ";

RE: Can't remove users from a project? - Added by Patrick Goetz about 6 years ago

Figuring out what to delete from this table seems a little opaque:

mysql> use projectdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from projectowners;
+--------+---------------------+----------------------+-------------------------------+
| DEF_id | DEF_timestamp       | REF|projects|project | REF|leginondata|UserData|user |
+--------+---------------------+----------------------+-------------------------------+
|      1 | 2018-01-30 10:46:26 |                    3 |                             6 |
|      2 | 2018-03-09 16:23:57 |                    7 |                             3 |
|      3 | 2018-03-16 11:40:11 |                    8 |                             8 |
|      4 | 2018-03-16 11:40:39 |                    8 |                             7 |
|      5 | 2018-03-19 14:52:14 |                    5 |                             4 |
|      6 | 2018-03-27 14:35:18 |                    9 |                             8 |
|      7 | 2018-04-19 12:28:30 |                   10 |                            10 |
|      8 | 2018-04-19 12:34:19 |                   10 |                             9 |
|      9 | 2018-04-19 12:41:45 |                   11 |                            10 |
|     10 | 2018-05-24 10:49:10 |                    1 |                             5 |
|     11 | 2018-05-24 10:49:18 |                    1 |                             3 |
|     12 | 2018-05-24 10:49:25 |                    3 |                             5 |
|     13 | 2018-05-24 10:56:14 |                    2 |                             9 |
|     14 | 2018-05-24 10:56:42 |                    6 |                             5 |
|     15 | 2018-05-24 10:56:48 |                    6 |                             3 |
|     16 | 2018-05-24 10:57:37 |                    4 |                             3 |
|     17 | 2018-05-24 10:57:41 |                    4 |                             5 |
+--------+---------------------+----------------------+-------------------------------+
17 rows in set (0.00 sec)

It seems like there must be some other moving parts; e.g. something mapping the user name to a user ID?

RE: Can't remove users from a project? - Added by Anchi Cheng about 6 years ago

Yes, that is what the field name "REF|leginondata|UserData|user" means. Read this as "This field references a table called 'UserData' in the database referenced as leginondata. The alias of this reference is 'user'".

When you configure the webviewer in config.php and when you configure sinedon.cfg for your system, you defined what physical database is mapped to the "leginondata" database. If you used the standard convention, the physical database is call leginondb.

Therefore, the user name you are looking for is in leginondb.UserData.firstname and leginondb.UserData.lastname.

RE: Can't remove users from a project? - Added by Patrick Goetz almost 6 years ago

So, I'm guessing this is my problem? The DB_USER is usr_object

mysql> use projectdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> DELETE FROM projectowners WHERE 'REF|leginondata|UserData|user' = "9" and 'REF|projects|project' = "10";
ERROR 1142 (42000): DELETE command denied to user 'usr_object'@'localhost' for table 'projectowners'

Looking over my install notes, the instructions I have for setting projectdb privileges for the usr_object user are:

GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON leginondb.* TO 'usr_object'@'localhost';
GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON projectdb.* TO 'usr_object'@'localhost';
GRANT ALTER, CREATE, INSERT, SELECT, UPDATE ON `ap%`.* TO 'usr_object'@    'localhost';

Notice no delete privileges, which is why the delete isn't working (confirmed by querying the mysql.db table). Are my database setup instructions incorrect? If I'm missing the DELETE privilege, are there any other privileges I'm also missing?

RE: Can't remove users from a project? - Added by Patrick Goetz almost 6 years ago

Yep, you are correct. I executed

GRANT DELETE ON leginondb.ViewerImageStatus TO usr_object@'localhost';
GRANT DELETE ON projectdb.shareexperiments TO usr_object@'localhost';
GRANT DELETE ON projectdb.projectowners TO usr_object@'localhost';
GRANT DELETE ON projectdb.processingdb TO usr_object@'localhost';

as mysql root and now deleting users from projects works. I must have missed the Additional Database Server Setup page. Maybe it would be a good idea to provide a link for it here?
http://emg.nysbc.org/projects/leginon/wiki/Database_Server_Installation

    (1-6/6)