| Author |
Topic |
|
aclarke
Posting Yak Master
133 Posts |
 Posted - 2003-08-22 : 20:17:03
|
| I can't figure out how to completely deny a user or role access to a database. If I do "deny all on t_customer to public" that denies public access to my t_customer table but I can't do a generic deny/grant/revoke. I am logged into the Query Analyzer as sa. Any ideas?Thanks,- Andrew Clarke. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-22 : 20:44:36
|
| You can deny access to a database user with sp_revokedbaccess. That's the most effective method. Books Online has the syntax.If you need to deny a LOGIN from accessing the server, look at sp_denylogin and sp_revokelogin. |
 |
|
|
aclarke
Posting Yak Master
133 Posts |
Posted - 2003-08-22 : 20:56:14
|
| Thanks for your response. My problem is that I've been using public for about 5 databases on my test server, and then adding my user to public and it's all good. Now, however, I need to add another database and create a new user and make that user ONLY able to access that 1 database and nothing else on the server. So, as far as I know I need to revoke public access from all my databases, and add access to all my databases from my main user that I'm already using as a login from my applications. Then I need to give this new user access to their new database.So it would be an extremely tedious process going through and manually revoking public access to each object in each database, and there has to be an easier way than using database metadata to loop through the objects and do this programatically...- Andrew. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-22 : 21:30:54
|
| Public is a database role, not a server role. You simply create a new LOGIN and allow that login to access only the one database. This is why each user should have their own login, or you should use Windows authentication. Sharing logins is the easiest and fastest way to create security headaches like this one. |
|
|
|
aclarke
Posting Yak Master
133 Posts |
Posted - 2003-08-22 : 21:51:28
|
| OK but that new user is a member of public, which has access to all the databases. That's the issue.The databases are accessed via JDBC. The program that accesses them has its own user, as do the 4 people who have rights to access the databases through Enterprise Manager. Because so far there have been very few people needing access, and everybody has had the same access rights for the last several years, I haven't had to worry about the bad initial security design until now!Is there a way to remove "public" access from each of the databases besides going through object by object on each database?Thanks again,- Andrew. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-24 : 10:29:21
|
quote:
OK but that new user is a member of public, which has access to all the databases. That's the issue.
No. They are NOT a member of public unless you grant their login access to the database. If their login is mapped to a user account in that database, go to the Users section in EM and drop them from the DB, or run sp_dropuser. You cannot remove public, it is a fixed database role.I don't know about how your program logs in, but if you can rewrite it to use separate logins for each person you'll avoid this problem. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-25 : 12:25:27
|
| Aclarke, why are you using this approach for security? In another thread, you are very concerned about the data in your SQL Server so you've got encryption going and other things, but you are using poor security inside SQL Server. Why?Tara |
|
|
|
aclarke
Posting Yak Master
133 Posts |
Posted - 2003-08-26 : 16:26:55
|
Well I feel like a real frickin' idiot...  My databases had GUEST ACCESS!!!!!! So it wasn't public that was giving me issues but the guest access. Once that was removed, everything started behaving as I expected it to.Tara, I'm not sure specifically what you are referring to with regards to my "poor security inside SQL Server". Obviously having guest access is despicably horrible "security", but what were you talking about? Basically I'm asking these questions in these threads because I'm trying to get the security right. I figure I deserve some slack as at least I'm trying to proactively fix all these issues!  |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-26 : 17:34:27
|
| I didn't mean to offend, I was just wondering why you were doing security the way you are. Instead of using public, you should create a role or roles and then assign the accounts to the roles. Then you grant permissions on the roles. Public shouldn't be given any access except maybe db_datareader.Tara |
|
|
|
aclarke
Posting Yak Master
133 Posts |
Posted - 2003-08-26 : 17:45:04
|
quote:
Originally posted by tduggan
I didn't mean to offend, I was just wondering why you were doing security the way you are. Instead of using public, you should create a role or roles and then assign the accounts to the roles. Then you grant permissions on the roles. Public shouldn't be given any access except maybe db_datareader.Tara
You didn't offend me at all - I just wanted to know what you thought I should be doing better. Each of the 4 people and 1 program that had access to the database server had their own login, but they were each a member of Public. It wasn't until now that any of the logins actually needed different security from any of the other ones, or from the public role. And as it turns out like I said in my last post that public wasn't the culprit anyway, but guest was. Now how guest access got onto some of these databases is another question...Thanks,- Andrew. |
|
|
|
|
Why doesn't "deny all to public" work?