transactions, roles & privileges oracle and ansi standard sql
DESCRIPTION
Transactions, Roles & Privileges Oracle and ANSI Standard SQL. Lecture 11. Transactions, Roles & Privileges. Privileges Roles Granting Privileges Revoking Privileges Synonyms Creating Synonyms Droping Synonyms Design Structures ACID Compliance. - PowerPoint PPT PresentationTRANSCRIPT
Transactions, Roles & Transactions, Roles & PrivilegesPrivileges
Oracle and ANSI Standard SQLOracle and ANSI Standard SQL
Lecture 11Lecture 11
Copyright 2006Copyright 2006 Page Page 22
Transactions, Roles & Transactions, Roles & PrivilegesPrivileges
PrivilegesPrivileges RolesRoles Granting PrivilegesGranting Privileges Revoking PrivilegesRevoking Privileges SynonymsSynonyms
Creating SynonymsCreating Synonyms Droping SynonymsDroping Synonyms
Design StructuresDesign Structures ACID ComplianceACID Compliance
Copyright 2006Copyright 2006 Page Page 33
Transactions, Roles & Transactions, Roles & Privileges Privileges
Privileges: Granted to other usersPrivileges: Granted to other users System PrivilegesSystem Privileges
Session – Session – CREATE SESSIONCREATE SESSION, , ALTER SESSIONALTER SESSION.. Table – Table – CREATE TABLE, CREATE ANY TABLE, ALTER ANY CREATE TABLE, CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE, SELECT ANY TABLE, UPDATE TABLE, DROP ANY TABLE, SELECT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE, FLASHBACK ANY ANY TABLE, DELETE ANY TABLE, FLASHBACK ANY TABLETABLE..
Index – Index – CREATE ANY INDEX, ALTER ANY INDEX, DROP CREATE ANY INDEX, ALTER ANY INDEX, DROP ANY INDEXANY INDEX..
Sequence – Sequence – CREATE SEQUENCE, CREATE ANY SEQUENCE, CREATE SEQUENCE, CREATE ANY SEQUENCE, ALTER ANY SEQUENCE, DROP ANY SEQUENCEALTER ANY SEQUENCE, DROP ANY SEQUENCE..
View – View – CREATE VIEW, CREATE ANY VIEW, DROP ANY CREATE VIEW, CREATE ANY VIEW, DROP ANY VIEWVIEW..
Copyright 2006Copyright 2006 Page Page 44
Transactions, Roles & Transactions, Roles & Privileges Privileges
Privileges: Granted to other usersPrivileges: Granted to other users Object PrivilegesObject Privileges
Select - Enables another user to query data or a sequence Select - Enables another user to query data or a sequence value.value.
Insert - Enables another user to enter data from a table or Insert - Enables another user to enter data from a table or view.view.
Update - Enables another user to change data from a table Update - Enables another user to change data from a table or view.or view.
Delete - Enables another user to remove data from a table Delete - Enables another user to remove data from a table or view.or view.
Index – Enables another user to create indexes on a table.Index – Enables another user to create indexes on a table. Reference – Enables another user to reference a primary key Reference – Enables another user to reference a primary key
in a foreign key constraint.in a foreign key constraint. Execute - Enables another user to run a stored function, Execute - Enables another user to run a stored function,
procedure, or package.procedure, or package. Alter - Enables another user to modify a table or a sequence.Alter - Enables another user to modify a table or a sequence. All – Enables another user to have all priviliges on a table.All – Enables another user to have all priviliges on a table.
Copyright 2006Copyright 2006 Page Page 55
Transactions, Roles & Transactions, Roles & Privileges Privileges
Privileges: Granting ProcessPrivileges: Granting Process Object PrivilegesObject Privileges
You grant privileges by using the You grant privileges by using the GRANTGRANT command.command.
You revoke privileges by using the You revoke privileges by using the REVOKEREVOKE command. command.
Grant OptionGrant Option You grant privileges along with the right You grant privileges along with the right
to grant the same privilege(s) to other to grant the same privilege(s) to other users.users.
Copyright 2006Copyright 2006 Page Page 66
Transactions, Roles & Transactions, Roles & Privileges Privileges Roles: DefinedRoles: Defined
Are collections of privileges.Are collections of privileges. Can be granted to users the same Can be granted to users the same
way as individual privileges.way as individual privileges. Enable the DBA to manage sets of Enable the DBA to manage sets of
privileges and change them one privileges and change them one place with cascading impacts.place with cascading impacts.
Copyright 2006Copyright 2006 Page Page 77
Transactions, Roles & Transactions, Roles & PrivilegesPrivileges
Granting Privileges Granting Privileges
GRANT <GRANT <privilegeprivilege>>
ON <ON <objectobject>>
TO <TO <schemaschema>;>;
Copyright 2006Copyright 2006 Page Page 88
Transactions, Roles & Transactions, Roles & PrivilegesPrivileges
RevokingRevoking Privileges Privileges
REVOKE <REVOKE <privilegeprivilege>>
FROM <FROM <useruser>;>;
Copyright 2006Copyright 2006 Page Page 99
Transactions, Roles & Transactions, Roles & Privileges Privileges
Synonyms: DefinedSynonyms: Defined Are aliases that enable a relative naming.Are aliases that enable a relative naming. Replace absolute reference by schema Replace absolute reference by schema
name, a dot, and object name.name, a dot, and object name. Can be deployed as public or private Can be deployed as public or private
variants:variants: Private synonyms are only available in a single Private synonyms are only available in a single
schema:schema: They simplify calls to objects.They simplify calls to objects. They translate the synonym to an absolute reference.They translate the synonym to an absolute reference.
Public synonyms are available throughout the Public synonyms are available throughout the database instance.database instance.
They simplify calls to objects.They simplify calls to objects. They translate the synonym to an absolute reference.They translate the synonym to an absolute reference.
Copyright 2006Copyright 2006 Page Page 1010
Transactions, Roles & Transactions, Roles & PrivilegesPrivileges
Synonym: Creating public synonymsSynonym: Creating public synonyms
CREATE PUBLIC SYNONYM <CREATE PUBLIC SYNONYM <synonym_namesynonym_name>>
FOR <FOR <schema.object_nameschema.object_name>;>;
Copyright 2006Copyright 2006 Page Page 1111
Transactions, Roles & Transactions, Roles & PrivilegesPrivileges
Synonym: Creating private synonymsSynonym: Creating private synonyms
CREATE SYNONYM <CREATE SYNONYM <synonym_namesynonym_name>>
FOR <FOR <schema.object_nameschema.object_name>;>;
Copyright 2006Copyright 2006 Page Page 1212
Transactions, Roles & Transactions, Roles & PrivilegesPrivileges
Synonym: Dropping synonymsSynonym: Dropping synonyms
DROP SYNONYM <DROP SYNONYM <synonym_namesynonym_name>;>;
Copyright 2006Copyright 2006 Page Page 1313
Transactions, Roles & Transactions, Roles & PrivilegesPrivileges
Design Structures: Definers’ rightsDesign Structures: Definers’ rights Is the default when creating stored Is the default when creating stored
programs.programs. Means that the stored program executes Means that the stored program executes
with the same privileges as the defining user.with the same privileges as the defining user. Can mean that calling the stored programs Can mean that calling the stored programs
lets it run against any schema level data.lets it run against any schema level data. TypicallyTypically means that users only access a means that users only access a
slice of data in any schema, like a private slice of data in any schema, like a private virtual database.virtual database.
Copyright 2006Copyright 2006 Page Page 1414
Transactions, Roles & Transactions, Roles & PrivilegesPrivileges
Design Structures: Definers’ rightsDesign Structures: Definers’ rights
User #1
User #2
User #3
User #4
Copyright 2006Copyright 2006 Page Page 1515
Transactions, Roles & Transactions, Roles & PrivilegesPrivileges
Design Structures: Invokers’ rightsDesign Structures: Invokers’ rights Is the override when creating stored Is the override when creating stored
programs.programs. Means that the stored program executes Means that the stored program executes
with the local privileges, which generally with the local privileges, which generally differ from the definer’s privileges.differ from the definer’s privileges.
TypicallyTypically means that users only access means that users only access their own schema data, like a distributed or their own schema data, like a distributed or local database.local database.
Copyright 2006Copyright 2006 Page Page 1616
Transactions, Roles & Transactions, Roles & PrivilegesPrivileges
Design Structures: Invokers’ rightsDesign Structures: Invokers’ rights
User #1
User #2
User #3
User #4
Copyright 2006Copyright 2006 Page Page 1717
Transactions, Roles & Transactions, Roles & Privileges Privileges ACID: DefinedACID: Defined
AA – ATOMIC, which means that everything – ATOMIC, which means that everything or nothing happens.or nothing happens.
C C – CONSISTENT, which means that – CONSISTENT, which means that everything happens the same whether everything happens the same whether processed serially or in parallel.processed serially or in parallel.
II – ISOLATED, which means partial results – ISOLATED, which means partial results are hidden from other users.are hidden from other users.
DD – DURABLE, which means changes – DURABLE, which means changes become permanent when finalized.become permanent when finalized.
Copyright 2006Copyright 2006 Page Page 1818
Transactions, Roles & Transactions, Roles & Privileges Privileges
ACID: DML statementsACID: DML statements Single DML statements:Single DML statements:
Are transactions.Are transactions. Lock affected rows.Lock affected rows. Prevent others from updating locked rows.Prevent others from updating locked rows. Changes are only visible to the session making Changes are only visible to the session making
them until a them until a COMMITCOMMIT command is executed. command is executed. Locks are also released when a Locks are also released when a ROLLBACKROLLBACK
command is executed, which undoes the prior command is executed, which undoes the prior change.change.
Copyright 2006Copyright 2006 Page Page 1919
Transactions, Roles & Transactions, Roles & Privileges Privileges
ACID: Sets of DML statementsACID: Sets of DML statements Multiple DML statements:Multiple DML statements:
Are compound transactions, which means they Are compound transactions, which means they are made up of smaller transaction units.are made up of smaller transaction units.
Lock affected rows in more than one table or view.Lock affected rows in more than one table or view. Prevent others from updating locked rows.Prevent others from updating locked rows. Changes are only visible to the session making Changes are only visible to the session making
them until a them until a COMMITCOMMIT command is executed. command is executed. Locks are also released when a Locks are also released when a ROLLBACKROLLBACK
command is executed, which undoes the prior command is executed, which undoes the prior change.change.
Copyright 2006Copyright 2006 Page Page 2020
Transactions, Roles & Transactions, Roles & Privileges Privileges
ACID: Data Control Statements (DCL)ACID: Data Control Statements (DCL) DCL statements are:DCL statements are:
The The SAVEPOINTSAVEPOINT command, that sets a marker command, that sets a marker that enables undoing transactions only to a that enables undoing transactions only to a save point.save point.
The The COMMITCOMMIT command, which makes command, which makes permanent the data change.permanent the data change.
The The ROLLBACKROLLBACK command, which can undo command, which can undo everything since:everything since:
The last The last COMMITCOMMIT command. command. A specific A specific SAVEPOINTSAVEPOINT command. command.
Copyright 2006Copyright 2006 Page Page 2121
Transactions, Roles & Transactions, Roles & PrivilegesPrivileges
ACID: ACID: ROLLBACKROLLBACK command command
ROLLBACK [TO <ROLLBACK [TO <savepoint_namesavepoint_name>];>];
Copyright 2006Copyright 2006 Page Page 2222
Transactions, Roles & Transactions, Roles & Privileges Privileges
ACID: ACID: SAVEPOINTSAVEPOINT command command
SAVEPOINT <SAVEPOINT <savepoint_namesavepoint_name>;>;
Copyright 2006Copyright 2006 Page Page 2323
Transactions, Roles & Transactions, Roles & Privileges Privileges
ACID: ACID: COMMITCOMMIT command command
SAVEPOINT <SAVEPOINT <savepoint_namesavepoint_name>;>;
Copyright 2006Copyright 2006 Page Page 2424
SummarySummary
PrivilegesPrivileges RolesRoles Granting PrivilegesGranting Privileges Revoking PrivilegesRevoking Privileges SynonymsSynonyms
Creating SynonymsCreating Synonyms Droping SynonymsDroping Synonyms
Design StructuresDesign Structures ACID ComplianceACID Compliance