DENY permission,SQL server: google

Showing posts with label google. Show all posts

Thursday, March 22, 2012

Deleting extended stored procedures in master

Hello
Our Audit group is requiring us to delete many extended stored procedures in
the master database. If you google something to that effect you will see
the list all over the Net of the specific procedures that are recommended
for deletion. You will also see the comment that deleting some of them can
break SQL Server functionality, etc... We deleted all of the
recommendations on a test server and had problems with log shipping, viewing
Agent properties, setting up new users, viewing SQL Server logs, etc. So we
added back xp_regread, xp_availablemedia, xp_getnetname, xp_ntsecenumdomains
and a few more to get those basic functions working again. I sense that
those other registry procedures are used by SQL Server upgrades/service
packs/security patches but we haven't tested that yet.
Has anyone documented what procedure breaks what function? I can see a
whole lotta testing that we'll have to do to put SQL Server through its
paces to see what breaks if we are forced to delete all these procedures.
Documentation on the xps seems a bit sparse from what I can tell.
Ultimately, if they are locked down in master I'm not convinced they should
be deleted. If someone gets as far as master, they could just add them back
anyway. Am I just being naive?
SteveIt's a fairly pointless exercise that leaves you in an unsupported
configuration which should factor into such a decision. The list of extended
procedures with public execute permissions is fairly small and relatively
benign. Plus, if you are running the SQL Service under a non admin domain
account the reach of some that are still public such as xp_regread or
xp_getfiledetails are further curtailed. Do any of your Audit group actually
have any real experience with SQL Server?
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Steve" <pezguy@.mn.rr.com> wrote in message
news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
> Hello
> Our Audit group is requiring us to delete many extended stored procedures
> in the master database. If you google something to that effect you will
> see the list all over the Net of the specific procedures that are
> recommended for deletion. You will also see the comment that deleting
> some of them can break SQL Server functionality, etc... We deleted all
> of the recommendations on a test server and had problems with log
> shipping, viewing Agent properties, setting up new users, viewing SQL
> Server logs, etc. So we added back xp_regread, xp_availablemedia,
> xp_getnetname, xp_ntsecenumdomains and a few more to get those basic
> functions working again. I sense that those other registry procedures are
> used by SQL Server upgrades/service packs/security patches but we haven't
> tested that yet.
> Has anyone documented what procedure breaks what function? I can see a
> whole lotta testing that we'll have to do to put SQL Server through its
> paces to see what breaks if we are forced to delete all these procedures.
> Documentation on the xps seems a bit sparse from what I can tell.
> Ultimately, if they are locked down in master I'm not convinced they
> should be deleted. If someone gets as far as master, they could just add
> them back anyway. Am I just being naive?
> Steve
>|||Do any of the Audit group have real experience with SQL Server? LOL...it
doesn't appear that way from what I can tell. Part of trying to convince
them that this is a silly requirement is to tell them how it breaks SQL
Server. Thanks for your comments Jasper!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
> It's a fairly pointless exercise that leaves you in an unsupported
> configuration which should factor into such a decision. The list of
> extended procedures with public execute permissions is fairly small and
> relatively benign. Plus, if you are running the SQL Service under a non
> admin domain account the reach of some that are still public such as
> xp_regread or xp_getfiledetails are further curtailed. Do any of your
> Audit group actually have any real experience with SQL Server?
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Steve" <pezguy@.mn.rr.com> wrote in message
> news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
>> Hello
>> Our Audit group is requiring us to delete many extended stored procedures
>> in the master database. If you google something to that effect you will
>> see the list all over the Net of the specific procedures that are
>> recommended for deletion. You will also see the comment that deleting
>> some of them can break SQL Server functionality, etc... We deleted all
>> of the recommendations on a test server and had problems with log
>> shipping, viewing Agent properties, setting up new users, viewing SQL
>> Server logs, etc. So we added back xp_regread, xp_availablemedia,
>> xp_getnetname, xp_ntsecenumdomains and a few more to get those basic
>> functions working again. I sense that those other registry procedures
>> are used by SQL Server upgrades/service packs/security patches but we
>> haven't tested that yet.
>> Has anyone documented what procedure breaks what function? I can see a
>> whole lotta testing that we'll have to do to put SQL Server through its
>> paces to see what breaks if we are forced to delete all these procedures.
>> Documentation on the xps seems a bit sparse from what I can tell.
>> Ultimately, if they are locked down in master I'm not convinced they
>> should be deleted. If someone gets as far as master, they could just add
>> them back anyway. Am I just being naive?
>> Steve
>|||I have to deal with similar groups were I work so am glad it's not just us
that have to suffer :-) It's all about risk and I would say the risk of
leaving these extended procs in place is extremely low if you factor in the
fact that PSS would probably be perfectly within their rights to refuse to
support such a configuration (not saying they would but they could) if you
did have a real issue. I would certainly press for a list of exploits that
these expose the server to. Too often security/audit departments lay down
rules with no real knowledge or understanding of the product in question and
without any real word basis for the restrictions they want to impose.
Generally the restrictions simply hamper the teams that have to support the
application rather than alleviate any security risk.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Steve" <pezguy@.mn.rr.com> wrote in message
news:qwOge.7$bn.2@.tornado.rdc-kc.rr.com...
> Do any of the Audit group have real experience with SQL Server? LOL...it
> doesn't appear that way from what I can tell. Part of trying to convince
> them that this is a silly requirement is to tell them how it breaks SQL
> Server. Thanks for your comments Jasper!
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:OAZylwnVFHA.1152@.tk2msftngp13.phx.gbl...
>> It's a fairly pointless exercise that leaves you in an unsupported
>> configuration which should factor into such a decision. The list of
>> extended procedures with public execute permissions is fairly small and
>> relatively benign. Plus, if you are running the SQL Service under a non
>> admin domain account the reach of some that are still public such as
>> xp_regread or xp_getfiledetails are further curtailed. Do any of your
>> Audit group actually have any real experience with SQL Server?
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Steve" <pezguy@.mn.rr.com> wrote in message
>> news:1Vuge.53142$215.38295@.tornado.rdc-kc.rr.com...
>> Hello
>> Our Audit group is requiring us to delete many extended stored
>> procedures in the master database. If you google something to that
>> effect you will see the list all over the Net of the specific procedures
>> that are recommended for deletion. You will also see the comment that
>> deleting some of them can break SQL Server functionality, etc... We
>> deleted all of the recommendations on a test server and had problems
>> with log shipping, viewing Agent properties, setting up new users,
>> viewing SQL Server logs, etc. So we added back xp_regread,
>> xp_availablemedia, xp_getnetname, xp_ntsecenumdomains and a few more to
>> get those basic functions working again. I sense that those other
>> registry procedures are used by SQL Server upgrades/service
>> packs/security patches but we haven't tested that yet.
>> Has anyone documented what procedure breaks what function? I can see a
>> whole lotta testing that we'll have to do to put SQL Server through its
>> paces to see what breaks if we are forced to delete all these
>> procedures. Documentation on the xps seems a bit sparse from what I can
>> tell.
>> Ultimately, if they are locked down in master I'm not convinced they
>> should be deleted. If someone gets as far as master, they could just
>> add them back anyway. Am I just being naive?
>> Steve
>>
>

