Friday, 12 December 2008

Write your own system stored procedure in SQL Server 2005 and 2008

I’ve found that writing stored procedures is always top of a DBA list of things to do in his or her spare time.

But sometimes I find that you write something so useful you want to use it on every database not just one, to do this you need to place it into the master database and here is how you do it

Use Master
GO
--incase it already exists
IF OBJECT_ID('dbo.sp_pagelocks', 'P') IS NOT NULL
DROP PROCEDURE dbo.sp_pagelocks;
GO
--my new procedure
CREATE PROCEDURE dbo.sp_pagelocks
as
select * from sys.indexes where allow_page_locks=0
go

you can now test it by going to another database and executing it.

Use mytestdb
Go
Exec sp_pagelocks

You see things attached to the dbo on the master database are automatically accessible by all the database but remember that this works both ways, it means if you write a delete or table update statement it can be used on all database maybe even on one you don’t want to change things on so be careful what procedures you write into the master database.

Word to the wise try to use it only for system related procedures.

Saturday, 6 December 2008

Enabling Application Inspection Using the Modular Policy Framework

Cisco ASA provides a modular policy framework to provide the application security or to perform quality of service, and yes that is quite a mouth full to say but MPF for short.

On large environments it can be sometimes be useful to have a policy to inspect the traffic for you to prevent denial of service attacks.

I'm going to walk show you step by step how to do this, I'm going to create one service policy for inspecting for FTP and DNS so you can see how this is done.
First we need and access list to check the traffic.

Access-list udptraffic permit udp any any

Next we need to create a class map – classifies the traffic that will be inspected. Various types of match criteria in a class map can be use to classify traffic. The primary criterion is the use of an access control list.

class-map UDPclass
match access-list udptraffic

Next we are going to create a policy map – configures security or QoS policies. A policy consists of a class command and its associated actions, additionally a policy map can contain multiple policies.

policy-map udppolicy
class UDPclass
inspect ftp
inspect dns maximum-length 512

Lastly you need to add this to a service policy - A service policy can be mapped to global meaning all interfaces or to a single interface directly.

service-policy udppolicy global

Your final result should look like this.

Access-list udptraffic permit udp any any
class-map UDPclass
match access-list udptraffic
policy-map udppolicy
class UDPclass
inspect ftp
inspect dns maximum-length 512
service-policy udppolicy global

In addition to creating these Cisco have included some default class map and policy namely inspection_default and asa_global_fw_policy this can be seen below

class-map inspection_default
match default-inspection-traffic
!
!
policy-map asa_global_fw_policy
class inspection_default
inspect dns maximum-length 512
inspect ftp
inspect h323 h225
inspect h323 ras
inspect netbios
inspect rsh
inspect rtsp
inspect skinny
inspect esmtp
inspect sqlnet
inspect sunrpc
inspect tftp
inspect sip
inspect xdmcp
inspect http
!
service-policy asa_global_fw_policy global


You are welcome to use the default policy and for most of you it will work fine I'm sure but i find that creating my own policy works better for me.