In my organisation, we use an ETL (Extract, Transform, Load) tool to transform some data and subsequently load it into a different system. The tool has several job definitions that define the actual work that has to be done. In the past, only a few users used these jobs and there were only 2 jobs anyway. It was configured once and then mostly forgotten about. However, as part of quite a large project, many new jobs are needed (we are counting 40+) and we expect the influx of hundreds of new users.
To prevent user confusion and data loss due to errors, we are now required to apply security policies to the job definitions so users can only run the jobs that they need to run from their role. In any decent system, this would have been easy: define some groups, change the security settings of the jobs and you're done. However, in our tool, it is not that simple.
While the system does mention "groups" in its documentation, the only use of this is to load a list of users from Active Directory groups. In the configuration file, you have to put in a comma-separated list of all AD groups that you want users to be imported from. The tool does not have any way to use AD groups as they exist, it is also not possible to create groups yourself.
On the job security settings page, the "who can run this job?" setting is very basic. It more-or-less looks like this:
This little dialog has some very serious flaws:
- The system does not distinguish between domains. If you are "Jane.Doe" from a domain named "ENGINEERING", a "Jane.Doe" from the "MARKETING" domain can run the same jobs.
- AD groups are not reflected anywhere in the selection box, you have to manually select the users. If a user ever changes role or department you will have to adjust the jobs that were/are to be assigned to him/her.
- We have users that are a member of multiple AD groups, e.g. someone who does work in the Sales dept but also for Marketing. These users may appear twice in the list, but that does not always happen.
- If #3 is the case and you do an "Add all" (>>), the system bluntly moves all entries from the list on the left hand side to the right. When you press the "Save" button, you are presented with a constraints violation which shows you the exact SQL query that was being executed.
I looked up the definition of the table, here it is:
CREATE TABLE [dbo].[JobUserAccess](
[job_name] [varchar](400) NOT NULL,
[user_name] [varchar](400) NOT NULL,
CONSTRAINT [PK_JobUserAccess] PRIMARY KEY CLUSTERED
| job_name | user_name |
| ConsolidateMarketing | Paula.Bean |
| ArbitraryNumberExpander | John.Doe |
This is it. No foreign keys, no constraints, no logical IDs. Just some strings that are tossed around from the front-end to the back-end.
Overall, this tool is a joke. It's security system is not maintainable, the stuff with constraints violations is just insane and there are many, many more issues that we are facing with this tool.
To those who might say "that tool must have been built by an attic hobbyist!", I am afraid I will have to disappoint you. This tool was built by a very large company. I won't mention their name, but the best-run businesses use their products.