Thank You gsiems, Gauis, Naresh.
I too liked the idea of having a separate object owner which doesn't log in and then grant DML privileges on the its objects to an app_user.
Plus, I will follow Naresh's advice on keeping schema name and schema owner's user name same.
I have 2 questions on this.
Does the below configuration,naming conventions follow best practices ? Any suggestions, however trivial, are welcome.
The Database name will be : crm_db
The schema name will be : crm_master
-- Create the database and schema which will be owned by crm_master
CREATE user crm_master login password 'tiger235' createdb ;
---- Now, Connect as crm_master user and create the database and schema
CREATE DATABASE crm_db OWNER crm_master;
--- Creating CRM_MASTER schema (same name as its owner user crm_master)
create schema crm_master authorization crm_master;
---Now create all objects (tables, views functions, ...etc)
create table crm_master.emp (empid integar, first_name text);
--- Create the app user
CREATE user crm_user login password '1sland#' ;
--- Grant SELECT + DML privileges on all objects owned by crm_master to crm_user
From the application, when I want to refer a schema object, do I always have to prefix schema name before the object name ?
select first_name from crm_master.emp ;
Is there a way I could avoid prefixing schema name before object names ?