Create pivot table from Access FE in Excel - Security

[jaryszek]

Hi Guys,



I have split database and in FE user has query with all records.

I did in Excel pivot table with source to my Access Database.
Problem is that every user can create connection to Access Database without any passwords and problems.

I want to give access only to administrator in order to create pivot table like that.
It is possible?

Or best idea is to secure Access database inside it ? So give permission to administrator and button to export Excel file ?

Best wishes and thank you for help,
Jacek

[ranman256]

I have a tUser table to store the IDs of those allowed in the system and their level.
Users must have access to the db folder AND be in the table.
tUsers table
userID
userName
userDept
userLevel


if userLevel = "A" (admin) then they can access everything
others M (manager), or U (user), less so

sub Form_Load()
LoadUser


btnAdmin.enabled = goUser.Level = "A"
btnReports.enabled = goUser.Level = "A" or goUser.Level = "M"
end sub

[jaryszek]

Hi ranman256 !

Thank you !

So you are allowing users to have access only to specific tables.

But i still can connect to your FE query using Excel...

sub Form_Load()LoadUser


btnAdmin.enabled = goUser.Level = "A"
btnReports.enabled = goUser.Level = "A" or goUser.Level = "M"
end sub

So if user has level "A" you are enabling buttons for Admin?
Could you please explain more how your sub is working?

I am using tempvariables.
For each user I am reading if he has level "Admin" or "User" and opening specific forms for them,

Thank you for help,
Jacek

[ranman256]

i have a class that get the userID and Level. You can just use global variable too. gvUserID, gvUserLevel
In the form load event, disable/enable buttons.

yes you can enable rights to table too, but the form is access to the tables. I hide the database so users cannot see tables:
autoexec macro:
runcode HideDB
openform frmMainMenu

Public Function HideDB()
    DoCmd.SelectObject acTable, , True
    DoCmd.RunCommand acCmdWindowHide
End Function

[jaryszek]

Thank you ranman256!
Nice way!
your class is has to be public, yes?

but still when you have queries or tables in FE you can access it via Excel external connection.
How can i secure it?

Best Wishes,
Jacek

[jaryszek]

Anyone?

Jacek

[jaryszek]

I have noticed that when i have split database on Access i have to input password when i want to connect to FE.

But when i have split database on Access FE and MS SQL Server BE and password is saved on FE i can connect to external data from Excel to Access FE to specific view without any problems.
Guys, how can i secure it?

Jacek

[jaryszek]

Hello Guys,

so your split databases are also unsecured?
What if you have a lot of views in FE and want to secure it ?

Jacek

[jaryszek]

Hi,

So Access FE is entirely unsecure?

Jacek

[NoellaG]

You have to secure the back end tables, cause that's where the data are. Easiest way to secure Access data is to move them to SQL server express (free software).

Kind regards
Noëlla

[jaryszek]

Hi,

thank you very much NoellaG .

So FE is very unsecure and i should not have queries and views within it ?
So what about splitting database on Acccess?

I know that MS SQL Server is better but i am thinking only about FE Access and queries and views.
How to secure it ?

Create queries only in VBA code? To avoid possibility to connect it from Excel?

It is very important issue here,

Best,
Jacek

[NoellaG]

Hi,

when getting data , every query and view must connect to the table to retrieve the data. So when you set the security there in a correct way, every user has to have the correct permissions to view/change the data, whatever methods they use to connect : Excel, SQL manager, Access, ...
As your database is on SQL server you have there all possibilities to manage the security, by rows and by columns in the tables and in the views.

[jaryszek]

Thank you my friend,

I have tables linked to MS SQL Server, I saved the password and now Access FE is not prompting user to give the password.

And Access queries can be connected from Excel without any password.
So how can i set up specific accesses in Access FE to save the password for users (only login form and no prompting about password) and secure FE from external connections?

Maybe using login form linked tables should be created after user login?
And in closed state in FE there should be not any linked tables to MS SQL Server, only after user login.

Best,
Jacek

[NoellaG]

Hi,

the queries in Access don't require a password because you saved the password in the connection string. To secure FE:
- go to SQL server
- create a login for your users
- Use the login to create a user on your database
- Assign precise those rights they need on the views and tables and procedures in your database to work with your application, remove the other rights
- login in SSMS with the created login and check if evereything works as intended
- remove the existing linked tables from your access FE and relink them with a new connection string, using the uyser you just created
- check if all works as intended

Other tip: instead of Access queries create views/procedures on your SQL database to get the data and set the correct rights

[jaryszek]

Wow thank you very much NoellaG !

So the best thing is to do all things in database and give all permisions in MS SQL Server.

And then you can join to the view with specific user
Very good !

Best Wishes,
Jacek