Transcript Lecture5
Lecture 5 Transactions and Security 1 Topics Transactions SAVEPOINT ROLLBACK COMMIT Security GRANT REVOKE 2 Transactions A transaction is a DML (Data Manipulation Language) operation which changes the database from one consistent state into another. 3 Transactions Oracle starts a transaction with the first SQL statement after a COMMIT, ROLLBACK or connection to the database. Oracle ends a transaction with a COMMIT, ROLLBACK or disconnection from the database. 4 Transactions Until changes are committed (made permanent) You can see the changes when the table is queried but, Others cannot see them when they query query your tables and, You can roll them back (discard them) if you change your mind or need to correct a mistake 5 Transactions Oracle issues an implicit COMMIT before and after any DDL (Data Definition Language - CREATE, ALTER, DROP) SQL statement. 6 Transactions There are a number of commands to help manage transactions SAVEPOINT savepoint ROLLBACK [TO [SAVEPOINT] savepoint] COMMIT 7 Creating a table called ‘BONUS’ (note the way the table has been created and had rows inserted in one operation) results 8 Making the ‘mistake’ Mistake as the managers are already in the table 9 NOTE: ONCE YOU COMMIT CHANGES YOU CANNOT ROLL THEM BACK 10 Security Object Privileges Tables, Views, etc System Privileges Tablespace, User 11 Grant GRANT is used to manage security. For instance, the DBA creates new and manages users with GRANT: GRANT <privilege [, privilege]> TO <user [, user]> [IDENTIFIED BY <password, [password]>; 12 Grant Privileges CONNECT allows logging on to the database RESOURCE create database objects DBA god 13 Grant GRANT can be used to grant other users access to objects owned by us GRANT <obj privilege [, obj privilege]> ON object TO <PUBLIC | user [, user]>; PUBLIC generic username used to describe all the users of the database 14 15 Grant Object privileges: ALTER DELETE INDEX INSERT SELECT UPDATE ALL 16 Grant GRANT SELECT ON myemp TO pddf1; GRANT UPDATE ON myemp TO PUBLIC; 17 Synonyms If pddf1 made the myemp table PUBLIC for SELECT, we would have to refer to it as SELECT * FROM pddf1.myemp; Allows us to refer to another's table 18 Allows different users to Create tables with the same name 19 Synonyms To make referring to other users tables easier, we can place a [PUBLIC] SYNONYM on the table (we would need the appropriate privilege). The DBA can do this for us (for PUBLIC): CREATE PUBLIC SYNONYM pub_emp FOR pddf1.myemp; 20 21 Synonyms To remove a synonym, we drop it (or rather the DBA does): DROP PUBLIC SYNONYM pub_emp; 22 Revoke Privileges can be revoked: Users can be removed: REVOKE CONNECT, RESOURCE FROM pddf1; 23 Revoke Access to tables can be stopped: REVOKE SELECT ON myemp FROM pddf1; 24 Summary Transactions SAVEPOINT ROLLBACK COMMIT Security GRANT REVOKE 25