What Security?

• Martin Brawley

  Valued Member

  Points: 63

  More actions

  August 23, 2005 at 10:45 am

  #168132

  We have a software product which has a vb front end and a SQL Server backend.  The application uses 2 databases.  One holds all the customers data ie inventory, sales orders etc and the other has sensitive data regarding the application settings ie the number of licenses the customer has purchased.

  At the moment both databases have full permissions and the only thing stopping my customers from increasing the number of licenses of the software they have is lack of knowledge.  However more and more customers are getting familiar with Enterprise Manager and even linking the tables in Access. 

  I need to restrict the way in which users can access the general data and deny any access to the application settings. 

  I was planning on using an “Application Role” to stop users accessing the data from anywhere apart from via my application, and creating a SQL Server user with restricted access to the data to allow linking in excel etc. 

  I have 2 questions – firstly is this the best method for securing the databases.  Secondly is there a way of denying access to the application data from everyone on the customer’s site - even the SQL Server administrator.

  Hope this makes sence

  Martin

• Jo Pattyn

  SSC-Dedicated

  Points: 32271

  More actions

  August 23, 2005 at 11:33 am

  #584302

  2)The sql server administrator will be able to reveal it all.

  Perhaps using encryption on sensitive data will hold them off changing it.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 23, 2005 at 1:52 pm

  #584364

  I agree with Jo; the kind of application data should be encrypted.

  there's a nice collection of sample encryption techniques here on SSC:

  http://www.sqlservercentral.com/scripts/contributions/610.asp

  changing the USER_LICENSES column from '5' to ' °oçD‘à' solves this kind of issue real fast.

  declare @ency nvarchar (20)

  set  @ency=dbo.ENC_TEA2('5','secret')

  print @ency

  print dbo.DEC_TEA2(@ency,'secret')

   

  just be aware that they sometimes return non-printable characters, that make them difficult to copy/paste for testing. try copying and pasting the results of

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• bledu

  SSChampion

  Points: 12335

  More actions

  August 24, 2005 at 2:01 am

  #584493

  i also think encryption is one of the ways to solve this problem.also consider encrypting the stored procedures that are used to access the licenses db.

  But why don't you use a storec proc that stops people from logging on.and encrypt the stored procs that you use for this function.

  and also why don't you put the security info in the same db, because you will probably end up having maintenance problems.

  ---

  Everything you can imagine is real.