class Ucert::DbDirectTracker
Class to handle db for BE user account IDs
Attributes
db_2_ad_user[R]
db_user_entitlement[R]
db_user_entitlement_report[RW]
Class constant variables
db_user_group_entitlement[R]
db_user_group_entitlement_report[RW]
Class constant variables
file_db_user_map[RW]
Class constant variables
verbose[RW]
Class constant variables
Public Class Methods
new(params ={})
click to toggle source
Instance default variables
# 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
Public Instance Methods
db_search_by_dn(dn)
click to toggle source
Search user entitlement record by AD DN
# 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
Also aliased as: search_by_dn
dn_2_index(dn)
click to toggle source
Retrieve the user index from the @db_user_entitlement data structure
# 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()
click to toggle source
Procedures to add additonal field 'dn' into the @db_user_entitlement data structure, by person the AD search
# 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)
click to toggle source
Parsing the db Access user entitlement report in text format
# 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()
click to toggle source
Print out the user entitlement table in plain text, to be imported into database
# 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
Also aliased as: print_user
save_db_user_map!(file=@file_db_user_map)
click to toggle source
Save the Prime to AD user mapping relation into the cache file
# 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
Also aliased as: save!