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
Create a New Database in the PCC for Relational Engine Access
Release Date: 12/15/2017 Modified Date: 02/07/2025 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 ...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 ...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 ...A Case Where PSQL 13 Client Is Not Compatible with PSQL 15 Server
Release Date: 02/04/2025 Version: 8.6 Do Users Need to Upgrade to PSQL 15 Client When Upgrading PSQL Server? We were often asked in the past if our users should upgrade their PSQL 13 client when they upgrade to the PSQL 15 server. In our past ...A Case to Optimize PSQL Query Performance with INNER JOIN SYCONTCT & SYCONREL Tables
Release Date: 11/29/2023 Version: 7.x & Up Q - With our custom web application, we attempt to find an existing customer and associated contact when creating new customers via the Elliott web services. We are finding the customer just fine, but when I ...