How to Create Database user and grant Privileges

How to Create Database user and grant Privileges

-- Create User via SYSDBA
CREATE USER testuser IDENTIFIED BY 123

-- Grant Connection Privilege via SYSDBA
GRANT CONNECT, DBA, RESOURCE TO testuser IDENTIFIED BY 123;

--Grant Select on HR Tables & Views --> Execute the Statements
SELECT 'GRANT SELECT ON ' || object_name || ' to testuser;'
  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 testuser;'
  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