I am doing some security work on SQL server and am concerned about the permi
sions on the public role. i want to strip down the permisions given to the
public role in the system databases. What are the implications of runnign sc
ripts to revoke all to publ
ic for all statements and objects in all databases. I have read this causes
problems? What have other folk done regarding this and is there a definitive
list anywhere?
Thanks in advanceGemmill,
We have many databases with all rights to user tables, views, stored
procedures, etc. revoked for public. (You can do this by simply never
granting any rights to public as well.) Also, make sure you do not have
the guest account. (I have never tried to revoke public rights to the
system tables, so I don't know if you can do that. I guess I can go try
when I get back to my office.)
The main implication is that you will break rights that your people have
become accustomed to.
Russell Fields
"Gemmill" <anonymous@.discussions.microsoft.com> wrote in message
news:2657C8D7-F49D-47D1-99B6-04D470BE6D71@.microsoft.com...
> I am doing some security work on SQL server and am concerned about the
permisions on the public role. i want to strip down the permisions given to
the public role in the system databases. What are the implications of
runnign scripts to revoke all to public for all statements and objects in
all databases. I have read this causes problems? What have other folk done
regarding this and is there a definitive list anywhere?
> Thanks in advance|||Thanks for the reply russell. Iam fairly comforatable with removing permissi
ons as susggested from user tables. But am concerned it will cause problems
if i carry it out on the system databases. I would like to "lock down" publi
c permissions on the system
databases without seriously affecting system operation. What have you done i
n regards to this.
Thanks|||My recommendation would be to leave the system tables alone. If there
are any security holes that turn up that arise from granting
permissions to public, then they'll probably be addressed in a service
pack, if they haven't been already. If you try mucking around in the
systems tables yourself, you'll probably just end up breaking things.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 27 Feb 2004 01:11:05 -0800, "gemmill"
<anonymous@.discussions.microsoft.com> wrote:
>Thanks for the reply russell. Iam fairly comforatable with removing permissions as
susggested from user tables. But am concerned it will cause problems if i carry it o
ut on the system databases. I would like to "lock down" public permissions on the sy
ste
m databases without seriously affecting system operation. What have you done in regards to
this.
>Thanks|||Best practices often tell you to remove PUBLIC role from all user objects an
d this should be done after a careful review of the permission required by y
our application.
Some experts also go as far as to recommend that you remove PUBLIC role perm
issions from system stored procedures and extended stored procedures to prev
ent attacks on your server. This should also be done, but only after carefu
l testing as removing PUBLI
C permissions to all stored procedures can often break some functionality in
Enterprise Manager. I do not know of a article/whitepaper that has studied
this and listed what exactly will be broken based on each system stored proc
edure.
Brian Kelley recently released a nice whitepaper on the PUBLIC role permissi
ons to system tables that you should read to see if it is any help.
SQL Server 2000: Permissions on System Tables Granted to Logins Due to the P
ublic Role
http://www.giac.org./practical/GSEC...Kelley_GSEC.pdf
Randy Dyess
www.Database-Security.Info|||Thanks for the reply's, much appreciated
Showing posts with label revoking. Show all posts
Showing posts with label revoking. Show all posts
Wednesday, March 21, 2012
Revoking permissions on system tables to the public role.
We have an audit issue that is requiring me to revoke the select permissions from the public role to the system tables. Has anyone had to do this?? What problems did you experience? Are their any tables that you were not able to change the permissions on? Any help is greatly appreciated...when you say "system tables" which ones...system tables reside within databases and of course within master,model,msdb, etc. ...
...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)
...as to what affect revoking system table access from the public role will have, you would first have to answer the above questions...
...also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?|||..when you say "system tables" which ones...system tables reside within databases and of course within master,model,msdb, etc. ...
--I mean all. From the master, msdb, and user databases. My concerns are mostly with the master and msdb databases.
...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)
--We use several types of applications. Everything from web pages( We are a web company), to PeopleSoft, Siebel, and Access.
also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?
-- I am not sure, but I believe that they will. This is related to the new Sarbanes-Oxley act.|||...we are working on Sarbanes as well...but there has been nothing to compell us to look at this type of restriction...
...I am not sure you can restrict this access and still have application functionality...
...if this is still something you would like to pursue, I would start at the database level, create a test database and userid's and a method of access.
First you would have to test to ensure the access method works for the objects created in the database. Once that was proven, I would try the revoke against the databases system tables. To obtain a list of system tables:
select name from sysobjects where type = 'S'
you can then use the "sp_helprotect" stored procedure to obtain the current security settings.
then use the REVOKE command to revoke public access from these objects...
...then the access methid would have to be tested again...
now as far as the system databases goes, i'm sure that revoking public access would allow funtionality to remain...but you'd have to follow a similiar test...good luck...|||Why on earth would SOX be interested in the system tables? Are you storing SOX-sensitive data in the system tables? Using system tables for storing user data is a really bad idea!
-PatP|||...we discussed SOX at lunch and agree that everyone seems to be going overboard in the name of SOX...including auditors who are just guessing at how to comply with the new expectations...(not that they ever knew!)...
...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)
...as to what affect revoking system table access from the public role will have, you would first have to answer the above questions...
...also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?|||..when you say "system tables" which ones...system tables reside within databases and of course within master,model,msdb, etc. ...
--I mean all. From the master, msdb, and user databases. My concerns are mostly with the master and msdb databases.
...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)
--We use several types of applications. Everything from web pages( We are a web company), to PeopleSoft, Siebel, and Access.
also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?
-- I am not sure, but I believe that they will. This is related to the new Sarbanes-Oxley act.|||...we are working on Sarbanes as well...but there has been nothing to compell us to look at this type of restriction...
...I am not sure you can restrict this access and still have application functionality...
...if this is still something you would like to pursue, I would start at the database level, create a test database and userid's and a method of access.
First you would have to test to ensure the access method works for the objects created in the database. Once that was proven, I would try the revoke against the databases system tables. To obtain a list of system tables:
select name from sysobjects where type = 'S'
you can then use the "sp_helprotect" stored procedure to obtain the current security settings.
then use the REVOKE command to revoke public access from these objects...
...then the access methid would have to be tested again...
now as far as the system databases goes, i'm sure that revoking public access would allow funtionality to remain...but you'd have to follow a similiar test...good luck...|||Why on earth would SOX be interested in the system tables? Are you storing SOX-sensitive data in the system tables? Using system tables for storing user data is a really bad idea!
-PatP|||...we discussed SOX at lunch and agree that everyone seems to be going overboard in the name of SOX...including auditors who are just guessing at how to comply with the new expectations...(not that they ever knew!)...
Tuesday, March 20, 2012
Revoking permissions from dbo
Hi,
Is there any possible way to revoke a permission in a database from someone
who is a database owner? I don't think there is, but I'm making a last ditch
attempt to find out (this is SQL Server 2005).
The reason is that we want to use the "VIEW ALL DATABASES" permission to
stop other users viewing databases that they don't own, but the only way to
do this is to make the user who is allowed to see the database the owner. Bu
t
by doing this, they get access to do things like backup databases etc which
is a security risk (this is a shared server).
All my research points to the fact that we can't stop them from backing up
databases etc if thye are the owner, and since we can't allow that, it means
"VIEW ALL DATABASES" is useless for shared hosts (whom I would think the
permission would be especially useful for if it was implemented with care).
We really want to hide all databases from other users, but we can't do it if
it means opening large holes in our security.
If anyone has any ideas before I give up, they would be appreciated!I don't have 2005 on this laptop. I believe though that you can deny view
definition on all but the databases a particular person owns. They can stil
l
see theirs, but they won't be able to see others. Isn't that, in effect, th
e
exact thing you are trying to achieve?
--
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Paul Woodland" wrote:
> Hi,
> Is there any possible way to revoke a permission in a database from someon
e
> who is a database owner? I don't think there is, but I'm making a last dit
ch
> attempt to find out (this is SQL Server 2005).
> The reason is that we want to use the "VIEW ALL DATABASES" permission to
> stop other users viewing databases that they don't own, but the only way t
o
> do this is to make the user who is allowed to see the database the owner.
But
> by doing this, they get access to do things like backup databases etc whic
h
> is a security risk (this is a shared server).
> All my research points to the fact that we can't stop them from backing up
> databases etc if thye are the owner, and since we can't allow that, it mea
ns
> "VIEW ALL DATABASES" is useless for shared hosts (whom I would think the
> permission would be especially useful for if it was implemented with care)
.
> We really want to hide all databases from other users, but we can't do it
if
> it means opening large holes in our security.
> If anyone has any ideas before I give up, they would be appreciated!
>|||Hi,
Thank you for your reply. Although doing that hides the databases fine
(thats not the problem), it's the security holes it opens up which I'm
worried about. As an example, if we set our customers as database owners the
n
they can use "backup database" whenever they wish to backup to any location
on the servers hard disk that the user SQL Server is running as has access t
o
(which is a limited number of places because it's running as a limited user,
but there are still places - such as where we store our own automated
backups). Being a shared hosting environment rather than a corporate
environment, we can't "trust" our users not to take advantage of this (for
example, they could overwrite our own backups).
The way I can think of to solve this is to somehow revoke the "backup
database" permission (and other permissions you don't want them to have)
from the user, but it won't let you revoke permissions from dbo. Which is wh
y
I'm stuck.
Any ideas?
"MeanOldDBA" wrote:
[vbcol=seagreen]
> I don't have 2005 on this laptop. I believe though that you can deny view
> definition on all but the databases a particular person owns. They can st
ill
> see theirs, but they won't be able to see others. Isn't that, in effect,
the
> exact thing you are trying to achieve?
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "Paul Woodland" wrote:
>
Is there any possible way to revoke a permission in a database from someone
who is a database owner? I don't think there is, but I'm making a last ditch
attempt to find out (this is SQL Server 2005).
The reason is that we want to use the "VIEW ALL DATABASES" permission to
stop other users viewing databases that they don't own, but the only way to
do this is to make the user who is allowed to see the database the owner. Bu
t
by doing this, they get access to do things like backup databases etc which
is a security risk (this is a shared server).
All my research points to the fact that we can't stop them from backing up
databases etc if thye are the owner, and since we can't allow that, it means
"VIEW ALL DATABASES" is useless for shared hosts (whom I would think the
permission would be especially useful for if it was implemented with care).
We really want to hide all databases from other users, but we can't do it if
it means opening large holes in our security.
If anyone has any ideas before I give up, they would be appreciated!I don't have 2005 on this laptop. I believe though that you can deny view
definition on all but the databases a particular person owns. They can stil
l
see theirs, but they won't be able to see others. Isn't that, in effect, th
e
exact thing you are trying to achieve?
--
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Paul Woodland" wrote:
> Hi,
> Is there any possible way to revoke a permission in a database from someon
e
> who is a database owner? I don't think there is, but I'm making a last dit
ch
> attempt to find out (this is SQL Server 2005).
> The reason is that we want to use the "VIEW ALL DATABASES" permission to
> stop other users viewing databases that they don't own, but the only way t
o
> do this is to make the user who is allowed to see the database the owner.
But
> by doing this, they get access to do things like backup databases etc whic
h
> is a security risk (this is a shared server).
> All my research points to the fact that we can't stop them from backing up
> databases etc if thye are the owner, and since we can't allow that, it mea
ns
> "VIEW ALL DATABASES" is useless for shared hosts (whom I would think the
> permission would be especially useful for if it was implemented with care)
.
> We really want to hide all databases from other users, but we can't do it
if
> it means opening large holes in our security.
> If anyone has any ideas before I give up, they would be appreciated!
>|||Hi,
Thank you for your reply. Although doing that hides the databases fine
(thats not the problem), it's the security holes it opens up which I'm
worried about. As an example, if we set our customers as database owners the
n
they can use "backup database" whenever they wish to backup to any location
on the servers hard disk that the user SQL Server is running as has access t
o
(which is a limited number of places because it's running as a limited user,
but there are still places - such as where we store our own automated
backups). Being a shared hosting environment rather than a corporate
environment, we can't "trust" our users not to take advantage of this (for
example, they could overwrite our own backups).
The way I can think of to solve this is to somehow revoke the "backup
database" permission (and other permissions you don't want them to have)
from the user, but it won't let you revoke permissions from dbo. Which is wh
y
I'm stuck.
Any ideas?
"MeanOldDBA" wrote:
[vbcol=seagreen]
> I don't have 2005 on this laptop. I believe though that you can deny view
> definition on all but the databases a particular person owns. They can st
ill
> see theirs, but they won't be able to see others. Isn't that, in effect,
the
> exact thing you are trying to achieve?
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "Paul Woodland" wrote:
>
Revoking Permissions
I am going to tighten permissions on my SQL Server. In one book, it is reco
mmended to remove all permissions on the public role to user databases. By
default, it looks like the public role is granted permissions to the system
tables and system stored pr
ocedures.
Does the public role need access to these?
Thank you,
JLFlemingBooks Online has this recommendation:
To protect against unauthorized data access, minimize the permissions
granted to the public role. Instead, grant permissions to other database
roles and to user accounts associated with logins
There's some additional guidance here:
SQL Server 2000 SP3 Security Features and Best Practices: Security Best
Practices Checklist
http://www.microsoft.com/technet/pr...n/sp3sec04.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
mmended to remove all permissions on the public role to user databases. By
default, it looks like the public role is granted permissions to the system
tables and system stored pr
ocedures.
Does the public role need access to these?
Thank you,
JLFlemingBooks Online has this recommendation:
To protect against unauthorized data access, minimize the permissions
granted to the public role. Instead, grant permissions to other database
roles and to user accounts associated with logins
There's some additional guidance here:
SQL Server 2000 SP3 Security Features and Best Practices: Security Best
Practices Checklist
http://www.microsoft.com/technet/pr...n/sp3sec04.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
Revoking permission to view SQL Stored procs
Hi
Is there a way which I can revoke a users ability to view SQL stored procs,
but still have the ability to execute?
I want to do this via T-SQL, and without just considering the 'WITH
ENCRYPTION' option
Thanks
hi Paul,
Paul Aspinall wrote:
> Hi
> Is there a way which I can revoke a users ability to view SQL stored
> procs, but still have the ability to execute?
> I want to do this via T-SQL, and without just considering the 'WITH
> ENCRYPTION' option
> Thanks
every database user is allowed to view the definition of stored procedures,
and you can not change this behaviour... unfortunately, as you already
pointed out, you can only consider the WITH ENCRYPTION option
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||What about granting only EXECUTE permissions and not CREATE, ALTER or
DROP ?
|||hi,
bd wrote:
> What about granting only EXECUTE permissions and not CREATE, ALTER or
> DROP ?
every database user has implicit permission to script objects, so he/she
will be able to access the object definition and DDL
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Is there a way which I can revoke a users ability to view SQL stored procs,
but still have the ability to execute?
I want to do this via T-SQL, and without just considering the 'WITH
ENCRYPTION' option
Thanks
hi Paul,
Paul Aspinall wrote:
> Hi
> Is there a way which I can revoke a users ability to view SQL stored
> procs, but still have the ability to execute?
> I want to do this via T-SQL, and without just considering the 'WITH
> ENCRYPTION' option
> Thanks
every database user is allowed to view the definition of stored procedures,
and you can not change this behaviour... unfortunately, as you already
pointed out, you can only consider the WITH ENCRYPTION option
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||What about granting only EXECUTE permissions and not CREATE, ALTER or
DROP ?
|||hi,
bd wrote:
> What about granting only EXECUTE permissions and not CREATE, ALTER or
> DROP ?
every database user has implicit permission to script objects, so he/she
will be able to access the object definition and DDL
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Revoking and Granting permissions.. SQL Security Bug\Issue ?
I am getting a very strange issue occurring when granting
and revoking permissions on Roles in my database.
As part of the testing that I'm doing in a Role I revoked
the EXEC permission on a stored procedure and when
attempting to run the function in my VB app which
referenced this procedure I got the error
message 'EXECUTE permission denied on stored
procedure...' which makes perfect sense. But when going
back and granting the permission back to the role when I
run the same function in my VB app I get a number
of 'SELECT permission denied on object.' errors.
And these SELECT statements are in reference to what the
stored procedure is running against ? Is this an issue
with SQL security ?
Can anybody provide any insight ?Hi,
I feel that some body have denied select permission on your user. This will
over rule the grant access given to the role assigned to your user. This can
be overcomed by granding select permission to your user, so as the deny will
be removed.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:170c01c426ae$f400e1c0$a001280a@.phx.gbl...
> I am getting a very strange issue occurring when granting
> and revoking permissions on Roles in my database.
> As part of the testing that I'm doing in a Role I revoked
> the EXEC permission on a stored procedure and when
> attempting to run the function in my VB app which
> referenced this procedure I got the error
> message 'EXECUTE permission denied on stored
> procedure...' which makes perfect sense. But when going
> back and granting the permission back to the role when I
> run the same function in my VB app I get a number
> of 'SELECT permission denied on object.' errors.
> And these SELECT statements are in reference to what the
> stored procedure is running against ? Is this an issue
> with SQL security ?
> Can anybody provide any insight ?|||Permissions on objects referenced by your stored procedure are not checked
as long as the following are true:
1) the objects have the same owner (unbroken ownership chain)
2) you are not referencing objects using dynamic SQL in your proc.
3) if the objects are in different databases and you are running SQL 2000
SP3, cross-database chaining needs to be enabled in the databases involved
and the object owners need to map to the same login (per #1 above)
You can read more about ownership chains in the SQL 2000 Books Online
<adminsql.chm::/ad_security_4iyb.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:170c01c426ae$f400e1c0$a001280a@.phx.gbl...
> I am getting a very strange issue occurring when granting
> and revoking permissions on Roles in my database.
> As part of the testing that I'm doing in a Role I revoked
> the EXEC permission on a stored procedure and when
> attempting to run the function in my VB app which
> referenced this procedure I got the error
> message 'EXECUTE permission denied on stored
> procedure...' which makes perfect sense. But when going
> back and granting the permission back to the role when I
> run the same function in my VB app I get a number
> of 'SELECT permission denied on object.' errors.
> And these SELECT statements are in reference to what the
> stored procedure is running against ? Is this an issue
> with SQL security ?
> Can anybody provide any insight ?
and revoking permissions on Roles in my database.
As part of the testing that I'm doing in a Role I revoked
the EXEC permission on a stored procedure and when
attempting to run the function in my VB app which
referenced this procedure I got the error
message 'EXECUTE permission denied on stored
procedure...' which makes perfect sense. But when going
back and granting the permission back to the role when I
run the same function in my VB app I get a number
of 'SELECT permission denied on object.' errors.
And these SELECT statements are in reference to what the
stored procedure is running against ? Is this an issue
with SQL security ?
Can anybody provide any insight ?Hi,
I feel that some body have denied select permission on your user. This will
over rule the grant access given to the role assigned to your user. This can
be overcomed by granding select permission to your user, so as the deny will
be removed.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:170c01c426ae$f400e1c0$a001280a@.phx.gbl...
> I am getting a very strange issue occurring when granting
> and revoking permissions on Roles in my database.
> As part of the testing that I'm doing in a Role I revoked
> the EXEC permission on a stored procedure and when
> attempting to run the function in my VB app which
> referenced this procedure I got the error
> message 'EXECUTE permission denied on stored
> procedure...' which makes perfect sense. But when going
> back and granting the permission back to the role when I
> run the same function in my VB app I get a number
> of 'SELECT permission denied on object.' errors.
> And these SELECT statements are in reference to what the
> stored procedure is running against ? Is this an issue
> with SQL security ?
> Can anybody provide any insight ?|||Permissions on objects referenced by your stored procedure are not checked
as long as the following are true:
1) the objects have the same owner (unbroken ownership chain)
2) you are not referencing objects using dynamic SQL in your proc.
3) if the objects are in different databases and you are running SQL 2000
SP3, cross-database chaining needs to be enabled in the databases involved
and the object owners need to map to the same login (per #1 above)
You can read more about ownership chains in the SQL 2000 Books Online
<adminsql.chm::/ad_security_4iyb.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:170c01c426ae$f400e1c0$a001280a@.phx.gbl...
> I am getting a very strange issue occurring when granting
> and revoking permissions on Roles in my database.
> As part of the testing that I'm doing in a Role I revoked
> the EXEC permission on a stored procedure and when
> attempting to run the function in my VB app which
> referenced this procedure I got the error
> message 'EXECUTE permission denied on stored
> procedure...' which makes perfect sense. But when going
> back and granting the permission back to the role when I
> run the same function in my VB app I get a number
> of 'SELECT permission denied on object.' errors.
> And these SELECT statements are in reference to what the
> stored procedure is running against ? Is this an issue
> with SQL security ?
> Can anybody provide any insight ?
revoking all the privileges from public
I am revoking all the privileges from public role from master and tempdb
database, will that be any thing wrong after that? just want expert idea
because I ran one tool and it gave me suggession to remove public rights
from system objects on master database.
LIke following are some of the commands
USE MASTER
REVOKE EXEC ON dt_droppropertiesbyid FROM PUBLIC
GO
REVOKE EXEC ON dt_checkinobject FROM PUBLIC
GO
REVOKE SELECT ON dtproperties FROM PUBLIC
GO
REVOKE EXEC ON dt_checkoutobject FROM PUBLIC
GO
REVOKE EXEC ON dt_checkoutobject_u FROM PUBLIC
GO
Please give any suggession,
Rogers,
Do not revoke all rights from public for these databases. That will break
many things.
RLF
"Rogers" <naissani@.hotmail.com> wrote in message
news:eLXU7qB4HHA.5804@.TK2MSFTNGP05.phx.gbl...
>I am revoking all the privileges from public role from master and tempdb
>database, will that be any thing wrong after that? just want expert idea
>because I ran one tool and it gave me suggession to remove public rights
>from system objects on master database.
> LIke following are some of the commands
> USE MASTER
> REVOKE EXEC ON dt_droppropertiesbyid FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkinobject FROM PUBLIC
> GO
> REVOKE SELECT ON dtproperties FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkoutobject FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkoutobject_u FROM PUBLIC
> GO
>
> Please give any suggession,
>
|||Thanks alot for your reply,
Is there any Microsoft article on that so I can present this to our Manager
and also refer that Microsoft doesn't recommend that.
Thanks
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eMITWCD4HHA.5776@.TK2MSFTNGP04.phx.gbl...
> Rogers,
> Do not revoke all rights from public for these databases. That will break
> many things.
> RLF
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:eLXU7qB4HHA.5804@.TK2MSFTNGP05.phx.gbl...
>
|||Rogers,
You don't say which version of SQL Server you are running, which may make
some difference.
First of all, you can revoke some rights in master and msdb and still have
the necessary things work, but revoking everything would be a disaster.
Basic system stored procedures that everyone needs would also be revoked.
You need to be more measured in your revoking of rights, which means you
need to understand what is still needed and what can be safely revoke. (If
you are using SQL Server 2005, note that some rights that were exposed in
SQL Server 2000 are now more tightly guarded.)
Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago, which
does suggest some areas for rights to be removed:
http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
Tools such as AuditPro also make suggestions.
So, I do not have a specific list for you, review the things in the BOL (or
do "exec sp_helprotect @.username='public'" in both master and msdb) to see
what is granted, then think about whether you want that or not. (It is
pointless to manipulate rights in tempdb, since it is rebuilt everytime the
SQL Server restarts and it has its own mechanism for privacy.)
I tried finding a real solid article on this, but (alas) could not.
RLF
Note: The following article offers no guidance on what to revoke and what
not to revoke, it merely tells you what you can do, not what it is wise to
do: http://support.microsoft.com/kb/75291
"Rogers" <naissani@.hotmail.com> wrote in message
news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
> Thanks alot for your reply,
> Is there any Microsoft article on that so I can present this to our
> Manager and also refer that Microsoft doesn't recommend that.
> Thanks
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eMITWCD4HHA.5776@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks alot, I am using SQL Server 2000.
Thanks
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:u5hPS6D4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Rogers,
> You don't say which version of SQL Server you are running, which may make
> some difference.
> First of all, you can revoke some rights in master and msdb and still have
> the necessary things work, but revoking everything would be a disaster.
> Basic system stored procedures that everyone needs would also be revoked.
> You need to be more measured in your revoking of rights, which means you
> need to understand what is still needed and what can be safely revoke.
> (If you are using SQL Server 2005, note that some rights that were exposed
> in SQL Server 2000 are now more tightly guarded.)
> Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago,
> which does suggest some areas for rights to be removed:
> http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
> Tools such as AuditPro also make suggestions.
> So, I do not have a specific list for you, review the things in the BOL
> (or do "exec sp_helprotect @.username='public'" in both master and msdb) to
> see what is granted, then think about whether you want that or not. (It
> is pointless to manipulate rights in tempdb, since it is rebuilt everytime
> the SQL Server restarts and it has its own mechanism for privacy.)
> I tried finding a real solid article on this, but (alas) could not.
> RLF
> Note: The following article offers no guidance on what to revoke and what
> not to revoke, it merely tells you what you can do, not what it is wise to
> do: http://support.microsoft.com/kb/75291
>
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
>
|||Please send me the list If you find any like which system objects in Master
and MSDB can be revoked from Public Role, and no impact on sql server.
Thanks alot for your help.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:u5hPS6D4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Rogers,
> You don't say which version of SQL Server you are running, which may make
> some difference.
> First of all, you can revoke some rights in master and msdb and still have
> the necessary things work, but revoking everything would be a disaster.
> Basic system stored procedures that everyone needs would also be revoked.
> You need to be more measured in your revoking of rights, which means you
> need to understand what is still needed and what can be safely revoke.
> (If you are using SQL Server 2005, note that some rights that were exposed
> in SQL Server 2000 are now more tightly guarded.)
> Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago,
> which does suggest some areas for rights to be removed:
> http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
> Tools such as AuditPro also make suggestions.
> So, I do not have a specific list for you, review the things in the BOL
> (or do "exec sp_helprotect @.username='public'" in both master and msdb) to
> see what is granted, then think about whether you want that or not. (It
> is pointless to manipulate rights in tempdb, since it is rebuilt everytime
> the SQL Server restarts and it has its own mechanism for privacy.)
> I tried finding a real solid article on this, but (alas) could not.
> RLF
> Note: The following article offers no guidance on what to revoke and what
> not to revoke, it merely tells you what you can do, not what it is wise to
> do: http://support.microsoft.com/kb/75291
>
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
>
database, will that be any thing wrong after that? just want expert idea
because I ran one tool and it gave me suggession to remove public rights
from system objects on master database.
LIke following are some of the commands
USE MASTER
REVOKE EXEC ON dt_droppropertiesbyid FROM PUBLIC
GO
REVOKE EXEC ON dt_checkinobject FROM PUBLIC
GO
REVOKE SELECT ON dtproperties FROM PUBLIC
GO
REVOKE EXEC ON dt_checkoutobject FROM PUBLIC
GO
REVOKE EXEC ON dt_checkoutobject_u FROM PUBLIC
GO
Please give any suggession,
Rogers,
Do not revoke all rights from public for these databases. That will break
many things.
RLF
"Rogers" <naissani@.hotmail.com> wrote in message
news:eLXU7qB4HHA.5804@.TK2MSFTNGP05.phx.gbl...
>I am revoking all the privileges from public role from master and tempdb
>database, will that be any thing wrong after that? just want expert idea
>because I ran one tool and it gave me suggession to remove public rights
>from system objects on master database.
> LIke following are some of the commands
> USE MASTER
> REVOKE EXEC ON dt_droppropertiesbyid FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkinobject FROM PUBLIC
> GO
> REVOKE SELECT ON dtproperties FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkoutobject FROM PUBLIC
> GO
> REVOKE EXEC ON dt_checkoutobject_u FROM PUBLIC
> GO
>
> Please give any suggession,
>
|||Thanks alot for your reply,
Is there any Microsoft article on that so I can present this to our Manager
and also refer that Microsoft doesn't recommend that.
Thanks
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eMITWCD4HHA.5776@.TK2MSFTNGP04.phx.gbl...
> Rogers,
> Do not revoke all rights from public for these databases. That will break
> many things.
> RLF
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:eLXU7qB4HHA.5804@.TK2MSFTNGP05.phx.gbl...
>
|||Rogers,
You don't say which version of SQL Server you are running, which may make
some difference.
First of all, you can revoke some rights in master and msdb and still have
the necessary things work, but revoking everything would be a disaster.
Basic system stored procedures that everyone needs would also be revoked.
You need to be more measured in your revoking of rights, which means you
need to understand what is still needed and what can be safely revoke. (If
you are using SQL Server 2005, note that some rights that were exposed in
SQL Server 2000 are now more tightly guarded.)
Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago, which
does suggest some areas for rights to be removed:
http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
Tools such as AuditPro also make suggestions.
So, I do not have a specific list for you, review the things in the BOL (or
do "exec sp_helprotect @.username='public'" in both master and msdb) to see
what is granted, then think about whether you want that or not. (It is
pointless to manipulate rights in tempdb, since it is rebuilt everytime the
SQL Server restarts and it has its own mechanism for privacy.)
I tried finding a real solid article on this, but (alas) could not.
RLF
Note: The following article offers no guidance on what to revoke and what
not to revoke, it merely tells you what you can do, not what it is wise to
do: http://support.microsoft.com/kb/75291
"Rogers" <naissani@.hotmail.com> wrote in message
news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
> Thanks alot for your reply,
> Is there any Microsoft article on that so I can present this to our
> Manager and also refer that Microsoft doesn't recommend that.
> Thanks
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eMITWCD4HHA.5776@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks alot, I am using SQL Server 2000.
Thanks
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:u5hPS6D4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Rogers,
> You don't say which version of SQL Server you are running, which may make
> some difference.
> First of all, you can revoke some rights in master and msdb and still have
> the necessary things work, but revoking everything would be a disaster.
> Basic system stored procedures that everyone needs would also be revoked.
> You need to be more measured in your revoking of rights, which means you
> need to understand what is still needed and what can be safely revoke.
> (If you are using SQL Server 2005, note that some rights that were exposed
> in SQL Server 2000 are now more tightly guarded.)
> Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago,
> which does suggest some areas for rights to be removed:
> http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
> Tools such as AuditPro also make suggestions.
> So, I do not have a specific list for you, review the things in the BOL
> (or do "exec sp_helprotect @.username='public'" in both master and msdb) to
> see what is granted, then think about whether you want that or not. (It
> is pointless to manipulate rights in tempdb, since it is rebuilt everytime
> the SQL Server restarts and it has its own mechanism for privacy.)
> I tried finding a real solid article on this, but (alas) could not.
> RLF
> Note: The following article offers no guidance on what to revoke and what
> not to revoke, it merely tells you what you can do, not what it is wise to
> do: http://support.microsoft.com/kb/75291
>
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
>
|||Please send me the list If you find any like which system objects in Master
and MSDB can be revoked from Public Role, and no impact on sql server.
Thanks alot for your help.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:u5hPS6D4HHA.3916@.TK2MSFTNGP02.phx.gbl...
> Rogers,
> You don't say which version of SQL Server you are running, which may make
> some difference.
> First of all, you can revoke some rights in master and msdb and still have
> the necessary things work, but revoking everything would be a disaster.
> Basic system stored procedures that everyone needs would also be revoked.
> You need to be more measured in your revoking of rights, which means you
> need to understand what is still needed and what can be safely revoke.
> (If you are using SQL Server 2005, note that some rights that were exposed
> in SQL Server 2000 are now more tightly guarded.)
> Here is a PowerPoint SQL Server 2000 oriented from about 3 years ago,
> which does suggest some areas for rights to be removed:
> http://download.microsoft.com/download/7/3/c/73ca9891-f3eb-4caf-bf60-51f4aca51706/DBA5_kburge.ppt#302,43,URLs
> Tools such as AuditPro also make suggestions.
> So, I do not have a specific list for you, review the things in the BOL
> (or do "exec sp_helprotect @.username='public'" in both master and msdb) to
> see what is granted, then think about whether you want that or not. (It
> is pointless to manipulate rights in tempdb, since it is rebuilt everytime
> the SQL Server restarts and it has its own mechanism for privacy.)
> I tried finding a real solid article on this, but (alas) could not.
> RLF
> Note: The following article offers no guidance on what to revoke and what
> not to revoke, it merely tells you what you can do, not what it is wise to
> do: http://support.microsoft.com/kb/75291
>
> "Rogers" <naissani@.hotmail.com> wrote in message
> news:Oqsq$FD4HHA.3900@.TK2MSFTNGP02.phx.gbl...
>
Revoking All Permissions - Secure by Default
I am designing my software to be 'secure by default'.
I only grant permissions to entities that actually require them, to help prevent unexpected attacks like injection attacks. To do this, I have a SQL script that configures all the permissions on my database. It is basically a series of GRANT commands. Every time I modify a stored procedure or perform a similar action that causes the permissions on an object to be lost, I run the script and it reconfigures all the security correctly, on every object.
Over time, permissions have been granted that are not necessary, or no longer required. This may have happened for many reasons, development and testing not least.
At the top of my script, I want to REVOKE all permissions explicitly granted or denied to my user (for the purposes of this question, PUBLIC) so that I know that only permissions explicitly granted in the script will be given to the user.
There are too many objects in my database to maintain a list of "REVOKE ALL ON <object> TO PUBLIC" commands with any reliability. The statement "REVOKE ALL ON ALL TO PUBLIC" does not work either.
Is there a simple way to do this?
Maybe.|||You can probably write a script to query the catalogs, find the permissions granted or denied to public, and issue a revoke statement for each of them. There isn't a simple statement to do this.
Another way would be to collect all the grants/denies made to public in your scripts and issue revokes for them.
Thanks
Laurentiu
I almost expected that this would be the only answer. Thanks.
Scripts are acceptable, but not 'my style', if you get my meaning. I will probably write a stored procedure that I can call to do this for me - which will have to be deployed to each of my databases - unfortunately.
Thanks.
Subscribe to:
Posts (Atom)