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?
Hi
As you don't have the means to stop physical access this is difficult. You
may want to make sure that you are auditing such events. If you want to
carry out encryption then it is probably better on the client as the DBA
will have access to the methods used at the database end. You may also want
to look throught the products and articles on
http://www.sqlsecurity.com/DesktopDe...ndex=0&tabid=1
John
"D.Rudiani" <rudi@.www.com> wrote in message
news:ervb1anWFHA.1796@.TK2MSFTNGP15.phx.gbl...
> 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?
>
Showing posts with label dba. Show all posts
Showing posts with label dba. Show all posts
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 sy
min 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
--
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 sy
min level access and you cannot and should not attempt to secureyour 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 sy
min 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
--
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?D.Rudiani
If you want to restict access to the database then remove him/her from
sysadmin server role and don't grant the access to the database
"D.Rudiani" <rudi@.www.com> wrote in message
news:%23dJk2anWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> 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?
>|||Hi,
Since the SQL SERVER machine is in the clients place and DBA needs to
perform administrative tasks ( BACKUP , RECOVERY , DBCC, REINDEX..),
it is very difficult to restrict the DBA. Only way to restrict is change the
OS Level admin password and SA password for sql server and do not hand it to
client side DBA. In this case he can not perform any administrative tasks.
Also, another requirement is that we do NOT want them to see the DB
structures, the tables, the data in the Databse!!!!
See Application roles in Books online.
Thanks
Hari
SQL Server MVP
"D.Rudiani" <rudi@.www.com> wrote in message
news:%23dJk2anWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> 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?
>sql
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?D.Rudiani
If you want to restict access to the database then remove him/her from
sysadmin server role and don't grant the access to the database
"D.Rudiani" <rudi@.www.com> wrote in message
news:%23dJk2anWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> 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?
>|||Hi,
Since the SQL SERVER machine is in the clients place and DBA needs to
perform administrative tasks ( BACKUP , RECOVERY , DBCC, REINDEX..),
it is very difficult to restrict the DBA. Only way to restrict is change the
OS Level admin password and SA password for sql server and do not hand it to
client side DBA. In this case he can not perform any administrative tasks.
Also, another requirement is that we do NOT want them to see the DB
structures, the tables, the data in the Databse!!!!
See Application roles in Books online.
Thanks
Hari
SQL Server MVP
"D.Rudiani" <rudi@.www.com> wrote in message
news:%23dJk2anWFHA.2448@.TK2MSFTNGP12.phx.gbl...
> 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?
>sql
Subscribe to:
Posts (Atom)