Re: How to protect a db from an sa user fetching its schema


"Magnus Österberg" <magnus.osterberg@xxxxxxxxxxx> wrote in message
news:epiDl5DSGHA.4740@xxxxxxxxxxxxxxxxxxxxxxx
On a customer-owned SQL Server 2000, we've got our db placed, together
with other software vendors databases. How can we protect from that the
customer and\or the other companies from fetching the db schema of our db?

All parties are allowd to login with the sa account. Can we by any means
configure our db such that only we can access the schema, and the sa can
not?

Must all parties (companies) be given individual logins? Then, what about
the sa account? Using this, one is still entitled to the schema of all
databses, right?

Members of the sysadmin fixed server role (which includes the built-in user
sa) cannot be denied access to anything in SQL Server.

But you don't have to be sa to read the schema of a db, because the public
role has select permissions for sysobjects, syscomments and the rest of the
system tables. All aspects of schema can be extracted from there.

You can stop non-sa users from browsing your tables, using stored procedures
or views and delegation/inheritance, but that may take a fair amount of
work, depending on your app, and can't be used with stored procedures that
dynamically generate the SQL they execute.

You can also encrypt your stored procedures, views, udf's, triggers, user
types and rules, which will keep everyone -- even sa -- from reading or
altering their definitions. There must surely be a way to crack it, for
users with enough skill and time, but this will keep the honest people
honest at the very least.

As for your table structures, as I implied above, they are, afaik,
conventionally unprotectable... otoh, how proprietary could they possibly
be?


-Mark


Any web page were I can read about this?

Magnus



.

Relevant Pages

  • Re: How to protect a db from an sa user fetching its schema
    ... All parties are allowd to login with the sa account. ... configure our db such that only we can access the schema, ... You can also encrypt your stored procedures, views, udf's, triggers, user ... honest at the very least. ...
    (microsoft.public.sqlserver.security)
  • Re: How to protect a db from an sa user fetching its schema
    ... All parties are allowd to login with the sa account. ... configure our db such that only we can access the schema, ... Must all parties be given individual logins? ...
    (microsoft.public.sqlserver.security)
  • Re: Problem with Stored Procedure that conatins several select statements
    ... If you test the stored procedures from the SQL Analyser, ... The Problem is that everytime I create a generated schema there is only ... one element in the response node called "Success" and not the two columns ... the final SELECT then the response elements are created as I expected. ...
    (microsoft.public.biztalk.general)
  • Re: SQL Best Practices Analyzer Rule: Use of Schema Qualified Tables/Views
    ... > How important is to specify the schame (dbo. ... > application may be improved by specifying schema names." ...
    (comp.databases.ms-sqlserver)
  • Re: A philosophical question about inserts
    ... Stored Procedures are the ideal -for all the benefits mentioned above. ... schema from them and have them call stored procedures or views to get the ... DBI/DBD) would prepare/execute: ... The main goal here is to isolate the developer from knowing the database ...
    (comp.databases.ms-sqlserver)