TCL- Transaction Control Statements
•COMMIT: Used to explicitly save the DML changes in the database.
Execution of DDL statements does commit the changes automatically
SQL> COMMIT;
•SAVEPOINT
SQL> SAVEPOINT A;
•ROLLBACK [TO savepoint name]: Used to undo the DML changes till the last commit or till the last savepoint.
SQL> ROLLBACK;
SQL> ROLLBACK TO SAVEPOINT A;
DCL- Data Control Language
Security - Granting Table Privileges
•Security is the prevention of unauthorized access to the database. Within an organization, the database administrator determines the types of access various users need for the database.
•Some users might be able to retrieve and update data in the database. Others might only be able to retrieve any data from the database but not make any changes to it. Still other users might be able to access only a portion of the database.
•User account - identified by a unique username and password
•User schema - all of the objects that the user creates and stores in the database
• Database objects
–Also called schema objects
–Objects in user schema
•Object owner has privileges to perform all possible actions on an object
•System Privileges
–Control the operations that the user can perform within the database
•Create user accounts
•Connecting to the database, creating new tables, shutting down the database, etc.
•Object Privileges
–Granted on individual database objects
–Controls operations that a user can perform on a specific object (insert data, delete data, etc.)
–When we create an object in user schema, we can then grant object privileges on that object to other database users
Roles
•Oracle provides easy and controlled privilege management through roles
•Roles are named groups of related privileges that are granted to users or other roles
•Roles are designed to ease the administration of end-user system and schema object privileges
•The following roles are defined automatically for Oracle databases
–CONNECT
–RESOURCE
–DBA
System Privileges
•Syntax
GRANT
Example:
GRANT CREATE SESSION TO smith;
GRANT dw_manager TO sh WITH ADMIN OPTION;
Object Privileges
•Syntax
GRANT privileges ON object TO user;
Example
GRANT SELECT,INSERT,UPDATE,DELETE ON emp TO smith;
•Syntax
REVOKE privileges ON object FROM user;
Example:
REVOKE DELETE ON emp FROM anderson;
Views
•A view is an object that contains no data of its own
•It is a kind of table whose contents are taken from other tables through the execution of a query
•As the values in the base tables change, the changes are automatically reflected in the views
Syntax:
CREATE VIEW
SQL> CREATE VIEW student_vw AS SELECT rollno, name, loc FROM student;
Views
•According to ANSI standard, views are read-only (not modifiable) if the CREATE VIEW statement:
–Contains SET and DISTINCT operators
–Contains a group function or computed columns or expressions
–Includes a GROUP BY or HAVING clause
–References to more than one table either through a join or a sub-query
–References to a non-updatable view
–Does not include NOT NULL column that does not have a DEFAULT clause
VIEWS
•Create view containing all the employees in dept 20
SQL> CREATE VIEW empv AS SELECT empno, ename, job FROM emp WHERE deptno = 20;
•To find out the base table on which a view is dependent
SQL > SELECT * FROM user_views;
Manipulating Views
•Inserting records in a view
SQL> INSERT INTO empv VALUES ( 2222 , ‘POOJA’, ‘ANALYST’);
•Updating views
SQL > UPDATE empv SET job = ‘MANAGER’ WHERE ename = ‘SMITH’;
CHECK Option
•Create a view with columns deptno, job, average salary, sum of salaries for each job in each dept
SQL > CREATE VIEW empview (deptno, job,avgsal, totsal) AS SELECT deptno, job,avg(sal), sum(sal) FROM emp GROUP BY deptno,job;
•Create a view containing the employee and department details
•With check option: any insert or update of a row into the view is rejected if the row does not meet the view definition
Views
•Create a view with check option
SQL > CREATE VIEW empv3 As SELECT * FROM emp WHERE deptno = 20 WITH CHECK OPTION;
–Note: The user can now enter values only for dept 20
•To drop a view
SQL > DROP VIEW empv;
Data Dictionary
•The SQL command
–SELECT * FROM DICT[IONARY];
•lists all tables and views of the data dictionary that are accessible to the user
–SELECT * FROM TAB;
•retrieves the names of all tables owned by the user who issues this command
–SELECT * FROM COL;
•returns all information about all the columns of one’s tables
Data Dictionary Views
•The views provided by the data dictionary are divided into three groups: USER, ALL, and DBA
•The group name builds the prefix for each view name. For some views, there are associated
synonyms
–USER: Tuples in the USER views contain information about objects owned by the account
–ALL: Rows in the ALL views include rows of the USER views and all information about objects that are accessible to the current user
–DBA: The DBA views encompass information about all database objects, regardless of the owner. Only users with DBA privileges can access these views
CREATE OR REPLACE SYNONYM alias_name FOR schema_name.table_name;
Data Dictionary Views
•USER Views
•USER_TABLES all tables with their name, number of columns, storage
•information, statistical information etc. (TABS)
•USER_CATALOG tables, views, and synonyms (CAT)
•USER_COL_COMMENTS comments on columns
•USER_CONSTRAINTS constraint definitions for tables
•USER_INDEXES all information about indexes created for tables (IND)
•USER_OBJECTS all database objects owned by the user (OBJ)
•USER_TAB_COLUMNS columns of the tables and views owned by the user(COLS)
•USER_TAB_COMMENTS comments on tables and views
•USER_TRIGGERS triggers defined by the user
•USER_USERS information about the current user
•USER_VIEWS views defined by the user
ALL Views
•ALL_CATALOG owner, name and type of all accessible tables, views, and synonyms
•ALL_TABLES owner and name of all accessible tables
•ALL_OBJECTS owner, type, and name of accessible database objects
•ALL_TRIGGERS . . .
•ALL_USERS . . .
•ALL_VIEWS . . .
DBA Views
•DBA_TABLES tables of all users in the database
•DBA_CATALOG tables, views, and synonyms defined in the database
•DBA_OBJECTS object of all users
•DBA_DATA_FILES information about data files
•DBA_USERS information about all users known in the database
Indexes
•Indices are created in an existing table to locate rows more quickly and efficiently
• It is possible to create an index on one or more columns of a table, and each index is given a name
•The users cannot see the indexes, they are just used to speed up queries
Create Index
•CREATE INDEX cust_indx ON customer(cname);
•CREATE INDEX cust_city_indx ON cutomer(cno);
•CREATE INDEX ABC ON Records (Rollno,Sname);
Guidelines for Indexing
•Create an index if the query statement retrieves less than 15% of the rows in a large table
•Primary key column should always be indexed
•Index columns are used for joins to improve performance on joins of multiple tables
Conditions when an Index will not be Invoked
•No WHERE clause
•Index key not used in a WHERE clause
•Use of operators like ‘NOT’ and ‘IS NULL’
•Use of only secondary key in a composite index
•Use of functions or expressions with the index key
•Use of index key and another column with logical ‘OR’ operator
