Solved

deny all objects but a few, in small code

Posted on 2010-08-25
17
438 Views
Last Modified: 2012-05-10
without 'deny'ing the whole DB one line at a time, is there a command to deny the whole DB objects and all, but except a handful, to a user?

thanks
0
Comment
Question by:anushahanna
17 Comments
 
LVL 11

Assisted Solution

by:Larissa T
Larissa T earned 50 total points
Comment Utility
You can use grant command to allow users only some actions on some object.
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
by default, all users are part of public role.

let's say there are some objects that have already been assigned to public role - select or executes.

so the new user is going to get the selects and executes also.

So to avoid it- hence the thought to block them from all objects, even if they have been assigned to public, and then start from scratch.
0
 
LVL 16

Assisted Solution

by:vdr1620
vdr1620 earned 50 total points
Comment Utility
in order to do assign roles to a group of users performing same functions , I would suggest you Add User Defined Database Role using below SQL ..

sp_addrole [@rolename =] 'role' [,[@ownername =] 'owner']
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
vdr1620, the goal is before, being able to add new roles, we need to deny to all objects with public permissions, at a minimum, so we can really be sure that the user has access to only the roles to be added.
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
Have you thought about removing PUBLIC from having access to any of the objects?  
Or are you saying that, now that you have the database all designed and created and everything is working, you want to apply that thought after the fact, i.e. Public has access to everything and now you want to remove it?
Also, which version of SQL Server?
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
>>that thought after the fact, i.e. Public has access to everything and now you want to remove it?

exactly!

2005.
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
Well, the Good News is that you are on SS2005.
Okay, how about this?
  1. Script out the permissions you want to grant to the GuestUser.
  2. Above that, script out the steps for dropping the GuestUser and then for creating the Guest User, in that order.
What that should accomplish is the removal of the GuestUser (and, consequently, all of the Guest User's permissions on everything) and then the recreation of the Guest User and subsequently the assignment of the permissions you want that user to have.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 6

Author Comment

by:anushahanna
Comment Utility
>>then for creating the Guest User, in that order.

the new user is going to be borm with permissions to all 'public' objects, not?

in the below code, I would wish the new user will be denied to *all* public objects, which may be many...


create table testtable1 (testcol1 smallint)

insert into testtable1 select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7

create table testtable2 (testcol2 smallint)

insert into testtable2 select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7



CREATE LOGIN testlogin WITH PASSWORD=N'testpw'

CREATE USER testuser FOR LOGIN testlogin



grant select on testtable1 to public

grant select on testtable2 to testuser



select count(*) from testtable1

select count(*) from testtable2

EXECUTE AS USER = 'testuser';

select count(*) from testtable1

select count(*) from testtable2

REVERT;



revoke select on testtable1 to public

revoke select on testtable2 to testuser

EXECUTE AS USER = 'testuser';

select count(*) from testtable1

select count(*) from testtable2

REVERT;

drop user testuser

drop login testlogin

drop table testtable1

drop table testtable2

Open in new window

0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
I'm not sure what your exercies is meant to prove.  You don't show any results of the queries and, quite frankly, I don't have time to copy the code and run it on a database just to find out what you have already found out. :-/
I was initially thinking of the GuestUser . . . sorry about that.
Essentially, you need to revoke/deny privileges on everything to the public role/user.  So what you need to do is to create a stored proc or an SSIS package that will insert the results of the execution each of the following queries into a table.  Once that is done, then you need to execute the SQL statement from each of the rows in that table.
The queries will build the Deny and Revoke statements needed to deny/revoke the privileges that the pulic user/role has.
You might be able to do it wiht SQL_3.

SQL_1:



SELECT 'DENY SELECT ON [sys].' + object_name(major_id) + ' to [public]'

FROM   sys.database_permissions

WHERE  grantee_principal_id=database_principal_id('public');



SQL_2:



SELECT 'revoke all on ' + QUOTENAME(name) + ' from [public]'

FROM   sysobjects

WHERE  objectproperty(id, 'IsMSShipped')=1;



SQL_3:



DENY ALL TO public;

Open in new window

0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
in the example in 33599329, i just showed that give public role to an objects gives permissions to any new user, and we want to avoid it for one new user.

thanks
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
8080_Diver, i think i mis-communicated.

if we do DENY ALL TO public then we essentially will block out all users who are supposed to have permissions to the public objects. the task at hand is to only block out the new user from accessing any of the public objects.
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 400 total points
Comment Utility
Anushahanna,
Yes, you definitely did miscommunicate . . . or, at least, we all seem to have misunderstood. ;-)
Okay, so you are allowing some users to have public access but you want to block public access for new users.  Right?
Why not go a slightly different route and:
  1. Create a group (e.g. OpenAccess) into which you place all of the current users who have access to the public objects;
  2. Deny All access to public;
  3. Then, when you create the new user, you just don't put them in that OpenAccess group at first.  
That way, you have full control over when the new user has access to things and what things the new user can access.  You also don't impact the existing users' access to things.
It may be a bit more work at first but it should pay off in the long run.
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
wow.. that was really neat and nice way to do it. Thanks so much, guru.
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
Sometimes, instead of trying to figure out how to unlock the front door, all one has to do is go around back and check the back door . . . because it is probably already unlocked. ;-)
The solution may not directly address your need but it resolves the problem, eh? ;-)
My pleasure . . . it was a fun puzzle. ;-)
0
 
LVL 6

Author Comment

by:anushahanna
Comment Utility
your attitude is to be prized and contagious.. keep it up...
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now