Feedback
Did this article resolve your question/issue?

   

Article

How to create a Script that will grant SELECT on all OpenEdge database tables

Information

 
TitleHow to create a Script that will grant SELECT on all OpenEdge database tables
URL NameP21021
Article Number000140219
EnvironmentProduct: Progress
Version: 9.1D, 9.1D
Product: OpenEdge
Version: 10.1A, 10.1B, 10.1C, 10.2A, 10.2B, 11.x
OS: All supported platforms
Question/Problem Description
How to create a Script that will grant SELECT on all tables in a Database
How to grant Select on all database tables for SQL users
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
While the option to grant all privileges (SELECT, INSERT, UPDATE, DELETE) can be granted to a user on a table, SQL privileges have to be granted via a SQL client on a table by table basis.  

Rather than running the GRANT statement on each table, an ABL program can be used to generate a SQL script with all the required GRANT statements for each table, then the generated SQL script can be run with a SQL client (e.g. SQLEXP) to grant the permissions needed.

1. Connect to the database with an ABL client and run the following ABL program which will create a file named allfiles.SQL:
  
​OUTPUT TO c:\temp\allfiles.SQL.

FOR EACH _file WHERE _file._file-num GT 0 AND _file._file-num LT 32000 NO-LOCK:
     PUT UNFORMATTED 'grant select on pub."' + _file._file-name + '" to public ~;'
     SKIP.
     PUT UNFORMATTED 'COMMIT;'
     SKIP.
END.
 
2. Execute the allfiles.SQL file from SQL Explorer in batch mode:
   
$   sqlexp -char -infile allfiles.SQL -outfile report.txt -db -S -H -user -password < password>
Workaround
Notes
Last Modified Date11/20/2020 7:14 AM
Attachment 
Files
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.