Introduction to Users in Oracle TimesTen-In Memory Database

Many of the user management features in Oracle TimesTen-In Memory Database will be familiar to people experienced with Oracle Database. This guide will provide a quick introduction to users and user management in Oracle TimesTen-In Memory Database.

Types of users

In Oracle TimesTen-In Memory Database there are three types of users; Instance Administrators, Internal Users, and External Users.

Instance Administrators – The OS user who installed the TimesTen instance is the instance administrator. The instance administrator has full control over everything within the TimesTen instance.

Internal Users – Internal user are users created and authenticated within a TimesTen instance. Internal users are created with the CREATE USER statement. Internal users can connect to the instance either locally or remotely.

External Users – External users are similar to internal users with the exception that external users are authenticated through the host OS. Internal users are created with the CREATE USER statement with the IDENTIFIED EXTERNALLY clause. The external user must correspond to an OS user on the same system hosting the TimesTen instance. Clients utilizing external users must be on the same host as the TimesTen instance.

Command> select * from sys.dba_users;
< SYS, 0, <NULL>, OPEN, <NULL>, <NULL>, USERS, TEMP, 2010-05-06 14:03:20.177944, DEFAULT, <NULL>, <NULL>, <NULL>, <NULL> >
< TTREP, 2, <NULL>, OPEN, <NULL>, <NULL>, USERS, TEMP, 2010-05-06 14:03:20.177944, DEFAULT, <NULL>, <NULL>, <NULL>, <NULL> >
< SYSTEM, 3, <NULL>, OPEN, <NULL>, <NULL>, USERS, TEMP, 2010-05-06 14:03:20.177944, DEFAULT, <NULL>, <NULL>, <NULL>, <NULL> >
< GRID, 4, <NULL>, OPEN, <NULL>, <NULL>, USERS, TEMP, 2010-05-06 14:03:20.177944, DEFAULT, <NULL>, <NULL>, <NULL>, <NULL> >
< ORATTADMIN, 10, <NULL>, OPEN, <NULL>, <NULL>, USERS, TEMP, 2010-05-06 14:03:20.177944, DEFAULT, <NULL>, <NULL>, <NULL>, <NULL> >
5 rows found.
Command>

On fresh data store you will five users in the system. The SYSTEM user is for internal use, SYS for system objects, GRID for cache grid object, TTREP for replication objects and one other user that is the instance administrator, ORATTADMIN in this case. For information on creating a data store see the post (link)

Below we create an internal user called test. Further we grant create session and create table to the user test. TimeTen has system and object privileges that will be very similar to people familiar with Oracle Database. See the documentation for complete details on privileges in Managing Access Control. (link)

Command> create user test identified by test;

User created.

Command> grant create session to test;
Command> grant create table to test;
Command>

We can now connect to the data store with the newly created user.

[orattadmin@ora1 ~]$ ttIsql

Copyright (c) 1996-2009, Oracle.  All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.


Command> connect "DSN=scratch_ttdb;UID=test;PWD=test";
Connection successful: DSN=scratch_ttdb;UID=test;DataStore=/opt/TimesTen/tt1121/info/db/SCRATCH_TTDB;DatabaseCharacterSet=WE8MSWIN1252;ConnectionCharacterSet=US7ASCII;DRIVER=/opt/TimesTen/tt1121/lib/libtten.so;TypeMode=0;
(Default setting AutoCommit=1)
Command> 

The following views can be queried to see users in the data store.

SYS.ALL_USERS– lists all users of the database that are visible to the current user.
SYS.USER_USERS-provides detail on the current user.
SYS.DBA_USERS-provides details on users of the database.