How to restrict evil create scripts?

[Ward Bekker]

Hi,

For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.

For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.

Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?

Regards,

Ward

[m.bohse]

Ward,

I would say as long as your permissions are set right you don't have to
worry. If your user only have rights in their own databases they
shouldn't be able to accessother user databases. Xp_Cmdshell which
would be able to delete files or run other OS commands is (by default)
only available to members of the sysadmin role.
So I would say create a empty datbase and grant the user only db_owner
or ddl_admin rights within the database.

Markus

[Erland Sommarskog]

Ward Bekker (wa**@NospaaMequanimity.nl) writes:

For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.

For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.

Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?

First of all, which version of SQL Server including service pack do you
have?

As M.Bohse said, run the scripts as a user who only have access in that
database, although in that database he need some privs. Very important:
make sure that cross-database chaining is turned off, and that the
database is not set as trustworthy on SQL 2005.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ward Bekker]

Hi Erland,

Thanks for the tips!

We are running SQL Server 2005.

Ward

Erland Sommarskog wrote:

Ward Bekker (wa**@NospaaMequanimity.nl) writes:

>For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.

For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.

Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?

First of all, which version of SQL Server including service pack do you
have?

As M.Bohse said, run the scripts as a user who only have access in that
database, although in that database he need some privs. Very important:
make sure that cross-database chaining is turned off, and that the
database is not set as trustworthy on SQL 2005.

[Erland Sommarskog]

Ward Bekker (wa**@NospaaMequanimity.nl) writes:

Hi Erland,

Thanks for the tips!

We are running SQL Server 2005.

In that case you should grant CONTROL on the dbo schema for the database
user. Maybe they are creating other scheams, but then they need to include
GRANTs for that.

You should also use

select name, is_trustworthy_on, is_db_chaining_on
from sys.databases

to make sure that the databases are not marked as trustworthy, or available
for DB-chaning.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx