Lost admin permissions

[rschirin]

After the dropping of otrs´s db and the reinstalling of it(with a softly modification) I lost the admin permissions for root@localhost user. how can I fix it?

[rschirin]

this is the sequence of imported file:
otrs-schema.mysql.sql
otrs-initial-insert.mysql.sql
otrs-schema-post.mysql.sql

this is what I add into initial-insert

Code: Select all

[size=85]# ----------------------------------------------------------
#  insert into table groups
# ----------------------------------------------------------
INSERT INTO groups (id, name, comments, valid_id, create_by, create_time, change_by, change_time)
    VALUES
    (1, 'G1', '', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table groups
# ----------------------------------------------------------
INSERT INTO groups (id, name, comments, valid_id, create_by, create_time, change_by, change_time)
    VALUES
    (2, 'G2', '', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table groups
# ----------------------------------------------------------
INSERT INTO groups (id, name, comments, valid_id, create_by, create_time, change_by, change_time)
    VALUES
    (3, 'G3', '', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table groups
# ----------------------------------------------------------
INSERT INTO groups (id, name, comments, valid_id, create_by, create_time, change_by, change_time)
    VALUES
    (4, 'G4', '', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table groups
# ----------------------------------------------------------
INSERT INTO groups (id, name, comments, valid_id, create_by, create_time, change_by, change_time)
    VALUES
    (5, 'G5', '', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table groups
# ----------------------------------------------------------
INSERT INTO groups (id, name, comments, valid_id, create_by, create_time, change_by, change_time)
    VALUES
    (6, 'G6', '', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table groups
# ----------------------------------------------------------
INSERT INTO groups (id, name, comments, valid_id, create_by, create_time, change_by, change_time)
    VALUES
    (7, 'G7', '', 1, 1, current_timestamp, 1, current_timestamp);

# ----------------------------------------------------------
#  insert into table group_user
# ----------------------------------------------------------
INSERT INTO group_user (user_id, group_id, permission_key, permission_value, create_by, create_time, change_by, change_time)
    VALUES
    (1, 1, 'rw', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table group_user
# ----------------------------------------------------------
INSERT INTO group_user (user_id, group_id, permission_key, permission_value, create_by, create_time, change_by, change_time)
    VALUES
    (1, 2, 'rw', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table group_user
# ----------------------------------------------------------
INSERT INTO group_user (user_id, group_id, permission_key, permission_value, create_by, create_time, change_by, change_time)
    VALUES
    (1, 3, 'rw', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table group_user
# ----------------------------------------------------------
INSERT INTO group_user (user_id, group_id, permission_key, permission_value, create_by, create_time, change_by, change_time)
    VALUES
    (1, 4, 'rw', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table group_user
# ----------------------------------------------------------
INSERT INTO group_user (user_id, group_id, permission_key, permission_value, create_by, create_time, change_by, change_time)
    VALUES
    (1, 5, 'rw', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table group_user
# ----------------------------------------------------------
INSERT INTO group_user (user_id, group_id, permission_key, permission_value, create_by, create_time, change_by, change_time)
    VALUES
    (1, 6, 'rw', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table group_user
# ----------------------------------------------------------
INSERT INTO group_user (user_id, group_id, permission_key, permission_value, create_by, create_time, change_by, change_time)
    VALUES
    (1, 7, 'rw', 1, 1, current_timestamp, 1, current_timestamp);[/size]

obviously, I replaced the insert groups and groups_user already existing into the file with my ¨insert¨

then I do the same thing with the queue

Code: Select all

[size=85]
# ----------------------------------------------------------
#  insert into table queue
# ----------------------------------------------------------
INSERT INTO queue (id, name, group_id, system_address_id, salutation_id, signature_id, follow_up_id, follow_up_lock, unlock_timeout, comments, valid_id, create_by, create_time, change_by, change_time)
    VALUES
    (1, 'Attrezzature aule audio, video, inf.', 1, 1, 1, 1, 1, 1, 0, 'All junk tickets.', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table queue
# ----------------------------------------------------------
INSERT INTO queue (id, name, group_id, system_address_id, salutation_id, signature_id, follow_up_id, follow_up_lock, unlock_timeout, comments, valid_id, create_by, create_time, change_by, change_time)
    VALUES
    (2, 'Attrezzature informatiche dipartimento', 1, 1, 1, 1, 1, 1, 0, 'All junk tickets.', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table queue
# ----------------------------------------------------------
INSERT INTO queue (id, name, group_id, system_address_id, salutation_id, signature_id, follow_up_id, follow_up_lock, unlock_timeout, comments, valid_id, create_by, create_time, change_by, change_time)
    VALUES
    (3, 'Prenotazioni lab. per esercitazioni', 1, 1, 1, 1, 1, 1, 0, 'All junk tickets.', 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------

# ----------------------------------------------------------
#  insert into table queue_standard_response
# ----------------------------------------------------------
INSERT INTO queue_standard_response (queue_id, standard_response_id, create_by, create_time, change_by, change_time)
    VALUES
    (1, 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table queue_standard_response
# ----------------------------------------------------------
INSERT INTO queue_standard_response (queue_id, standard_response_id, create_by, create_time, change_by, change_time)
    VALUES
    (2, 1, 1, current_timestamp, 1, current_timestamp);
# ----------------------------------------------------------
#  insert into table queue_standard_response
# ----------------------------------------------------------
INSERT INTO queue_standard_response (queue_id, standard_response_id, create_by, create_time, change_by, change_time)
    VALUES
    (3, 1, 1, current_timestamp, 1, current_timestamp);[/size]

but after that my root@localhost lost his permissions as admin.

[crythias]

There's a code I posted before ... it's in group_user
This is a helpful select query to verify what should be checked.

Code: Select all

SELECT groups.name, group_user.group_id, users.login, group_user.user_id, permission_key, permission_value FROM `group_user` left join users on users.id=group_user.user_id left join groups on groups.id = group_user.group_id where users.login='root@localhost' and groups.name = 'admin'

Code: Select all

UPDATE group_user set permission_key='rw', permission_value='1' where user_id=theuseridnumber and group_id=thegroupidnumber;

Use at your own risk. backup database before trying anything. If you don't want to do this, use a gui and hand change it. Backup database first. Changing database by hand is not recommended and if you don't know what you're doing, you can lose data. (That said, this is relatively painless. Just be smart.)

Also, among the other reasons you should not be using root@localhost.

[rschirin]

what does the first code?

is there a way to import more than one group or queue letting OTRS takes care to fix the database?

edit: how is it possible to restart otrs service on ubuntu? I noticed that if I use a query via SQL, otrs doesn't see it immediatly but I have to update something into the software. it seems as if it doesn't see immediately the new data into the database.

[crythias]

what does the first code?

It attempts to determine the permissions and user id for user and admin group. You get some appropriate numbers and put them in the second query. But if they don't exist, and you don't understand it, get some professional assistance.

is there a way to import more than one group or queue letting OTRS takes care to fix the database?

This is outside the scope of this topic. Create a new one. Also, don't create customer-based queues. Create queues based upon agents and types of work agents perform.

how is it possible to restart otrs service on ubuntu?

OTRS services are Cron, MySQL and Apache.
otrs/bin/Cron.sh stop
otrs/bin/Cron.sh start
read the README and documentation on starting and stopping other services

I noticed that if I use a query via SQL, otrs doesn't see it immediatly but I have to update something into the software

This is likely to do with caching. You can run the otrs delete cache utility.

[rschirin]

what do you mean with cache utility?

[caiobatalha]

I resolve this problem in my company

1) You need create a new user
2) You need configure rules to this user
3) If you can, you may change in console, the permission to the root

Go to Steeps:

1)
-- CREATE A NEW ADMIN
INSERT INTO `otrs`.`users` (`login`, `pw`, `title`, `first_name`, `last_name`, `valid_id`, `create_time`, `create_by`, `change_time`, `change_by`)
VALUES
('admin', '8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92', 'admin', 'Admin', 'OTRS', '1', current_timestamp, '1', current_timestamp, '1');
--LOGIN: admin
--PASSWORD: 123456

-- SEARCH ID OF NEW USER (USER_ID)
select id, login from users where login like 'admin'

2)
-- GRANT ROOT FEATURES TO NE USER
-- IN MY CASE, USER_ID IS 8
INSERT INTO group_user (user_id, group_id, permission_key, permission_value, create_by, create_time, change_by, change_time)
VALUES
(8, 1, 'rw', 1, 1, current_timestamp, 1, current_timestamp);

INSERT INTO group_user (user_id, group_id, permission_key, permission_value, create_by, create_time, change_by, change_time)
VALUES
(8, 2, 'rw', 1, 1, current_timestamp, 1, current_timestamp);

INSERT INTO group_user (user_id, group_id, permission_key, permission_value, create_by, create_time, change_by, change_time)
VALUES
(8, 3, 'rw', 1, 1, current_timestamp, 1, current_timestamp);

3) Now just log with this new user (admin - 123456), and reconfigure root@localhost or just use this new user, or bouth

Enjoy !

[root]

I resolve this problem in my company

1) You need create a new user

I strongly recommend using

Code: Select all

bin/otrs.AddUser.pl

instead of modifying the table.

2) You need configure rules to this user

This can be done by

Code: Select all

bin/otrs.AddUser2Role.pl
bin/otrs.AddUser2Group.pl

3) Now just log with this new user (admin - 123456), and reconfigure root@localhost or just use this new user, or bouth

It's always the best approach don't do regular administrative work with root@localhost. Just add an admin account for each administrator, it's free of charge.