Change "root@localhost" Validity to "valid" via Database
Moderator: crythias
-
- Znuny newbie
- Posts: 64
- Joined: 09 Feb 2015, 12:03
- Znuny Version: 4.0.5
- Real Name: Zohair Yousafi
Change "root@localhost" Validity to "valid" via Database
I was playing around with access rights and unfortunately I removed the "Admin" access rights from my user. I had also disabled the "root@localhost" user by making it "invalid" in the system. How can I connect to the database and change the validity of the "root@localhost" user back to "valid" so I can login and fix this issue I have created?
-
- Znuny guru
- Posts: 2210
- Joined: 13 Mar 2014, 09:16
- Znuny Version: 6.0.14
- Real Name: Rolf Straub
Re: Change "root@localhost" Validity to "valid" via Database
Script otrs.AddUser2Group.pl accepts a param -ValidID (which is not shown if you just execute the script). Try to fix your account with that script.
Edit: Didn't read through. The script sets itself to valid, no matter what. But you could try to fix your user with it anyhow, maybe setting the user to valid is done implicitly.
Edit: Didn't read through. The script sets itself to valid, no matter what. But you could try to fix your user with it anyhow, maybe setting the user to valid is done implicitly.
Currently using: OTRS 6.0.14 -- MariaDB -- Ubuntu 16 LTS
-
- Znuny newbie
- Posts: 64
- Joined: 09 Feb 2015, 12:03
- Znuny Version: 4.0.5
- Real Name: Zohair Yousafi
Re: Change "root@localhost" Validity to "valid" via Database
Hi RStraub,
Thank you for the prompt response. I am sorry I don't quite understand what you mean. How do I use that script to "fix" the user? Meaning how do I execute the script? I am sorry but I am a newbie and I don't quite understand how to "fix" the user.
Regards,
Zohair Yousafi
Thank you for the prompt response. I am sorry I don't quite understand what you mean. How do I use that script to "fix" the user? Meaning how do I execute the script? I am sorry but I am a newbie and I don't quite understand how to "fix" the user.
Regards,
Zohair Yousafi
-
- Znuny guru
- Posts: 2210
- Joined: 13 Mar 2014, 09:16
- Znuny Version: 6.0.14
- Real Name: Rolf Straub
Re: Change "root@localhost" Validity to "valid" via Database
Just tried to use it, it does NOT set the user to valid. Guess back to your idea then to update the user in the DB:
1) Login to your OTRS-DB
2) Exeucte this line (if you're using mysql):
3) Delete the cache and rebuild config:
1) Login to your OTRS-DB
2) Exeucte this line (if you're using mysql):
Code: Select all
update users set valid_id=1 where login=root@localhost;
Code: Select all
cd /opt/otrs/
bin/otrs.DeleteCache.pl
bin/otrs.RebuildConfig.pl
Currently using: OTRS 6.0.14 -- MariaDB -- Ubuntu 16 LTS
-
- Znuny newbie
- Posts: 64
- Joined: 09 Feb 2015, 12:03
- Znuny Version: 4.0.5
- Real Name: Zohair Yousafi
Re: Change "root@localhost" Validity to "valid" via Database
Hi RStraub,
I am not using MySQL unfortunately. I am using PostgreSQL as that was the default database when I installed OTRS using the OTRS Appliance. Basically the setup is that I installed the OTRS Appliance on the Oracle VM VirtualBox. I can connect to all of the files using FileZilla; however I can't seem to find the database nor do I understand how I can connect to the database. I would imagine the update command in PostgreSQL would be similar?
Regards,
Zohair Yousafi
I am not using MySQL unfortunately. I am using PostgreSQL as that was the default database when I installed OTRS using the OTRS Appliance. Basically the setup is that I installed the OTRS Appliance on the Oracle VM VirtualBox. I can connect to all of the files using FileZilla; however I can't seem to find the database nor do I understand how I can connect to the database. I would imagine the update command in PostgreSQL would be similar?
Regards,
Zohair Yousafi
-
- Znuny guru
- Posts: 2210
- Joined: 13 Mar 2014, 09:16
- Znuny Version: 6.0.14
- Real Name: Rolf Straub
Re: Change "root@localhost" Validity to "valid" via Database
I think that would be the same, yes.
You can check your postgress login data in this file (if the appliance uses it as well):
/opt/otrs/Kernel/Config.pm
then connect to your database via
You can check your postgress login data in this file (if the appliance uses it as well):
/opt/otrs/Kernel/Config.pm
then connect to your database via
Code: Select all
psql DBNAME USERNAME
Currently using: OTRS 6.0.14 -- MariaDB -- Ubuntu 16 LTS
-
- Znuny newbie
- Posts: 64
- Joined: 09 Feb 2015, 12:03
- Znuny Version: 4.0.5
- Real Name: Zohair Yousafi
Re: Change "root@localhost" Validity to "valid" via Database
I tried but I am getting the following error:
What am I doing wrong?
Code: Select all
login as: root
root@192.168.0.225's password:
Linux otrs 3.2.0-0.bpo.4-amd64 #1 SMP Debian 3.2.63-2+deb7u1~bpo60+1 x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
root@otrs:~# psql otrs otrs
psql: FATAL: Ident-Authentifizierung f?r Benutzer >>otrs<< fehlgeschlagen
root@otrs:~#
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: Change "root@localhost" Validity to "valid" via Database
Code: Select all
psql -h 127.0.0.1 DBNAME USERNAME
Code: Select all
bin/otrs.AddUser.pl -f firstname -l lastname -p password -g admin -e user@example.com username
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny newbie
- Posts: 64
- Joined: 09 Feb 2015, 12:03
- Znuny Version: 4.0.5
- Real Name: Zohair Yousafi
Re: Change "root@localhost" Validity to "valid" via Database
Hi crythias,
Thank you for helping me connect to the database. I have tried running the update query but the user is still not logging in so that leads me to believe the query has not run successfully. I then tried creating a new user; however that user won't login either. Here's the entries that I made:
The username that I think I created is "admin" with the password "12". If that's true then why isn't the user logging in?
Thank you for helping me connect to the database. I have tried running the update query but the user is still not logging in so that leads me to believe the query has not run successfully. I then tried creating a new user; however that user won't login either. Here's the entries that I made:
Code: Select all
otrs=> update users set valid_id=1 where login=root@localhost;
FEHLER: Spalte »root« existiert nicht
LINE 1: update users set valid_id=1 where login=root@localhost;
^
otrs=> bin/otrs.AddUser.pl -f Zohair -l Yousafi -p 12 -g admin -e zy@soloinsight.com admin
otrs->
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: Change "root@localhost" Validity to "valid" via Database
note this should be a shell command, not a psql command.zyousafi wrote:otrs=> bin/otrs.AddUser.pl -f Zohair -l Yousafi -p 12 -g admin -e zy@soloinsight.com admin
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny newbie
- Posts: 64
- Joined: 09 Feb 2015, 12:03
- Znuny Version: 4.0.5
- Real Name: Zohair Yousafi
Re: Change "root@localhost" Validity to "valid" via Database
Thank you for pointing me in the right direction; however I am getting another error now:
How can the file not exist? Am I in the wrong directory?
Code: Select all
login as: root
root@192.168.0.225's password:
Linux otrs 3.2.0-0.bpo.4-amd64 #1 SMP Debian 3.2.63-2+deb7u1~bpo60+1 x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Tue May 26 19:12:19 2015 from 192.168.0.98
root@otrs:~# bin/otrs.AddUser.pl -f Zohair -l Yousafi -p 12 -g admin -e zy@soloinsight.com admin
-bash: bin/otrs.AddUser.pl: No such file or directory
root@otrs:~#
-
- Znuny guru
- Posts: 2210
- Joined: 13 Mar 2014, 09:16
- Znuny Version: 6.0.14
- Real Name: Rolf Straub
Re: Change "root@localhost" Validity to "valid" via Database
As root user, you probably are.
Try (before executing your line):
If that does not help, try (as root this time):
Try (before executing your line):
Code: Select all
su - otrs
cd /opt/otrs/
Code: Select all
updatedb
locate otrs.AddUser.pl
Currently using: OTRS 6.0.14 -- MariaDB -- Ubuntu 16 LTS
-
- Znuny newbie
- Posts: 64
- Joined: 09 Feb 2015, 12:03
- Znuny Version: 4.0.5
- Real Name: Zohair Yousafi
Re: Change "root@localhost" Validity to "valid" via Database
Well RStraub I get a message that says:
That's great and thank you for that but when I try logging in with the username "admin" and the password "12" I get an error message that says the username or password your entered is incorrect.
Code: Select all
User - added. User id is 19.
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: Change "root@localhost" Validity to "valid" via Database
Yes. Any commands that are otrs specific are intended to be run relative to the OTRS install directory. In linux, this is usually /opt/otrs (but could be anywhere) and in Windows probably c:\program files\OTRS\OTRSzyousafi wrote:Code: Select all
root@otrs:~# bin/otrs.AddUser.pl -f Zohair -l Yousafi -p 12 -g admin -e zy@soloinsight.com admin -bash: bin/otrs.AddUser.pl: No such file or directory root@otrs:~#
How can the file not exist? Am I in the wrong directory?
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny newbie
- Posts: 64
- Joined: 09 Feb 2015, 12:03
- Znuny Version: 4.0.5
- Real Name: Zohair Yousafi
Re: Change "root@localhost" Validity to "valid" via Database
Thank you for all of the help you guys but it seems the problem is far bigger than I originally thought. I was able to access the database with your help and I was able to change the validity of the "root@localhost" user; however it seems that the "root@localhost" user has lost "Admin" rights. I logged in with "root@localhost" but I can't see the "Admin" button at the top of the page. How can this be possible? All I did was change the validity of the "root@localhost" user to "Invalid". Does that cancel the user's access rights? Please keep in mind I have deactivated "Agent-Group" relations.
-
- Znuny newbie
- Posts: 64
- Joined: 09 Feb 2015, 12:03
- Znuny Version: 4.0.5
- Real Name: Zohair Yousafi
Re: Change "root@localhost" Validity to "valid" via Database
Why does the "root@localhost" user not have access to the "Admin" tab? Here is the output from the group_user table in OTRS:
user_id 1 = root@localhost
group_id 2 = admin
permission_key 'rw' = read/write
Code: Select all
otrs=> select * from group_user;
user_id | group_id | permission_key | permission_value | create_time | create_by | change_time | change_by
---------+----------+----------------+------------------+---------------------+-----------+---------------------+-----------
1 | 2 | rw | 1 | 2015-05-27 13:50:50 | 1 | 2015-05-27 13:50:50 | 1
1 | 1 | rw | 1 | 2015-05-27 13:56:17 | 1 | 2015-05-27 13:56:17 | 1
1 | 3 | rw | 1 | 2015-05-27 13:57:12 | 1 | 2015-05-27 13:57:12 | 1
(3 rows)
group_id 2 = admin
permission_key 'rw' = read/write
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: Change "root@localhost" Validity to "valid" via Database
How did you disable agent group relations and why? And how do you have a user that is admin now that it matters root@localhost?
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny newbie
- Posts: 64
- Joined: 09 Feb 2015, 12:03
- Znuny Version: 4.0.5
- Real Name: Zohair Yousafi
Re: Change "root@localhost" Validity to "valid" via Database
I disabled Agent<>Group Relations via the "SysConfig" option. The logic was that groups were going to be used for access rights and roles were going to be used for assigning those access rights to agents therefore I only needed the Agent<>Role Relations and not the Agent<>Group Relations. When I set OTRS the very first thing I did was create an agent and give that agent full access to everything via an admin role that I created. When I did that, and I have been administering OTRS for our company now for the past several months, I made the "root@localhost" agent invalid. Unfortunately while testing a few new settings I removed 'rw' access from the admin role that I created and I was no longer able to access to "Admin" option from OTRS.
I have run queries on the DB directly and made sure all of the access rights are there in the DB; however what's in the DB is not reflecting now on the front-end.
I have run queries on the DB directly and made sure all of the access rights are there in the DB; however what's in the DB is not reflecting now on the front-end.
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: Change "root@localhost" Validity to "valid" via Database
Sounds like you need to make sure root@localhost is in the admin role
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny newbie
- Posts: 64
- Joined: 09 Feb 2015, 12:03
- Znuny Version: 4.0.5
- Real Name: Zohair Yousafi
Re: Change "root@localhost" Validity to "valid" via Database
root@localhost is definitely in the admin role:
user_id 1 = root@localhost
role_id 1 = admin
Code: Select all
user_id | role_id | create_time | create_by | change_time | change_by
---------+---------+---------------------+-----------+---------------------+-----------
1 | 1 | 2015-05-27 14:30:07 | 1 | 2015-05-27 14:30:07 | 1
role_id 1 = admin
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: Change "root@localhost" Validity to "valid" via Database
ok, well, like I said, you now have an admin user, so root@localhost isn't so important.
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny newbie
- Posts: 64
- Joined: 09 Feb 2015, 12:03
- Znuny Version: 4.0.5
- Real Name: Zohair Yousafi
Re: Change "root@localhost" Validity to "valid" via Database
Yes I have an "admin" user but the "admin" user does not have access to the "Admin" tab. That's what the issue is. Basically I have been "locked" out of my own system...
-
- Znuny newbie
- Posts: 64
- Joined: 09 Feb 2015, 12:03
- Znuny Version: 4.0.5
- Real Name: Zohair Yousafi
Re: Change "root@localhost" Validity to "valid" via Database
I thought if I was able to enable the "root@localhost" user then I would get the access that I needed; however that just hasn't been the case. The default "root@localhost" user does not have the "Admin" tab nor does the "admin" user that I created for myself.
-
- Moderator
- Posts: 10169
- Joined: 04 May 2010, 18:38
- Znuny Version: 5.0.x
- Location: SouthWest Florida, USA
- Contact:
Re: Change "root@localhost" Validity to "valid" via Database
look at Kernel/Config/Files/ZZZAAuto.pm
Search for:
look at the words listed under 'Group'.
Look for existing groups or just empty it out for the purpose of testing:
This should allow everyone to have access to admin group. But it seems that you've got other issues to resolve like finding and resetting that group membership you've disabled.
Search for:
Code: Select all
$Self->{'Frontend::Module'}->{'Admin'} = {
'Description' => 'Admin-Area',
'Group' => [
'admin'
],
Look for existing groups or just empty it out for the purpose of testing:
Code: Select all
''
OTRS 6.0.x (private/testing/public) on Linux with MySQL database.
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
Please edit your signature to include your OTRS version, Operating System, and database type.
Click Subscribe Topic below to get notifications. Consider amending your topic title to include [SOLVED] if it is so.
Need help? Before you ask
-
- Znuny newbie
- Posts: 64
- Joined: 09 Feb 2015, 12:03
- Znuny Version: 4.0.5
- Real Name: Zohair Yousafi
Re: Change "root@localhost" Validity to "valid" via Database
Issue resolved. I gave "Admin" access to another group (i.e. the group was called "gccr") within OTRS via the "ZZZAAuto.pm" file. I logged in with that user. Gave myself the rights I needed. Thank you crythias for all of your help!