How to Enable PSQL Relational ODBC Security
Release: 04/29/2021
Version: 7.5 & Higher
Q - At the moment, our PSQL ODBC access does not require login and everyone has full access to the Elliott database when they connect through ODBC. We are looking at a scenario where most users have read-only access to the database without using credentials. We want to create two or three users that will have write capabilities (such as our shipping system and admins.)
A - To turn on PSQL ODBC Relational security, first you will bring up PSQL Control Center, right click on the database and choose "Properties." See sample screen below:
In the properties window, go to "Security" and choose "Enable (Local database authentication)" and enter the Master password (up to 8 digits long). See sample screen below:
This will create one user "Master" for this database. This "Master" user is like the admin or supervisor and has all rights. The next step is to create database users with specific rights. Bring up <ElliottRoot>\Bin85\Grant.sql file in PSQL control center to review various examples on how to create database users.
The requirement that “most users have read-only access to the database without using credentials” is not possible. You either turn on or turn off the ODBC security. Once you turn it on, everyone is required a login when accessing ODBC. This includes your third party application, like the shipping manifest system.
You could create a user to access a lot of tables with read-only access. If you review the grant.sql, you will see the example of how to do that. In our grant.sql example, by default, we don’t give people access to GL or Payroll because we think most of the people should not have any business with that module. You probably need to customize grant.sql further to make it fit your needs better. After you customize it, rename it to something like ABCGrant.SQL where ABC is your company name. This will prevent future updates and upgrades from overriding your changes.
Keep in mind when you use script like ABCGrant.SQL, you are modifying Elliott's original DDF files. That means if you need to upgrade to a new set of DDFs (e.g. ,for accessing new columns and new tables), then that new set of DDFs comes without security. So you need to re-apply this ABCGrant.SQL again. So keep this file in a safe place and don’t lose it. If you modify security in the future, make sure your modification is reflected in this ABCGrant.SQL.
EMK
Related Articles
How Do I Turn on PSQL Relational Engine Security?
Q - I let my users use Crystal Report to access Elliott's data through ODBC. But they can access every single table in Elliott, including sensitive data in Payroll and General Ledger. How do I turn on PSQL's Relational Engine security so my users ...Slow PSQL Relational Engine Performance
Release Date: 12/15/2017 Many users encounter situations where a particular relational engine SQL query can sometimes be slow. This can be complicated to diagnose due to a lot of reasons. Sometimes it's because of a SQL SELECT statement that's not ...IPV6 May Cause Problem for PSQL 11 Relational Query
Release Date: 01/20/19 IPV6 (Internet Protocol Version 6) is designed to eventually replace IPV4. IPV4 has an IP address like 192.168.1.1. Each segment can have a value from 0 - 255, so the theoretical maximum number of IPV4 addresses is a little ...Anti-Virus Caused Failure on Crystal Reports ODBC Access
Release Date: 05/22/18 Q - I have a Crystal Report that has ran against our production Elliott data without issue. I am trying to run this same report against the test Elliott data. I am getting "failed to retrieve data" messages when trying to run ...Create a New Database in the PCC for Relational Engine Access
Release Date: 12/15/2017 Modified Date: 06/30/2020 Elliott Business Software relies on PSQL database engines. PSQL consists of two types of engines: transactional and relational. The transactional engine used to be called "Btrieve," which is a record ...