Task 4: Grant SQL Privileges to the Cache Administration User on the Oracle Database

The cache administration user on the Oracle database requires a specific set of SQL privileges to perform cache operations in the Oracle database.

Run the grantCacheAdminPrivileges.sql script as the Oracle database administrator to grant to the Oracle cache administration user the minimum set of SQL privileges required to perform cache operations. The script is available at timesten_home/install/oraclescripts/grantCacheAdminPrivileges.sql of your TimesTen instance.

The following example for a non-autonomous Oracle Database grants the required SQL privileges to the cacheadmin user for cache operations in the Oracle database:

SQL> @grantCacheAdminPrivileges.sql cacheadmin

Please enter the administrator user id
The value chosen for administrator user id is cacheadmin

***************** Creation of TT_CACHE_ADMIN_ROLE starts ******************
0. Creating TT_CACHE_ADMIN_ROLE role
** Creation of TT_CACHE_ADMIN_ROLE done successfully **
***************** Initialization for cache admin begins ******************
0. Granting the CREATE SESSION privilege to CACHEADMIN
1. Granting the TT_CACHE_ADMIN_ROLE to CACHEADMIN
2. Granting the DBMS_LOCK package privilege to CACHEADMIN
3. Granting the DBMS_DDL package privilege to CACHEADMIN
4. Granting the DBMS_FLASHBACK package privilege to CACHEADMIN
5. Granting the CREATE SEQUENCE privilege to CACHEADMIN
6. Granting the CREATE CLUSTER privilege to CACHEADMIN
7. Granting the CREATE OPERATOR privilege to CACHEADMIN
8. Granting the CREATE INDEXTYPE privilege to CACHEADMIN
9. Granting the CREATE TABLE privilege to CACHEADMIN
10. Granting the CREATE PROCEDURE  privilege to CACHEADMIN
11. Granting the CREATE ANY TRIGGER  privilege to CACHEADMIN
12. Granting the GRANT UNLIMITED TABLESPACE privilege to CACHEADMIN
13. Granting the DBMS_LOB package privilege to CACHEADMIN
14. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHEADMIN
15. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHEADMIN
16. Checking if the cache administrator user has permissions on the default
tablespace
     Permission exists
18. Granting the CREATE TYPE privilege to CACHEADMIN
19. Granting the SELECT on SYS.GV$LOCK privilege to CACHEADMIN
20. Granting the SELECT on SYS.GV$SESSION privilege  to CACHEADMIN
21. Granting the SELECT on SYS.DBA_DATA_FILES privilege  to CACHEADMIN
22. Granting the SELECT on SYS.USER_USERS privilege  to CACHEADMIN
23. Granting the SELECT on SYS.USER_FREE_SPACE privilege  to CACHEADMIN
24. Granting the SELECT on SYS.USER_TS_QUOTAS privilege  to CACHEADMIN
25. Granting the SELECT on SYS.USER_SYS_PRIVS privilege  to CACHEADMIN
26. Granting the SELECT on SYS.V$DATABASE privilege  to CACHEADMIN (optional)
27. Granting the SELECT on SYS.GV$PROCESS privilege  to CACHEADMIN (optional)
28. Granting the SELECT ANY TRANSACTION privilege to CACHEADMIN
29. Creating the TTCACHEADM.TT_07_ARDL_CG_COUNTER table
30. Granting SELECT privilege on TTCACHEADM.TT_07_ARDL_CG_COUNTER table to
PUBLIC
********* Initialization for cache admin user done successfully *********

For Autonomous Transaction Processing, Step 16 output is as follows:

16. Checking if the cache administrator user has permissions on the default tablespace

No existing permission.

Autonomous Transaction Processing automatically configures tablespaces. Therefore, this permission is not necessary.

The cache administration user on the Oracle database also needs specific privileges on each user table that is cached in TimesTen. The exact privileges depend on the type of cache groups being used, such as:

• For read-only cache groups, grant the SELECT privilege on all the user tables that will be cached to the cache administration user.

  SQL> GRANT SELECT ON sales.customers TO cacheadmin;

• For read-write (AWT) cache groups, the cache administration user needs SELECT, INSERT, UPDATE, and DELETE privileges on all the user tables that will be cached,

  SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON sales.customers TO cacheadmin;

Later on when you are creating a DSN within TimesTen, you will need to know the Oracle database character set, which must match on both the Oracle and TimesTen databases.

SQL> SELECT value FROM nls_database_parameters WHERE parameter='NLS_CHARACTERSET';
VALUE
---------------------------------------------------------------------------
AL32UTF8