How to restrict evil create scripts?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Ward Bekker
    #1

    How to restrict evil create scripts?

    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
    Tags: None
    • m.bohse@quest-consultants.com
      #2
      Re: How to restrict evil create scripts?

      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

        Comment

        • Erland Sommarskog
          #3
          Re: How to restrict evil create scripts?

          Ward Bekker (ward@NospaaMeq uanimity.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, esquel@sommarsk og.se

          Books Online for SQL Server 2005 at
          http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

          Books Online for SQL Server 2000 at
          http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

            Comment

            • Ward Bekker
              #4
              Re: How to restrict evil create scripts?

              Hi Erland,

              Thanks for the tips!

              We are running SQL Server 2005.

              Ward

              Erland Sommarskog wrote:
              Ward Bekker (ward@NospaaMeq uanimity.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.
              >
              >

                Comment

                • Erland Sommarskog
                  #5
                  Re: How to restrict evil create scripts?

                  Ward Bekker (ward@NospaaMeq uanimity.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, esquel@sommarsk og.se

                  Books Online for SQL Server 2005 at
                  http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

                  Books Online for SQL Server 2000 at
                  http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                    Comment

                    Previous template Next
                    Working...