Saturday, February 25, 2012

Delete trigger in SQL Server 7

Have gone through BOL and Google, but can't find the answer... please help
with a simple Q. I'm trying to create a simple cascade delete trigger in SQL
Server 7 where deleting "parent" records in table X delete corresponding
child records in table Y.

Table X
=========
X_ID
SOME_VAL

Table Y
=========
Y_ID
X_ID
SOME_VAL

When there is no relationship between X.X_ID and Y.X_ID, the following
trigger works fine:

CREATE TRIGGER "temp" ON x
FOR DELETE
AS

delete
from y
where x_id in (select x_id from deleted)

However, when a relationship is created to enforce referential integrity,
the trigger fails, with a "DELETE statement conflicted with COLUMN REFERENCE
constraint" error. I've seen examples where the trigger says (for example)
"AFTER INSERT", where presumably the code is specifically run after the
event that triggers it -- is there a way of forcing the trigger to run
before the delete from table X is executed? I've tried using "BEFORE
DELETE", but no dice :-\

Thanks!

--
Aidan Whitehall <aidanwhitehall@.fairbanks.co.uk>
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd +44 (0)1695 51775Itzik Ben-Gan and Thomas Moreau have some examples of cascading RI triggers
in their white paper:
<http://www.msdn.microsoft.com/libra...ry/en-us/dnsql2
k/html/sql_refintegrity.asp?frame=true>.

The main consideration is that you cannot have declarative referential
integrity in place when you perform cascading actions in triggers in SQL 7,
..

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Aidan Whitehall" <aidanwhitehall@.fairbanks.co.uk> wrote in message
news:btph6b$kri$1@.sparta.btinternet.com...
> Have gone through BOL and Google, but can't find the answer... please help
> with a simple Q. I'm trying to create a simple cascade delete trigger in
SQL
> Server 7 where deleting "parent" records in table X delete corresponding
> child records in table Y.
> Table X
> =========
> X_ID
> SOME_VAL
> Table Y
> =========
> Y_ID
> X_ID
> SOME_VAL
>
> When there is no relationship between X.X_ID and Y.X_ID, the following
> trigger works fine:
> CREATE TRIGGER "temp" ON x
> FOR DELETE
> AS
> delete
> from y
> where x_id in (select x_id from deleted)
> However, when a relationship is created to enforce referential integrity,
> the trigger fails, with a "DELETE statement conflicted with COLUMN
REFERENCE
> constraint" error. I've seen examples where the trigger says (for example)
> "AFTER INSERT", where presumably the code is specifically run after the
> event that triggers it -- is there a way of forcing the trigger to run
> before the delete from table X is executed? I've tried using "BEFORE
> DELETE", but no dice :-\
>
> Thanks!
> --
> Aidan Whitehall <aidanwhitehall@.fairbanks.co.uk>
> Macromedia ColdFusion Developer
> Fairbanks Environmental Ltd +44 (0)1695 51775|||> Itzik Ben-Gan and Thomas Moreau have some examples of cascading RI
triggers
> in their white paper:
<http://www.msdn.microsoft.com/libra...ry/en-us/dnsql2
> k/html/sql_refintegrity.asp?frame=true>.

K, thanks -- I'll check that out.

> The main consideration is that you cannot have declarative referential
> integrity in place when you perform cascading actions in triggers in SQL
7,

Damn, damn, damn.

Well, thanks for the clarification, anyway.

Regards

--
Aidan Whitehall <aidanwhitehall@.fairbanks.co.uk>
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd +44 (0)1695 51775|||Aidan Whitehall wrote:

>>The main consideration is that you cannot have declarative referential
>>integrity in place when you perform cascading actions in triggers in SQL
> 7,
> Damn, damn, damn.
> Well, thanks for the clarification, anyway.

If I may request it ... would someone please confirm the above statement
about SQL Server. This statement is untrue is other commercial RDBMS
products, such as Oracle and DB2, and I would be surprised if SQL Server
didn't allow this very basic functionality. Is is still true in the
current version?

Thanks.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||> If I may request it ... would someone please confirm the above statement
> about SQL Server. This statement is untrue is other commercial RDBMS
> products, such as Oracle and DB2, and I would be surprised if SQL Server
> didn't allow this very basic functionality. Is is still true in the
> current version?

One can implement cascading actions via DRI or INSTEAD OF triggers in
current version of SQL Server (SQL Server 2000).

Unfortunately, Aidan is using an older version (SQL 7) in which only the
AFTER trigger model is available. Consequently, it is necessary to enforce
referential integrity in triggers instead of DRI in cases when cascading
actions are performed in triggers. Another alternative is to perform
cascading functions via stored procedures.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Daniel Morgan" <damorgan@.x.washington.edu> wrote in message
news:1073846407.832632@.yasure...
> Aidan Whitehall wrote:
> >>The main consideration is that you cannot have declarative referential
> >>integrity in place when you perform cascading actions in triggers in SQL
> > 7,
> > Damn, damn, damn.
> > Well, thanks for the clarification, anyway.
> If I may request it ... would someone please confirm the above statement
> about SQL Server. This statement is untrue is other commercial RDBMS
> products, such as Oracle and DB2, and I would be surprised if SQL Server
> didn't allow this very basic functionality. Is is still true in the
> current version?
> Thanks.
> --
> Daniel Morgan
> http://www.outreach.washington.edu/...oad/oad_crs.asp
> http://www.outreach.washington.edu/...aoa/aoa_crs.asp
> damorgan@.x.washington.edu
> (replace 'x' with a 'u' to reply)|||Dan Guzman wrote:

>>If I may request it ... would someone please confirm the above statement
>>about SQL Server. This statement is untrue is other commercial RDBMS
>>products, such as Oracle and DB2, and I would be surprised if SQL Server
>>didn't allow this very basic functionality. Is is still true in the
>>current version?
>
> One can implement cascading actions via DRI or INSTEAD OF triggers in
> current version of SQL Server (SQL Server 2000).
> Unfortunately, Aidan is using an older version (SQL 7) in which only the
> AFTER trigger model is available. Consequently, it is necessary to enforce
> referential integrity in triggers instead of DRI in cases when cascading
> actions are performed in triggers. Another alternative is to perform
> cascading functions via stored procedures.

Thanks. I was a bit surprised that such basic functionality might not
exist. In Oracle one can always perform cascading deletes in any code
but the referential constraint, itself, will perform the function. For
example:

ALTER TABLE child
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (test)
REFERENCES parent (test)
ON DELETE CASCADE;

Thanks again for the clarification.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||> Thanks. I was a bit surprised that such basic functionality might not
> exist. In Oracle one can always perform cascading deletes in any code
> but the referential constraint, itself, will perform the function. For
> example:
> ALTER TABLE child
> ADD CONSTRAINT fk_child_parent
> FOREIGN KEY (test)
> REFERENCES parent (test)
> ON DELETE CASCADE;

The same syntax works in SQL 2000. Thank goodness for ANSI standards ;-)

BEGIN TRAN

CREATE TABLE parent(test int NOT NULL PRIMARY KEY)
CREATE TABLE child(test int NOT NULL PRIMARY KEY)

ALTER TABLE child
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (test)
REFERENCES parent (test)
ON DELETE CASCADE;

insert into parent values(1)
insert into child values(1)

delete from parent
select * from parent --no rows
select * from child --no rows

ROLLBACK

--
Hope this helps.

Dan Guzman
SQL Server MVP|||Dan Guzman wrote:

>>Thanks. I was a bit surprised that such basic functionality might not
>>exist. In Oracle one can always perform cascading deletes in any code
>>but the referential constraint, itself, will perform the function. For
>>example:
>>
>>ALTER TABLE child
>>ADD CONSTRAINT fk_child_parent
>>FOREIGN KEY (test)
>>REFERENCES parent (test)
>>ON DELETE CASCADE;
>
> The same syntax works in SQL 2000. Thank goodness for ANSI standards ;-)
> BEGIN TRAN
> CREATE TABLE parent(test int NOT NULL PRIMARY KEY)
> CREATE TABLE child(test int NOT NULL PRIMARY KEY)
> ALTER TABLE child
> ADD CONSTRAINT fk_child_parent
> FOREIGN KEY (test)
> REFERENCES parent (test)
> ON DELETE CASCADE;
> insert into parent values(1)
> insert into child values(1)
> delete from parent
> select * from parent --no rows
> select * from child --no rows
> ROLLBACK

Excellent. Thanks.

Like I said ... I would have been quite surprised if it didn't exist.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)

Thursday, March 22, 2012

Deleting extended stored procedures in master

Deleting extended stored procedures in master

Deleting extended stored procedures in master

Saturday, February 25, 2012

Delete trigger in SQL Server 7

DENY permission,SQL server

Blog Archive

About Me