Class: Ucert::DbDirectTracker

Inherits:
Object
  • Object
show all
Includes:
Utils
Defined in:
lib/ucert/db_direct_tracker.rb

Overview

Class to handle db for BE user account IDs

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods included from Utils

#file_2_list, #is_fqdn?, #is_ip?, #is_url?, #list_2_file, #load_known_user_map_from_file, #nslookup, #search_ad, #update_dn

Constructor Details

#initialize(params = {}) ⇒ DbDirectTracker

Instance default variables



20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
 
# File 'lib/ucert/db_direct_tracker.rb', line 20

def initialize (params ={})
   @verbose=params.fetch(:verbose, false)
   # DB-Direct user entitlement report generation: Logon to Deutsche Bank Direct sites https://db-direct.us.gtb.db.com/
   # Once logon, click "Administration" -> "Users", select "Maintenance Users" to lauch the application. Refer to Capture_main.PNG.
   #
	# In the application window, select "Reports" -> "Active User Reports" -> "Account Permissions"; in the report generation form, press "DOWNLOAD CSV" button
	# then save the report to a local drive. Open the CSV file in Excel. Save the file again into Excel Workbook format.
	#  Refer to Capture_rpt.PNG for the screenshot
	# Note: Limitation of the current user permission report - it does not list the admin users who are setup by the DB account manager
   @db_user_entitlement_report = File.dirname(__FILE__)+"/../../data/db_direct/accountpermission.xlsx"
   # db Access to AD user map file
   @file_db_user_map =  File.dirname(__FILE__)+"/../../data/db_direct/db_direct_user_map.txt"
	# Load user map from the local cacsh file
	@db_2_ad_user=load_known_user_map_from_file(@file_db_user_map)
	# Load the user entitlement instance variable from the user report
	@db_user_entitlement=parse_db_user_entitlement_report(@db_user_entitlement_report)
	# Procedure to add DN foreign key to the @db_user_entitlement, by performing the AD search
	insert_dn
	# Save the user map to local cache file
	save!
end

Instance Attribute Details

#db_2_ad_user ⇒ Object (readonly)

Returns the value of attribute db_2_ad_user.



17
18
19
 
# File 'lib/ucert/db_direct_tracker.rb', line 17

def db_2_ad_user
  @db_2_ad_user
end

#db_user_entitlement ⇒ Object (readonly)

Returns the value of attribute db_user_entitlement.



17
18
19
 
# File 'lib/ucert/db_direct_tracker.rb', line 17

def db_user_entitlement
  @db_user_entitlement
end

#db_user_entitlement_report ⇒ Object

Class constant variables



16
17
18
 
# File 'lib/ucert/db_direct_tracker.rb', line 16

def db_user_entitlement_report
  @db_user_entitlement_report
end

#db_user_group_entitlement ⇒ Object (readonly)

Returns the value of attribute db_user_group_entitlement.



17
18
19
 
# File 'lib/ucert/db_direct_tracker.rb', line 17

def db_user_group_entitlement
  @db_user_group_entitlement
end

#db_user_group_entitlement_report ⇒ Object

Class constant variables



16
17
18
 
# File 'lib/ucert/db_direct_tracker.rb', line 16

def db_user_group_entitlement_report
  @db_user_group_entitlement_report
end

#file_db_user_map ⇒ Object

Class constant variables



16
17
18
 
# File 'lib/ucert/db_direct_tracker.rb', line 16

def file_db_user_map
  @file_db_user_map
end

#verbose ⇒ Object

Class constant variables



16
17
18
 
# File 'lib/ucert/db_direct_tracker.rb', line 16

def verbose
  @verbose
end

Instance Method Details

#db_search_by_dn(dn) ⇒ Object Also known as: search_by_dn

Search user entitlement record by AD DN



190
191
192
193
194
195
196
197
198
199
200
 
# File 'lib/ucert/db_direct_tracker.rb', line 190

def db_search_by_dn (dn)
	begin
     puts "Perform search on the user entitlement records by AD DN: #{dn}" if @verbose
     @db_user_entitlement.each do |key, val|
         return val if @db_user_entitlement[key]['DN'].eql? dn
     end
		return nil
   rescue => ee
     puts "Exception on method #{__method__}: #{ee}"
   end
end

#dn_2_index(dn) ⇒ Object

Retrieve the user index from the @db_user_entitlement data structure



105
106
107
108
109
110
111
112
113
 
# File 'lib/ucert/db_direct_tracker.rb', line 105

def dn_2_index (dn)
		begin
       (1..@db_user_entitlement.count).map do |index|
         return index if @db_user_entitlement[index]["DN"]==dn
       end
		rescue => ee
			puts "Exception on method #{__method__}: #{ee}"
		end
end

#insert_dn ⇒ Object

Procedures to add additonal field ‘dn’ into the @db_user_entitlement data structure, by person the AD search



116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
 
# File 'lib/ucert/db_direct_tracker.rb', line 116

