Chain of Stored Procedure Security

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Chain of Stored Procedure Security

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-02 : 11:03:34

John writes "Hi, we have a role setup on out SQL SERVER 7.0 database. For each stored procedure I created, I gave EXEC permission of the group role to this group setup.

A user in this group tried to run the following procedure(this is just a partial view of it).

CREATE PROC dbo.USP_PREPARE_BILLING_TRENDS

@MONTH INTEGER,
@YEAR INTEGER,
@STARTRANGE CHAR(6),
@ENDRANGE CHAR(6),
@HOLDCO CHAR(5),
@CLIENT CHAR(4),
@HOLDCOVAR CHAR(1)

AS

EXEC USP_DTS_DELETE_ALL_RECORDS 'BILLING_TRENDS_TEMP'

So, here we have a stored procedure that calls another
stored procedure. When the user runs's this, an error occurs indicating that he does not have permission to delete the 'BILLING_TRENDS_TEMP' table object.

No table level permissions is set for the tables in the database, just EXEC access on the stored procedures. Does that not give the user access to do anything that the stored procedure does?

The same user runs the following stored procedure:

CREATE PROC dbo.USP_TAX_DELETE_SERVICES_TYPE

@SERVICE CHAR(5)
AS

DELETE SERVICES_TYPE
WHERE SERVICE = @SERVICE

The user successfully executes this command without error, and again EXEC access to the stored procedure is present, and no table permissions are present. Also present are INSERT and UPDATE procedures on the 'SERVICES_TYPE' table.

Is the SQL SERVER getting lost with the first procedure above procedure that is calling another procedure('USP_DTS_DELETE_ALL_RECORDS') here and the chain of security broken?

Any help would be appreciated. "

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-07-02 : 19:33:38

Do either of these sprocs contain Dynamic SQL? If so, then you have to have at least SELECT permissions on the tables for them to run.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-03 : 07:54:32

Theoretically a stored procedure will inherit permissions from it's owner. Anyone given permission on the SP will be able to access anything owned by the SP owner in that database (actually I think it is the SP creator). If the SP tries to access objects owned by another user then the chain of ownership is broken and user permissions will be checked.
I don't believe this is always the case though so it is always worth checking. If all objects are owned by dbo and permissions are controlled by SP access then this is never a problem.

In your case check the owner of the second stored proc called - if this is a different user then permissions will be checked. Also check if the SP owner has permission on the table.
Are you deleting from the table or trying to drop the table (I hope the former).

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources