Manipulating current user permission in code

[Medhat Nasr]

Hi all,

I have 4 MS Access .ade projects. Each of these .ades connects to the same centralized SQL Server database and performs its own specialized activities on the relevant portion of the database.

Each .ade has its own Login Form to the Server and the Database using either Windows or SQL Server Authentication.

Further, I used the following additional routine to decide each user permitted activities on the data (Edit, Add, or ReadOnly):

1. The user should provide a specific Password within a Dialog Form to open the selected Form in the selected –data- mode (Edit, Add, or ReadOnly).

2. The required Password to open any Form in a specific –data- mode is stored in a Table within the Database – which is to be DLooked up in VBA code, and the required password is to be assigned temporarily to a Public Variable on which the user input is to be verified.

3. If the Password entered by the user in the Dialog Form matches the Public Variable value, then, the Form selected by the user will open in the selected –data- mode, otherwise, the user will get an Error message.

4. For this VBA code to work, users must be granted – at least - the SELECT Permission on the “PassWords” Table. And to do that without enabling users to open the “PassWords” Table and view all the Pass Words stored there, it was planned that every user should only have MS Access runtime installed on his machine.

My question is: In case that any user could have the full version of MS Access installed on his machine, is it possible to manipulate in code - the current user permission on the “PassWords” Table, so that he is temporarily granted the necessary SELECT permission only when the VBA DLookup Method is needed to run and then, just after the required password value is assigned to the Public Variable, this permission is taken back, so that the user can’t open the “PassWords” Table.

Thanks for being my favorite group.

 

[Sylvain Lafontaine]

Instead of granting use to the table itself, you should use a stored procedure (SP) that will verify or return the required password from this table; possibly using the function SUSER_SNAME from inside the SP to verify the current login account.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server (French)



Hi all,

I have 4 MS Access .ade projects. Each of these .ades connects to the same centralized SQL Server database and performs its own specialized activities on the relevant portion of the database.

Each .ade has its own Login Form to the Server and the Database using either Windows or SQL Server Authentication.

Further, I used the following additional routine to decide each user permitted activities on the data (Edit, Add, or ReadOnly):

1. The user should provide a specific Password within a Dialog Form to open the selected Form in the selected –data- mode (Edit, Add, or ReadOnly).

2. The required Password to open any Form in a specific –data- mode is stored in a Table within the Database – which is to be DLooked up in VBA code, and the required password is to be assigned temporarily to a Public Variable on which the user input is to be verified.

3. If the Password entered by the user in the Dialog Form matches the Public Variable value, then, the Form selected by the user will open in the selected –data- mode, otherwise, the user will get an Error message.

4. For this VBA code to work, users must be granted – at least - the SELECT Permission on the “PassWords” Table. And to do that without enabling users to open the “PassWords” Table and view all the Pass Words stored there, it was planned that every user should only have MS Access runtime installed on his machine.

My question is: In case that any user could have the full version of MS Access installed on his machine, is it possible to manipulate in code - the current user permission on the “PassWords” Table, so that he is temporarily granted the necessary SELECT permission only when the VBA DLookup Method is needed to run and then, just after the required password value is assigned to the Public Variable, this permission is taken back, so that the user can’t open the “PassWords” Table.

Thanks for being my favorite group.

 

[Paul Shapiro]

Absolutely. If security is important, you can't restrict users by hoping they don't learn how to get directly to the data. Plenty of tools exist if their account is authorized to access the SQL Server database.

I would create a stored procedure which accepts a username and password. Return a boolean to indicate if the login was successful. The stored procedure also ensures you avoid the SQL injection vulnerability that would come from building a dynamic sql string with the user's input.

If your users are in a Windows domain, SQL Server works very nicely with integrated authentication. Use a connection string that specifies using the current user's Windows account. Setup database roles in SQL Server for the appropriate user groups, and assign users to a Windows group that has been granted the appropriate database roles. This keeps it much more manageable as things change in the future.
Instead of granting use to the table itself, you should use a stored procedure (SP) that will verify or return the required password from this table; possibly using the function SUSER_SNAME from inside the SP to verify the current login account.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server (French)



