System Privileges
There are system privileges available in TimesTen.
About System Privileges
Aside from the instance administrator, the most powerful system privilege is
ADMIN
, which enables the user to perform system operations or
operations on any database object. Only the instance administrator or a user with the
ADMIN
privilege can grant or revoke system privileges to other
users.
An individual user can view their own system privileges in the
SYS.USER_SYS_PRIVS
system view. A user with the
ADMIN
privilege can view all system privileges for all users in the
SYS.DBA_SYS_PRIVS
system table. See User Privilege Views.
Instance Administrator
The instance administrator, a member of the TimesTen users group, is the user who creates the TimesTen installation and all TimesTen instances. This user has a number of special privileges and capabilities beyond those of other administrative users.
These are described in the following sections:
Instance Administrator Privileges
There are privileges that only the instance administrator can do.
-
Remove the TimesTen installation.
-
Create, modify (including upgrade), or destroy a TimesTen instance.
-
Create or destroy a database.
-
Load or unload a database manually (
ramPolicy manual
usingttAdmin -ramLoad
). -
Load a database when changes to first connection attribute settings are applied.
-
Open or close a database.
-
Restore a database.
-
Start and stop the TimesTen daemon.
-
Restart the TimesTen server.
In addition, for TimesTen Scaleout, only the instance administrator can execute any commands of the ttGridAdmin
utility. Among many other functions, including those listed above, only the TimesTen Scaleout instance administrator can create a grid, create database definitions and connectables, change the distribution map of an existing database, create repositories, and perform backups, restores, exports, and imports.
See the following for related information:
-
Instance Administrator, Understanding the TimesTen Users Group, and TimesTen Instances in Oracle TimesTen In-Memory Database Installation, Migration, and Upgrade Guide
-
TimesTen Scaleout Architecture and The Operating System User in Oracle TimesTen In-Memory Database Scaleout User's Guide
Note:
-
The instance administrator cannot be the root user.
-
You cannot change to a different instance administrator.
-
In TimesTen Scaleout, the instance administrator's user name, user ID, group name, and group ID must all be the same on all hosts of the grid.
Instance Administrator Ownership and Privileges for Database and Log Directories
The instance administrator owns the database directory (indicated by the
DataStore
connection attribute), where checkpoint files are written,
and the log directory (indicated by the LogDir
connection attribute).
Proper ownership and permissions must be set for these directories. In addition to the owner being the instance administrator, the group must be the TimesTen users group and the directory permissions must be set for read/write/execute permission for owner and group with no access by anyone else.
Administrative Privileges
The ADMIN
privilege confers system privileges and privileges on all
database objects, which enables these users to perform administrative tasks and any valid
database operation. Only the instance administrator or another user with
ADMIN
privilege can grant ADMIN
privilege.
A user with the ADMIN
privilege can do the following:
-
Perform create, alter, drop, select, update, insert, or delete operations on all database objects.
-
Grant or revoke all privileges.
-
Perform checkpointing operations.
-
Create and delete users.
-
View system tables, views, and packages.
-
Create, alter or drop replication schemas or active standby pairs.
Note:
For more information on viewing privileges for users from system tables or views, see User Privilege Views.
To grant the ADMIN
privilege to the user terry
, the instance administrator or another user with ADMIN
privilege executes this statement:
GRANT ADMIN TO terry;
To grant the SELECT
privilege to terry
on the departments
table owned by pat
:
GRANT SELECT ON pat.departments TO terry;
Note:
Since pat
is the owner of departments, pat
may also grant the SELECT
object privilege to terry
.
Privileges to Connect to the Database
A user must be granted the CREATE SESSION
system privilege by the
instance administrator or a user with the ADMIN
privilege in order to
connect to the database.
The following example grants the CREATE SESSION
privilege
to pat
:
Command> GRANT CREATE SESSION TO pat;
Note:
TimesTen databases are accessed through Data Source Names (DSNs). If a user tries to use a DSN that has connection attributes for which they do not have privileges, such as first connection attributes, they receive an error.
ANY Keyword
Privileges used with the ANY
keyword enable the user to perform the
operation on any object of the specified type in the database.
These system privileges are CREATE ANY
object_type
, DROP ANY
object_type
, ALTER ANY
object_type
, SELECT ANY
object_type
, UPDATE ANY TABLE
, INSERT
ANY TABLE
, DELETE ANY TABLE
, and EXECUTE ANY
PROCEDURE
.
ANY TABLE
also includes views and materialized views.
ALL PRIVILEGES
ALL PRIVILEGES
, which can be granted by the instance administrator
or a user with ADMIN
privilege, grants system privileges to a user.
If you want to limit the privileges granted, you can grant ALL
PRIVILEGES
then revoke those system privileges that you do not want the
user to have.
Once granted, ALL PRIVILEGES
can subsequently be revoked.
Privilege Hierarchy
There is a hierarchy of privileges. Higher level privileges confer related lower
level privileges. For example, the ADMIN
privilege confers system
privileges. The SELECT ANY TABLE
privilege confers the
SELECT
privilege on any individual table.
When a user needs a privilege for an operation, first verify whether the user already has the privilege through a higher level privilege. For example, if the user pat
needs to have the SELECT
privilege for terry.table2
, you can check the following:
-
Has
pat
been granted theSELECT ANY TABLE
privilege? This privilege meanspat
would haveSELECT
on any table, view, or materialized view. -
Has
pat
been granted theADMIN
privilege? This would mean thatpat
can perform any valid SQL operation.
If you grant a privilege that is included in a higher level privilege, no error occurs. However, when you revoke privileges, they must be revoked in the same unit as granted (ANY
level or object level).
The following series of statements is allowed, and pat
can still
update the hr.employees
table because of the UPDATE ANY
TABLE
privilege. (The second statement of course is unnecessary, but the
third statement would not be allowed without it.)
Command> GRANT UPDATE ANY TABLE TO pat; Commanc> GRANT UPDATE ON hr.employees TO pat; Command> REVOKE UPDATE ON hr.employees FROM pat;
This next example also leaves pat
with the ability to update hr.employees
, because that was granted explicitly:
Command> GRANT UPDATE ANY TABLE TO pat; Commanc> GRANT UPDATE ON hr.employees TO pat; Command> REVOKE UPDATE ANY TABLE FROM pat;
The following example attempts to revoke the ability to update the
hr.employees
table from the user, but is not allowed because there
was no GRANT
statement for that specific object.
Command> GRANT UPDATE ANY TABLE TO pat; Command> REVOKE UPDATE ON hr.employees FROM pat; 15143: REVOKE failed: User PAT does not have object privilege UPDATE on HR.EMPLOYEES The command failed.
See Privilege Hierarchy in Oracle TimesTen In-Memory Database SQL Reference.
Additional System Privileges
In addition to the ADMIN
privilege, some system privileges authorize
a range of operations across certain areas of database functionality.
-
XLA
: You must have theXLA
system privilege to connect as an XLA reader, who can have global impact on the system. An XLA reader can create extra log volume and can cause long log holds if they do not advance their bookmarks. -
CACHE_MANAGER
: TheCACHE_MANAGER
privilege is required for cache group administrator operations. See Privileges for Cache Groups.
Privileges Through the PUBLIC Role
The instance administrator or a user with the ADMIN
privilege can
grant or revoke default privileges for all users by granting or revoking privileges for the
PUBLIC
role.
Note:
-
If a user has been explicitly granted a privilege, it is not revoked if that privilege is revoked from
PUBLIC
. -
Any privileges that were granted to
PUBLIC
by userSYS
cannot be revoked. These privileges, granted as part of database creation, are shown when you execute the following SQL statement:Command> SELECT * FROM DBA_TAB_PRIVS WHERE GRANTOR = 'SYS'
In the following example, user pat
is granted the SELECT
ANY TABLE
privilege and PUBLIC
is granted the SELECT ANY
TABLE
privilege. Then all system privileges are displayed from the
SYS.DBA_SYS_PRIVS
view. (See User Privilege Views.) As shown, revoking SELECT ANY TABLE
from PUBLIC
does
not revoke SELECT ANY TABLE
from pat
. (The second column
indicates a privilege held by the user. The third column, NO
in the example,
indicates whether the user can grant that privilege to others.)
Command> GRANT SELECT ANY TABLE TO PAT; Command> GRANT SELECT ANY TABLE TO PUBLIC; Command> SELECT * FROM SYS.DBA_SYS_PRIVS; < SYS, ADMIN, NO > < PUBLIC, SELECT ANY TABLE, NO > < SYSTEM, ADMIN, NO > < PAT, ADMIN, NO > < PAT, SELECT ANY TABLE, NO > 5 rows found. Command> REVOKE SELECT ANY TABLE FROM PUBLIC; Command> select * from sys.dba_sys_privs; < SYS, ADMIN, NO > < SYSTEM, ADMIN, NO > < PAT, ADMIN, NO > < PAT, SELECT ANY TABLE, NO > 4 rows found.
By default in a newly created TimesTen database, PUBLIC
has SELECT
and EXECUTE
privileges on various system tables and views and PL/SQL functions, procedures and packages. You can see the list of privileges granted to PUBLIC
by querying the SYS.DBA_TAB_PRIVS
view. In the query below, the privilege granted to PUBLIC
is in the fifth column, as indicated by the DESCRIBE
statement that precedes the query.
Command> DESC SYS.DBA_TAB_PRIVS; View SYS.DBA_TAB_PRIVS: Columns: GRANTEE VARCHAR2 (30) INLINE OWNER VARCHAR2 (30) INLINE TABLE_NAME VARCHAR2 (30) INLINE GRANTOR VARCHAR2 (30) INLINE PRIVILEGE VARCHAR2 (40) INLINE NOT NULL GRANTABLE VARCHAR2 (3) INLINE NOT NULL HIERARCHY VARCHAR2 (3) INLINE NOT NULL 1 view found. Command> SELECT * FROM SYS.DBA_TAB_PRIVS WHERE GRANTEE='PUBLIC'; < PUBLIC, SYS, TABLES, SYS, SELECT, NO, NO > < PUBLIC, SYS, COLUMNS, SYS, SELECT, NO, NO > < PUBLIC, SYS, INDEXES, SYS, SELECT, NO, NO > < PUBLIC, SYS, USER_COL_PRIVS, SYS, SELECT, NO, NO > < PUBLIC, SYS, PUBLIC_DEPENDENCY, SYS, SELECT, NO, NO > < PUBLIC, SYS, USER_OBJECT_SIZE, SYS, SELECT, NO, NO > < PUBLIC, SYS, STANDARD, SYS, EXECUTE, NO, NO > < PUBLIC, SYS, UTL_IDENT, SYS, EXECUTE, NO, NO > < PUBLIC, SYS, TT_DB_VERSION, SYS, EXECUTE, NO, NO > < PUBLIC, SYS, PLITBLM, SYS, EXECUTE, NO, NO > < PUBLIC, SYS, DBMS_OUTPUT, SYS, EXECUTE, NO, NO > < PUBLIC, SYS, DBMS_SQL, SYS, EXECUTE, NO, NO > < PUBLIC, SYS, DBMS_STANDARD, SYS, EXECUTE, NO, NO > < PUBLIC, SYS, DBMS_PREPROCESSOR, SYS, EXECUTE, NO, NO > < PUBLIC, SYS, UTL_RAW, SYS, EXECUTE, NO, NO > < PUBLIC, SYS, DBMS_UTILITY, SYS, EXECUTE, NO, NO > < PUBLIC, SYS, DBMS_RANDOM, SYS, EXECUTE, NO, NO > ... 57 rows found.