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:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
# 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
andNLS_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
- Tables, Views, Sequences:
- Run-time Account
- Tables, Views:
SELECT, INSERT, UPDATE, DELETE
- Sequences:
SELECT
- Tables, Views:
- 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:
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
# 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'; |
Code Block | ||||||
---|---|---|---|---|---|---|
| ||||||
# 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
...