Transcript lecture4

Chapters 19 and 20
 Ch. 19: By What Authority?
•
•
•
•
Users
Roles
Grant and revoke
Synonyms
 Ch. 20: Changing the Oracle Surroundings
•
•
•
•
Indexes
Clusters
Sequences
Tablespaces
1
Ch. 19: CREATE USER

CREATE USER
•
•

Changing passwords
•

Alter user username identified by password;
Password Management – determined by DBA created profiles.
•
•
•
•

Create user username identified by password;
Create user username identified externally;
Password lifetime
Grace period
Account lock rules
Password reuse rules
Moving to another user, connect sqlplus command
•
•
•
CONNECT
CONNECT username
CONNECT username / password
2
Ch. 19: CREATE ROLE

Roles manage sets of privileges.

CREATE ROLE
•

Create role rolename;
Standard Oracle Roles
•
•
•
CONNECT – connect to database and perform very limited functions.
RESOURCE – for basic users.
DBA – all system privileges.

Maximum roles allowed is set at startup,
max_enabled_roles parameter (30 on phoenix).

Roles can have passwords, but do not by default.
3
Ch. 19: GRANT and REVOKE

Grant for object privileges
GRANT {privilege, … | ALL} [ (column,…) ]
on object to {user | role}
[with grant option]
[with hierarchy option];

Grant for system privileges
GRANT {system privilege | role | ALL}
to {user | role} [, {user | role}, …]
[identified by password]
[with admin option];

Revoke takes privileges from roles or users.
REVOKE {system privilege | role | ALL}
[, {system privilege | role | ALL} …]
from {user | role} [, {user | role}, …];
4
Ch. 19: Synonyms, Examples, Other

What you can grant to other users
•
•
•

Synonyms provide for another name for an object.
•

CREATE [PUBLIC] SYNONYM SYNONYM FOR SCHEMA.OBJECT[@LINK];
Examples:
•
•
•
•
•

Tables: alter, references, index, on commit refresh, query rewrite, all
PL/SQL Procedures and Functions: execute
Sequences: select, alter
CREATE ROLE MYTEAM;
GRANT MYTEAM TO JOE, TOM, SUE;
GRANT SELECT ON MYTABLE TO MYTEAM;
GRANT UPDATE (COL1) ON MYTABLE TO MYTEAM;
CREATE PUBLIC SYNONYM TAB1 FOR MYSCHEMA.MYTABLE;
Advanced Options for Security by User
•
•
Virtual private database (VPD) adds a where clause to all commands issued by the
user to restrict data to only his view of the database.
Oracle Label Security uses security labels on all rows, users only have access to
those in their hierarchy.
5
Ch. 20: CREATE INDEX

Indexes:
•
Contains values of data from a table and their location
•
Pro: Used to speed data retrieval, also can enforce uniqueness.
•
Con: Slows updates and deletes.
•
When to use: if columns are typically used in where clause searches
•
Primary keys and unique constraints create a unique index automatically.
•
NULL values are not indexed.

Syntax:
CREATE [BITMAP|UNIQUE] INDEX index ON table(COL1 [, COL2…] )
[REVERSE];

Other Oracle Index Features:
•
Reverse indexes builds an index on the indexed column in reverse byte
order thus increasing the dispersal of values.
•
Bitmap indexes, new in 8 or 8i ?, associates a bitmap to values and only
stores the bitmap in the index. Use with low cardinality values (e.g. T/F)
•
Function based indexes, new in 8i. Normal indexes are not used if a
function is used on the where clause column that you’re searching on.
6
Ch. 20: CREATE CLUSTER

Clusters:
• Stores different tables physically together.
• The cluster key is the column that the data would normally be
joined together with.

Syntax Example:
Create cluster BOOKandAUTHOR(col1 varchar2(1000));
Create table BOOKSHELF
(title
varchar2(100) primary key,
… )
cluster BOOKandAUTHOR(title);
7
Ch. 20: CREATE SEQUENCE

Sequences:
• Creates a unique sequentially valued number.
• Used during insert and update commands usually.
• Typically used to create a primary key.
• NextVal and CurrVal get the next/current value of the sequence.

Syntax:
•
•
•
Create sequence myseq increment by 1 start with 1;
Insert into CUSTOMER (id, name) values (myseq.nextval, ‘a’);
Update OTHERTABLE set id = myseq.currval;
8
Ch. 20: Tablespaces and terminology






Tablespace
• Tablespaces are a logical organization of space.
• Tablespaces owns the database’s datafiles.
• Database objects are stored within tablespaces.
Database: a logical collection of shared data stored in tablespaces.
File: A physical datafile belonging to a single tablespace.
Segment: A set of one or more extents that contains all the data for a
specific structure within a tablespace.
Extent: A set of contiguous data blocks with in a database that make
up a segment.
Block: One or more physical file blocks allocated from an existing
datafile.
9
Ch. 20: ERD
Database
 Entity-Relationship
Diagram showing:
 Database
 Tablespace
 Extents
 Segments
 Files
 Blocks
Operating
System Files
Tablespace
Extent
Block
Free
Used
Segment
10
Data
Index
Cluster
Rollback
Temporary
Cache
Ch. 20: Tablespace clause syntax
 Tablespace Clause: indicates Tablespace and other storage options for
tables, indexes, constraints.
 Storage Clause: indicates how extents are managed, overrides the
default storage parameters of tablespace.
 Syntax:
CREATE TABLE tablename
(column_a
type NOT NULL,
column_btype,...
)
STORAGE ( INITIAL 100K
NEXT 20K
MINEXTENTS 1
MAXEXTENTS 99
PCTINCREASE 50 )
TABLESPACE USER_DATA
PCTFREE 5
PCTUSED 30;
11
Ch. 20: Tablespace and Segment Example
SQL> select file_name, tablespace_name, bytes, blocks from dba_data_files
FILE_NAME
-----------------------------------------/opt/oracle/u1/oradata/OS80/users01.dbf
/opt/oracle/u1/oradata/OS80/tools01.dbf
/opt/oracle/u1/oradata/OS80/temp01.dbf
/opt/oracle/u1/oradata/OS80/rbs01.dbf
/opt/oracle/u1/oradata/OS80/system01.dbf
/opt/oracle/u1/oradata/OS80/n311_t1.dbf
/opt/oracle/u1/oradata/OS80/csgrad_t1.dbf
SQL>
1
2
3*
TABLESPACE_NAME
BYTES
BLOCKS
--------------- ---------- ---------USERS
1048576
512
TOOLS
26214400
12800
TEMP
6291456
3072
RBS
15728640
7680
SYSTEM
83886080
40960
USERDATA_N311
95180800
46475
USERDATA_GRAD
10485760
5120
l
select tablespace_name, segment_name, segment_type, bytes, blocks, extents
from dba_segments
where owner = 'SCOTT'
TABLESPACE_NAME
--------------SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SYSTEM
SEGMENT_NA
---------BONUS
SALGRADE
WORKER
TEMP_USERS
LEDGER
DEPT
EMP
PK_DEPT
PK_EMP
SEGMENT_TYPE
BYTES
BLOCKS
EXTENTS
------------------ ---------- ---------- ---------TABLE
10240
5
1
TABLE
10240
5
1
TABLE
10240
5
1
TABLE
10240
5
1
TABLE
20480
10
2
TABLE
10240
5
1
TABLE
10240
5
1
INDEX
10240
5
1
INDEX
10240
5
1
9 rows selected.
12