| Author |
Topic |
|
Bambola
Posting Yak Master
103 Posts |
 Posted - 2003-01-28 : 08:44:02
|
| Hi, Is it possible to give a user ONLY the possibility to execute one stored procedure on a certain database and nothing more?we created a user, gave him permission to that sproc, and denied all the rest and so far so good.Problem is that if I connect to server with that user I can still "see" all objects under master msdb and tempdb. Is there a way to prevent that without having to add the user to those databases? Thanks!Bambola. |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2003-01-28 : 08:48:49
|
I don't think you can prevent it ... enterprise manager (em) shouldn't be your main route of accessing the database for that user you should create a WSH script if it needs to be scheduled or if you are using a web application it shouldn't be an issue either (I would suggest looking in to having ASPNET account either synchronized between web server and db server or having a specific domain account if the machines are on a domain) other than that ... i'm not sure if you revoke permissions in the other database it won't show the information ... you could always give it a try and let us know  |
 |
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-01-28 : 09:05:20
|
| I am not sure I am following you.Currently if I am connecting to our server with user 'foo', I can see all objects of the listed databases.The only way I found to prevent it, was to add 'foo' to those databases and DENY sysobjects TO foo. But it's a dirty solution and I don't want to use it. Any alternatives?Bambola. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2003-01-28 : 09:15:47
|
| I would use an access data project (access 2000) or better connect it to the database in question make sure it connects using nt security then provide 1 for every table or sp returning data the user needs to see or modify. Next I would set a main form to load at startup options that allows navigation to all the other forms. (important step)Next save a copy close the original and open copy. Reason is because when you take an adp and you make an ade it also makes the adp and ade and you can't go back.In the copy go to startup again and uncheck all menus, toolbars, database view etc...make sure you check under advanced too.Next go to database tools/options and save the adp as and ade.Now you have a quick interface and they can only see what you allow them to see.make sure you delete the adp copy since it's now useless.Edited by - ValterBorges on 01/28/2003 09:17:37 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-28 : 12:16:50
|
| Why is seeing the objects a problem? Just because the user can see an object doesn't mean that the user can do anything to the object. A lot of people are trying to get this changed in future releases of SQL Server, so if you want it too, send an e-mail to MS at sqlwish@microsoft.com. |
|
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-01-29 : 06:21:15
|
quote:
Why is seeing the objects a problem? Just because the user can see an object doesn't mean that the user can do anything to the object. A lot of people are trying to get this changed in future releases of SQL Server, so if you want it too, send an e-mail to MS at sqlwish@microsoft.com.
with "seeing" objects I mean seeing code of sprocs, table content etc.Bambola. |
|
|
|
srf
Starting Member
42 Posts |
Posted - 2003-01-30 : 15:38:05
|
| So do sp_helprotect on sysobjects and sp_helptext. "public" has select and execute permissions respectively on my servers, which I assume is the default of SQL Server. Just remove those permissions, though I don't know what that'll end up breaking and if it's even supported. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-30 : 16:30:10
|
quote:
with "seeing" objects I mean seeing code of sprocs, table content etc.
Well I just created a user on my machine and just granted it public access on one database. This user could see all objects in that database and could see that other databases exist. But the user could not see any table names, views, stored procedures, etc. from another user database. It could, however, see tables (and other objects) from master and other system databases. But I don't understand why this is a problem. So what if a user can see data in master (or any other non-user database), it doesn't contain any sensitive or confidential information. |
|
|
|
Bambola
Posting Yak Master
103 Posts |
Posted - 2003-01-31 : 07:55:58
|
| I am not hiding anything special under master database. Yes, there are a few general sprocs there that took me a some time to write, but it's not really that. I just cannot understand the logic behind this:You create a user because you want him to have only some permissions [In this case user is suppose to be able to execute only *one* sproc under database x], and by default this user can 'select * from any_table_under_master'??? Sure, if I add this user to master database and DENY select on sysobjects, he won't be able to do a thing. But doesn't it seem strange to you guys that this is the only way to prevent it? I don't want to add any user to master that has nothing to do there to begin with. Bambola.Edited by - bambola on 01/31/2003 08:15:07Edited by - bambola on 01/31/2003 08:16:24 |
|
|
|
srf
Starting Member
42 Posts |
Posted - 2003-01-31 : 13:33:38
|
| Logins have access to a finite list of system tables in Master by default. I created a test user table and public did not have select access by default. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-31 : 17:34:57
|
| Well that's my point. If you create a user, it needs to have access to system tables. But there isn't any data in there that would need to be protected. However, if you have create any user objects in the master database (please say that you didn't because no user objects should be located in the master database), this user will not have any privileges to that table unless you have granted the privileges to the user or to public. |
|
|
|
|
Pemissions to only execute a sproc.