Transactions, Roles & Privileges Oracle and ANSI Standard SQL

1. Transactions, Roles & PrivilegesOracle and ANSI Standard SQL Lecture 11

2. Transactions, Roles & Privileges • Privileges • Roles • Granting Privileges • Revoking Privileges • Synonyms • Creating Synonyms • Droping Synonyms • Design Structures • ACID Compliance

3. 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.

4. 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.

5. 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.

6. 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.

7. Transactions, Roles & PrivilegesGranting Privileges GRANT <privilege> ON <object> TO <schema>;

8. Transactions, Roles & PrivilegesRevoking Privileges REVOKE <privilege> FROM <user>;

9. 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. • They simplify calls to objects. • They translate the synonym to an absolute reference.

10. Transactions, Roles & PrivilegesSynonym: Creating public synonyms CREATE PUBLIC SYNONYM <synonym_name> FOR <schema.object_name>;

11. Transactions, Roles & PrivilegesSynonym: Creating private synonyms CREATE SYNONYM <synonym_name> FOR <schema.object_name>;

12. Transactions, Roles & PrivilegesSynonym: Dropping synonyms DROP SYNONYM <synonym_name>;

13. Transactions, Roles & PrivilegesDesign 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.

14. Transactions, Roles & PrivilegesDesign Structures: Definers’ rights

15. Transactions, Roles & PrivilegesDesign 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.

16. Transactions, Roles & PrivilegesDesign Structures: Invokers’ rights

17. 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.

18. 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.

19. 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.

20. 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: • The last COMMIT command. • A specific SAVEPOINT command.

21. Transactions, Roles & PrivilegesACID: ROLLBACK command ROLLBACK [TO <savepoint_name>];

22. Transactions, Roles & Privileges ACID: SAVEPOINT command SAVEPOINT <savepoint_name>;

23. Transactions, Roles & Privileges ACID: COMMIT command SAVEPOINT <savepoint_name>;

24. Summary • Privileges • Roles • Granting Privileges • Revoking Privileges • Synonyms • Creating Synonyms • Droping Synonyms • Design Structures • ACID Compliance