> The CLI Guy 

Please contact your system administrator...

Avaya CMS - Deletion of a Designer Report Owner

14 Jul 2019  ·  Comments:  ·  Tags: Avaya, Informix, SQL, Solaris

Summary

As a sysadmin one of my responsibilities is the management of an Avaya telephone system, a component of which is Avaya Call Management System (CMS). Avaya CMS collects call traffic data and provides a means of reporting against it. In addition to the reports included as standard, CMS also provides the ability to write your own, which in CMS terminology are referred to as Designer Reports.

I recently needed to delete the account of an ex-employee who had authored numerous designer reports. Before doing so, I wanted to understand the impact this would have on all the reports they’d authored, this post details my findings.

I was working with the following CMS product versions:

List the Designer Reports Owned by the User to be Deleted

Objective

I wanted to produce a list of designer reports where the user I was planning to delete was the owner. I couldn’t find a means of achieving this with Avaya CMS Supervisor, furthermore I failed to find any guidance in the CMS documentation or by searching online, so I decided to examine the database. You may wonder how I knew my way around the database, I didn’t. I just pieced things together by performing tests and analysing the results.

Database - Access

CMS R16 uses an Informix Dynamic Server (IDS) database. I didn’t want to install any additional software on the CMS server or have to open any firewall ports between my workstation and the server in order to access the database. Informix ships with a utility called DB-Access, the purpose of which is to access and manipulate Informix databases. It has a menu-driven text user interface and a command line interface, all you need to use it is an SSH session to the server.

DB-Access itself doesn’t require a root account in order to use it, however it is required in order to query the CMS DB for a list of designer reports owned by a specific user due to the way database-level privileges have been assigned. I’ll return to the subject of privileges later.

So with that said, to use DB-Access:

dbaccess cms@cms_ol - <<!
SELECT * FROM users; 
!

Database - Privileges

I’ll attempt to explain how privileges have been assigned in the cms database and why it’s necessary to use a root account. This is a bit of a tangent from the subject of this post but it’s worth mentioning.

In Informix, you don’t create user accounts, instead you grant privileges to operating system accounts. Each database has a sysusers table in which database-level privileges are recorded and a systabauth table in which table-level privileges are recorded. The username field of the sysusers table and the grantee field of the systabauth table hold the OS usernames.

NB: I won’t be covering assigning/modifying Informix privileges in this post, however I will say that you should do so using GRANT, NOT by modifying the sysusers and systabauth tables directly. Please consult the Informix documentation for details.

Let’s start by ascertaining the database-level privileges with the following query:

dbaccess cms@cms_ol - <<!
SELECT username, usertype FROM cms:sysusers;
!

The result shows that root has a usertype of D which denotes DBA privilege and public (which means any user) has usertype of R which denotes Resource privilege:

username                         usertype

root                             D
public                           R

2 row(s) retrieved.

This means that root effectively has free-reign of the database. All other users (public) have the second most powerful privilege assigned (Resource) enabling queries to be executed provided they have the necessary privileges on the target table(s).

For the purpose of the objective, we need to perform a SELECT query against the users and custobjects tables of the cms database. Let’s check the table-level privileges on each table using the following queries:

dbaccess cms@cms_ol - <<!
SELECT * FROM systabauth
  WHERE tabid = (SELECT tabid FROM systables
                   WHERE tabname = 'users');
!
dbaccess cms@cms_ol - <<!
SELECT * FROM systabauth
  WHERE tabid = (SELECT tabid FROM systables
              WHERE tabname = 'custobjects');
!

Both queries return zero rows indicating that neither table has any table-level privileges assigned.

In conclusion, since there are no table-level privileges on users and custobjects, only a root account can be used to accomplish the objective.

If you work in an environment where multiple administrators work on CMS and they are not permitted to share a single root account then there are a few options, such as:

# Create a new user with a UID of 0:
# NB: I'd normally make bash the default shell (useradd -s /bin/bash ...) but 
# it wasn't present on my Solaris 10 server.
  useradd -u 0 -o root2
  passwd root2
  
# Set the UID of an existing user to 0:
  usermod -u 0 -o root2

Database - Query

This query will list all reports owned by thecliguy:

