How to Create Database user and grant Privileges

-- Create User via SYSDBA

CREATE USER bijoy IDENTIFIED BY abcd123

-- Grant Connection Privilege via SYSDBA

GRANT CONNECT, DBA, RESOURCE TO bijoy IDENTIFIED BY abcd123;

--Grant Select on HR Tables & Views --> Execute the Statements

SELECT 'GRANT SELECT ON ' || object_name || ' to bijoy;'

  FROM dba_objects

 WHERE owner = 'HR' AND object_type IN ('TABLE', 'VIEW')

--Grant Execute on Packages --> Execute the Statements

SELECT 'GRANT EXECUTE  ON ' || object_name || ' to bijoy;'

  FROM dba_objects

 WHERE object_name LIKE 'XXX%' AND object_type IN ('PACKAGE', 'PACKAGE BODY')

-- View Package Code

SELECT DBMS_METADATA.get_ddl ('PACKAGE', 'XX_HR_PACKAGE', 'APPS') FROM DUAL;

Note:

If one wants to avoid using the schema name in the beginning of any object in the custom schema, we need to create public synonyms as show below to avoid the same

E.g.: Select * from apps.per_all_people_f

SELECT    'CREATE OR REPLACE PUBLIC SYNONYM '

       || object_name

       || ' FOR '

       || object_name

       || ';'

  FROM dba_objects

 WHERE owner = 'HR' AND object_type = 'TABLE'

Once the Public Synonyms are create, one can use the objects without using the schema name.

E.g.: Select * from per_all_people_f