Page History

...

When creating the database then consider using a to use UTF based character encoding and a collating sequence that supports UTF, for example:

# set up database
CREATE DATBASE JS7
...
CHARACTER SET AL32UTF8;

# consider linguistic sorting for Unicode with WHERE clauses
ALTER SESSION SET NLS_COMP='LINUGUISTIC';

# consider linguistic sorting for Unicode with ORDER BY clauses
ALTER SESSION SET NLS_SORT='LINUGUISTIC';

Note:

• Consider that NLS_COMP and NLS_SORT have to use the same value.

Accounts

Frequent practice includes to set up two accounts: an owner account that owns the schema and & objects and a run-time account that is granted permissions to access objects of the owner schema. Alternatively a single account can be used that owns the schema.

...

• Owner Account
  • Tables, Views, Sequences: CREATE, DROP, ALTER
  • Procedures: EXECUTE
• Run-time Account
  • Tables, Views: SELECT, INSERT, UPDATE, DELETE
  • Sequences: SELECT
• If a single account is used then all above permissions have to be assigned this account.

There are number of ways how to assign permissions, e.g. by use of roles. One option is to create the objects in the owner schema and to generate the respective GRANT commands like this: commands and CREATE SYNONYM commands for the run-time account like this:

# generate SQL statement to grant permissions to run-time account
SELECT DISTINCT
       CASE
            WHEN object_type = 'SEQUENCE' THEN 'GRANT SELECT'
            WHEN object_type IN ('TABLE', 'VIEW') THEN 'GRANT SELECT,INSERT,UPDATE,DELETE'
       END
       || ' ON JS7_OWNER.'|| object_name || ' TO JS7_USER;' 
  FROM dba_objects 
 WHERE object_type IN ('TABLE','VIEW','SEQUENCE') and owner='JS7_OWNER';

# generate SQL statement to create synonyms for run-time account

SELECT 'CREATE SYNONYM ' || 'JS7_USER.' || table_name || ' FOR '|| owner || '.' || table_name ||';' FROM all_tables WHERE owner = 'JS7_OWNER';
SELECT 'CREATE SYNONYM ' || 'JS7_USER.' || view_name || ' FOR '|| owner || '.' || view_name ||';' FROM all_views WHERE owner = 'JS7_OWNER';
SELECT 'CREATE SYNONYM ' || 'JS7_USER.' || sequence_name || ' FOR '|| sequence_owner || '.' || sequence_name ||';' FROM all_sequences WHERE sequence_owner = 'JS7_OWNER';

Setup for PostgreSQL®

Database

...