Hi all,

I have 4 MS Access .ade projects. Each of these .ades connects to the same centralized SQL Server database and performs its own specialized activities on the relevant portion of the database.

Each .ade has its own Login Form to the Server and the Database using either Windows or SQL Server Authentication.

Further, I used the following additional routine to decide each user permitted activities on the data (Edit, Add, or ReadOnly):

1. The user should provide a specific Password within a Dialog Form to open the selected Form in the selected –data- mode (Edit, Add, or ReadOnly).

2. The required Password to open any Form in a specific –data- mode is stored in a Table within the Database – which is to be DLooked up in VBA code, and the required password is to be assigned temporarily to a Public Variable on which the user input is to be verified.

3. If the Password entered by the user in the Dialog Form matches the Public Variable value, then, the Form selected by the user will open in the selected –data- mode, otherwise, the user will get an Error message.

4. For this VBA code to work, users must be granted – at least - the SELECT Permission on the “PassWords” Table. And to do that without enabling users to open the “PassWords” Table and view all the Pass Words stored there, it was planned that every user should only have MS Access runtime installed on his machine.

My question is: In case that any user could have the full version of MS Access installed on his machine, is it possible to manipulate in code - the current user permission on the “PassWords” Table, so that he is temporarily granted the necessary SELECT permission only when the VBA DLookup Method is needed to run and then, just after the required password value is assigned to the Public Variable, this permission is taken back, so that the user can’t open the “PassWords” Table.

Thanks for being my favorite group.

 

[Medhat Nasr]

Thank you Sylvain, Paul



Your advice seems to be the only real solution to my data security question.



Unfortunately I’m not very familiar with using stored procedures that far, and it will take me quite a while to get there.



It’d be great help if you can tell me where I should start digging.



Thanks again.

Absolutely. If security is important, you can't restrict users by hoping they don't learn how to get directly to the data. Plenty of tools exist if their account is authorized to access the SQL Server database.

I would create a stored procedure which accepts a username and password. Return a boolean to indicate if the login was successful. The stored procedure also ensures you avoid the SQL injection vulnerability that would come from building a dynamic sql string with the user's input.

If your users are in a Windows domain, SQL Server works very nicely with integrated authentication. Use a connection string that specifies using the current user's Windows account. Setup database roles in SQL Server for the appropriate user groups, and assign users to a Windows group that has been granted the appropriate database roles. This keeps it much more manageable as things change in the future.
Instead of granting use to the table itself, you should use a stored procedure (SP) that will verify or return the required password from this table; possibly using the function SUSER_SNAME from inside the SP to verify the current login account.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server (French)



Hi all,

I have 4 MS Access .ade projects. Each of these .ades connects to the same centralized SQL Server database and performs its own specialized activities on the relevant portion of the database.

Each .ade has its own Login Form to the Server and the Database using either Windows or SQL Server Authentication.

Further, I used the following additional routine to decide each user permitted activities on the data (Edit, Add, or ReadOnly):

1. The user should provide a specific Password within a Dialog Form to open the selected Form in the selected –data- mode (Edit, Add, or ReadOnly).

2. The required Password to open any Form in a specific –data- mode is stored in a Table within the Database – which is to be DLooked up in VBA code, and the required password is to be assigned temporarily to a Public Variable on which the user input is to be verified.

3. If the Password entered by the user in the Dialog Form matches the Public Variable value, then, the Form selected by the user will open in the selected –data- mode, otherwise, the user will get an Error message.

4. For this VBA code to work, users must be granted – at least - the SELECT Permission on the “PassWords” Table. And to do that without enabling users to open the “PassWords” Table and view all the Pass Words stored there, it was planned that every user should only have MS Access runtime installed on his machine.

My question is: In case that any user could have the full version of MS Access installed on his machine, is it possible to manipulate in code - the current user permission on the “PassWords” Table, so that he is temporarily granted the necessary SELECT permission only when the VBA DLookup Method is needed to run and then, just after the required password value is assigned to the Public Variable, this permission is taken back, so that the user can’t open the “PassWords” Table.

Thanks for being my favorite group.