Results 1 to 9 of 9

Thread: [RESOLVED] MySQL (with VB6) questions

  1. #1

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Resolved [RESOLVED] MySQL (with VB6) questions

    Hey, I know this isn't exactly the correct forum for these questions, but I'm doing all this from VB6, so I thought I'd ask here anyway. And, I'm just most familiar with this forum.

    Ok, we're setting up an interface to a MySQL database on a server within a WAN. Basically, the hookup is like this. We've got the MySQL server going ... we're hooked up to it via ODBC to MS-Access (MDB) files ... and then, we're opening those MDB files with a VB6 program for various manipulations. That's all just the details though.

    Specifically, there are starting to be some questions about setting up some security/privacy protocols, and two questions have come up:

    1) With MySQL, is there a way to allow someone write-only access? We'd like this so we can build an "upload" function via the ODBC so that several sites can "push" records into the MySQL tables without necessarily being able to query them.

    2) When setting up various MySQL schemas, is it possible to get access permissions down to the "field/column" level? Or is it always limited to the "table" level?

    I know there are some good DB people who float around this forum, so I appreciate you letting me ask about this here.

    Best Regards,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  2. #2
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: MySQL (with VB6) questions

    As for access level, it's either db or table... I don't think it can go into the cols/fields.
    For writeability... that's different... usually it's people want to make things read-only, which an unupdtable view is for... when it comes to write-only, I'm usually dealing with SQL Server and so grant only EXECUTE permissions on the sproc so that's all they can do. Not sure how that would work in your Access/MySQL configuration.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: MySQL (with VB6) questions

    Quote Originally Posted by techgnome View Post
    As for access level, it's either db or table... I don't think it can go into the cols/fields.
    Yeah, that's what I was thinking, but I thought I'd ask anyway.



    Quote Originally Posted by techgnome View Post
    when it comes to write-only, I'm usually dealing with SQL Server and so grant only EXECUTE permissions on the sproc so that's all they can do. Not sure how that would work in your Access/MySQL configuration.
    Yeah, I thought we could get this one. I just wasn't sure. Regarding using ODBC to do this, I'm fine if it just errors if they attempt to open/view/read the table. The uploading will be done from either VBA or VB6 or a combination, so erroring is fine. We'll trap for that in the uploading/writing.

    ----------------

    We'll continue playing around with it. I'm not the one who even has the permissions to change these MySQL permissions, so it's just good to have some idea what I'm talking about when I talk to those folks. Also, since it's all weekly Zoom meetings, I'd like to reduce the trial-and-error iterations as much as I possibly can.

    Thanks,
    Elroy
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

  4. #4
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: MySQL (with VB6) questions

    That's one of the problems, a true "MiddleWare"-layer (these days, usually a WebServer-instance), could solve:
    - proper encryption "on the wire" (via certificates and https) comes "basically for free" as well
    - also Authentication (knowing the current User at the side of the middleware and making sure the User is "who he claims to be")
    - and finally, Authorization (deciding at this middle-level, which authorized User has access to what MiddleWare-functions)
    - not to mention, the avoidance of all kind of "port- and firewall hazzles", because 80 (and 443 for https) are "already open these days" (for outgoing requests)

    I've suggested this at some earlier point already (recommending the IIS as the Instance which hosts your MiddleWare-CodeModules).

    These days, direct (Internet-based) access to DBs (via their Network-based drivers) is rarely done,
    instead http (in conjunction with a properly written "WebAPI") is the way server-resources are accessed,
    because with such a WebAPI in place, you can be extremely variable in your "choice of a Client"
    Good http-request-libs are available for any language and for any platform (or also in "any Browser").

    Maybe it's not too late, to introduce such a layer into your project yet
    (VB/VBA-access then via WinHttp-5.1 COM-Object, which exists also in a 64Bit-version).

    HTH

    Olaf

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: MySQL (with VB6) questions

    Quote Originally Posted by techgnome View Post
    As for access level, it's either db or table... I don't think it can go into the cols/fields.
    -tg
    Have to disagree.
    Scroll down about one third. MySQl allows granting columnwise
    https://www.mysqltutorial.org/mysql-grant.aspx/

    Code:
    GRANT 
       SELECT (employeeNumber,lastName, firstName,email), 
       UPDATE(lastName) 
    ON employees 
    TO bob@localhost;
    EDIT: ... and if you read carefully, you'll see that you can actually grant write-only on a database, table, column....
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: MySQL (with VB6) questions

    Quote Originally Posted by Zvoni View Post
    Have to disagree.
    Scroll down about one third. MySQl allows granting columnwise
    https://www.mysqltutorial.org/mysql-grant.aspx/

    Code:
    GRANT 
       SELECT (employeeNumber,lastName, firstName,email), 
       UPDATE(lastName) 
    ON employees 
    TO bob@localhost;
    EDIT: ... and if you read carefully, you'll see that you can actually grant write-only on a database, table, column....
    sweet...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: MySQL (with VB6) questions

    Quote Originally Posted by Zvoni View Post
    Have to disagree.
    Scroll down about one third. MySQl allows granting columnwise
    https://www.mysqltutorial.org/mysql-grant.aspx/

    Code:
    GRANT 
       SELECT (employeeNumber,lastName, firstName,email), 
       UPDATE(lastName) 
    ON employees 
    TO bob@localhost;
    EDIT: ... and if you read carefully, you'll see that you can actually grant write-only on a database, table, column....
    Btw, MSSQL uses the same syntax for GRANT

    Code:
    GRANT SELECT(employeeNumber, lastName, firstName, email) ON dbo.employees TO bob
    cheers,
    </wqw>

  8. #8
    Banned
    Join Date
    May 2020
    Location
    https://t.me/pump_upp
    Posts
    42

    Re: MySQL (with VB6) questions

    Quote Originally Posted by Elroy View Post
    Hey, I know this isn't exactly the correct forum for these questions, but I'm doing all this from VB6, so I thought I'd ask here anyway. And, I'm just most familiar with this forum.

    Ok, we're setting up an interface to a MySQL database on a server within a WAN. Basically, the hookup is like this. We've got the MySQL server going ... we're hooked up to it via ODBC to MS-Access (MDB) files ... and then, we're opening those MDB files with a VB6 program for various manipulations. That's all just the details though.

    Specifically, there are starting to be some questions about setting up some security/privacy protocols, and two questions have come up:

    1) With MySQL, is there a way to allow someone write-only access? We'd like this so we can build an "upload" function via the ODBC so that several sites can "push" records into the MySQL tables without necessarily being able to query them.

    2) When setting up various MySQL schemas, is it possible to get access permissions down to the "field/column" level? Or is it always limited to the "table" level?

    I know there are some good DB people who float around this forum, so I appreciate you letting me ask about this here.

    Best Regards,
    Elroy
    I have the same idea as yours ... however I want to be able to do it all myself and don't want to depend on * other people's .dll because I want to learn and develop it myself ...

    I have consulted a lot of Olaf's source code ... she writes very well ... but I still want to follow Olaf's instructions on the following topics but so far I have not done it

    That is the topic that is very interesting and suitable for me
    You can refer to the following topic

    https://www.vbforums.com/showthread....30#post5498330

  9. #9

    Thread Starter
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    9,853

    Re: MySQL (with VB6) questions

    Thanks for all the input. I'm good to go at this point.
    Any software I post in these forums written by me is provided "AS IS" without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. To all, peace and happiness.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width