locked
does application role prevents individuals to access to db via login and pass of application? RRS feed

  • Question

  • hi
    I need to know if i set application role for a database.Can anybody copy my mdf files to his computer and attach that to the his sql server and consequently see the tables and objects of my db?or the role exist internally in the .mdf file?
    Brainstorm your Brain and find solution,if no result stuck to Brainstormer.
    Sunday, October 18, 2009 6:27 AM

Answers

  • I think we have some communication problem here. An end-ser do not need access to the database files. Only the SQL Server service does. The end user uses some application which uses some netlib to talk to the SQL Server service, which in turn accesses the database files. I.e., you don't grant the end-user file access in the first place.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Sunday, October 18, 2009 1:17 PM
  • Additionally, anyone with sysadmin authority to the SQL Server can do anything they want to a database.  They can access and modify any object as well as read or write any piece of data.  Members of sysadmin can also grant permissions to other to perform any task that they choose.  There is an option in SQL Server 2008 Enterprise Edition that allows you to encrypt the database at rest such that if someone were to grab the data files or backups from a database, they would be inaccessible on another machine, without also having the certificate that is protecting them.  However, again, a member of the sysadmin role would be the one to setup the encryption and would therefore have access to the certificate that is used to encrypt.

    On top of that, anyone with local administrator, OU administrator, domain administrator, or enterprise administrator authority in Windows can gain all of the privileges that a sysadmin within SQL Server would have.

    You can't override or prevent either one.

    So, when you are providing a database that back ends your application to a customer, you don't have that kind of control over it.  Your customer is going to have sysadmin authority in the SQL Server as well as administrator authority in Windows, so they would be able to do anything they chose to do directly to your database.  No, you can't solve this by switching to any other database platform, because every other database platform would allow your customer direct access to the database as well.  The only way for you to do what you are asking of is for you to host the database for your customer and then lock down their access to only those things you want to grant, but that also means you have to perform all of the management tasks necessary and will also severely limit your customer base since few people will buy an application to run their business that doesn't allow them to control their own data.

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Sunday, October 18, 2009 6:22 PM

All replies

  • Application roles does nothing for protect if the user can attach or restore your database into his/hers own SQL Server.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Sunday, October 18, 2009 6:33 AM
  • Thanks Tibork for your reply,
    but is it any way to secure the mdf files to not be accessed via users?suppose i have a .mdf file which is for my customer and i dont want him to get access to this file via Sql server located in different place ?
    Brainstorm your Brain and find solution,if no result stuck to Brainstormer.
    Sunday, October 18, 2009 8:11 AM
  • mdf files can be copied to any other location when your sql server is not running . So make sure that you are not granting full access to anyone at the file system level. (It is similar to securing other files on the disk). If anyone gains access to your mdf files , they will be able to attach it on to their sql server.

    Thanks, Leks
    Sunday, October 18, 2009 8:29 AM
  • suppose i have no access to the file permissions on remote server.does sql server internally has any option to set login and pass to the file .mdf itself.?
    Brainstorm your Brain and find solution,if no result stuck to Brainstormer.
    Sunday, October 18, 2009 10:29 AM
  • NO. SQL engine cannot perform file control at the disk level.

    Thanks, Leks
    Sunday, October 18, 2009 1:04 PM
  • I think we have some communication problem here. An end-ser do not need access to the database files. Only the SQL Server service does. The end user uses some application which uses some netlib to talk to the SQL Server service, which in turn accesses the database files. I.e., you don't grant the end-user file access in the first place.
    Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi
    Sunday, October 18, 2009 1:17 PM
  • Additionally, anyone with sysadmin authority to the SQL Server can do anything they want to a database.  They can access and modify any object as well as read or write any piece of data.  Members of sysadmin can also grant permissions to other to perform any task that they choose.  There is an option in SQL Server 2008 Enterprise Edition that allows you to encrypt the database at rest such that if someone were to grab the data files or backups from a database, they would be inaccessible on another machine, without also having the certificate that is protecting them.  However, again, a member of the sysadmin role would be the one to setup the encryption and would therefore have access to the certificate that is used to encrypt.

    On top of that, anyone with local administrator, OU administrator, domain administrator, or enterprise administrator authority in Windows can gain all of the privileges that a sysadmin within SQL Server would have.

    You can't override or prevent either one.

    So, when you are providing a database that back ends your application to a customer, you don't have that kind of control over it.  Your customer is going to have sysadmin authority in the SQL Server as well as administrator authority in Windows, so they would be able to do anything they chose to do directly to your database.  No, you can't solve this by switching to any other database platform, because every other database platform would allow your customer direct access to the database as well.  The only way for you to do what you are asking of is for you to host the database for your customer and then lock down their access to only those things you want to grant, but that also means you have to perform all of the management tasks necessary and will also severely limit your customer base since few people will buy an application to run their business that doesn't allow them to control their own data.

    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Sunday, October 18, 2009 6:22 PM
  • Additionally, anyone with sysadmin authority to the SQL Server can do anything they want to a database.  They can access and modify any object as well as read or write any piece of data.  Members of sysadmin can also grant permissions to other to perform any task that they choose.  There is an option in SQL Server 2008 Enterprise Edition that allows you to encrypt the database at rest such that if someone were to grab the data files or backups from a database, they would be inaccessible on another machine, without also having the certificate that is protecting them.  However, again, a member of the sysadmin role would be the one to setup the encryption and would therefore have access to the certificate that is used to encrypt.

    On top of that, anyone with local administrator, OU administrator, domain administrator, or enterprise administrator authority in Windows can gain all of the privileges that a sysadmin within SQL Server would have.



    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    do you mean again the user can copy the mdf files in another comp and have access to it?

    Brainstorm your Brain and find solution,if no result stuck to Brainstormer.
    Saturday, October 24, 2009 4:47 AM
  • Hi ,

    Micheal Hotek here means about the sysadmin role of the sql server .

    -- do you mean again the user can copy the mdf files in another comp and have access to it?

    SYSADMIN is for sql server (which has all privileges in a sql server) . Dont baffle yourself this term with the windows folder access level . Your mdf files can be accessed by anyone who has full privilege on the FOLDER where SQL data and logfiles sit on the disk , only when sql server is stopped (data and logfile arent used by other processes.) 


    Thanks, Leks
    Saturday, October 24, 2009 5:58 AM