CODEWITHSUNDEEP
mysqlsqlsql-serverdatabaseoracle-database
Tommy
Tommy

Reputation: 656

Allow DBMS User Connection only through specific Application

I am developing a multi-user-application windows desktop application. For User Management i use the DMBS (Oracle, MySQL, MSSQL, Postgre). So each user has an database user account.

I do not want the user to connect to the database directly and read / write data. The data should only be accessible connection should only be valid if they use my application.

I found this website by SQLDUDE where he describes some techniques.

One way to access the data only through a specific application is a logon trigger that checks for application_name. However he also describes that this method can easily be spoofed as the user can specify the application_name once he knows it in the connection string. (detailed explanation here - see Solution for Scenario #2)

He also mentions Application Roles.

A more secure approach you could use for this is called "Application Roles". When connecting from an application you assume a particular role and only that role is granted privileges required in the database.

So basically the user logs in with his login credentials but has no rights at all (only connect).

Then inside the application i call sp_setapprole with a password, once the connection is established so the application rules are granted.

Once this call succeeds then the connection gets the privileges of the application role and loses privileges of the actual user, which is what we want. So if someone tried to connect to the database from SSMS or SQLCMD, they will access the DB using their credentials, which won’t have the required permissions on the tables, since only the application role has the rights on the tables. This is more secure & reliable approach, but one that requires application code change and as a DBA you will have to create the application role in SQL Server.

So Application Roles sounds like the way to go.

My question is:

Are application roles DMBS standard and available to most DBMS systems?

Is there way to trace the sp_setapprole login (e.g. with WireShark)?

Of course someone could reverse-engineer the application and get the credentials for the application role - but i guess that's unavoidable :)

Upvotes: 0

Views: 344

Answers (1)

Felipe Moreno
Felipe Moreno

Reputation: 508

I've seen one simpler solution in place and it worked fine. Here it is:

  • The application is responsible for creating users at the database
  • Before creating the user, the application hashed/encrypted their password, so if you chose password "123456" it would be created as "RRU2992191910" (just an example)
  • If the user tried to connect to the database with "123456" it wouldn't be successful
  • Only the application was able to connect to database, because it hashed/encrypted the password informed by the user

This is not the most secure solution in the world, but it's very simple and does what you want. And it could be ported to different RDBMS's with no extra cost.

Upvotes: 1

Related Questions