Today we will learn how to create Oracle Type Object. If you are working on a large project with lots of tables and data, you might have seen the usage of Packages and DB Objects to perform database specific operations.
Oracle Type Object
Here we will see how to create a database object in Oracle DB.
CREATE OR REPLACE TYPE "MY_DBA"."EMP_OBJ" AS OBJECT (empFirstName VARCHAR2(80), empLastName VARCHAR2(80), empId NUMBER, ); GRANT EXECUTE on EMP_OBJ to MY_USER;
Notice that you need to be logged in with MY_DBA user to create the object and MY_USER is the application user that I am providing privilege to execute this package.
From Security point of view, it’s always better to have a DBA user for DDL changes and an application specific user that will have only the required privileges to perform the database operations.
If I don’t separate these accesses, then the application user will have full access to the DB Object/Packages and they can drop it also, mistakenly or may be by a hacker.