The best countermeasure for controlling stored procedures is
simply to delete them. You can do so by taking these steps:
2. Expand the
Extended Stored Procedures folder within the database.
3. Right click on
the stored procedure.
4. For SQL
Servers 7.x, 8.x, and 2000 select the Delete feature to remove the stored
procedure.
If removing the stored procedures outright isn't an option
before extensive testing is performed, you can restrict the permissions on the
stored procedures. To perform ACLing on the SPs, follow this procedure:
2. Expand the
Extended Stored Procedures folder within the database.
3. Right click on
the stored procedure.
4. Select
Properties.
5. Click on the
Permissions tab.
6. Change the
permissions as desired.
Part of the Transact-SQL reference, Microsoft SQL 2000 provides
a litany of simple functions that can be called directly within an SQL string to
provide valuable information. Here is a short list of helpful functions:
db_id()
|
Displays the
database ID. Example: select
db_id(); or use mydb; select db_id()
|
db_name()
|
Displays the
currently used database name and is helpful in understanding what database is
being used by default. Example: select db_name();
|
file_name(<ID>)
|
Displays the logical
file name for the supplied file identifier (ID) and is helpful for enumerating
all the files within a database. Example: select file_name(1) or use mydb; select file_name(1);
|
Getdate()
|
Displays the date
and time on the system. select
getdate()
|
object_name(<parm>)
|
Displays the
database object name and is helpful in enumerating the various objects within a
database. select
object_name(1) or use mydb; select object_name(1)
|
setuser <user>
|
Impersonates a user
but is valid only if existing user is member of sysadmin or db_owner fixed
database role. setuser
'jane';
|
current_user
|
Displays the
currently logged in user for the SQL session and is helpful in identifying the
user making database queries. select
current_user;
|
These Transact-SQL functions can be enormously helpful to an
attacker.
An attacker would salivate over this information. Other keywords that can be
used are:
CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
KEY_COLUMN_USAGE
PARAMETERS
REFERENTIAL_CONSTRAINTS
ROUTINES
ROUTINE_COLUMNS
SCHEMATA
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS
Unfortunately, this functionality, also, is inherent in
Microsoft SQL Server and cannot be shut off.
Master.dbo Default System Stored and Extended Stored Procedures | |
Procedure
|
Description
|
sp_addmessage
|
Adds a new error
message to the sysmessages table.
|
sp_configure
|
Displays or changes
global configuration settings such as c2 audit mode, allow updates, remote
access, remote login timeout, user connections, and the like.
|
sp_help
|
Can enumerate just
about anything on the SQL Server itself, including all objects.
|
sp_helpdb
|
Lists the databases
available and, when used with a database name as a parameter, displays specific
database information.
|
sp_helpprotect
|
Displays information
on permissions for objects.
|
sp_OACreate
|
Creates an instance
of the OLE object.
|
sp_OADestroy
|
Destroys an OLE
object.
|
sp_OAGetErrorInfo
|
Displays OLE
Automation error information.
|
sp_OAGetProperty
|
Displays a property
value of an OLE object.
|
sp_OAMethod
|
Calls a method of an
OLE object.
|
sp_OASetProperty
|
Sets a property of
an OLE object.
|
sp_OAStop
|
Stops the OLE
Automation stored procedure.
|
sp_password
|
Adds or changes a
password for an SQL Server login. Examples: EXEC sp_password 'oldpass', 'newpass',
'sa'.
|
sp_tables
|
Displays the tables
for the current database. Helpful in enumerating all the tables within a
database. Examples: EXEC
sp_tables or use mydb; EXEC sp_tables;.
|
sp_who
|
Displays information
on SQL Server connections such as status, login name, host name where the
connection is coming from, and database name and commands such as SELECT.
|
xp_availablemedia
|
Reveals the
available drives on the machine.
|
xp_cmdshell
|
Runs arbitrary
commands with administrator privilege.
|
xp_deletemail
|
Deletes a message
from the Microsoft SQL Server inbox.
|
xp_dirtree
|
Allows a directory
tree to be obtained.
|
xp_dsninfo
|
Displays the ODBC
DSN information.
|
xp_enumdsn
|
Enumerates ODBC data
sources on the server.
|
xp_enumgroups
|
Displays a list of
Windows groups on the system. Example: EXEC master..xp_dirtree.
|
xp_eventlog
|
Displays event logs
from the Windows system.
|
xp_fixeddrives
|
Displays the fixed
drives on the system and its free space in MB.
|
xp_getfiledetails
|
Displays the
properties of a given file. Example: EXEC master..xp_getfiledetails
'c:\winnt.ini'.
|
xp_getnetname
|
Displays the running
system's NetBIOS name.
|
xp_grantlogin
|
Grants login rights
of the specified user. Example: EXEC
master..xp_grantlogin administrator.
|
xp_logevent
|
Logs a user-defined
message in the SQL Server log file.
|
xp_loginconfig
|
Reveals information
about the security mode of the server.
|
xp_logininfo
|
Displays the login
information of the various users.
|
xp_makecab
|
Allows the user to
create a compressed archive of files on the server (or any files the server can
access).
|
xp_msver
|
Displays the
Microsoft SQL Server version, including all information about the operating
system. Example: EXEC
master..xp_msver.
|
xp_ntsec_enumdomains
|
Enumerates domains
that the server can access.
|
xp_readerrorlog
|
Displays the SQL
Server error log.
|
xp_readmail
|
Reads a mail message
in the SQL Server inbox.
|
xp_regaddmultistring
|
Adds a multi string
registry key.
|
xp_regdeletekey
|
Deletes a registry
key.
|
xp_regdeletevalue
|
Deletes a value
within a registry key.
|
xp_regenumkeys
|
Enumerates registry
key.
|
xp_regenumvalues
|
Enumerates registry
key values.
|
xp_regread
|
Reads a registry
key.
|
xp_regremovemultistring
|
Removes a
multistrong registry key.
|
xp_regwrite
|
Writes to a registry
key.
|
xp_revokelogin
|
Revokes access from
a Windows group or user.
|
xp_sendmail
|
Sends a message to
someone.
|
xp_servicecontrol
|
Allows a user to
start or stop a Windows service. Examples: EXEC master..xp_servicecontrol
'start', 'schedule'.
|
xp_startmail
|
Starts an SQL Server
mail client session.
|
xp_stopmail
|
Stops an SQL Server
mail client session.
|
xp_subdirs
|
Displays a list of
subdirectories.
|
xp_terminate_process
|
Terminates a
process, given its process ID (PID).
|
xp_unc_to_drive
|
Unknown.
|