Managing Security for Oracle Database Users
About User Security
Each Oracle database has a list of valid database users. To access a database, a user must run a database application, and connect to the database instance using a valid user name defined in the database. Oracle Database enables you to set up security for your users in a variety of ways. When you create user accounts, you can specify limits to the user account. You can also set limits on the amount of various system resources available to each user as part of the security domain of that user. Oracle Database provides a set of database views that you can query to find information such as resource and session information.
Creating a New User Account
You create a database user with the CREATE USER statement. To create a user, you must have the CREATE USER system privilege. Because it is a powerful privilege, a database administrator or security administrator is usually the only user who has the CREATE USER system privilege.
Example: Creating a User Account with the CREATE SESSION Privilege
CREATE USER j ward
IDENTIFIED BY password
DEFAULT TABLE SPACE data_ts
QUOTA 100M ON test_ts
QUOTA 500K ON data_ts
TEMPORARY TABLE SPACE temp_ts
GRANT CREATE SESSION TO j ward;
A newly created user cannot connect to the database until you grant the user the CREATE SESSION system privileges. So, immediately after you create the user account, use the GRANT SQL statement to grant the user these privileges. If the user must access Oracle Enterprise Manager, you should also grant the user the SELECT ANY DICTIONARY privilege.
Note: As a security administrator, you should create your own roles and assign only those privileges that are needed. For example, many users formerly granted the CONNECT privilege did not need the additional privileges CONNECT used to provide. Instead, only CREATE SESSION was actually needed, and in fact, that is the only privilege CONNECT presently retains.
Specifying a User Name
Within each database, a user name must be unique with respect to other user names and roles. A user and role cannot have the same name. Furthermore, each user has an associated schema. Within a schema, each schema object must have a unique name.
Assigning a Default Table space for the User
Each user should have a default table space. When a schema object is created in the user’s schema and the DDL statement does not specify a table space to contain the object, Oracle Database stores the object in the default user’s table space. Using a table space other than SYSTEM reduces contention between data dictionary objects and user objects for the same data files. In general, do not store user data in the SYSTEM table space.
You can also set a user default table space during user creation, and change it later with the ALTER USER statement. Changing the user default table space affects only objects created after the setting is changed. When you specify the default table space for a user, also specify a quota on that table space.
Assigning a Table space Quota for the User
You can assign each user a table space quota for any table space (except a temporary table space). Assigning a quota accomplishes the following:
■Users with privileges to create certain types of objects can create those objects in the specified table space.
■Oracle Database limits the amount of space that can be allocated for storage of a user’s objects within the specified table space to the amount of the quota.
By default, a user has no quota on any table space in the database. If the user has the privilege to create a schema object, then you must assign a quota to allow the user to create objects. At a minimum, assign users a quota for the default table space, and additional quotas for other table spaces in which they can create objects. You can assign a user either individual quotas for a specific amount of disk space in each table space or an unlimited amount of disk space in all table spaces. Specific quotas prevent a user’s objects from using too much space in the database.
■If a user has already exceeded a new table space quota, then the objects of a user in the table space cannot be allocated more space until the combined space of these objects is less than the new quota.
■If a user has not exceeded a new table space quota, or if the space used by the objects of the user in the table space falls under a new table space quota, then the user’s objects can be allocated space up to the new quota.
Assigning a Temporary Table space for the User
You should assign each user a temporary table space. When a user executes a SQL statement that requires a temporary segment, Oracle Database stores the segment in the temporary table space of the user. These temporary segments are created by the system when performing sort or join operations. Temporary segments are owned by SYS, which has resource privileges in all table spaces.
Specifying a Profile for the User
You can specify a profile when you create a user. A profile is a set of limits on database resources and password access to the database. If you do not specify a profile, then Oracle Database assigns the user a default profile.
Setting a Default Role for the User
A role is a named group of related privileges that you grant as a group to users or other roles. A default role is automatically enabled for a user when the user creates a session. You can assign a user zero or more default roles. You cannot set default roles for a user in the CREATE USER statement. When you first create a user, the default role setting for the user is ALL, which causes all roles
Note: If your SYSTEM table space is locally managed, then users must be assigned a specific default (locally managed) temporary table space. They may not be allowed to default to using the SYSTEM table space because temporary objects cannot be placed in locally managed permanent table spaces.