Scripting Security
-
Hi,
I'm a bit of a SQL Server n00b but I need to do something for our boss. Our company is using SQL Server 2000 and I'm trying to look for a way of preventing a user from scripting our whole database. From what I see even a public user who has access to either Enterprise Manager or Query Analyzer has the 'copy' action enabled which allows him/her to view the stored procedure and then take it home with him/her.
Is there a way of disabling the copy/scripting option from Enterprise Manager or Query Analyzer?
Thanks!!!
-
Unfortunately, there's not a whole lot you can do if the user has access to the database. By default, the public role has SELECT permissions against both sysobjects and syscomments. To undo this puts the database in an unsupported configuration so far as Microsoft is concerned.
You can audit for such activity using profiler/sql traces, but that means parsing text fields to try and watch for such activity. About the best thing you can do is some sort of procedural option which makes it an offense to take source code off-site without authorization. Keep in mind that if you use source control to store your SQL Server code, that has to be watched, too.
K. Brian Kelley
@kbriankelley -
Thanks for the reply Brian but unfortunately that's not the answer my boss will accept~ ='( So I'll have to keep on researching. With regards to a developer who uses Enterprise Manager and Query Analyzer, what options do we have from preventing him from scripting the database?
You mentioned that the public role has SELECT permissions on system tables and stored procedures. What would happen if I removed all those permissions from the public role?
Are there other editors out there that can be used to reference a SQL Server 2000 database without a scripting option?
Many thanks,
Andrew
-
Short answer is nothing should happen on removing syscomments access, except if you have to call in support you'll have to add it back. I wrote this all up for my GSEC practical:
SQL Server 2000: Permissions on System Tables Granted to Logins Due to the Public Role
K. Brian Kelley
@kbriankelley -
Hi Brian,
Well I've removed the SELECT access from the public role and now users can't see the Stored Procedure code if they try to view the code via Enterprise Manager (Double clicking on the stored procedure) or through Query Analyzer (Right click -> Edit the stored procedure), however I was wondering two things.
1) Are there any other ways of accessing the code behind the stored procedure?
2) Will removing the SELECT access from the public role has other dire consequences for the database when users access it?
Many thanks,
Andrew
-
The code itself is in the syscomments table. Therefore, if public doesn't have access to that table, they can't see the code by doing a direct select. However, there are system stored procedures such as sp_helptext which also allows the ability to get at the info (which I cite in the practical). That particular stored procedure is in the master database and public has EXECUTE access.
One other thing to keep in mind is if any of the users are either owners of the database or members of the sysadmin fixed server role. By default those map into the database as dbo. This user account, dbo, is a special account in that it ignores all permissions. Meaning that a login which maps in as dbo can still get to all the information. BTW, this is how the SQL Server specific mechanisms work, and that's why revoking access to the public role doesn't break anything with respect to SQL Server internally itself.
That reminds me... one database role to also watch for is db_datareader. This role has implicit SELECT permissions against any table or view. Meaning even though you've revoked permission to the public role, members of this role still have access.
As you can see, trying to block access gets to be a mess pretty quickly.
K. Brian Kelley
@kbriankelley -
Just a question/observation ... Are these truly 'end users' ? If they are truly 'end users' why do they have access to Query Analyzer and Enterprise Manager ? If they are not true 'end users' but rather developers, then access rights can be pared down as stated earlier in the post.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Unless there is a corporate policy restricting them from such tools, if they have access to the database, they can script the objects. It's one of those catch 22s. SQL Server 2000 wasn't written with information disclosure issues in mind because when it released the world was a far different place. SQL Server 2005 does a better job of restricting the schema information.
K. Brian Kelley
@kbriankelley
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply