Update OwnCloud’s Admin UserID

After I installed OwnCloud on a local server, I realized that my choice of the administrative username was ambiguous and that I would never be able to remember it. The interface from OwnCloud allows you to change the full name of a user, but not the login name (user id) from a user via its interface. As OwnCloud stores its complete configuration in a database, I decided to update the information directly in the MySQL instance. The documentation of OwnCloud did not show exactly which tables join or use the ids of the user, so we needed to find out ourselves to be sure.

In a first step I created a dump of the OwnCloud database:

mysqldump -u root -p oc_database < oc_database.sql

The dump file contains the table descriptions as well as the INSERT statements you would need to recover the database. Therefore whenever the userid was used in the database, the INSERT statement would contain it. Thus an easy grep command delivers all records referencing or including the user id.

cat oc_database.sql | grep "YOUR-OLD-USERNAME"

You then get a list of thows lines in the file, which contain the userid you would like to change. As these are all INSERT statements, you can easily see which table is referencing the username. All you have to do now is to update all these tables, which have been delivered by the grep command. As the naming of the userid column is not completely consistent in OwnCloud, you should check each table individually, by issuing a descibe command beforehand:

DESCRIBE oc_contacts_addressbooks;

Then you can be sure which column you need to update. The UPDATE statement is potentially harmful, if you forget to set the WHERE clause properly, all you data will be overwritten. As we have a dump anyways, this would not be critical, but nevertheless its better to check in advance. You could verify which rows would be deleted by issuing a SELECT statement with the same WHERE clause before.

In my case the OwnCloud instance was rather young, so only a few tables contained traces of the old username. In my case, the following update statements have been necessary, your milage may vary. In some cases, the userid is not normalized (i.e. a seperate column), but used as a string in another column. One example for this would be the oc_storages table, where the userid is used in the column id like this “home::YOUR-NEW-USERNAME”. So remember to update the complete string, not just the username.

UPDATE oc_contacts_addressbooks SET userid="YOUR-NEW-USERNAME" WHERE userid="YOUR-OLD-USERNAME";
UPDATE oc_group_user SET uid="YOUR-NEW-USERNAME" WHERE uid="YOUR-OLD-USERNAME";
UPDATE oc_preferences SET userid="YOUR-NEW-USERNAME" WHERE userid="YOUR-OLD-USERNAME";
UPDATE oc_storages SET id="home::YOUR-NEW-USERNAME" WHERE id="home::YOUR-OLD-USERNAME";
UPDATE oc_users SET uid="YOUR-NEW-USERNAME"  WHERE uid="YOUR-OLD-USERNAME";

After this change, verify that a new data directory for the new user has been created and move the data accordingly.