Wednesday, March 28, 2012

restrict access to a database

Hi all,
Okay, we've got a product which has SQL Server as the backend database.
The client has the product as well as the database.
But the DBA at the clients end messes up with the data & we are held
responsible (that the product is what is messing up the data & NOT their
DBA).
How can we prevent this,
ie. try to encrypt the database or restrict access to the database to the
DBA.
Also, another requirement is that we do NOT want them to see the DB
structures, the tables, the data in the Databse!!!!
options,
1. Get them to install a new instance of SQL Server & NOT give the DBA or
anyone the password & have the password embeded in the application (thus
only the application can access the SQL Database).
NOTE : THe product and the SQL Servers WILL have to be at the clients end.
any other options?Do you intend to manage that server 100%? In other words you will be solely
and entirely responsible for monitoring, security, backups, performance,
maintenance and troubleshooting to an agreed SLA? If the answer is yes, then
of course you could in principle lock down the server and deny user access
to the database (I'm assuming that you used stored procs as your data access
tier so that you can deny object-level permissions to users).
If you can't answer yes to all of the above then the client has a legitimate
need to manage the database themselves and IMO you cannot reasonably deny
them symin level access and you cannot and should not attempt to secure
your system from the DBA. It seems to me that your best course of action in
that case would be to come to an agreement with the customer about the
division of responsibilities for the system and maybe implement some logging
process so that you have an audit trail of what has occurred to the data.
David Portas
SQL Server MVP
--|||wow!
how can i set object level permissions for users (including the DBA)?
& yes, we do have SPs.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:Is-dnXwsyoLKoxTfRVn-vA@.giganews.com...
> Do you intend to manage that server 100%? In other words you will be
solely
> and entirely responsible for monitoring, security, backups, performance,
> maintenance and troubleshooting to an agreed SLA? If the answer is yes,
then
> of course you could in principle lock down the server and deny user access
> to the database (I'm assuming that you used stored procs as your data
access
> tier so that you can deny object-level permissions to users).
> If you can't answer yes to all of the above then the client has a
legitimate
> need to manage the database themselves and IMO you cannot reasonably deny
> them symin level access and you cannot and should not attempt to secure
> your system from the DBA. It seems to me that your best course of action
in
> that case would be to come to an agreement with the customer about the
> division of responsibilities for the system and maybe implement some
logging
> process so that you have an audit trail of what has occurred to the data.
> --
> David Portas
> SQL Server MVP
> --
>|||EXEC sp_addrolemember 'db_denydatawriter', 'user name'
EXEC sp_addrolemember 'db_denydatareader', 'user name'
GRANT EXECUTE TO user_name ON usp_proc_name
David Portas
SQL Server MVP
--

No comments:

Post a Comment