Security Mgmt Using SQL & OOPS in Oracle

1. Security Mgmt Using SQL & OOPS in Oracle -Grant Privilege -Revoke Privileges -Object types -Nested Tables -Varying Arrays -Large Objects -References -Features of object -Structure of simple object -Object views

2. -Grant Privilege: Grant statement provides various types of access to database object such as tables, views & sequence etc. Syntax:- Grant <Object Privileges> ON <ObjectName> To<UserName> [With Grant Option]

3. Object Privileges:- Each object privilege that is granted authorizes the grantee to perform some operation on the object. A user can grant all the privileges or grant only specific object privileges. The list of object privileges is as follows:- ALTER: To change the table definition with the ALTER TABLE command. DELETE: To remove the records from the table with the DELETE command. INDEX: To create an index on the table with the CREATE INDEX command. INSERT: To add records to the table with the INSERT command. SELECT: To query the table with the SELECT command. UPDATE: To modify the records in the tables with the UPDATE command.

4. Example 1: Give the user shrikant all data manipulation permissions on the table emp_mstr • GRANT ALL ON emp_mstr TO shrikant; • Example 2: Give the user Hansel permission to only view & modify records in the table cust_mstr. • GRANT SELECT,UPDATE ON cust_mstr TO HANSEL; • Example 3: View the content of the fd_mstr table that belongs to suhas. • SELECT * FROM suhas.fd_mstr;

5. Revoke Privileges:- Privileges once given can be denied to a user using the REVOKE command. The object owner can revoke privileges granted to another user. Revoking Permission Using the Revoke Statement: The revoke statement is used to deny the grant given on an object. Syntax:- REVOKE <Object Privileges> ON<ObjectName> FROM<UserNaME>

6. Examples:- 1)Take back all privileges on the table NOMINEE_MSTR from Anil. • REVOKE ALL ON NOMINEE_MSTR from Anil; 2)ALL privileges on the table NOMINEE_MSTR have been granted to Anil.Take back the Delete privilege on the table. • REVOKE DELETE ON NOMINEE_MSTR from Anil;

7. Object types:- Oracle supports many different types of objects. Oracle data type of NUMBER,DATE & VARCHAR2 ,the abstract data type is a consists of one or more subtypes.(describe data) An abstract data type can be used to create an object table. e. g an abstract data type for an address may consist of the following column:

8. Example1:- CREATE TYPE ADDRESS_TY AS OBJECT (street varchar2(50),city varchar2(25),state varchar2(25),zip number(10)); Output: Type Created. Abstract data types can be nested & can contain references to other abstract data types. Example 2:- CREATE TYPE PERSON_TY AS OBJECT (name varchar2(20),address ADDRESS _TY); Output: Type Created.

9. Nested Tables:- A nested table is a table within a table.It is a collection of rows, represented as a column within the main table.For each record within the main table,the nested table may contain multiple rows. Example:- Consider a table that contained info. About dept.,in which each dept. may have many projects in progress at any one time. Dept table & Project table Nested table allows storing the information about projects within the department table.

10. Varying Arrays:- A varying array is a set of objects, each with the same data type. When a table is created with a varying array, the array is a nested table with a limited set of rows. It is also know as VARRAYS, allows storing repeating attributes in tables. Example: CREATE TYPE company_address_ty AS VARRAY(3)of varchar2(100); NOTE:- This stmt.creates a VARRAY type called company_address_ty, which can hold a maximum 3 elements of data type VARCHAR2(100),i.e 3 entries per record,each storing address information for the company.

11. Large Objects:- A large object, or LOB is capable of storing large volumes of data. LOB DATATYPE:- BLOB,CLOB,NCLOB ,BFILE BLOB:- Binary data,& can extend to 4GB in length. CLOB:- Character data ,& can store data upto to 4GB in length. NCLOB:- store CLOB data for multibyte character sets. BFILE:- It is a pointer to an external file.The file referenced by BFILE exist at OS level.

12. References:- • The Referencing object(REF’s data type)is something that is new to oracle. • This data type acts as a pointer to an object. • A REF can also be used in a manner similar to a foreign key in a RDBMS. • A REF is used primarily to store an object identifier,& to allow the user to select that object. • REF’s establish relationship between two object tables, same as a PK/FK relationship in relational table.

13. Features of object:- • An object has a name. • The operation that affect an object are called methods. Naming Conventions For Object:- • Table & column names will be singular(such as Employee, name & state). • Abstract data type names will be singular nouns with a _TY suffix(such as PERSON_TY or ADDRESS_TY). • Table & data type names will always be uppercase(PERSON_TY). • Column names will always be lower case(state). • Object view names will singular nouns with a _OV suffix(PERSON_OV or ADDRESS_OV). • Nested table names will be plural nouns with a _NT suffix(WORKERS_NT). • Varying arrays names will be plural nouns with a _VA suffix(WORKERS_VA).

14. Structure of simple object:- Data cannot be inserted into PERSON_TY. A data type describe data ,it does not store data . To store data ,a table that uses this data type has to be created.(possible to store data ). Create a table CUSTOMER.A customer has a customer_id & all the attributes of a person(PERSON_TY data type). CREATE TABLE CUSTOMER(customer_idnumber, PERSON PERSON_TY). o/p ----------- Type created.

15. Object views:- 1)It allow two different ways to enter data i.e. table can be treated as a relational table & object table. 2) They allow creation of abstract data types within tables that already exist. Example:- CREATE OR REPLACE VIEW CUSTOMER_OV(customer_id,person)AS SELECT customer_id,PERSON_TY(name,ADDRESS_TY(street,city,state,zip)) FROM CUSTOMER;

16. THANK YOU!!!