Skip to main content

RADplus 2024 Update 74

  1. Last updated
  2. Save as PDF

Product Requirements and Recommendations

RADplus required

Recommended Update Level

RADplus 2024 Monthly Release 2024.01.02

Product Update Description

The process for granting and updating user's SQL privileges is updated.

Required Updates

None

Included Updates

22, 23, 50, 71

Details
NEW0 CHANGED0 FIXED1
Fixed (1)
SQL Table access
The system internal process for granting or updating a user's SQL table privileges is updated to ensure results, are as expected KB0077036 v0.01
Topics
• Forms • SQL Data Access
 
Acceptance Tests
AV-98639 Summary | Details
SQL Table access
Note - These testing guidelines assume the user is skilled in the use of, at a minimum, the following:
  • Print Error Log (PM)
  • Registry Settings (PM)
  • User Definition
Scenario 1: Validate a users (ODBC) SQL table permissions when querying tables via a "Database" and/or "ECP" server
Specific Setup:
  • Have a standard cache "Database" server for testing and if applicable an "ECP" server configured with the database server as its remote database, so that reports can be run via the "ECP" server to display data in the database server
  • In form "User Definition" create a new user [SqlUser]
  • In field "Select Tables for Product SQL Access" assign the user access to tables in two or more applications. For this test:
  • [TableA] and [TableB] are assigned in application "PM"
  • [TableC] and [TableD] are assigned in application "CWS"
  • Populate all other required fields and submit the form
  • Create a second user in form "User Definition" with no SQL table access assigned [NoSqlAccess]
  • Populate all other required fields and submit the form
  • Using the windows "ODBC Data Source Administrator" setup the following connections for [SqlUser]
  • [ConnectionA] configured to point to application "PM" on the "Database" server
  • [ConnectionB] configured to point to application "CWS" on the "Database" server
  • [ConnectionC] configured to point to application "PM on the "ECP" server
  • [ConnectionD] configured to point to application "CWS" on the "ECP" server
  • Test and validate each connection is successful
  • Have access to form "Registry "Settings"
Steps
  1. Open the desired reporting software, for example "Crystal Reports"
  2. Select the "ODBC" [ConnectionA], set to connect to the "PM" application on the "Database" server
  3. Validate [TableA] and [TableB] are accessible, as expected
  4. Close that connection
  5. Select database "ODBC" connection [ConnectionB], set to connect to the "CWS" application on the "Database" server
  6. Validate [TableC] and [TableD] are accessible, as expected
  7. Close that connection
  8. Repeat step 1a for "ECP" connection [ConnectionC]
  9. Validate results are the same, as expected
  10. Repeat step 1b for the "ECP" [ConnectionD]
  11. Validate results are the same, as expected
  12. Open the "ODBC Data Source Administrator" application
  13. Populate the "Host(IPAddress)", "Port" fields with the proper credentials for the "Database" server and set the "Cache Namespace" field to the "PM"
  14. In the "User Name" field, populate the 'UserID' for the user [NoSqlAccess]
  15. Populate the password for user [NoSqlAccess] in the "Password" field
  16. Click to [Test Connection]
  17. Validate the message states "Connectivity Test Failed" as expected, as the user has no SQL tables assigned to them
  18. Repeat step 2a for the "Database" server, but change "Cache Namespace" field to the "CWS"
  19. Click to [Test Connection]
  20. Validate the results are the same
  21. Populate the "Host(IPAddress)", "Port" fields with the proper credentials for the "ECP" server and set the "Cache Namespace" field to the "PM"
  22. Leave the user name and password field set to [NoSQLAccess]
  23. Click to [Test Connection]
  24. Validate message states "Connectivity Test Failed" as expected, as the user has no SQL tables assigned to them
  25. Repeat step 2c, but change "Cache Namespace" field to the "CWS"
  26. Click to [Test Connection]
  27. Validate message states "Connectivity Test Failed" as expected, as the user has no SQL tables assigned to them
  28. Log into Avatar on the "Database" server
  29. Open form 'User Definition'
  30. Select [SqlUser] and navigate to field "Select Tables for Product SQL Access"
  31. Revoke access to [TableA] in the "PM" application and [TableC] in the "CWS" application
  32. Submit the form
  33. Open the desired reporting software
  34. Click to make a connection to application "PM" the "Database" server via [ConnectionA]
  35. Validate [TableA] is not accessible and [TableB] is accessible
  36. Click to make a connection to application "CWS" on the "Database" server via [ConnectionB]
  37. Validate [TableC] is not accessible and [TableD] is accessible
  38. Click to make a connection to application "PM" on "ECP" server via [ConnectionC],
  39. Validate [TableA] is not accessible and [TableB] is accessible
  40. Click to make a connection to application "CWS" on "ECP" server via [ConnectionD],
  41. Validate [TableC] is not accessible and [TableD] is accessible
  42. Open form 'User Definition'
  43. Select [SqlUser] and navigate to field "Select Tables for Product SQL Access"
  44. Revoke all table access in one of the applications, for this example "CWS" is used
  45. Submit the form
  46. Open the desired reporting software
  47. Click to make a connection to application "PM" the "Database" server via [ConnectionA]
  48. Validate [TableA] and [TableB] are accessible
  49. Click to make a connection to application "CWS" on the "Database" server via [ConnectionB]
  50. Validate the connection fails as expected, as the user does not have any access to tables in that application
  51. Click to make a connection to application "PM" on "ECP" server via [ConnectionC],
  52. Validate [TableA] and [TableB] are accessible
  53. Click to make a connection to application "CWS" on "ECP" server via [ConnectionD],
  54. Validate the connection fails as expected, as user does not have any access to tables in that application
  55. Open form "Registry Settings"
  56. Select registry setting "Avatar Report Multiple System Codes"
  57. Set the registry setting value to "Y"
  58. Submit the form
  59. Open form "User Definition", and select user [NoSqlAccess]
  60. Set prompt "Allow Product SQL Access" to "Yes"
  61. Click "Select Tables for Product SQL Access" and
  62. Assign the user access to [TableA] in "PM" and [TableC] in "CWS"
  63. Click [Save]
  64. Set prompt "Allow Product SQL Access to Additional Datasets" to "Yes"
  65. In the "Product SQL Access Datasets" field
  66. Select root system codes, [RootA] and [RootB]
  67. Submit the form
  68. Open the desired reporting software
  69. Click to make a connection to application "PM" the "Database" server via [ConnectionA]
  70. Validate data rows for [TableA] are displayed for the [RootA] system code and the [RootB] system code, as expected
  71. Click to make a connection to application "CWS" on the "Database" server via [ConnectionB]
  72. Validate data rows for [TableC] are displayed for the [RootA] system code and the [RootB] system code, as expected
  73. Click to make a connection to application "PM" on "ECP" server via [ConnectionC],
  74. Validate data rows for [TableA] are displayed for the [RootA] system code and the [RootB] system code, as expected
  75. Click to make a connection to application "CWS" on "ECP" server via [ConnectionD],
  76. Validate data rows for [TableC] are displayed for the [RootA] system code and the [RootB] system code, as expected
  77. Open the "Print Error Log" report
  78. Run the report
  79. Validate results show there are no errors reported
Topics
• SQL Data Access

 

  1. Back to top
  • Was this article helpful?

Recommended articles

  1. Page type
    Reference
  2. Tags
    1. content:release
    2. Forms
    3. llm-no-index
    4. RADplus 2024 Update 74
    5. role:sysadmin
    6. SQL Data Access