Solved

deny all objects but a few, in small code

Posted on 2010-08-25
17
440 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
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

831 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