def insert_dn
		begin
			tracker = Ucert::AdTracker.new(:verbose=>false)
			 @db_user_entitlement.each do |index, record|
				puts "\n\nPerform DN lookup for record: #{record}" if @verbose
				key1 = record['WebSSO Id'] if record['WebSSO Id']
				key2 = record['User Id'] if record['User Id']
				key3 = record['First Name'] + record['Last Name'] if record['First Name'] and record['Last Name']
				my_key = record['User Id'].upcase
				puts "Perform 1st order search from the local cache: #{my_key}" if @verbose
				if @db_2_ad_user.key?(my_key)
					dn=@db_2_ad_user[my_key]
					# additional logic to update the existing DN record
					unless tracker.ad_person_records.key?(dn)
						dn = update_dn(tracker,dn)
					end
					puts "Found in the local cache file: #{dn}" if @verbose
				else
					if dn.nil? and !key1.nil?
						puts "Perform 2nd order search only if the 1st one fail, by using: #{key1}" if @verbose
						dn = tracker.ad_search_by_text(key1, "person")
					end
					if dn.nil? and !key2.nil?
						puts "Perform 3rd order search only if the last fail, by using: #{key2}" if @verbose
						dn = tracker.ad_search_by_text(key2, "person")
					end
					if dn.nil? and !key3.nil?
						puts "Perform 4th order search only if the last fail, by using: #{key3}" if @verbose
						dn = tracker.ad_search_by_text(key3, "person")
					end
				end
				@db_user_entitlement[index]['DN'] = dn
			end
			tracker=nil
		rescue => ee
			puts "Exception on method #{__method__}: #{ee}"
		end
end

#parse_db_user_entitlement_report(file) ⇒ Object

Parsing the db Access user entitlement report in text format



43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
# File 'lib/ucert/db_direct_tracker.rb', line 43

def parse_db_user_entitlement_report (file)
     begin
			puts "Parse the user entitlement report: #{file}" if @verbose
     	db_user_entitlement=Hash.new
     	user_index=1
			row_cnt=0
			header=Array.new
			workbook = RubyXL::Parser.parse(file)
       worksheet = workbook[0]
       worksheet.count.times do |row|
				row_cnt+=1
				puts "\nParsing workbook row: #{row_cnt}" if @verbose
				entry=Array.new
				# Processing Header Row
				if row_cnt==1
					0.upto(worksheet[row].size) do |col|
						if worksheet[row][col].nil?
							header.push(nil)
						else
							header.push(worksheet[row][col].value.to_s)
						end
					end
					next
				else  # Processing the record row
					0.upto(worksheet[row].size) do |col|
						if worksheet[row][col].nil?
							entry.push(nil)
						else
							entry.push(worksheet[row][col].value.to_s)
						end
					end
				end
				record = header.zip(entry)
				puts "Processing record:\n #{header}\n\n#{record}" if @verbose
         #user_index+=1
         record_h=Hash[record[0..6]]
				right_h=Hash[record[7..17]]
				puts "Adding DB-direct Entitlement Record: #{record_h}" if @verbose
         if db_user_entitlement.key?(user_index)
					puts "Checking record User_ID field match: #{db_user_entitlement[user_index]['User Id']}, #{record_h['User Id']}" if @verbose
					if db_user_entitlement[user_index]["User Id"] === record_h["User Id"]
						db_user_entitlement[user_index]["Entitlements"].push(right_h)
					else
						user_index+=1
						db_user_entitlement[user_index]=record_h
						db_user_entitlement[user_index]["Entitlements"]=[right_h]
						puts "Processing user record number: #{user_index}" if @verbose
					end
				else
					db_user_entitlement[user_index]=Hash.new
					db_user_entitlement[user_index]=record_h
					db_user_entitlement[user_index]["Entitlements"]=[right_h]
				end
       end
       doc=nil
       return db_user_entitlement
     rescue => ee
			puts "Exception on method #{__method__}: #{ee}"
		end
end

#print_user_entitlement ⇒ Object Also known as: print_user

Print out the user entitlement table in plain text, to be imported into database



156
157
158
159
160
161
162
163
164
165
166
 
# File 'lib/ucert/db_direct_tracker.rb', line 156

def print_user_entitlement
	begin
     puts "user Entitlement Report in Plain-text Format" if @verbose
     puts "User Id|WebSSO Id|First Name|Last Name|Product|Company|Bank Branch|Account|Account Currency|Entitlements|DN" if @verbose
		@db_user_entitlement.values.map do |rec|
         puts "#{rec['User Id']}|#{rec['WebSSO Id']}|#{rec['First Name']}|#{rec['Last Name']}|#{rec['Product']}|#{rec['Company']}|#{rec['Bank Branch']}|#{rec['Entitlements']}|#{rec['DN']}"
     end
	rescue => ee
		puts "Exception on method #{__method__}: #{ee}"
	end
end

#save_db_user_map!(file = @file_db_user_map) ⇒ Object Also known as: save!

Save the Prime to AD user mapping relation into the cache file



170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
 
# File 'lib/ucert/db_direct_tracker.rb', line 170

def save_db_user_map!(file=@file_db_user_map)
	puts "Saving the known Prime to AD user mapping relationship to file: #{file} ..." if @verbose
	begin
		timestamp=Time.now
		f=File.open(file, 'w')
		f.write "# local db for Securitites to AD user map file created by the #{self.class} class #{__method__} method at: #{timestamp}"
		@db_user_entitlement.values.map do |record|
			key = record['User Id'].upcase
			value = record['DN']
			f.write "\n#{key}|#{value}"
		end
		f.close
		puts "db Securitites to AD user map file is successfully saved to: #{file}" if @verbose
	rescue => ee
		puts "Exception on method #{__method__}: #{ee}" if @verbose
	end
end