Solved

deny all objects but a few, in small code

Posted on 2010-08-25
17
442 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 11

Assisted Solution

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

Author Comment

by:anushahanna
ID: 33521299
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
ID: 33521575
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 6

Author Comment

by:anushahanna
ID: 33522326
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
ID: 33589850
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
ID: 33590063
>>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
ID: 33591650
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
 
LVL 6

Author Comment

by:anushahanna
ID: 33599329
>>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
ID: 33599856
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
ID: 33647349
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
ID: 33647353
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
ID: 33647870
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
ID: 33649116
wow.. that was really neat and nice way to do it. Thanks so much, guru.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 33650338
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
ID: 33651563
your attitude is to be prized and contagious.. keep it up...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Display Date and Time 7 47
EF5: Update Model from Database not working 3 69
Remove () 9 32
T-SQL: Trying to use a "NOT IN (Subquery)" in CASE Statement 2 26
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

736 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