Sqlserver

Users and Access
Rights
Managing User
Accounts
©2010 Elsevier Inc. All rights reserved.
10.1016/B978-0-12-375697-8.50012-1


264 Chapter 12: Users and Access Rights
CREATE USER user_name IDENTIFIED BY ‘password’
Oracle’s version uses the following pattern:
CREATE USER user_name
IDENTIFIED BY password
DEFAULT TABLESPACE tablespace_name
QUOTAstorage_space_allocation
The DEFAULT TABLESPACE and QUOTA clauses set the area
of the database the user will use for temporary storage and the
amount of temporary storage the user can fill.
Postgres varies the syntax slightly:
CREATE USER user_name
PASSWORD ‘password’
Postgres also supports clauses to allow/disallow the creation of
databases and the creation of other users.
SQL Server uses yetanother similar syntax:
CREATE LOGIN user_name
WITH PASSWORD = ‘password’
In contrast, DB2 does not provide its own user names and pass-
words. Instead, it uses a person’s account with the operating sys-
tem. In other words, once a user is authenticated by the operating
system, DB2 requires no further account authorization. Access
rights to database elements are therefore linked to OSaccounts
rather than to special DB2 accounts.1
Having a user ID does not necessarily give a user the right to
access the database. Although the details are implementation de-
pendent, you typically will find that the DBMS has extended the
1 For more information on DB2 security, see http://www.databasesecurity.
com/db2/db2cert2v8-a4.pdf.
Granting and Revoking Access Rights 265
GRANTcommand—which we will discuss shortly—to sup-
port user-level access. For example,
GRANT CONNECT TO user_id
grants the user the right to connect to the database. Connect
rights, however, do not give the user the right to create data-
base elements or access existing database elements. The right to
create database elements usually must be granted by someone
with DBA rights, using a syntaxsimilar to
GRANT RESOURCE TO user_id
Rights to database elements such as tables and views are given
using the SQL GRANT command (discussed in the next sec-
tion of this chapter).
DBA rights permit a user to grant connect and resource rights
to others, to create accounts, and access all database elements.
Any user ID with DBA rights also can assign them to another
user ID:
GRANT DBA TOuser_name
Because DBA rights have such broad access, in most cases they
will be restricted to only one or two user IDs.
When you create an element of database structure, the user
name under which you are working becomes that element’s
owner. The owner has the right to do anything to that element;
all other users have no rights at all. This means that if tables
and views are going toaccessible to other users, you must grant
them access rights.2
2 Some major DBMSs (for example, Oracle and DB2) also provide
support for multilevel security (MLS). An MLS scheme classifies data
into levels, such as top secret, secret, classified, and unclassified. Users are
then given clearance levels. A user can view data at or below his or her
clearance level and cannot change aclassification level to anything less
Granting and
Revoking Access
Rights


266 Chapter 12: Users and Access Rights
There are six types of access rights that you can grant:
◊ SELECT: Allows a user to retrieve data from a table or
view.
◊ INSERT: Allows a user to insert new rows into a table
or updatable view. Permission may be granted to spe-
cific columns rather than the entire databaseelement.
◊ UPDATE: Allows a user to modify rows in a table or
updatable view. Permission may be granted to specific
columns rather than the entire database element.
◊ DELETE: Allows a user to delete rows from a table or
updatable view.
◊ REFERENCES: Allows a user to reference a table as a
foreign key in a table he or she creates. Permission may
be granted to specific columns rather...