Read only user in Progess database

We have been attempting the same thing and I think the error is saying that you can't change permissions unless you have exclusive access to the database. I ran the grant list over the weekend after stopping and starting the database to ensure everyone was out and it ran fine.

Allen Tucker
IT Manager
Espey Mfg. & Electronics Corp.
Email: atucker@...



--- In vantage@yahoogroups.com, "Matt Caldwell" <matt@...> wrote:
>
> We have downloaded the ODBC Kit, but it is of no help. We are on
> Progress 10.1b. I installed Squirrel SQL Client, so I could run the
> "Grant Select" statements on the database. The issue that we are
> running into is that when I use the Data Administration console, it
> shows that ALL of our tables have an " * " in all of the permission
> fields on all tables. To me, any user we create will have access to
> everything (Read, Write, Create, Delete, Dump, and Load). Is there a
> SQL script for Squirrel SQL (that works) to change the " * " to
> "sysprogress"? I changed the Customer table permissions from " * " to
> "sysprogress" manually, and ran the provided examples in the ODBC kit to
> grant access to the newly created odbcuser user. They did nothing at
> all. Is there a better way to remove the default permissions on each
> table, and set up a read-only user?
>
>
>
> To create the user, I ran(this worked):
>
> CREATE USER 'odbcuser','odbcuser';
>
> COMMIT;
>
>
>
>
>
> Then to grant permissions to that user, I ran(This did nothing, with or
> without the COMMIT):
>
> GRANT SELECT ON PUB.customer TO odbcuser;
>
> COMMIT;
>
>
>
> When this query above runs, I get the following message:
>
> Query 1 of 2 elapsed time (seconds) - Total: 0.006, SQL query: 0.006,
> Building output: 0
>
> Error: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL
> statement at or about "COMMIT" (10713)
>
> SQLState: HY000
>
> ErrorCode: -210056
>
> Error occured in:
>
> COMMIT
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
> Of cmulford_66
> Sent: Thursday, April 09, 2009 11:51 AM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Read only user in Progess database
>
>
>
>
>
>
>
>
> Did you download the ODBCkit from the ftp site? It's a pretty basic once
> you get the txt files changed to your liking.
>
> --- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
> "rlsand50" <bob.sanders@> wrote:
> >
> > We would like to have a read only user in our Progress database that
> we can use for ODBC calls to Vantage for data. We have tried several
> things but cannot figure out how to do this. We have even looked at some
> documentation on the subject. Anyone have a good resource or step by
> step method of doing this? Thanks!
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
We would like to have a read only user in our Progress database that we can use for ODBC calls to Vantage for data. We have tried several things but cannot figure out how to do this. We have even looked at some documentation on the subject. Anyone have a good resource or step by step method of doing this? Thanks!
Did you download the ODBCkit from the ftp site? It's a pretty basic once you get the txt files changed to your liking.

--- In vantage@yahoogroups.com, "rlsand50" <bob.sanders@...> wrote:
>
> We would like to have a read only user in our Progress database that we can use for ODBC calls to Vantage for data. We have tried several things but cannot figure out how to do this. We have even looked at some documentation on the subject. Anyone have a good resource or step by step method of doing this? Thanks!
>
What is the link to the ftp site?




--- In vantage@yahoogroups.com, "cmulford_66" <cmulford_66@...> wrote:
>
> Did you download the ODBCkit from the ftp site? It's a pretty basic once you get the txt files changed to your liking.
>
> --- In vantage@yahoogroups.com, "rlsand50" <bob.sanders@> wrote:
> >
> > We would like to have a read only user in our Progress database that we can use for ODBC calls to Vantage for data. We have tried several things but cannot figure out how to do this. We have even looked at some documentation on the subject. Anyone have a good resource or step by step method of doing this? Thanks!
> >
>
ftp://ftpmpls.epicor.com/

________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Lynn
Sent: Thursday, April 09, 2009 1:39 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Read only user in Progess database




What is the link to the ftp site?

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "cmulford_66" <cmulford_66@...> wrote:
>
> Did you download the ODBCkit from the ftp site? It's a pretty basic once you get the txt files changed to your liking.
>
> --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "rlsand50" <bob.sanders@> wrote:
> >
> > We would like to have a read only user in our Progress database that we can use for ODBC calls to Vantage for data. We have tried several things but cannot figure out how to do this. We have even looked at some documentation on the subject. Anyone have a good resource or step by step method of doing this? Thanks!
> >
>





[Non-text portions of this message have been removed]
Thank you Todd!

I've been looking for this detailed documentation in the ODBC kit for months. Now I finally have it.

I will very soon need to offer other users ODBC to print labels on-demand outside of Vantage and I was reluctant to do so until I had the instructions for implementing user security rights on tables.

Thank you very much!

Lynn


