Shulou(Shulou.com)05/31 Report--

This article to share with you is about SQL Server system table what is used, Xiaobian think quite practical, so share to everyone to learn, I hope you can read this article after some harvest, not much to say, follow Xiaobian to see it.

The role of each system table in SQL Server

sysaltfiles master save database files

syscharsets main database character set and sort order

sysconfigurations master database configuration options

syscurconfigures master database current configuration options

sysdatabases in the primary database server

syslanguages main database language

syins main database login account information

sysoledbusers main database link server login information

sysprocesses master database process

sysremotelogins master database remote login account

syscolumns per database column

sysconstraints per-database constraints

sysfilegroups Each database filegroup

sysfiles Each database file

sysforeignkeys External keywords per database

sysindexes index per database

sysmenbers Members of each database role

sysobjects all database objects per database

syspermissions per-database permissions

systypes user-defined data types per database

sysusers per database user

SQL Server Security Checklist

1. Make sure you have the latest patches for NT/2000 and SQL Server installed. Needless to say, you should have them installed, but I think it's best to remind you here.

2. Evaluate and select a network protocol that considers maximum security without compromising functionality. Multiprotocol is a wise choice, but it sometimes cannot be used in heterogeneous environments.

3. Set strong passwords for "sa" and "probe" accounts to enhance their security. Set a strong password and keep it in a safe place. Note: probe accounts are

Used for performance analysis and distribution transmission. When used in standard security mode, setting a strong password for this account can affect the use of certain features.

4. Use a low-privilege user as the query operator account for SQL Server services, not LocalSystem or sa. this account should have minimal rights (note as a service

The right to run is a must) and should contain (but not stop) attacks on servers in case of compromise. Note when using Enterprise Manager to do the above settings, files, registry and usage

ACLs on the rights of the person are processed simultaneously.

5. Make sure all SQL Server data and system files are installed in NTFS partitions, and appropriate ACs are applied. What if someone gets access to the system

This layer of permissions prevents intruders from destroying data and avoiding a catastrophe.

6. Turn off Xp_cmdshell if you don't use it. If you are using SQL 6.5, at least restrict non-sa users to using SQL ExecutiveCmdExec account actions in Server Options.

XP_cmdshell.

In any isql/ osql window (or query analyzer):

use master

exec sp_dropextendedprocxp_cmdshell

For more information on SQLExecutiveCmdExec, see the following articles:

.

If you don't need xp_cmdshell, disable it. Remember that a system administrator can always add it back if needed. It's good or bad-an intruder could have

Now it's not there, just add it back. Consider also removing the dll below but must test before removing because some dlls are used by several programs at the same time. To find out if other programs make

Use the same dll:

Get the dll first.

select o.name,c.text from dbo.syscomments c , dbo.sysobjects o where c.id=o.id and o.name=xp_cmdshell

Second, use the same dll to discover whether other extended storage operations use that dll.

select o.name,c.text from dbo.syscomments c , dbo.sysobjects o where c.id=o.id and c.text=xplog70.dll

The user can do the same for the other processes you want to remove in the following steps.

7. Disable object linking and embedding automation stores if not needed (Warning-some Enterprise Manager functionality may be lost when these stores are disabled). These include:

Sp_OACreate

Sp_OADestroy

Sp_OAGetErrorInfo

Sp_OAGetProperty

Sp_OAMethod

Sp_OASetProperty

Sp_OAStop

If you decide to disable the process then write a script for them so that you can add them back later when you need them. Remember, what we're doing here is locking.

Define an application's functionality-your development platform should be placed on other machines.

8. Disable registry accessors you don't need. (Same warning as above) These include:

Xp_regaddmultistring

Xp_regdeletekey

Xp_regdeletevalue

Xp_regenumvalues

Xp_regremovemultistring

Note: I used to list xp_regenerate/ xp_regwrite here but removal of these programs affects some major features including logging and SP installation, so their removal is not pushed

Recommendation.

9. Remove other system storage processes that you believe pose a threat. Such processes are quite numerous, and they also waste some cpu time. Be careful not to start with a configured server

On doing so. First test on the development machine to make sure that this does not affect any system functionality. Below is a list of our recommendations for your evaluation:

sp_sdidebug

xp_availablemedia

xp_cmdshell

xp_deletemail

xp_dirtree

xp_dropwebtask

xp_dsninfo

xp_enumdsn

xp_enumerrorlogs

xp_enumgroups

xp_enumqueuedtasks

xp_eventlog

xp_findnextmsg

xp_fixeddrives

xp_getfiledetails

xp_getnetname

xp_grantlogin

xp_logevent

xp_loginconfig

xp_logininfo

xp_makewebtask

xp_msver xp_perfend

xp_perfmonitor

xp_perfsample

xp_perfstart

xp_readerrorlog

xp_readmail

xp_revokelogin

xp_runwebtask

xp_schedulersignal

xp_sendmail

xp_servicecontrol

xp_snmp_getstate

xp_snmp_raisetrap

xp_sprintf

xp_sqlinventory

xp_sqlregister

xp_sqltrace

xp_sscanf

xp_startmail

xp_stopmail

xp_subdirs

xp_unc_to_drive

xp_dirtree

10. Disable default logins in Enterprise Manager under Security Options. (SQL 6.5 only) When using integrated security, this leaves unacknowledged users out of the syins table.

Access to a valid database server.

11. Remove guest accounts from the database to keep unauthorized users out. The exceptions are master and tempdb databases, for which guest accounts are required.

12. If not necessary, disable SQL Mail completely. Its existence makes it possible for potential attackers to deliver potential trojans, viruses or simply implement a DOS attack

13. Check master.. Sp_helpstartup looks for suspicious Trojan processes. Make sure no one has planted a secret backdoor here. Use Sp_unmakestartup to remove any

Suspicious process.

14. Check master.. Sp_password See if trojan code is present. Compare your product scripts with the default scripts of a newly installed system and save conveniently.

15. Record all user access. Make these settings from Enterprise Manager or enter the following items in Query Analyzer by logging in as SA:

xp_instance_regwrite NHKEY_LOCAL_MACHINE, NSOFTWARE\ Microsoft\MSSQLServer\MSSQLServer,NAuditLevel,REG_DWORD,3

16. Rewrite applications to use more user-defined storage and viewing processes so general access to tables can be disabled. Here you should also see that since you don't have to do frequent query planning exercises

The performance of the Group is as follows:

17. Remove unwanted network protocols.

18. Note the physical security of SQL Server. Lock it in a secure room and keep the key secure. Whenever there's a chance to get to the server, there's always a way in.

19. Set up a scheduled task to run:

findstr/C:" Login Failed"7\log\*.* ">\mssql7\log\*.*

Then redirect the output to a text file or email, so you monitor failed login attempts. This also provides a good way for system administrators to log attacks. There are also a lot of things

A third-party tool for analyzing NT log events. Note: You may need to replace the path with the path where you installed SQL.

20. Set illegal access and login failure log alerts. Go to "Manager SQL Server Messages " in Enterprise Manager and search for any messages that you do not have permission to access.

Start with "login failed" and "denied". Make sure all the information you are interested in is recorded in the event log. Then set an alert on that information, send an email or a letter,

Find an operator who can respond to problems in a timely manner.

21. Make sure that roles at the server and database levels are granted only to the users who need them. While SQL Server Security Model 7 has many enhancements, it also adds an additional licensing layer

We must monitor this layer to make sure no one is granted more permissions than necessary.

22. Always review group or role membership and make sure to assign permissions to groups so that your audit work can be simplified. Make sure that public groups cannot perform selections from the system list while you are at it

Operation.

23. Take some time to audit requests for login with blank passwords. Use the following code for null password checking:

using the body

Choose a name,

password

from syslogins

where password is null

order by name

24. If possible, leverage integrated security policies in your organization. By using an integrated security policy, you can rely on system security, minimizing administrative effort from maintaining two separate

From the security model. This also keeps the password from approaching the concatenation string.

25. Check the permissions of all access processes and extended storage processes for non-sa users. Use the following query to periodically query which processes have public storage permissions. (Used in SQL Server)

"type" instead of "xtype"):

Use master

select sysobjects.name

from sysobjects,sysprotects

where sysprotects.uid=0

AND xtype in (X,P)

AND sysobjects.id=sysprotects.id

Order by name

26. When using Enterprise Manager, use an integrated security policy. In the past, enterprise administrators have been found to store the "sa" password in the plaintext of the registry in standard security mode. Note: Even if you change the mode, the password will remain in the registry

The above is what SQL Server system tables are used for, Xiaobian believes that some knowledge points may be what we see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.