dbaccess cms@cms_ol - <<!
SELECT custobjects.name AS report_name, custobjects.subsystem, 
  custobjects.owner AS owner_id, users.l_name AS owner_username, 
  custobjects.path AS report_path
  FROM custobjects 
  INNER JOIN users ON custobjects.owner=users.UID
  WHERE users.l_name = 'thecliguy'
!

Example result:

report_name     Agent Group Report (modified)
subsystem       Real-Time
owner_id        15
owner_username  thecliguy
report_path     30

report_name     Group Attendance Weekly
subsystem       Historical
owner_id        15
owner_username  thecliguy
report_path     76

report_name     VDN Performance Report Daily
subsystem       Historical
owner_id        15
owner_username  thecliguy
report_path     77

3 row(s) retrieved.

I think that all the field and alias names are pretty self-explanatory except for report_path. Based on my own observations and from reading various Avaya related forum posts, CMS stores designer reports as files in /cms/cow/reports/designer. Files are named with a number, this is what the value of the report_path field refers to.

Deleting the User

Option 1: Change the Owner of Each Report Prior to Deleting the User

Now that we know which reports are owned by the user to be deleted, prior to deletion we can use Avaya CMS Supervisor to navigate to each report and change the value of the Owner field to the name of another user:

CMS Report Properties

When prompted, select Overwrite the existing report: CMS Report Properties

NB: At one point I found that CMS Supervisor would produce the following error after selecting Overwrite the existing report:

---------------------------
Avaya CMS Supervisor
---------------------------
The operation could not be completed.

Error: 28031 - This is not a valid object
Location: frmSelectRept:cmdProp_Click
---------------------------
OK
---------------------------

This was resulting in report names being prefixed with three tildes (~~~) and the owner failing to be updated. To resolve this, I just had to close and reopen CMS Supervisor.

Option 2: Delete the User and Allow CMS Supervisor to Change the Owner of Each Report

An alternative to changing the owner of each report is to just delete the user with Avaya CMS Supervisor, it’ll take care of the change of ownership for you. However, there’s a quirk, read on for details…

To delete a user navigate to: Tools > User Permissions > User Data > Find the user you wish to delete and press the delete button.

On pressing delete, two Yes/No dialogue boxes are produced:

  1. User may have files which will be removed, do you still want to delete this user?
  2. User has designer reports, do you want to move the reports to your id?

The first is presented when deleting any user. The second is presented only when deleting a user who is the owner of designer reports.

When selecting Yes to the second dialogue, ownership of designer reports is changed to the user performing the deletion. However, as I alluded to earlier there’s a quirk… After deletion, the owner field of each affected report contains the previous owner’s ID instead of the current owner’s name:

CMS Report Properties After Deletion of Owner

Querying the database’s custobjects table shows that ownership has been updated, so it occurred to me that CMS Supervisor was using some other data source. I found that closing CMS Supervisor, deleting its cache file (%APPDATA%\Avaya\CMS Supervisor R18\Cache\CVS_Cache.tmp) and then reopening caused the owner to display correctly.

It’s worth noting what happens if you click No to the second dialogue; the reports that were owned by the deleted user are removed from the custobjects table and the files removed from /cms/cow/reports/designer. However, the reports remain visible within CMS Supervisor. Attempting to run such a report results in the error Report does not exist on the server. followed by The operation could not be completed. Error: 28011 - Does not exist. Location: frmSelectRept:RSel_SelReport. To resolve this, close CMS Supervisor, delete the cache file (%APPDATA%\Avaya\CMS Supervisor R18\Cache\CVS_Cache.tmp) and reopen CMS Supervisor, after which the reports will no longer be visible.

Resources

To assist with the writing of this post I needed to be able to experiment with Solaris and Informix in a sandboxed environment away from my production CMS server.

Solaris 10 is available to download free of charge from Oracle for non-production use under the OTN licence. Downloads are available in the form of an ISO file and a VM template for Oracle VM VirtualBox.

A Developer Edition of Informix IDS is available to download free of charge from IBM.

Comments

Leaving comments has been disabled for this post.

Copyright © 2018 - 2022 thecliguy.co.uk
For details, see Licences and Copyright