June 26, 2015 at 5:43 am
Hi Guys,
If I granted execute right to Schema1 for sp MyProc;
And MyProc updates something in Schema2
Will it work? Or will it fail as its a different Schema?
Cheers
Alex
June 26, 2015 at 2:27 pm
alex.sqldba (6/26/2015)
Hi Guys,If I granted execute right to Schema1 for sp MyProc;
And MyProc updates something in Schema2
Will it work? Or will it fail as its a different Schema?
Cheers
Alex
Time for me to learn something about SQL Server security. My knowledge on that topic is limited. Why would you grant something like EXECUTE to a schema, instead of a user, a login, or a group ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 26, 2015 at 2:39 pm
alex.sqldba (6/26/2015)
Hi Guys,If I granted execute right to Schema1 for sp MyProc;
And MyProc updates something in Schema2
Will it work? Or will it fail as its a different Schema?
Cheers
Alex
If the user has permission to execute the procedure then it is assumed that anything that procedure does is acceptable. Procedure calls do NOT check permissions on every object being referenced internally. That would be a huge performance waste of time.
What I don't quite understand is your first comment. It sounds like you tried to grant execute rights on a schema to a procedure. That just doesn't make sense but I assume it was written a little strangely.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2015 at 2:40 pm
sgmunson (6/26/2015)
alex.sqldba (6/26/2015)
Hi Guys,If I granted execute right to Schema1 for sp MyProc;
And MyProc updates something in Schema2
Will it work? Or will it fail as its a different Schema?
Cheers
Alex
Time for me to learn something about SQL Server security. My knowledge on that topic is limited. Why would you grant something like EXECUTE to a schema, instead of a user, a login, or a group ?
Users in that schema will inherit the right to execute the stored procedure, no need to grant execute rights to the users as new procedures are created.
June 26, 2015 at 2:40 pm
no, it will not work.
database ownership changing breaks if the object called(ie your dbo.procedure) references another schema or another database.
in that case, the caller needs permissions to the objects involved in the underlying procedure involved as well.
Msg 229, Level 14, State 5, Procedure myPROC, Line 2
The SELECT permission was denied on the object 'table2', database 'WHATEVER', schema 'BETA'.
here's a full setup example
--create a sample database
CREATE DATABASE WHATEVER
GO
USE WHATEVER
GO
CREATE USER [userA] WITHOUT LOGIN
CREATE USER [userB] WITHOUT LOGIN
CREATE USER [userC] WITHOUT LOGIN
GO
CREATE SCHEMA ALPHA
GO
CREATE SCHEMA BETA
GO
--in order for these userA and userB to create anything, they need rights
ALTER AUTHORIZATION ON SCHEMA ::[ALPHA] TO [userA]
ALTER AUTHORIZATION ON SCHEMA ::[BETA] TO [userB]
--create the objects now
CREATE TABLE [ALPHA].table1 (
tid int identity(1,1) not null primary key,
sometext varchar(30) )
insert into [ALPHA].table1
SELECT 'ONE' UNION ALL SELECT 'TWO'
CREATE TABLE [BETA].table2 (
tid int identity(1,1) not null primary key,
sometext varchar(30) )
insert into [BETA].table2
SELECT 'THREE' UNION ALL SELECT 'FOUR'
GO
CREATE PROCEDURE [dbo].myPROC AS
SELECT *
FROM [ALPHA].table1 t1
LEFT OUTER JOIN [BETA].table2 t2
ON t1.tid = t2.tid
GO
GRANT EXECUTE ON [dbo].myPROC TO [userA]
GRANT EXECUTE ON [dbo].myPROC TO [userC]
GO
execute as user='userA'
execute myPROC
revert
/*cleanup
drop database WHATEVER
drop login userA
drop login userB
drop login userC
*/
Lowell
June 29, 2015 at 10:54 am
Hi All,
Thanks for your help with this, your examples made me realise I am going about this all wrong anyway! Which in itself is not a bad lesson.
I'll start another thread for advice on what I need to achieve, as I think it will end up off topic in here and not easily found by someone else.
Cheers All,
See you in another thread.
Alex
July 4, 2015 at 10:08 pm
Sean Lange (6/26/2015)
alex.sqldba (6/26/2015)
Hi Guys,If I granted execute right to Schema1 for sp MyProc;
And MyProc updates something in Schema2
Will it work? Or will it fail as its a different Schema?
Cheers
Alex
If the user has permission to execute the procedure then it is assumed that anything that procedure does is acceptable. Procedure calls do NOT check permissions on every object being referenced internally. That would be a huge performance waste of time.
That only holds up when the referenced objects have the same owner as the called object *.
If Schema1 and Schema2 have the same owner then it will work fine * since permission checks would be bypassed but if the schemas have different owners then permissions are again checked and the caller must have explicit permissions on called object. A new ownership chain can again be started in that scenario.
To the OP, this would let you move across schemas no problem maintaining your ownership chain in the process, however this relegates the scheam to a classification container and not much of a security container any longer:
CREATE SCHEMA Schema1 AUTHORIZATION dbo;
CREATE SCHEMA Schema2 AUTHORIZATION dbo;
* Note that an object's owner can be changed to something other than the owner of the object's schema for additional security options (and confusion).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 6, 2015 at 12:33 am
OR you could have the EXECUTE AS SYNTAX within the procedure, the main thing is all procedures are not created alike, so each procedures and its execute permission needs to be clealy understood , if for any reason your procedures use dynamic sql then you need to be extra careful with the permissions since most system can be exploited via this loop hole.
Permissions need to be assigned from the lowest level up
start with the procedure , then map the procedure to a schema other than dbo, bind schema to users, add application roles where needed, map users to login using windows authentication as far as possible , make sure each application uses its own login and not shared across the board. Make sure the service account for sql i not under a prevliaged account.
For very sensitive information considering hashing or encryption as needed.
For very sensitive database consider a dedicated server with TDE enabled.
July 9, 2015 at 2:28 am
Jayanth_Kurup (7/6/2015)
OR you could have the EXECUTE AS SYNTAX within the procedure, the main thing is all procedures are not created alike, so each procedures and its execute permission needs to be clealy understood ,
Using EXECUTE AS does not have to be an either/OR with other features. You can employ the feature in a stored procedure and an ownership chain will begin within that procedure where the security context specified in the AS clause takes the place of the caller.
if for any reason your procedures use dynamic sql then you need to be extra careful with the permissions since most system can be exploited via this loop hole.
Dynamic SQL is important to consider for permissions because ownership chains do not transfer to the called SQL statement but SQL injection is a whole other topic. As long as you're using sp_executesql and parameterizing the statement then you'll be protected.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply