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 can't access those sensitive areas?

A - In the M:\Elliott7\DDF40 folder, there’s a file labeled GRANT.SQL.  It is a sample file showing you how you can customize your own security on the relational engine side (i.e., ODBC).  To use GRANT.SQL, you must first turn on security in the relational engine by providing a password.  You can do so by right clicking on the database in the PSQL control center and choosing “Properties.” Then go to “Security.” On the “Database Security” tab, check “Enable Security,” and enter the Master Password.



From there, a User Master is created with that password.  The newly created User Master is a super user that has no restrictions and can do everything.  After you enable the security, go to Crystal Report. If you use ODBC to connect to Elliott data, you will find that the system will now prompt you for a user ID and password.  You can use the User Master and the password you chose to enable the security.

Since you don’t want to allow everybody to use the User Master to login, you can use the GRANT.SQL as a sample to help you create additional users, and fine tune the rights to each table.

In PSQL, you can create a user or group.  You can grant rights to a user or group.  For each table, you can grant rights like SELECT, UPDATE and DELETE.  We recommend that you only grant the SELECT right so you will not accidentally update Elliott data through the relational engine.  Updating Elliott through the relational engine can potentially corrupt Elliott data, so you have to be very careful.  To set up relational security, it makes sense to create a group and grant rights on the group level.  Then indicate which users belong to what group. So if you have multiple users in similar positions, all you have to do is to create a new user and indicate that the user belongs to the group.  Be aware that PSQL has a limitation that allows each user to belong to one group only.  The following syntax will create a group called STAFF:
            CREATE GROUP STAFF;
The following syntax will grant the select right to the customer (ARCUSFIL) table for the group (or user) STAFF:
            GRANT SELECT ON TABLE ARCUSFIL TO STAFF;
You can use the following syntax to create a user that belongs to STAFF:
            GRANT LOGIN TO JOHN:123456 IN GROUP STAFF;
This will create a user JOHN with the password 123456 that belongs to the group STAFF. So now if you login ODBC user JOHN, you will be able to use the “SELECT” statement on the customer (ARCUSFIL) table. The SELECT statement only returns data; it does not update or delete records, which is what Crystal Report does in the background.

The process to determine which tables you should grant users access to is complicated.  We try to give you sample scripts with GRANT.SQL, but that will not be 100%. So if you choose to go this route by turning on the relational engine security, don’t be surprised if that causes some problems.  If you have existing users that have unlimited access, expect that they will complain that something is broken.  If that should happen, you have to find out which table you missed and grant the SELECT right to that user.  This can be time consuming and is a tedious process.  Typically, if you have IT staff, they will be in charge of getting the relational security to work. If you don’t, this will be a burden on whoever has the part-time IT role. So you are now forewarned about this potential situation.

Whatever SQL statement you use to create your custom relational security, you must save it for future use.  This is very important because from time to time Netcellent will deliver new database schema (DDF files) to you.  The new schema will override your existing custom security.  So you will need to retrieve your saved SQL file and apply the security statements again.


Pervasive PSQL

  1. Btrieve Error Codes 001 - 199
  2. Btrieve Error Codes 3000 - 3099
  3. Btrieve Error Codes 3100 - 3199
  4. PSQL Version Required by Each Elliott Version
  5. Do I Need to Change PSQL Server Engine Default Parameters After Installing It?
  6. New Elliott PSQL Server Processor and RAM Suggestions
  7. Can I Dynamically Adjust Elliott / PSQL 11 Server Memory?
  8. Received "Your Computer Does Not Have PSQL 10 or 11 Client " Even though PSQL Client Is Just Installed
  9. Btrieve Error 161 on Password File When Starting Up Elliott
  10. Problems with Using Pervasive Rebuild Utility on APOPNFIL and AROPNFIL Tables
  11. Security Issue with Installing PSQL Client Remotely on User's Workstation
  12. PSQL and Distributed File System (DFS)
  13. How Do I Turn on PSQL Relational Engine Security?
  14. An Example of Debugging NOTE_ORD_VIEW PSQL Expression Evaluation Error
  15. Btrieve Error 025 on COP Open Order by Salesman Report
  16. What Is the *.^01 File for My PSQL Btrieve Table?
  17. Suggested Files to be Monitored by Audit Master
  18. Pervasive Backup Agent Is Not Compatible with Creating Work Files
  19. Hardware Recommendations for Your PSQL Database Server
  20. How to Optimize SQL SELECT Statement When Retrieving Data from Invoice History
  21. New User-Defined Functions in Elliott DDF
  22. How to Improve Query Performance When Retrieving Data from Notes & Invoice History
  23. How to Retrieve Tracking Number for an Order from Notes
  24. Actian PSQL Not Started Automatically After Server Reboot
  25. Create a New Database in the PCC for Relational Engine Access
  26. Slow PSQL Relational Engine Performance
  27. IPV6 May Cause Problem for PSQL 11 Relational Query
  28. DDF Files in DATA Folder May Confuse PSQL
  29. What to Do When PSQL 11 License Is Disabled
  30. Quick Installation Guide for Audit Master
  31. Quick User Guide for Audit Master

Feedback and Knowledge Base