Transcript FROM

Transactions, Roles & Privileges
Oracle and ANSI Standard SQL
Lecture 11
Transactions, Roles & Privileges





Privileges
Roles
Granting Privileges
Revoking Privileges
Synonyms




Copyright 2006
Creating Synonyms
Droping Synonyms
Design Structures
ACID Compliance
Page 2
Transactions, Roles & Privileges
Privileges: Granted to other users

System Privileges





Session – CREATE SESSION, ALTER SESSION.
Table – CREATE TABLE, CREATE ANY TABLE, ALTER ANY
TABLE, DROP ANY TABLE, SELECT ANY TABLE, UPDATE
ANY TABLE, DELETE ANY TABLE, FLASHBACK ANY
TABLE.
Index – CREATE ANY INDEX, ALTER ANY INDEX, DROP
ANY INDEX.
Sequence – CREATE SEQUENCE, CREATE ANY SEQUENCE,
ALTER ANY SEQUENCE, DROP ANY SEQUENCE.
View – CREATE VIEW, CREATE ANY VIEW, DROP ANY
VIEW.
Copyright 2006
Page 3
Transactions, Roles & Privileges
Privileges: Granted to other users

Object Privileges









Select - Enables another user to query data or a sequence value.
Insert - Enables another user to enter data from a table or view.
Update - Enables another user to change data from a table or
view.
Delete - Enables another user to remove data from a table or
view.
Index – Enables another user to create indexes on a table.
Reference – Enables another user to reference a primary key in
a foreign key constraint.
Execute - Enables another user to run a stored function,
procedure, or package.
Alter - Enables another user to modify a table or a sequence.
All – Enables another user to have all priviliges on a table.
Copyright 2006
Page 4
Transactions, Roles & Privileges
Privileges: Granting Process

Object Privileges



You grant privileges by using the GRANT
command.
You revoke privileges by using the REVOKE
command.
Grant Option

You grant privileges along with the right to
grant the same privilege(s) to other users.
Copyright 2006
Page 5
Transactions, Roles & Privileges
Roles: Defined



Are collections of privileges.
Can be granted to users the same way as
individual privileges.
Enable the DBA to manage sets of
privileges and change them one place with
cascading impacts.
Copyright 2006
Page 6
Transactions, Roles & Privileges
Granting Privileges
GRANT <privilege>
ON <object>
TO <schema>;
Copyright 2006
Page 7
Transactions, Roles & Privileges
Revoking Privileges
REVOKE <privilege>
FROM <user>;
Copyright 2006
Page 8
Transactions, Roles & Privileges
Synonyms: Defined



Are aliases that enable a relative naming.
Replace absolute reference by schema name, a
dot, and object name.
Can be deployed as public or private variants:

Private synonyms are only available in a single
schema:



They simplify calls to objects.
They translate the synonym to an absolute reference.
Public synonyms are available throughout the
database instance.


Copyright 2006
They simplify calls to objects.
They translate the synonym to an absolute reference.
Page 9
Transactions, Roles & Privileges
Synonym: Creating public synonyms
CREATE PUBLIC SYNONYM <synonym_name>
FOR <schema.object_name>;
Copyright 2006
Page 10
Transactions, Roles & Privileges
Synonym: Creating private synonyms
CREATE SYNONYM <synonym_name>
FOR <schema.object_name>;
Copyright 2006
Page 11
Transactions, Roles & Privileges
Synonym: Dropping synonyms
DROP SYNONYM <synonym_name>;
Copyright 2006
Page 12
Transactions, Roles & Privileges
Design Structures: Definers’ rights




Is the default when creating stored programs.
Means that the stored program executes with
the same privileges as the defining user.
Can mean that calling the stored programs lets it
run against any schema level data.
Typically means that users only access a slice of
data in any schema, like a private virtual
database.
Copyright 2006
Page 13
Transactions, Roles & Privileges
Design Structures: Definers’ rights
Copyright 2006
User #1
User #3
User #2
User #4
Page 14
Transactions, Roles & Privileges
Design Structures: Invokers’ rights



Is the override when creating stored programs.
Means that the stored program executes with
the local privileges, which generally differ from
the definer’s privileges.
Typically means that users only access their own
schema data, like a distributed or local
database.
Copyright 2006
Page 15
Transactions, Roles & Privileges
Design Structures: Invokers’ rights
Copyright 2006
User #1
User #3
User #2
User #4
Page 16
Transactions, Roles & Privileges
ACID: Defined




A – ATOMIC, which means that everything or
nothing happens.
C – CONSISTENT, which means that everything
happens the same whether processed serially or
in parallel.
I – ISOLATED, which means partial results are
hidden from other users.
D – DURABLE, which means changes become
permanent when finalized.
Copyright 2006
Page 17
Transactions, Roles & Privileges
ACID: DML statements

Single DML statements:





Are transactions.
Lock affected rows.
Prevent others from updating locked rows.
Changes are only visible to the session making them
until a COMMIT command is executed.
Locks are also released when a ROLLBACK command
is executed, which undoes the prior change.
Copyright 2006
Page 18
Transactions, Roles & Privileges
ACID: Sets of DML statements

Multiple DML statements:





Are compound transactions, which means they are
made up of smaller transaction units.
Lock affected rows in more than one table or view.
Prevent others from updating locked rows.
Changes are only visible to the session making them
until a COMMIT command is executed.
Locks are also released when a ROLLBACK command
is executed, which undoes the prior change.
Copyright 2006
Page 19
Transactions, Roles & Privileges
ACID: Data Control Statements (DCL)

DCL statements are:



The SAVEPOINT command, that sets a marker that
enables undoing transactions only to a save point.
The COMMIT command, which makes permanent the
data change.
The ROLLBACK command, which can undo everything
since:


Copyright 2006
The last COMMIT command.
A specific SAVEPOINT command.
Page 20
Transactions, Roles & Privileges
ACID: ROLLBACK command
ROLLBACK [TO <savepoint_name>];
Copyright 2006
Page 21
Transactions, Roles & Privileges
ACID: SAVEPOINT command
SAVEPOINT <savepoint_name>;
Copyright 2006
Page 22
Transactions, Roles & Privileges
ACID: COMMIT command
SAVEPOINT <savepoint_name>;
Copyright 2006
Page 23
Summary





Privileges
Roles
Granting Privileges
Revoking Privileges
Synonyms




Copyright 2006
Creating Synonyms
Droping Synonyms
Design Structures
ACID Compliance
Page 24