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.