GRANT support in MS ACCESS

Question

ALL,

Does GRANT-ing permissions supported in MS ACCESS?

I am connecting to accdb with ODBC driver from C++ app and trying to issue a GRANT command, but getting an error saying expecting DELETE, INSERT, PROCEDURE, SELECT or UPDATE.

It is on Windows 8.1 with MSVC 2017 Community.

The command I'm trying is:

GRANT SELECT ON MSysObjects TO Admin;

TIA!!

void uc_to_str_cpy(SQLWCHAR *dest, const std::wstring &src)
{
    const wchar_t *temp = src.c_str();
    while( *dest )
    {
        dest++;
    }
    while( *temp )
    {
        *dest = *temp;
        dest++;
        temp++;
    }
    *dest++ = 0;
    *dest = 0;
}

std::wstring query8 = L"GRANT SELECT ON MSysObjects TO Admin;";
query = new SQLWCHAR[query8.length() + 2];
memset( query, '\0', query8.length() + 2 );
uc_to_str_cpy( query, query8 );
ret = SQLExecDirect( m_hstmt, query, SQL_NTS );
delete[] query;
query = NULL;
if( ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO )
{
    GetErrorMessage( errorMsg, 1 );
    result = 1;
}

The documentation says [yes, it is supported](https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/grant-statement-microsoft-access-sql). — Robert Harvey, Dec 15 '20 at 01:35
AFAIK, Access hasn't had built-in user security since Access2003. Perhaps GRANT applies if Access is connecting to Oracle, SQLServer, etc. — June7, Dec 15 '20 at 04:15
@June7, possible, but unlikely. I am trying to get access to the internal ACCESS table, that I can't query otherwise - `MSysObjects` Any idea how to run `SELECT * FROM MSysObjects;` — Igor, Dec 15 '20 at 04:44
Or in other words - I need a way to check the table exists before creating. — Igor, Dec 15 '20 at 05:15
I have no problem querying MSysObjects table from within Access. https://stackoverflow.com/questions/2985513/check-if-access-table-exists. That is one way to check if a table already exists. Others https://stackoverflow.com/questions/2985513/check-if-access-table-exists or https://stackoverflow.com/questions/3350645/how-to-check-if-a-table-exists-in-ms-access-for-vb-macros or https://stackoverflow.com/questions/42059223/vba-script-to-check-if-table-exist-on-ms-access-delete-if-it-does. No idea how to translate any of this to C++. Why do you want to dynamically create table? — June7, Dec 15 '20 at 05:45
@June7, those are VB code and you are not querying the table. You ust using some VB code. Can you run this: `SELECT name FROM MSysObjects WHERE type IN (1, 4, 6) AND name = 'abcatcol';` — Igor, Dec 15 '20 at 05:55
I did say no idea how to translate to C++. Some of those responses did show query. Yes, that query works. — June7, Dec 15 '20 at 05:58
@HansUp, you are onto smth here. When I did the change in the ODBC settings and run my code I got this error - `cannot open the Microsoft access database engine workgroup information file`. Any idea what's wrong? I don't have Kaspersky installed, have CoMoDo FW here — Igor, Dec 15 '20 at 20:38
@Igor I deleted that comment because I tested afterward but could not make it work. I got past the "workgroup information file" complaint by giving it one with the SystemDB option, but then got another error which I don't recall. I gave up on ODBC for this. — HansUp, Dec 16 '20 at 17:21
@HansUp, what `SystemDB option`? Can you be more specific? I want to see if I can get past that as well and maybe I'll find a solution for that another error. Or maybe I won't get that at all? — Igor, Dec 16 '20 at 17:46
Such as [SystemDB=C:\mydatabase.mdw;](https://www.connectionstrings.com/microsoft-access-accdb-odbc-driver/workgroup/) ... giving it the full path to an existing MDW (workgroup information) file. Good luck! — HansUp, Dec 16 '20 at 17:51

score 0 · Answer 1 · answered Dec 15 '20 at 08:00

Grant does exist for Access. In most cases you can query against MSYSobjects. However, you could try several things:

First, try running your program (.exe) with elevated rights (right click and run as admin).

next up. You don't mention if this is JET (mdb) format, or is ACE (accdb format).

HOWEVER - using ODBC (as opposed to the oleDB driver), to my knowledge DOES NOT support Grant. So, quite sure you are out of luck.

You could I suppose consider creating a ADO object.

I find that EVEN inside of Access, if I use the built in ADO object, then grant will work.

but if I use a straight DAO object, then it does not work.

and for ODBC? Well, I find that grant does not seem to be supported - so, this looks like ODBC does NOT support the grant.

Also, use of GRANT in most cases also means that the connection string needs to include the work group file (it is by default automatic opened - even with ODBC, but using oleDB against such a database (from .net), I again find that GRANT does execute with oleDB - but gives an error message about the workgroup file having not been specified.

However, with ODBC - I get a error - syntax. So, at this point? ODBC does not support the grant DDL.

thx. First - I'm still developing and I'm running it under MSVC. Second - I did say I'm connecting to accdb ATM. Now, are you saying that it is driver fault - the Windows MS ACCESS ODBC driver does not support `GRANT` command? As you can see I'm calling SQLExecute() ODBC API. Also - could you give me some sample code of creating ADO object and issuing the GRANT command? — Igor, Dec 15 '20 at 08:21
I don't have a ADO example - should be easy to find one. But yes - I am flat out stating that ODBC does not support or accept the GRANT command. So, you have to give ADO a try. — Albert D. Kallal, Dec 15 '20 at 08:47

GRANT support in MS ACCESS

1 Answers1