Sequences in Database Management Systems
Chapter 5
Sequences
Sequences
Logically represents subsets of data from one or
more tables
View
Generates numeric values
Sequence
Basic unit of storage; composed of rows
Table
Gives alternative names to objects
Synonym
Improves the performance of some queries
Index
Description
Object
Sequences
•
A sequence:
–
Can automatically generate unique numbers or to recycle and
use the same numbers again.
–
is a user-created database object that can be shared by
multiple users to generate integers.
–
Can be used to create a primary key value
–
A sequence is generated and incremented (or decremented)
by an internal Oracle routine. This can be time-saving
because it Replaces application code
–
Speeds up the efficiency of accessing sequence values when
cached in memory
–
Sequence numbers are stored and generated independent of
tables. Therefore, the same sequence can be used for
multiple tables.
CREATE
SEQUENCE
Statement:
Syntax
•
Define a sequence to generate sequential numbers
automatically:
CREATE SEQUENCE
sequence
[INCREMENT BY
n
]
[START WITH
n
]
[{MAXVALUE n
|
NOMAXVALUE
}]
[{MINVALUE n
|
NOMINVALUE
}]
[{CYCLE | NOCYCLE
}]
[{CACHE n
| NOCACHE}];
CREATE
SEQUENCE
Statement:
Syntax
–
In the syntax:
•
sequence
Is the name of the sequence generator
•
INCREMENT
BY
n
Specifies the interval between
sequence numbers, where
n
is an integer (If this clause is omitted,
the sequence
increments by 1.)
•
START
WITH
n
Specifies the first sequence
number to be generated (If this clause is omitted, the sequence
starts with 1.)
•
MAXVALUE
n
Specifies the maximum value the
sequence can generate
•
NOMAXVALUE
Specifies a maximum value of 10^27 for
an ascending sequence and –1 for a descending sequence (This is
the default option.)
•
MINVALUE
n
Specifies the minimum sequence value
•
NOMINVALUE
Specifies a minimum value of 1 for an
ascending sequence and –(10^26) for a descending sequence (This
is the default option.)
CREATE
SEQUENCE
Statement:
Syntax
CYCLE | NOCYCLE
Specifies whether the
sequence continues to generate values after reaching
its maximum or minimum value (
NOCYCLE
is the
default option.)
CACHE
n
| NOCACHE
Specifies how many values
the Oracle server pre-allocates and keeps in memory
(By default, the Oracle server caches 20 values.)
To improve performance,
SQL
Server pre-allocates the
number of
sequence
numbers specified by
the
CACHE
argument. For an example, a
new
sequence
is created with a starting value of 1 and
a
cache
size of 15. When the first value is needed,
values 1 through 15 are made available from memory.
Creating a Sequence
–
Create a sequence named
DEPT_DEPTID_SEQ
to be
used for the primary key of the
DEPARTMENTS
table.
–
Do not use the
CYCLE
option.
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
NEXTVAL
and
CURRVAL
•
After you create your sequence, it generates
sequential numbers for use in your tables.
Reference the sequence values by using the
NEXTVAL
and
CURRVAL
•
NEXTVAL
returns the next available sequence value.
It returns a unique value every time it is referenced,
even for different users.
•
CURRVAL
obtains the current sequence value.
•
NEXTVAL
must be issued for that sequence before
CURRVAL
contains a value.
R
u
l
e
s
f
o
r
U
s
i
n
g
N
E
X
T
V
A
L
a
n
d
C
U
R
R
V
A
L
–
You can use
NEXTVAL
and
CURRVAL
in the following
contexts:
•
The
SELECT
list of a
SELECT
statement that is not part of
a subquery
•
The
SELECT
list of a subquery in an
INSERT
statement
•
The
VALUES
clause of an
INSERT
statement
•
The
SET
clause of an
UPDATE
statement
–
You cannot use
NEXTVAL
and
CURRVAL
in the
following contexts:
•
The
SELECT
list of a view
•
A
SELECT
statement with the
DISTINCT
keyword
•
A
SELECT
statement with
GROUP
BY
,
HAVING
, or
ORDER
BY
clauses
•
A subquery in a
SELECT
,
DELETE
, or
UPDATE
statement
•
The
DEFAULT
expression in a
CREATE
TABLE
or
ALTER
TABLE
statement
Using a Sequence
–
Insert a new department named “Support” in
location ID 2500:
–
View the current value for the
DEPT_DEPTID_SEQ
sequence:
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL,
'Support', 2500);
SELECT
dept_deptid_seq.CURRVAL
FROM
dual;
Caching Sequence Values
–
Caching sequence values in memory gives faster
access to those values.
–
Gaps in sequence values can occur when:
•
A rollback occurs
•
The system crashes
•
A sequence is used in another table
Modifying a Sequence
•
Change the increment value, maximum value,
minimum value, cycle option, or cache option:
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
Guidelines for Modifying
a Sequence
–
You must be the owner or have the
ALTER
privilege
for the sequence.
–
Only future sequence numbers are affected.
–
The sequence must be dropped and re-created to
restart the sequence at a different number.
–
Some validation is performed. For example, a new
MAXVALUE
that is less than the current sequence
number cannot be imposed.
–
To remove a sequence, use the
DROP
statement:
DROP SEQUENCE dept_deptid_seq;
D
a
t
a
b
a
s
e
S
e
c
u
r
i
t
y
D
a
t
a
b
a
s
e
S
e
c
u
r
i
t
y
•
Multi-user database systems like Oracle
include security to control how the
database is accessed and used for
example security Mechanisms:
–
Prevent unauthorized database access
–
Prevent unauthorized access to schema
objects
–
Control disk usage
–
Audit user actions
D
a
t
a
b
a
s
e
s
e
c
u
r
i
t
y
s
y
s
t
e
m
s
e
c
u
r
i
t
y
D
a
t
a
s
e
c
u
r
i
t
y
S
y
s
t
e
m
S
e
c
u
r
i
t
y
c
o
v
e
r
s
a
c
c
e
s
s
a
n
d
u
s
e
o
f
t
h
e
d
a
t
a
b
a
s
e
a
t
t
h
e
s
y
s
t
e
m
l
e
v
e
l
,
s
u
c
h
a
s
:
•
the username and password
•
the disk space allocated to users,
•
and the system operations that users can
perform
D
a
t
a
s
e
c
u
r
i
t
y
c
o
v
e
r
s
a
c
c
e
s
s
a
n
d
u
s
e
o
f
t
h
e
d
a
t
a
b
a
s
e
o
b
j
e
c
t
s
a
n
d
t
h
e
a
c
t
i
o
n
s
t
h
a
t
t
h
o
s
e
u
s
e
r
s
c
a
n
h
a
v
e
o
n
t
h
e
o
b
j
e
c
t
s
s
u
c
h
a
s
s
e
l
e
c
t
i
n
g
d
a
t
a
f
r
o
m
a
t
a
b
l
e
o
r
r
e
t
r
i
e
v
i
n
g
a
v
a
l
u
e
f
r
o
m
a
s
e
q
u
e
n
c
e
P
r
i
v
i
l
e
g
e
s
•
P
r
i
v
i
l
e
g
e
s
a
r
e
t
h
e
r
i
g
h
t
t
o
e
x
e
c
u
t
e
p
a
r
t
i
c
u
l
a
r
S
Q
L
s
t
a
t
e
m
e
n
t
s
.
T
h
e
d
a
t
a
b
a
s
e
a
d
m
i
n
i
s
t
r
a
t
o
r
(
D
B
A
)
i
s
a
h
i
g
h
-
l
e
v
e
l
u
s
e
r
w
i
t
h
t
h
e
a
b
i
l
i
t
y
t
o
g
r
a
n
t
u
s
e
r
s
a
c
c
e
s
s
t
o
t
h
e
d
a
t
a
b
a
s
e
a
n
d
i
t
s
o
b
j
e
c
t
s
•
System privileges: Gaining access to the
database
•
O
b
j
e
c
t
p
r
i
v
i
l
e
g
e
s
:
M
a
n
i
p
u
l
a
t
i
n
g
t
h
e
c
o
n
t
e
n
t
o
f
t
h
e
d
a
t
a
b
a
s
e
o
b
j
e
c
t
s
s
c
h
e
m
a
•
A schema is a collection of objects, such
as tables, views, and sequences.
•
The schema is owned by a database user
and has the same name as that user.
S
y
s
t
e
m
P
r
i
v
i
l
e
g
e
s
•
M
o
r
e
t
h
a
n
1
0
0
p
r
i
v
i
l
e
g
e
s
a
r
e
a
v
a
i
l
a
b
l
e
.
•
T
h
e
d
a
t
a
b
a
s
e
a
d
m
i
n
i
s
t
r
a
t
o
r
h
a
s
h
i
g
h
-
l
e
v
e
l
s
y
s
t
e
m
p
r
i
v
i
l
e
g
e
s
f
o
r
t
a
s
k
s
s
u
c
h
a
s
:
–
C
r
e
a
t
i
n
g
n
e
w
u
s
e
r
s
–
R
e
m
o
v
i
n
g
u
s
e
r
s
–
R
e
m
o
v
i
n
g
t
a
b
l
e
s
–
B
a
c
k
i
n
g
u
p
t
a
b
l
e
s
C
r
e
a
t
i
n
g
U
s
e
r
s
•
The DBA creates the user by executing
the CREATE USER statement.
•
The user does not have any privileges at
this point.
•
The DBA can then grant privileges to that
user.
•
These privileges determine what the
user can do at the database level.
C
r
e
a
t
i
n
g
U
s
e
r
s
•
The syntax for creating a user is:
C
R
E
A
T
E
U
S
E
R
u
s
e
r
I
D
E
N
T
I
F
I
E
D
B
Y
p
a
s
s
w
o
r
d
D
E
F
A
U
L
T
T
A
B
L
E
S
P
A
C
E
s
y
s
t
e
m
T
E
M
P
O
R
A
R
Y
T
A
B
L
E
S
P
A
C
E
t
e
m
p
Q
U
O
T
A
U
N
L
I
M
I
T
E
D
o
n
s
y
s
t
e
m
;
•
Example:
C
R
E
A
T
E
U
S
E
R
d
e
m
o
I
D
E
N
T
I
F
I
E
D
B
Y
d
e
m
o
D
E
F
A
U
L
T
T
A
B
L
E
S
P
A
C
E
s
y
s
t
e
m
T
E
M
P
O
R
A
R
Y
T
A
B
L
E
S
P
A
C
E
t
e
m
p
Q
U
O
T
A
U
N
L
I
M
I
T
E
D
o
n
s
y
s
t
e
m
;
U
s
e
r
c
r
e
a
t
e
d
.
U
s
e
r
S
y
s
t
e
m
P
r
i
v
i
l
e
g
e
s
•
O
n
c
e
a
u
s
e
r
i
s
c
r
e
a
t
e
d
,
t
h
e
D
B
A
c
a
n
g
r
a
n
t
s
p
e
c
i
f
i
c
s
y
s
t
e
m
p
r
i
v
i
l
e
g
e
s
t
o
a
u
s
e
r
.
G
R
A
N
T
p
r
i
v
i
l
e
g
e
T
O
u
s
e
r
;
•
A
n
a
p
p
l
i
c
a
t
i
o
n
d
e
v
e
l
o
p
e
r
,
f
o
r
e
x
a
m
p
l
e
,
m
a
y
h
a
v
e
t
h
e
f
o
l
l
o
w
i
n
g
s
y
s
t
e
m
p
r
i
v
i
l
e
g
e
s
:
–
C
R
E
A
T
E
S
E
S
S
I
O
N
–
C
R
E
A
T
E
T
A
B
L
E
–
C
R
E
A
T
E
S
E
Q
U
E
N
C
E
–
C
R
E
A
T
E
V
I
E
W
–
C
R
E
A
T
E
P
R
O
C
E
D
U
R
E
U
s
e
r
S
y
s
t
e
m
P
r
i
v
i
l
e
g
e
s
G
r
a
n
t
i
n
g
S
y
s
t
e
m
P
r
i
v
i
l
e
g
e
s
•
T
h
e
D
B
A
c
a
n
g
r
a
n
t
a
u
s
e
r
s
p
e
c
i
f
i
c
s
y
s
t
e
m
p
r
i
v
i
l
e
g
e
s
.
E
x
a
m
p
l
e
:
•
GRANT create session, create table,
create sequence, create view
TO Demo;
W
h
a
t
I
s
a
R
o
l
e
?
•
A role is a named group of related
privileges that can be granted to the user.
•
This method makes it easier to revoke and
maintain privileges.
•
user can have access to several roles, and
several users can be assigned the same
role
C
r
e
a
t
i
n
g
a
n
d
A
s
s
i
g
n
i
n
g
a
R
o
l
e
•
F
i
r
s
t
,
t
h
e
D
B
A
m
u
s
t
c
r
e
a
t
e
t
h
e
r
o
l
e
.
T
h
e
n
t
h
e
D
B
A
c
a
n
a
s
s
i
g
n
p
r
i
v
i
l
e
g
e
s
t
o
t
h
e
r
o
l
e
a
n
d
u
s
e
r
s
t
o
t
h
e
r
o
l
e
.
S
y
n
t
a
x
C
R
E
A
T
E
R
O
L
E
r
o
l
e
;
C
r
e
a
t
i
n
g
a
n
d
G
r
a
n
t
i
n
g
P
r
i
v
i
l
e
g
e
s
t
o
a
R
o
l
e
•
C
r
e
a
t
e
a
r
o
l
e
C
R
E
A
T
E
R
O
L
E
m
a
n
a
g
e
r
;
•
G
r
a
n
t
p
r
i
v
i
l
e
g
e
s
t
o
a
r
o
l
e
G
R
A
N
T
c
r
e
a
t
e
t
a
b
l
e
,
c
r
e
a
t
e
v
i
e
w
T
O
m
a
n
a
g
e
r
;
•
G
r
a
n
t
a
r
o
l
e
t
o
u
s
e
r
s
G
R
A
N
T
m
a
n
a
g
e
r
T
O
M
a
h
a
,
N
o
r
a
;
O
b
j
e
c
t
P
r
i
v
i
l
e
g
e
s
•
An object privilege is a privilege or right to
perform a particular action on a specific
(object) table, view, sequence, or procedure
•
Each object has a particular set of grantable
privileges. The table in the next slide lists the
privileges for various objects
O
b
j
e
c
t
P
r
i
v
i
l
e
g
e
s
O
b
j
e
c
t
P
r
i
v
i
l
e
g
e
s
•
Object privileges vary from object to object.
•
An owner has all the privileges on the object.
•
A
n
o
w
n
e
r
c
a
n
g
i
v
e
s
p
e
c
i
f
i
c
p
r
i
v
i
l
e
g
e
s
o
n
t
h
a
t
o
w
n
e
r
’
s
o
b
j
e
c
t
.
•
S
y
n
t
a
x
:
G
R
A
N
T
o
b
j
e
c
t
_
p
r
i
v
i
l
e
g
e
[
(
c
o
l
u
m
n
s
)
]
O
N
o
b
j
e
c
t
T
O
u
s
e
r
[
W
I
T
H
G
R
A
N
T
O
P
T
I
O
N
]
;
•
If the grant includes WITH GRANT OPTION, then the
grantee can further grant the object privilege to other
users; otherwise, the grantee can use the privilege but
cannot grant it to other users.
G
r
a
n
t
i
n
g
O
b
j
e
c
t
P
r
i
v
i
l
e
g
e
s
•
G
r
a
n
t
q
u
e
r
y
p
r
i
v
i
l
e
g
e
s
o
n
t
h
e
E
M
P
L
O
Y
E
E
S
t
a
b
l
e
.
G
R
A
N
T
s
e
l
e
c
t
O
N
e
m
p
l
o
y
e
e
s
T
O
n
o
r
a
h
,
s
a
r
a
h
;
•
G
r
a
n
t
p
r
i
v
i
l
e
g
e
s
t
o
u
p
d
a
t
e
s
p
e
c
i
f
i
c
c
o
l
u
m
n
s
t
o
u
s
e
r
s
a
n
d
r
o
l
e
s
.
G
R
A
N
T
u
p
d
a
t
e
(
d
e
p
a
r
t
m
e
n
t
_
n
a
m
e
,
l
o
c
a
t
i
o
n
_
i
d
)
O
N
d
e
p
a
r
t
m
e
n
t
s
T
O
d
e
m
o
,
m
a
n
a
g
e
r
;
U
s
i
n
g
t
h
e
W
I
T
H
G
R
A
N
T
O
P
T
I
O
N
a
n
d
P
U
B
L
I
C
K
e
y
w
o
r
d
s
•
G
i
v
e
a
u
s
e
r
a
u
t
h
o
r
i
t
y
t
o
p
a
s
s
a
l
o
n
g
p
r
i
v
i
l
e
g
e
s
.
G
R
A
N
T
s
e
l
e
c
t
,
i
n
s
e
r
t
O
N
d
e
p
a
r
t
m
e
n
t
s
T
O
d
e
m
o
W
I
T
H
G
R
A
N
T
O
P
T
I
O
N
;
•
A
l
l
o
w
a
l
l
u
s
e
r
s
o
n
t
h
e
s
y
s
t
e
m
t
o
q
u
e
r
y
d
a
t
a
f
r
o
m
A
l
i
c
e
’
s
D
E
P
A
R
T
M
E
N
T
S
t
a
b
l
e
.
G
R
A
N
T
s
e
l
e
c
t
O
N
a
l
i
c
e
.
d
e
p
a
r
t
m
e
n
t
s
T
O
P
U
B
L
I
C
;
G
u
i
d
e
l
i
n
e
s
•
To grant privileges on an object, the object must
be in your own schema, or you must have been
granted the object privileges WITH GRANT
OPTION .
• An object owner can grant any object privilege
on the object to any other user or role of the
database.
• The owner of an object automatically acquires
all object privileges on that object.
H
o
w
t
o
R
e
v
o
k
e
O
b
j
e
c
t
P
r
i
v
i
l
e
g
e
s
•
Remove privileges granted to other users
by using the REVOKE statement. When
you use the REVOKEstatement you
prevent the user from doing specific
actions depending on the privileges you
revoke from the user.
H
o
w
t
o
R
e
v
o
k
e
O
b
j
e
c
t
P
r
i
v
i
l
e
g
e
s
•
S
y
n
t
a
x
:
R
E
V
O
K
E
p
r
i
v
i
l
e
g
e
,
A
L
L
O
N
o
b
j
e
c
t
F
R
O
M
u
s
e
r
,
r
o
l
e
,
P
U
B
L
I
C
;
E
x
a
m
p
l
e
:
R
E
V
O
K
E
s
e
l
e
c
t
,
i
n
s
e
r
t
O
N
d
e
p
a
r
t
m
e
n
t
s
F
R
O
M
d
e
m
o
;
H
o
w
t
o
R
e
v
o
k
e
O
b
j
e
c
t
P
r
i
v
i
l
e
g
e
s
•
P
r
i
v
i
l
e
g
e
s
g
r
a
n
t
e
d
t
o
o
t
h
e
r
s
t
h
r
o
u
g
h
t
h
e
W
I
T
H
G
R
A
N
T
O
P
T
I
O
N
c
l
a
u
s
e
a
r
e
a
l
s
o
r
e
v
o
k
e
d
.
•
For example, if user A grants SELECT privilege on a
table to user B including the WITH GRANT OPTION
clause, user B can grant to user C the SELECT privilege
with the WITH GRANT OPTION clause as well,
and user C can then grant to user D the SELECT
privilege. If user A revokes privilege from user B, then
the privileges granted to users C and D are also
revoked.
Sequences in a database are objects that generate unique numeric values automatically. They can be used to create primary key values and improve query performance. By defining sequences using the CREATE SEQUENCE statement in SQL, you can control the generation of sequential numbers with options like INCREMENT BY, START WITH, MAXVALUE, MINVALUE, CYCLE, and CACHE. Sequences provide a convenient way to manage and generate integer values independently of tables, making them a valuable tool for database administrators.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Chapter 5 Sequences
Sequences Object Description Table Basic unit of storage; composed of rows View Logically represents subsets of data from one or more tables Sequence Generates numeric values Index Improves the performance of some queries Synonym Gives alternative names to objects
Sequences A sequence: Can automatically generate unique numbers or to recycle and use the same numbers again. is a user-created database object that can be shared by multiple users to generate integers. Can be used to create a primary key value A sequence is generated and incremented (or decremented) by an internal Oracle routine. This can be time-saving because it Replaces application code Speeds up the efficiency of accessing sequence values when cached in memory Sequence numbers are stored and generated independent of tables. Therefore, the same sequence can be used for multiple tables. 2 4 6 8 10 1 3 5 7 9
CREATE SEQUENCE Statement: Syntax Define a sequence to generate sequential numbers automatically: CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}];
CREATE SEQUENCE Statement: Syntax In the syntax: sequence Is the name of the sequence generator INCREMENT BY n sequence numbers, where n is an integer (If this clause is omitted, the sequence START WITH n number to be generated (If this clause is omitted, the sequence starts with 1.) MAXVALUE n Specifies the maximum value the sequence can generate NOMAXVALUE Specifies a maximum value of 10^27 for an ascending sequence and 1 for a descending sequence (This is the default option.) MINVALUE n Specifies the minimum sequence value NOMINVALUE Specifies a minimum value of 1 for an ascending sequence and (10^26) for a descending sequence (This is the default option.) Specifies the interval between increments by 1.) Specifies the first sequence
CREATE SEQUENCE Statement: Syntax CYCLE | NOCYCLE sequence continues to generate values after reaching its maximum or minimum value (NOCYCLE is the default option.) CACHE n | NOCACHE the Oracle server pre-allocates and keeps in memory (By default, the Oracle server caches 20 values.) To improve performance, SQL Server pre-allocates the number of sequence numbers specified by the CACHE argument. For an example, a new sequence is created with a starting value of 1 and a cache size of 15. When the first value is needed, values 1 through 15 are made available from memory. Specifies whether the Specifies how many values
Creating a Sequence Create a sequence named DEPT_DEPTID_SEQ to be used for the primary key of the DEPARTMENTS table. Do not use the CYCLE option. CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE;
NEXTVAL and CURRVAL After you create your sequence, it generates sequential numbers for use in your tables. Reference the sequence values by using the NEXTVAL and CURRVAL NEXTVAL returns the next available sequence value. It returns a unique value every time it is referenced, even for different users. CURRVAL obtains the current sequence value. NEXTVAL must be issued for that sequence before CURRVAL contains a value.
Rules for Using NEXTVAL and CURRVAL You can use NEXTVAL and CURRVAL in the following contexts: The SELECT list of a SELECT statement that is not part of a subquery The SELECT list of a subquery in an INSERT statement The VALUES clause of an INSERT statement The SET clause of an UPDATE statement You cannot use NEXTVAL and CURRVAL in the following contexts: The SELECT list of a view A SELECT statement with the DISTINCT keyword A SELECT statement with GROUP BY, HAVING, or ORDER BY clauses A subquery in a SELECT, DELETE, or UPDATE statement The DEFAULT expression in a CREATE TABLE or ALTER TABLE statement
Using a Sequence Insert a new department named Support in location ID 2500: INSERT INTO departments(department_id, department_name, location_id) VALUES (dept_deptid_seq.NEXTVAL, 'Support', 2500); View the current value for the DEPT_DEPTID_SEQ sequence: SELECT FROM dept_deptid_seq.CURRVAL dual;
Caching Sequence Values Caching sequence values in memory gives faster access to those values. Gaps in sequence values can occur when: A rollback occurs The system crashes A sequence is used in another table
Modifying a Sequence Change the increment value, maximum value, minimum value, cycle option, or cache option: ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE;
Guidelines for Modifying a Sequence You must be the owner or have the ALTER privilege for the sequence. Only future sequence numbers are affected. The sequence must be dropped and re-created to restart the sequence at a different number. Some validation is performed. For example, a new MAXVALUE that is less than the current sequence number cannot be imposed. To remove a sequence, use the DROP statement: DROP SEQUENCE dept_deptid_seq;
Database Security Multi-user database systems like Oracle include security to control how the database is accessed and used for example security Mechanisms: Prevent unauthorized database access Prevent unauthorized access to schema objects Control disk usage Audit user actions
Database security Data security system security
System Security covers access and use of the database at the system level, such as: the username and password the disk space allocated to users, and the system operations that users can perform
Data security covers access and use of the database objects and the actions that those users can have on the objects such as selecting data from a table or retrieving a value from a sequence
Privileges Privileges are the right to execute particular SQL statements. The database administrator (DBA) is a high-level user with the ability to grant users access to the database and its objects System privileges: Gaining access to the database Object privileges: Manipulating the content of the database objects
schema A schema is a collection of objects, such as tables, views, and sequences. The schema is owned by a database user and has the same name as that user.
System Privileges More than 100 privileges are available. The database administrator has high- level system privileges for tasks such as: Creating new users Removing users Removing tables Backing up tables
System Privilege Operations Authorized CREATE USER Grantee can create other Oracle users (a privilege required for a DBA role). DROP USER Grantee can drop another user. DROP ANY TABLE Grantee can drop a table in any schema. Grantee can back up any table in any schema with the export utility Grantee can create tables in any schema. BACKUP ANY TABLE CREATE ANY TABLE SELECT ANY TABLE Grantee can query tables, views, or snapshots in any schema
Creating Users The DBA creates the user by executing the CREATE USER statement. The user does not have any privileges at this point. The DBA can then grant privileges to that user. These privileges determine what the user can do at the database level.
Creating Users The syntax for creating a user is: CREATE USER user IDENTIFIED BY password DEFAULT TABLESPACE system TEMPORARY TABLESPACE temp QUOTA UNLIMITED on system; Example: CREATE USER demo IDENTIFIED BY demo DEFAULT TABLESPACE system TEMPORARY TABLESPACE temp QUOTA UNLIMITED on system; User created.
User System Privileges Once a user is created, the DBA can grant specific system privileges to a user. GRANT privilege TO user; An application developer, for example, may have the following system privileges: CREATE SESSION CREATE TABLE CREATE SEQUENCE CREATE VIEW CREATE PROCEDURE
Granting System Privileges The DBA can grant a user specific system privileges. Example: GRANT create session, create table, GRANT create session, create table, create sequence, create view create sequence, create view TO TO Demo; Demo;
What Is a Role? A role is a named group of related privileges that can be granted to the user. This method makes it easier to revoke and maintain privileges. user can have access to several roles, and several users can be assigned the same role
Creating and Assigning a Role First, the DBA must create the role. Then the DBA can assign privileges to the role and users to the role. Syntax CREATE ROLE role;
Creating and Granting Privileges to a Role Create a role CREATE ROLE manager; Grant privileges to a role GRANT create table, create view TO manager; Grant a role to users GRANT manager TO Maha, Nora;
Object Privileges An object privilege is a privilege or right to perform a particular action on a specific (object) table, view, sequence, or procedure Each object has a particular set of grantable privileges. The table in the next slide lists the privileges for various objects
Object Privileges Object privileges vary from object to object. An owner has all the privileges on the object. An owner can give specific privileges on that owner s object. Syntax: GRANT object_privilege [(columns)] ON object TO user [WITH GRANT OPTION]; If the grant includes WITH GRANT OPTION, then the grantee can further grant the object privilege to other users; otherwise, the grantee can use the privilege but cannot grant it to other users.
Granting Object Privileges Grant query privileges on the EMPLOYEES table. GRANT select ON employees TO norah, sarah; Grant privileges to update specific columns to users and roles. GRANT update (department_name, location_id) ON departments TO demo, manager;
Using the WITH GRANT OPTION and PUBLIC Keywords Give a user authority to pass along privileges. GRANT select, insert ON departments TO demo WITH GRANT OPTION; Allow all users on the system to query data from Alice s DEPARTMENTS table. GRANT select ON alice.departments TO PUBLIC;
Guidelines To grant privileges on an object, the object must be in your own schema, or you must have been granted the object privileges WITH GRANT OPTION . An object owner can grant any object privilege on the object to any other user or role of the database. The owner of an object automatically acquires all object privileges on that object.
How to Revoke Object Privileges Remove privileges granted to other users by using the REVOKE statement. When you use the REVOKEstatement you prevent the user from doing specific actions depending on the privileges you revoke from the user.
How to Revoke Object Privileges Syntax: REVOKE privilege ,ALL ON object FROM user,role,PUBLIC; Example: REVOKE select, insert ON departments FROM demo;
How to Revoke Object Privileges Privileges granted to others through the WITH GRANT OPTION clause are also revoked. For example, if user A grants SELECT privilege on a table to user B including the WITH GRANT OPTION clause, user B can grant to user C the SELECT privilege with the WITH GRANT OPTION clause as well, and user C can then grant to user D the SELECT privilege. If user A revokes privilege from user B, then the privileges granted to users C and D are also revoked.
