Change "root@localhost" Validity to "valid" via Database

Moderator: crythias

Post Reply
zyousafi
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

Post by zyousafi »

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?
RStraub
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

Post by RStraub »

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.
Currently using: OTRS 6.0.14 -- MariaDB -- Ubuntu 16 LTS
zyousafi
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

Post by zyousafi »

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
RStraub
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

Post by RStraub »

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):

Code: Select all

update users set valid_id=1 where login=root@localhost;
3) Delete the cache and rebuild config:

Code: Select all

cd /opt/otrs/
bin/otrs.DeleteCache.pl
bin/otrs.RebuildConfig.pl
Currently using: OTRS 6.0.14 -- MariaDB -- Ubuntu 16 LTS
zyousafi
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

Post by zyousafi »

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
RStraub
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

Post by RStraub »

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

Code: Select all

psql DBNAME USERNAME
Currently using: OTRS 6.0.14 -- MariaDB -- Ubuntu 16 LTS
zyousafi
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

Post by zyousafi »

I tried but I am getting the following error:

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:~#
What am I doing wrong?
crythias
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

Post by crythias »

Code: Select all

psql -h 127.0.0.1 DBNAME USERNAME
but it'd be better to just add a new admin user:

Code: Select all

bin/otrs.AddUser.pl -f firstname -l lastname -p password -g admin -e user@example.com username
Then enable the root@localhost once you're in.
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
zyousafi
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

Post by zyousafi »

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:

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->
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?
crythias
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

Post by crythias »

zyousafi wrote:otrs=> bin/otrs.AddUser.pl -f Zohair -l Yousafi -p 12 -g admin -e zy@soloinsight.com admin
note this should be a shell command, not a psql command.
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
zyousafi
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

Post by zyousafi »

Thank you for pointing me in the right direction; however I am getting another error now:

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:~#
How can the file not exist? Am I in the wrong directory?
RStraub
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

Post by RStraub »

As root user, you probably are.

Try (before executing your line):

Code: Select all

su - otrs
cd /opt/otrs/
If that does not help, try (as root this time):

Code: Select all

updatedb
locate otrs.AddUser.pl
Currently using: OTRS 6.0.14 -- MariaDB -- Ubuntu 16 LTS
zyousafi
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

Post by zyousafi »

Well RStraub I get a message that says:

Code: Select all

User - added. User id is 19.
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.
crythias
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

Post by crythias »

zyousafi 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?
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\OTRS
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
zyousafi
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

Post by zyousafi »

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.
zyousafi
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

Post by zyousafi »

Why does the "root@localhost" user not have access to the "Admin" tab? Here is the output from the group_user table in OTRS:

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)
user_id 1 = root@localhost
group_id 2 = admin
permission_key 'rw' = read/write
crythias
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

Post by crythias »

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
zyousafi
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

Post by zyousafi »

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.
crythias
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

Post by crythias »

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
zyousafi
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

Post by zyousafi »

root@localhost is definitely in the admin role:

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
user_id 1 = root@localhost
role_id 1 = admin
crythias
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

Post by crythias »

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
zyousafi
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

Post by zyousafi »

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...
zyousafi
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

Post by zyousafi »

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.
crythias
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

Post by crythias »

look at Kernel/Config/Files/ZZZAAuto.pm
Search for:

Code: Select all

$Self->{'Frontend::Module'}->{'Admin'} =  {
  'Description' => 'Admin-Area',
  'Group' => [
    'admin'
  ],
look at the words listed under 'Group'.
Look for existing groups or just empty it out for the purpose of testing:

Code: Select all

''
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.
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
zyousafi
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

Post by zyousafi »

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! :)
Post Reply