SQL SECURITY CREATOR

Database
Enthusiast

SQL SECURITY CREATOR

I prefer creating procedures using SQL SECURITY CREATOR. My concern is what will happen to the procedure if my user id is either disabled/deactivated/deleted from the DB when I leave my company?
  • procedure
Tags (1)

Accepted Solutions
Senior Supporter

Re: SQL SECURITY CREATOR

Hi,

 

Good call!  No I hadn't and re-testing shows that the user can no longer use the SP.

 

My new test is:

- user1 creates an SP (with 'sql security creator') in db1 which accesses tables in db2

- user2 can call the SP

- if user1 is now dropped, user2 can no longer successfully call the SP (get security error "user2 cannot insert into table in db2").

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
5 REPLIES
Senior Supporter

Re: SQL SECURITY CREATOR

Hi,

 

You should be ok.

 

I've just setup and run a test on (TD15.10):

- create user1

- user1 creates an SP using 'sql security creator'

- user2 can call that procedure (and it works Smiley Happy )

- drop user1

- user2 can call that procedure (and it still works Smiley Happy )

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: SQL SECURITY CREATOR

Thanks Dave for testing it out.

In your test did you ensure that there are tables in the procedure which only user1 has access and user2 does not and/or the db on which the procedure is stored does not have access to those tables with grant option?

Senior Supporter

Re: SQL SECURITY CREATOR

Hi,

 

Good call!  No I hadn't and re-testing shows that the user can no longer use the SP.

 

My new test is:

- user1 creates an SP (with 'sql security creator') in db1 which accesses tables in db2

- user2 can call the SP

- if user1 is now dropped, user2 can no longer successfully call the SP (get security error "user2 cannot insert into table in db2").

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: SQL SECURITY CREATOR

Thank you Dave

Enthusiast

Re: SQL SECURITY CREATOR

Dear Dave,

Can you try this test with SQL SECURITY DEFINER the default one ? I am expecting it to fail and come to the conclusion that only SQL SECURITY OWNER is the safest in terms of user id being dropped.