I'll have probably to use sql server soon but prior to that I have a
question concerning priviledges and security.
Is it possible for someone to do like in access, ie creating a
db/table that is locked with a password? My guess is that it will be
yes but in cas of... Now is it possible for someone to make a db/table
read only rather than to lock it totally?
Also can the guy who has an administrator priviledge on the server
determine easily what is the password for a db even if he's not the
guy who created it?
thanksOn 30 Aug 2004 17:31:13 -0700, J.Beaulieu wrote:
>Hi
> I'll have probably to use sql server soon but prior to that I have a
>question concerning priviledges and security.
>Is it possible for someone to do like in access, ie creating a
>db/table that is locked with a password? My guess is that it will be
>yes but in cas of... Now is it possible for someone to make a db/table
>read only rather than to lock it totally?
>Also can the guy who has an administrator priviledge on the server
>determine easily what is the password for a db even if he's not the
>guy who created it?
>thanks
Hi J.,
The answer to all these questions is "no".
Access is a great tool - but comparing SQL Server to it is dangerous and
often misleading. Access control for different users in SQL Server is
completely different (and lots more professional) than anything Access has
to offer. I'll try to give you a brief overview.
A SQL Server database is a collection of tables, views, stored procedures,
triggers, etc. It can roughly be compared to an Access .mdf file. One SQL
Server server can host many SQL Server databases.
To gain access to a SQL Server database, you must first log in to the
server. There are two variations:
1. Trusted connection: SQL Server communicates with the Windows operating
system to find out what domain you are logged in to and what user name you
have. If you are user foo on domain bar, SQL Server will check if access
to the server is allowed for user bar\foo.
2. SQL Server login: You provide a name and a password. SQL Server checks
if the name supplied has access to the server and if the password matches.
The password is stored in encrypted form and I have never heard of a case
where the password was decrypted.
A new SQL Server installation will only allow access for administrators;
all other users can only gain access if someone has given them access to
the server.
Once you are logged in to the server, SQL Server will check which of the
databases on the server you may access. It is possible (though not
obvious) to find out which databases exist on a server, but it's
impossible to access databases unless someone has allowed you access.
Further access control within the database is also possible. For each
table or view, permission to insert, delete, update or select (view) rows
can be granted to (or revoked from) individual users. For update and
select permissions, this can even be drilled down to the column level (eg.
allow the managers to see rows in the Personnel table, but not the column
holding the Salary). For stored procedures, permission to execute can be
given to or taken from users. Other notable permissions are the permission
to create new objects in the database or the permission to grant
permissions to other users.
If you have many users, managing permissions is easier if you set up
roles. An example: you set up a role "Auditor". Now, you can grant select
permission deny insert, update and delete permissions on all tables and
all views to this role. Whenever a new auditor is hired, the administrator
merely has to add the Auditor role to the userid of the new auditor and
(s)he has read access to all data.
There's lots more to SQL Server security than this, but I'll stop her for
now. If you want to find out more, read about it in Books Online. It's on
your computer if you have installed SQL Server. If it isn't, you can also
find it online:
http://msdn.microsoft.com/library/e...asp?frame=true
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||jfbeaulieu2003@.yahoo.com (J.Beaulieu) wrote in message news:<eb1b8a74.0408301631.5a911870@.posting.google.com>...
> Hi
> I'll have probably to use sql server soon but prior to that I have a
> question concerning priviledges and security.
> Is it possible for someone to do like in access, ie creating a
> db/table that is locked with a password? My guess is that it will be
> yes but in cas of... Now is it possible for someone to make a db/table
> read only rather than to lock it totally?
> Also can the guy who has an administrator priviledge on the server
> determine easily what is the password for a db even if he's not the
> guy who created it?
> thanks
In MSSQL, you control access to databases and tables with permissions.
First, you grant someone access to the server, then to databases, and
finally to individual objects (not just tables). If you want a
read-only table, for example, you only GRANT SELECT, you don't GRANT
UPDATE.
A member of the sysadmin role can do anything in any database, but
there are other roles which provide more restricted permissions.
Someone can be in the db_owner role for database A, and be able to do
anything in that database, but without access to database B he can't
do anything there.
Check out the "Managing Security" section of Books Online, and
especially "Managing Permissions" to get more information. Make sure
you look at the section called "Using Ownership Chains", as it's a
very important part of MSSQL security.
Simon
No comments:
Post a Comment