...
| Code Block |
|---|
| language | bash |
|---|
| title | Example how to set up the database for Unicode support |
|---|
| linenumbers | true |
|---|
| collapse | true |
|---|
|
# add database
CREATE DATABASE js7 CHARACTER SET utf8 COLLATE utf8_general_ci; |
...
| Code Block |
|---|
| language | bash |
|---|
| title | Example how to create accounts |
|---|
| linenumbers | true | collapse | true |
|---|
|
# add account
CREATE USER 'jobscheduler'@'%' IDENTIFIED BY 'jobscheduler'; |
...
The following permissions are required for the schema:
- to manage objectsOwner account
- Tables, Views, Functions, Stored Procedures:
CREATE, DROP, ALTER
- Runto access objects at run-time account
- Tables, Views:
SELECT, INSERT, UPDATE, DELETE - Functions, Stored Procedures:
EXECUTE
| Code Block |
|---|
| language | bash |
|---|
| title | Example how to grant permissions to accounts |
|---|
| linenumbers | true | collapse | true |
|---|
|
# grant all permissions
GRANT ALL ON js7.* TO 'jobscheduler'@'%';
# alternatively grant individual permissions
GRANT CREATE, DROP, ALTER, EXECUTE, SELECT, UPDATE, INSERT, DELETE on js7.* to 'jobscheduler'@'%'; |
...
| Code Block |
|---|
| language | bash |
|---|
| title | Example how to set up the database for Unicode support |
|---|
| linenumbers | true | collapse | true |
|---|
|
# set up database
CREATE DATBASE JS7
...
CHARACTER SET AL32UTF8;
# consider linguistic sorting for Unicode
ALTER SESSION SET NLS_COMP='LINUGUISTIC'; |
...
| Code Block |
|---|
| language | bash |
|---|
| title | Example how to create accounts |
|---|
| linenumbers | true | collapse | true |
|---|
|
# add owner account
CREATE USER JS7_OWNER IDENTIFIED BY JS7_OWNER;
GRANT CONNECT, RESOURCE TO JS7_OWNER;
# add run-tine account
CREATE USER JS7_USER IDENTIFIED BY JS7_USER;
GRANT CONNECT, RESOURCE TO JS7_USER; |
...
| Code Block |
|---|
| language | bash |
|---|
| title | Example how to grant permissions to accounts |
|---|
| linenumbers | true | collapse | true |
|---|
|
# 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'; |
...