Securing ODBC Connections?

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Securing ODBC Connections?

4D Tech mailing list
Hi Everyone,

I've been digging through the docs and not finding answers to my
questions, which might be under my nose, but I'd appreciate help:

> An outside application (non-4D; "real world" stuff) - needs to pull data
from our 4D Server (Win, v15.x) via an ODBC connection.  I can see how to
turn ODBC on, but it is not clear how the 4D password system works for
authentication (how is the userid and password passed inside the SQL query
from the outside system?)

> I don't quite get the concept of 'Schemas' - it appears that I need to
put the tables I will allow access via ODBC into a Schema, but it is not
clear what I need to do with the other tables (that should not be
accessible via ODBC).

This is the source of confusion, from the 4D Design Ref (v15), talking
abtout setting types of access to schemas:
"More specifically, if you only assign Read Only type access to one group
this will not have any effect since this group as well as all the others
will continue to benefit from Read/Write access (assigned to <Everybody>
by default). In order to set a Read Only type access, you also need to
configure the Read/Write access."

I"m not clear at all about why "this will not have any effect...")

> Ideally I'd like to create a view and only allow the ODBC connection to
query on the view.  I'm not clear on how this works with the Schema idea.

Anyone have some experience they are willing to share?

I most appreciate it!

Bob Miller
Chomerics, a division of Parker Hannifin Corporation



**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

RE: Securing ODBC Connections?

4D Tech mailing list
Hi Bob,

> This is the source of confusion, from the 4D Design Ref (v15), talking abtout setting types of access to schemas:
> "More specifically, if you only assign Read Only type access to one group this will not have any effect since this group as well as all the others will continue to benefit from Read/Write access (assigned to <Everybody> by default). In order to set a Read Only type access, you also need to configure the Read/Write access."
> I"m not clear at all about why "this will not have any effect...")

Let's say you have a group named Group1.
If you assign Group1 to READ ONLY for this Schema, but leave <Everybody> set to Read/Write access, then Group1 would effectively get Read/Write access because they are included in the Everybody pseudo-group.
That is what is meant by the READ ONLY setting would have no effect.
If you want Group1 to be READ ONLY then you need to also check the READ/WRITE group to be sure they do not have access.

Another way to look at it, is let's assume you have User1 who belongs to both Group1 and Group2.
Then you assign Group1 to READ ONLY and Group2 to READ/WRITE for the schema applied to this table.
Then you login as User1 whom belongs to both Group1 and Group2.
In this situation the User1 would have READ/WRITE.

This tech note is a bit old but it may help explain the use of Schemas:
http://kb.4d.com/assetid=51814

-Tim



**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Securing ODBC Connections?

4D Tech mailing list
You should also have an on sql connection method with log in info

we have code that looks like the following

*SET QUERY DESTINATION*(Into current selection)

*Case of *


*: *(*User in group*(ONSQLAuthenUser_txt;" "))

*CHANGE CURRENT USER*("SQL_ReadOnly";"12345")

*: *(*User in group*(ONSQLAuthenUser_txt;"ReadOnly"))

*CHANGE CURRENT USER*(ONSQLAuthenUser_txt;ONSQLAuthUserPW_txt)

*: *(*User in group*(ONSQLAuthenUser_txt;"ReadWrite"))

*CHANGE CURRENT USER*(ONSQLAuthenUser_txt;ONSQLAuthUserPW_txt)

*Else *

*If *(*Not*(*User in group*(ONSQLAuthenUser_txt;"ActiveUsers")))

*C_LONGINT*($Processid_l)

$Processid_l:=*New process*
("ut_LogExternalConnects";128*1024;"ONSQLAuthenticate";ONSQLAuthenUser_txt;
ONSQLAuthenIP_txt;ONSQLAuthUserPW_txt;"InactiveUser")

*Else *


*End if *

SQL_ConnectionOK_b:=*False*

*End case *

*$0*:=SQL_ConnectionOK_b

*ON ERR CALL*("")


Regards


Chuck

On Tue, Jun 6, 2017 at 2:53 PM, Timothy Penner via 4D_Tech <
[hidden email]> wrote:

> Hi Bob,
>
> > This is the source of confusion, from the 4D Design Ref (v15), talking
> abtout setting types of access to schemas:
> > "More specifically, if you only assign Read Only type access to one
> group this will not have any effect since this group as well as all the
> others will continue to benefit from Read/Write access (assigned to
> <Everybody> by default). In order to set a Read Only type access, you also
> need to configure the Read/Write access."
> > I"m not clear at all about why "this will not have any effect...")
>
> Let's say you have a group named Group1.
> If you assign Group1 to READ ONLY for this Schema, but leave <Everybody>
> set to Read/Write access, then Group1 would effectively get Read/Write
> access because they are included in the Everybody pseudo-group.
> That is what is meant by the READ ONLY setting would have no effect.
> If you want Group1 to be READ ONLY then you need to also check the
> READ/WRITE group to be sure they do not have access.
>
> Another way to look at it, is let's assume you have User1 who belongs to
> both Group1 and Group2.
> Then you assign Group1 to READ ONLY and Group2 to READ/WRITE for the
> schema applied to this table.
> Then you login as User1 whom belongs to both Group1 and Group2.
> In this situation the User1 would have READ/WRITE.
>
> This tech note is a bit old but it may help explain the use of Schemas:
> http://kb.4d.com/assetid=51814
>
> -Tim
>
>
>
> **********************************************************************
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: http://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************




--
-----------------------------------------------------------------------------------------
 Chuck Miller Voice: (617) 739-0306 Fax: (617) 232-1064
 Informed Solutions, Inc.
 Brookline, MA 02446 USA Registered 4D Developer
       Providers of 4D, Sybase & SQL Sever connectivity
          http://www.informed-solutions.com
-----------------------------------------------------------------------------------------
This message and any attached documents contain information which may be
confidential, subject to privilege or exempt from disclosure under
applicable law.  These materials are intended only for the use of the
intended recipient. If you are not the intended recipient of this
transmission, you are hereby notified that any distribution, disclosure,
printing, copying, storage, modification or the taking of any action in
reliance upon this transmission is strictly prohibited.  Delivery of this
message to any person other than the intended recipient shall not
compromise or waive such confidentiality, privilege or exemption
from disclosure as to this communication.
**********************************************************************
4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: http://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************