--- In vantage@yahoogroups.com, Todd Caughey <caugheyt@...> wrote:
>
> ftp://ftpmpls.epicor.com/
>
> ________________________________
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Lynn
> Sent: Thursday, April 09, 2009 1:39 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Read only user in Progess database
>
>
>
>
> What is the link to the ftp site?
>
> --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "cmulford_66" <cmulford_66@> wrote:
> >
> > Did you download the ODBCkit from the ftp site? It's a pretty basic once you get the txt files changed to your liking.
> >
> > --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, "rlsand50" <bob.sanders@> wrote:
> > >
> > > We would like to have a read only user in our Progress database that we can use for ODBC calls to Vantage for data. We have tried several things but cannot figure out how to do this. We have even looked at some documentation on the subject. Anyone have a good resource or step by step method of doing this? Thanks!
> > >
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Credit to cmulford_66. They knew what to look for...all I had was a ready link to the FTP address.
-Todd C.

________________________________
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Lynn
Sent: Thursday, April 09, 2009 2:00 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Read only user in Progess database




Thank you Todd!

I've been looking for this detailed documentation in the ODBC kit for months. Now I finally have it.

I will very soon need to offer other users ODBC to print labels on-demand outside of Vantage and I was reluctant to do so until I had the instructions for implementing user security rights on tables.

Thank you very much!

Lynn

--- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, Todd Caughey <caugheyt@...> wrote:
>
> ftp://ftpmpls.epicor.com/
>
> ________________________________
> From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Lynn
> Sent: Thursday, April 09, 2009 1:39 PM
> To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
> Subject: [Vantage] Re: Read only user in Progess database
>
>
>
>
> What is the link to the ftp site?
>
> --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>, "cmulford_66" <cmulford_66@> wrote:
> >
> > Did you download the ODBCkit from the ftp site? It's a pretty basic once you get the txt files changed to your liking.
> >
> > --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>, "rlsand50" <bob.sanders@> wrote:
> > >
> > > We would like to have a read only user in our Progress database that we can use for ODBC calls to Vantage for data. We have tried several things but cannot figure out how to do this. We have even looked at some documentation on the subject. Anyone have a good resource or step by step method of doing this? Thanks!
> > >
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>





[Non-text portions of this message have been removed]
Glad we could help Lynn but after seeing the original question again I'm not sure that's what they were looking for. The odbckit is used to set permissions on table/field views, not read-write permissions. By default, everyone is a read only DB user and the DSN is usually set to 'read uncommited'. A ProVision license is needed to allow writes to a Progress db. The Data Administrator is used to set permissions in that case. The DSN also needs tweaked.




--- In vantage@yahoogroups.com, Todd Caughey <caugheyt@...> wrote:
>
> Credit to cmulford_66. They knew what to look for...all I had was a ready link to the FTP address.
> -Todd C.
>
> ________________________________
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Lynn
> Sent: Thursday, April 09, 2009 2:00 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] Re: Read only user in Progess database
>
>
>
>
> Thank you Todd!
>
> I've been looking for this detailed documentation in the ODBC kit for months. Now I finally have it.
>
> I will very soon need to offer other users ODBC to print labels on-demand outside of Vantage and I was reluctant to do so until I had the instructions for implementing user security rights on tables.
>
> Thank you very much!
>
> Lynn
>
> --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>, Todd Caughey <caugheyt@> wrote:
> >
> > ftp://ftpmpls.epicor.com/
> >
> > ________________________________
> > From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Lynn
> > Sent: Thursday, April 09, 2009 1:39 PM
> > To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
> > Subject: [Vantage] Re: Read only user in Progess database
> >
> >
> >
> >
> > What is the link to the ftp site?
> >
> > --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>, "cmulford_66" <cmulford_66@> wrote:
> > >
> > > Did you download the ODBCkit from the ftp site? It's a pretty basic once you get the txt files changed to your liking.
> > >
> > > --- In vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>, "rlsand50" <bob.sanders@> wrote:
> > > >
> > > > We would like to have a read only user in our Progress database that we can use for ODBC calls to Vantage for data. We have tried several things but cannot figure out how to do this. We have even looked at some documentation on the subject. Anyone have a good resource or step by step method of doing this? Thanks!
> > > >
> > >
> >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Hi Chris,

> Glad we could help Lynn but after seeing the original question again I'm
> not sure that's what they were looking for. The odbckit is used to set
> permissions on table/field views, not read-write permissions.

What I did was created a user called "odbcuser" and granted only Select
Privileges to the tables that I wanted them to see. The ODBC toolkit I used
had a file with all of the table names with the GRANT statements in it. I'm
not sure if it has been updated.

I agree that you still want to set "READ UNCOMMITTED" in your ODBC DSNs for
performance reasons.

Mark W.
803.404



I have two countries that were spelled incorrectly during the original Vantage setup.

The interface to the country master file setup does not seem to indicate that there is a second "internal" field for the list of countries, thereby allowing me not to break any references by changing a "country description" but only the one field which holds the country name. Since there have been transactions with customers from these countries, it will not let me modify the country name spelling from the user interface.



Does anyone have thoughts on potential problems if I were to connect to the db from ODBC and change the spelling in the master file and any additional thoughts or detail that might need to be considered here?





Thanks

Carey

_________________________________________________________________
Rediscover Hotmail®: Get e-mail storage that grows with you.
http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage1_042009

[Non-text portions of this message have been removed]
It's not supported by Epicor but it can be done with an update statement via ODBC. I have done this the past on 6.1 Progress 9.1. (DO NOT USE THE REPLACE FUNCTION! Only update statements - trust me). As far as I know you will need the ProVision license for progress to do this in 10.1 as well.


--- In vantage@yahoogroups.com, Carey S <rotary1@...> wrote:
>
>
> 803.404
>
>
>
> I have two countries that were spelled incorrectly during the original Vantage setup.
>
> The interface to the country master file setup does not seem to indicate that there is a second "internal" field for the list of countries, thereby allowing me not to break any references by changing a "country description" but only the one field which holds the country name. Since there have been transactions with customers from these countries, it will not let me modify the country name spelling from the user interface.
>
>
>
> Does anyone have thoughts on potential problems if I were to connect to the db from ODBC and change the spelling in the master file and any additional thoughts or detail that might need to be considered here?
>
>
>
>
>
> Thanks
>
> Carey
>
> _________________________________________________________________
> Rediscover Hotmail®: Get e-mail storage that grows with you.
> http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage1_042009
>
> [Non-text portions of this message have been removed]
>
If you have CRM have you tried exporting the customer list for these
countries, and importing them back in?
I just did that and change the country even though I had shipped with the
"wrong" spelled country.
Or it you have only a few - try creating the correctly spelled country and
replacing it on the customer record, contacts and bill to ..

In fact after I corrected the country, I deleted the wrong country, even
though I had shipped and invoice.

This was not done by touching OBDC, pretty dangerous stuff.


Brz in Mpls.


_____

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
cmulford_66
Sent: Friday, April 10, 2009 1:19 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Country spelled wrong





It's not supported by Epicor but it can be done with an update statement via
ODBC. I have done this the past on 6.1 Progress 9.1. (DO NOT USE THE REPLACE
FUNCTION! Only update statements - trust me). As far as I know you will need
the ProVision license for progress to do this in 10.1 as well.

--- In vantage@yahoogroups <mailto:vantage%40yahoogroups.com> .com, Carey S
<rotary1@...> wrote:
>
>
> 803.404
>
>
>
> I have two countries that were spelled incorrectly during the original
Vantage setup.
>
> The interface to the country master file setup does not seem to indicate
that there is a second "internal" field for the list of countries, thereby
allowing me not to break any references by changing a "country description"
but only the one field which holds the country name. Since there have been
transactions with customers from these countries, it will not let me modify
the country name spelling from the user interface.
>
>
>
> Does anyone have thoughts on potential problems if I were to connect to
the db from ODBC and change the spelling in the master file and any
additional thoughts or detail that might need to be considered here?
>
>
>
>
>
> Thanks
>
> Carey
>
> __________________________________________________________
> Rediscover HotmailR: Get e-mail storage that grows with you.
> http://windowslive.
<http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_St
orage1_042009>
com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Storage1_042009
>
> [Non-text portions of this message have been removed]
>






[Non-text portions of this message have been removed]
We have downloaded the ODBC Kit, but it is of no help. We are on
Progress 10.1b. I installed Squirrel SQL Client, so I could run the
"Grant Select" statements on the database. The issue that we are
running into is that when I use the Data Administration console, it
shows that ALL of our tables have an " * " in all of the permission
fields on all tables. To me, any user we create will have access to
everything (Read, Write, Create, Delete, Dump, and Load). Is there a
SQL script for Squirrel SQL (that works) to change the " * " to
"sysprogress"? I changed the Customer table permissions from " * " to
"sysprogress" manually, and ran the provided examples in the ODBC kit to
grant access to the newly created odbcuser user. They did nothing at
all. Is there a better way to remove the default permissions on each
table, and set up a read-only user?



To create the user, I ran(this worked):

CREATE USER 'odbcuser','odbcuser';

COMMIT;





Then to grant permissions to that user, I ran(This did nothing, with or
without the COMMIT):

GRANT SELECT ON PUB.customer TO odbcuser;

COMMIT;



When this query above runs, I get the following message:

Query 1 of 2 elapsed time (seconds) - Total: 0.006, SQL query: 0.006,
Building output: 0

Error: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL
statement at or about "COMMIT" (10713)

SQLState: HY000

ErrorCode: -210056

Error occured in:

COMMIT



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of cmulford_66
Sent: Thursday, April 09, 2009 11:51 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Read only user in Progess database








Did you download the ODBCkit from the ftp site? It's a pretty basic once
you get the txt files changed to your liking.

--- In vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ,
"rlsand50" <bob.sanders@...> wrote:
>
> We would like to have a read only user in our Progress database that
we can use for ODBC calls to Vantage for data. We have tried several
things but cannot figure out how to do this. We have even looked at some
documentation on the subject. Anyone have a good resource or step by
step method of doing this? Thanks!
>





[Non-text portions of this message have been removed]