Open up Progress 10.2b with the SQL Broker.

Posted by youngblood on 16-Feb-2015 13:13

Hi everyone, I'm new to progress and I'm trying to 'open up' our Progress environment with the use of the SQL Broker that enables any ODBC/JDBC clients to query the database.

We've install the broker in our developmenent environnment and everything seems to be working as expected (I got Squirrel--an SQL Client-- to connect, I've also manage to have Pentaho--a ETL engin-- do the same).

At this point, I've kind of understood that the security is sort of 'open by default'. Is there an easy way to have/setup a userId with read only permission on the whole database (for export/reporting purpose example).

Any thoughts/concerns/best pratices about the use of the SQL broker?

Thanks

OpenEdge General - Forum

All Replies

Posted by Chandra Sekhar on 17-Feb-2015 03:37

Hi,
 
I think you have not created any user and logging as default user. The default user has all permissions on the database (i.e default user becomes the DBA of the database).
 
In order to have a read only user, do the following.
 

1)      Create a DBA user and grant dba permissions with the following command

CREATE USER ‘dbauser’,’dbauser_passwd’;  

GRANT dba,resource TO 'dbauser';  è This user becomes the DBA

2)      Connect using DBA user and Create a regular user

·         CREATE USER ‘ro_user’,’ro_user_passwd’;

·         Now grant the SELECT permissions on tables to ‘ro_user’

GRANT SELECT ON pub.customer TO ‘ro_user’;
GRANT SELECT ON pub.order TO ‘ro_user’;
 
You have to grant permissions for the required tables explicitly, we don’t have an option to grant SELECT permission on entire database.
 
Hope this helps..
 
Thanks and Regards,
Chandu
 
[collapse]
From: youngblood [mailto:bounce-youngblood@community.progress.com]
Sent: Tuesday, February 17, 2015 12:44 AM
To: TU.OE.General@community.progress.com
Subject: [Technical Users - OE General] Open up Progress 10.2b with the SQL Broker.
 
Thread created by youngblood

Hi everyone, I'm new to progress and I'm trying to 'open up' our Progress environment with the use of the SQL Broker that enables any ODBC/JDBC clients to query the database.

We've install the broker in our developmenent environnment and everything seems to be working as expected (I got Squirrel--an SQL Client-- to connect, I've also manage to have Pentaho--a ETL engin-- do the same).

At this point, I've kind of understood that the security is sort of 'open by default'. Is there an easy way to have/setup a userId with read only permission on the whole database (for export/reporting purpose example).

Any thoughts/concerns/best pratices about the use of the SQL broker?

Thanks

Stop receiving emails on this subject.

Flag this post as spam/abuse.

[/collapse]

Posted by Frank de Laat on 17-Feb-2015 11:07

Hi,

It's very easily generate a SQL script to GRANT all privileges on all the database tables using 4GL.

Details can be found in the knowledge base:

KB  S000022114 - How to generate SQL script to GRANT all privileges on all the database tables using 4GL?

Don't forget to change the GRANT ALL statement, otherwise you end up with an user having too much privileges.

Hope this helps.

Kind regards,

Frank

Posted by youngblood on 17-Feb-2015 15:11

Thanks!!

Olivier Quirion
Chef De service programmation et analyse/programming and analysis Service supervisor 
Siège Social/Head Office
450.787.3411  # 10340 
olivier.quirion@bonduelle.com

[collapse]
2015-02-17 12:08 GMT-05:00 Frank de Laat <bounce-frankdelaat@community.progress.com>:
Reply by Frank de Laat

Hi,

It's very easily generate a SQL script to GRANT all privileges on all the database tables using 4GL.

Details can be found in the knowledge base:

KB  S000022114 - How to generate SQL script to GRANT all privileges on all the database tables using 4GL?

Don't forget to change the GRANT ALL statement, otherwise you end up with an user having too much privileges.

Hope this helps.

Kind regards,

Frank

Stop receiving emails on this subject.

Flag this post as spam/abuse.


[/collapse]

This thread is closed