Public Role for SQL 2000

For SQL 2000, I see that the Public role has some privileges like SELECT,
UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
(Tables, views, &Stored procedures .)
Is that a security concern?
What will happen if I remove these privileges or revoke them?
Will that effect the rest of the users?
I read many threads on the internet, but no one could tell me the answers
for the 3 questions above.
Thanks in advance.wit1 (wit1@.hotmail.com) writes:
> For SQL 2000, I see that the Public role has some privileges like SELECT,
> UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
> (Tables, views, &Stored procedures .)
That is not the default.

> Is that a security concern?
It does not sound like the best security to me.

> What will happen if I remove these privileges or revoke them?
Impossible to tell as it depends on the application using the database.

> Will that effect the rest of the users?
Again, that depends on the application using the database.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi, I too have same questions.
I tried to drop this public role but not able to drop.
I tried to Revoke Select permission but not able to do that. It is not
giving any error but not working as expected. Any urgent reply will be
gratly appreciated.
Thank,
Tushar Vaja
"wit1" <wit1@.hotmail.com> wrote in message
news:%23DBcPkqiHHA.1244@.TK2MSFTNGP04.phx.gbl...
> For SQL 2000, I see that the Public role has some privileges like SELECT,
> UPDATE, DELETE, & EXECUTE, permissions for some User's database objects
> (Tables, views, &Stored procedures .)
>
> Is that a security concern?
> What will happen if I remove these privileges or revoke them?
> Will that effect the rest of the users?
>
> I read many threads on the internet, but no one could tell me the answers
> for the 3 questions above.
> Thanks in advance.
>
>|||> Hi, I too have same questions.
> I tried to drop this public role but not able to drop.
> I tried to Revoke Select permission but not able to do that. It is not
> giving any error but not working as expected. Any urgent reply will be
> gratly appreciated.
> Thank,
> Tushar Vaja
>
> "wit1" <wit1@.hotmail.com> wrote in message
> news:%23DBcPkqiHHA.1244@.TK2MSFTNGP04.phx.gbl...
>|||Tushar (tushar_vaja@.yahoo.co.in) writes:
> I tried to drop this public role but not able to drop.
> I tried to Revoke Select permission but not able to do that. It is not
> giving any error but not working as expected. Any urgent reply will be
> gratly appreciated.
You could try DENY, but since everyone is in public, this could have
the undesired effect that no one can access anything.
But maybe there is some misunderstanding? Could you clarify more precisely
what tables that are accessible to users who should not get there?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi, thanks for reply. My problem is below:
Whenever user run my exe on his system, my app will create two DB on his
local system. 1 DB contain general info and second DB contain some very
confidenmt info. Now i do not want user(the person who has installed my app)
to access the second DB but he can access 1st DB if he wants.
I do not knwo how to implemet this thing. Please help.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns99337CC774783Yazorman@.127.0.0.1...
> Tushar (tushar_vaja@.yahoo.co.in) writes:
> You could try DENY, but since everyone is in public, this could have
> the undesired effect that no one can access anything.
> But maybe there is some misunderstanding? Could you clarify more precisely
> what tables that are accessible to users who should not get there?
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Tushar (tushar_vaja@.yahoo.co.in) writes:
> Whenever user run my exe on his system, my app will create two DB on his
> local system. 1 DB contain general info and second DB contain some very
> confidenmt info. Now i do not want user(the person who has installed my
> app) to access the second DB but he can access 1st DB if he wants. I do
> not knwo how to implemet this thing. Please help.
Presumably the user who installed the application will have admin rights
on the machine, and you cannot hide anything from an administrator.
You can of course, store the data in the database encrypted, and then
your application could decrypt the data as needed. But since the
application would have to hide the encryption key somewhere, it's not
safe from a user who is dead set from accessing the data, but at least
it protects you from the stray wanderer.
In any case, you need to cover this situation in the license agreement,
and explicitly say that disclosing the data in the database is not
permitted.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,
The following excerpt is taken from Chapter 5 - Microsoft SQL Server 2000
Security of Microsoft SQL Server 2000 Administrator's Pocket Consultant (ISB
N
0-7356-1129-7):
public is the default role for all database users. Users inherit the
permissions and privileges of the public role, and this role represents
their minimum permissions and privileges. Any role that you assign to a
user, beyond the public role, add permissions and privileges. If you want
all database users to have specific permissions, assign the permissions to
the public role.
The following excerpt is taken from Chapter 8 - Managing SQL Serer 2005
Security of Microsoft SQL Server 2005 Administrator's Pocket Consultant:
The guest user is a special user that you can add to a database to allow
anyone with a valid SQL Server login to access the database... Before using
the guest user, you should not the following information about the account:
The guest user is a member of the public server role and inherits the
permissions of this role.
The guest user must exist in a database before anyone can access it as a
guest.
The guest user is used only when a user account has access to SQL Server
but does not have access to the database through this user account.
Other topics around public server role that may be of interest include:
http://www.microsoft.com/technet/pr...in/sqlops3.mspx
http://www.microsoft.com/technet/pr...n/sp3sec01.mspx
http://www.microsoft.com/technet/pr...ploy/mysql.mspx
10 Steps to help Secure SQL Server 2000
https://www.microsoft.com/sql/prodi...n/sp3sec04.mspx
SQL Server 2000 SP3 Security Features and Best Practices: Implementation of
Server Level Security and Object Level Security
http://www.microsoft.com/technet/pr...n/sp3sec02.mspx
SQL Server 2005 (BOL) - Security Considerations for a SQL Server Installatio
n
http://msdn2.microsoft.com/en-us/library/ms144228.aspx
Regards,
Keith Wilson
Disclaimer: this posting is provided "as is" without implied or express
warranties.
"Tushar" wrote:

>
>