Permission question

Question

Permission question

ajjee

SSC Rookie

Points: 25
More actions
June 3, 2010 at 7:29 am

#235894

Hi,
I have the following scenario that i need help with
I have a Login 'Smith' which is mapped to the user 'smith' and is member of the 'Public' role.
and i have 4 tables in the DB TableA,TableB, TableC, TableD
I want the login 'Smith' to be able to Ins/upd/Del/Sel on TableA but only Sel permission on All the rest of the Tables(TableB, TableC, TableD).
So far i have done the following:
GRANT SELECT,insert,update,delete ON TableA to Smith
DENY update,delete,insert ON TableB to Smith
DENY update,delete,insert ON TableC to Smith
DENY update,delete,insert ON TableD to Smith
GRANT SELECT ONON TableB to Smith
GRANT SELECT ONON TableC to Smith
GRANT SELECT ON ON TableD to Smith
The problem:
the above permission works fine as long as i type the t-sql
when i tried to run a stored proc it asks for Execute permission for the user smith on the sp. After granting the exec permission on sp to the login 'Smith'.
When i run the stored proc with logic to updates tableB,tableC or TableD
it is still updating the DB which i don't want
the question is how to prevent that i don't want to grant permission to every single SP and function also i don't want to use roles.
Thanks for a quick response.
Regards,
Ajjee

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323743 More actions · Answer 1

permissions are not cross checked the way you are thinking.

this is working by design...an example might be you do not want "smith" to touch the tables directly, and only thru authorized stored procedures.

once you grant execute permissions to the procedure, whatever the procedure does internally , whether insert,update or delete, it will be performed without checking further whether the calling user has permissions to the objects inside/used by the stored procedure.

if he has EXECUTE permissions to the proc, the proc will run and do it's thing.

it's very typical that an end user has no access to the tables themselves, and onyl acces to views and stored procs.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323743 More actions · Answer 2

also using roles is the recommended best practice for assigning permissions. If you do not want to use them, it's more likely you are not familiar with how useful they are.

My shop has a rule that says no permissions may be assigned to an individual user, just to help reinforce the fact that it is a best practice.

create a role with the right permissions, and add your user "smith" to the role. it's the right way to do it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ajjee SSC Rookie Points: 25 More actions · Answer 3

Thanks for your quick response,

but thats not good whats the point of giving the option to put permission on the table when its not going to work? nobody is going to change the permission on the tables when it doesnt achive any thing

can you suggest any other solution to my problem?

Regards,

Ajjee

Lowell SSC Guru Points: 323743 More actions · Answer 4

denying UPDATE at the table level prevents "smith" from directly updating your table with the statement UPDATE TableB SET somevalue = ''

if smith should not update tableB, he should not have EXECUTE permission to your procedure. that goes back to having the correct roles...

if smith should be able to only select from certaint ables, and never update, creating a role that gives select to 4 tables(out of hundreds), with no rights to procedures is probably what you want.

another role, which might have "jane" as a member might have the permissions to execute procedures, as it's part of her positions typical functions.

quite often, access to the actual procedure is handled by an application, and a further set of permissions, to determine if the user has access to

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ajjee SSC Rookie Points: 25 More actions · Answer 5

Thanks again, your post is making sense to me now

do i have to create two roles how to i handle the ability to be able to update the TableA freely.?

Regards,

Ajjee

ajjee SSC Rookie Points: 25 More actions · Answer 6

Hi,

Sorry i Still can't figure this one out can someone please suggest how should i use the roles to achive the above.

Thanks..

Ajjee

Lowell SSC Guru Points: 323743 More actions · Answer 7

you did not really provide any definitive requirements; your procedure is unnamed and your fourtables are pseudo names(TABLEA,etc)

here is some pseudo code that is the basics of what i think you asked for.

I'm assuming there are a lot more tables int eh database, and you want two groups to have very limited access to four of those tables. because of that, you have to list all the tables you want to

grant/deny access to.

CREATE ROLE [OneTableAccess]

--give reader rights for four specific tables to this group

GRANT SELECT,INSERT,UPDATE,DELETE ON TableA to [OneTableAccess]

GRANT SELECT ON TableB to [OneTableAccess]

GRANT SELECT ON TableC to [OneTableAccess]

GRANT SELECT ON TableD to [OneTableAccess]

DENY UPDATE,DELETE,INSERT ON TABLEB TO [OneTableAccess]

DENY UPDATE,DELETE,INSERT ON TABLEC TO [OneTableAccess]

DENY UPDATE,DELETE,INSERT ON TABLED TO [OneTableAccess]

DENY EXECUTE ON SomeProcedure TO [OneTableAccess]

CREATE ROLE [FourTableAccess]

GRANT SELECT,INSERT,UPDATE,DELETE ON TableA to [FourTableAccess]

GRANT SELECT,INSERT,UPDATE,DELETE ON TableB to [FourTableAccess]

GRANT SELECT,INSERT,UPDATE,DELETE ON TableC to [FourTableAccess]

GRANT SELECT,INSERT,UPDATE,DELETE ON TableD to [FourTableAccess]

GRANT EXECUTE ON SomeProcedure TO [OneTableAccess]

EXEC sp_addrolemember N'OneTableAccess', N'smith'

EXEC sp_addrolemember N'FourTableAccess', N'jane'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ajjee SSC Rookie Points: 25 More actions · Answer 8

Thanks verymuch for your help , Appologies for not providing you with full details your solution still doesn't help because

1-

I would have to set permission on every single storedproc by doing:

DENY EXECUTE ON SomeProcedureNo1 TO [OneTableAccess]

to.......

DENY EXECUTE ON SomeProcedureNo100 TO [OneTableAccess]

2-I want the user "Smith" to be able to Select the data and some storedproc in our case only return the selected data which fine for Smith to see. it will be a mission to go through 1000 sp to see if its is just a select based SP or is it modifying the data.

3- is it not true that in your approch the user Smith will still be able to create a New storeproc to modify the data by default the new SP written by user smith will not part to the role OneTalbeAccess

Iam sorry for being a pain but i am trying to look for a solution to this problem for a two days now and the pressure at work is building up

Many thanks in advance

Ajjee... 🙁