Resolved How do I to grant read only SQL access to ODBC user

lkeller

New Member
Using the data dictionary, I created a new ODBC user. I do not want this user to have access to all tables and only want the user to have read only access to a few tables. I've created the script with the tables I want to grant access on using the example found in article P55080 on the progress community website. My issue is how do I run the command to grant the access? According to the article I access proenv and run a sqlexp command, but how do I found the port number to input? I tried running this with what I thought was the port number but it did not connect to the DB.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Your database should have a SQL-specific broker that spawns SQL servers that handle the connection requests from SQL clients. The SQL broker has its own TCP port, given by the value of its -S parameter. You can find this port number in a few ways: looking for the SQL broker in your OS process list, looking at the startup configuration of your database (which might be in a shell script, or in a .pf file, or in conmgr.properties), or by finding the SQL broker in the promon screen called Servers By Broker (promon R&D, 1, 17).

Note also that the user that runs the SQL script in sqlexp must be a DBA, i.e. it must have permission to grant privileges to other users.
 

lkeller

New Member
Your database should have a SQL-specific broker that spawns SQL servers that handle the connection requests from SQL clients. The SQL broker has its own TCP port, given by the value of its -S parameter. You can find this port number in a few ways: looking for the SQL broker in your OS process list, looking at the startup configuration of your database (which might be in a shell script, or in a .pf file, or in conmgr.properties), or by finding the SQL broker in the promon screen called Servers By Broker (promon R&D, 1, 17).

Note also that the user that runs the SQL script in sqlexp must be a DBA, i.e. it must have permission to grant privileges to other users.
Thank you @Rob Fitzpatrick for that information. I think I found the sql port in the conmgr.properties. I'll try running the command using that port and see if that solves my issue.
 

lkeller

New Member
Your database should have a SQL-specific broker that spawns SQL servers that handle the connection requests from SQL clients. The SQL broker has its own TCP port, given by the value of its -S parameter. You can find this port number in a few ways: looking for the SQL broker in your OS process list, looking at the startup configuration of your database (which might be in a shell script, or in a .pf file, or in conmgr.properties), or by finding the SQL broker in the promon screen called Servers By Broker (promon R&D, 1, 17).

Note also that the user that runs the SQL script in sqlexp must be a DBA, i.e. it must have permission to grant privileges to other users.
In the conmgr.properties I see a port and it says the type is sql. I tried using that port to connect to the DB using the sqlexp command in proenv and it failed to connect. I looked at the log file and below are the errors. Assuming that means I do not have the correct port, but I think it is the correct one as I have confirmed the port I am trying to connect to is the ODBC port I connect to in order to query data..

If I'm trying to connect to a linux DB from a Windows computer using proenv, once I'm at the proenv prompt do I need to run sqlexp to connect or prowin? Or is there another way I should be trying to connect to it to run this script to grant access?

Exception at Wed Mar 22 11:36:41 CDT 2023: java.sql.SQLNonTransientConnectionException
Message (throw): ### Connect stack trace. ###
Message (excp): [DataDirect][OpenEdge JDBC Driver]Error establishing socket to host and port: localhost:#####. Reason: Connection refused: connect
Stack Trace:
java.sql.SQLNonTransientConnectionException: [DataDirect][OpenEdge JDBC Driver]Error establishing socket to host and port: localhost:#####. Reason: Connection refused: connect
at com.ddtek.jdbc.openedgebase.ddb9.b(Unknown Source)
at com.ddtek.jdbc.openedgebase.ddb9.a(Unknown Source)
at com.ddtek.jdbc.openedgebase.ddb8.b(Unknown Source)
at com.ddtek.jdbc.openedgebase.ddb8.a(Unknown Source)
at com.ddtek.jdbc.openedge.OpenEdgeImplConnection.j(Unknown Source)
at com.ddtek.jdbc.openedgebase.BaseConnection.b(Unknown Source)
at com.ddtek.jdbc.openedgebase.BaseConnection.k(Unknown Source)
at com.ddtek.jdbc.openedgebase.BaseConnection.b(Unknown Source)
at com.ddtek.jdbc.openedgebase.BaseConnection.a(Unknown Source)
at com.ddtek.jdbc.openedgebase.BaseDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at com.progress.sql.explorer.SQLConnectServer.call(SQLConnectServer.java:41)
at com.progress.common.rmiregistry.TryIt.run(TryIt.java:221)
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
If I'm trying to connect to a linux DB from a Windows computer using proenv, once I'm at the proenv prompt do I need to run sqlexp to connect or prowin? Or is there another way I should be trying to connect to it to run this script to grant access?
Proenv is a command prompt that sets a few OE-specific environment variables like DLC. Sqlexp is a command-line SQL client that ships with OE. Prowin.exe is a graphical ABL client.

You want to run a SQL script to grant privileges, so you need to run it from a SQL client.

Let's start with your OE release, the sqlexp command line you used, and the relevant content of conmgr.properties.
 
Hi ,
trying to connect to a linux DB from a Windows computer
=> Error establishing socket to host and port: localhost:#####. Reason: Connection refused: connect
Do you specify the -H value on the sqlexp (the linux DB name)?

Patrice
 

lkeller

New Member
Hi ,
trying to connect to a linux DB from a Windows computer
=> Error establishing socket to host and port: localhost:#####. Reason: Connection refused: connect
Do you specify the -H value on the sqlexp (the linux DB name)?

Patrice
That's a good point, and no I did not, because the example of the command in the article I read did not include that. I will give that a shot and see if it works. What would be the format of that command? proenv> sqlexp -H <hostname> -db <dbname> -S <portnumber> -user admin -password ##### -char -infile grantrouser.sql
 

lkeller

New Member
Proenv is a command prompt that sets a few OE-specific environment variables like DLC. Sqlexp is a command-line SQL client that ships with OE. Prowin.exe is a graphical ABL client.

You want to run a SQL script to grant privileges, so you need to run it from a SQL client.

Let's start with your OE release, the sqlexp command line you used, and the relevant content of conmgr.properties.
That's a good point, and no I did not, because the example of the command in the article I read did not include that. I will give that a shot and see if it works. What would be the format of that command? proenv> sqlexp -H <hostname> -db <dbname> -S <portnumber> -user admin -password ##### -char -infile grantrouser.sql
@Patrice Perrot the -H was the key to solving my issue. Thank you both for your help. I really appreciate it.
 
Top