How to Enable PSQL Relational ODBC Security
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