Friday , July 21 2017
Home / Oracle DBA / Managing Security for Oracle Database Users

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. This chapter also describes profiles. A profile is collection of attributes that apply to a user. It enables a single point of reference for any of multiple users that share those exact attributes.

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 2-1 creates a user and specifies the user password, default table space, temporary table space where temporary segments are created, table space quotas, and profile. It also grants the user the minimum privilege,CREATE SESSION, to log in to the database session.

Example 2-1 Creating a User Account with the CREATE SESSION Privilege and Assigning a Default Tablespace for the User

CREATE USER Sarath
IDENTIFIED BY password
DEFAULT TABLESPACE data_ts
QUOTA 100M ON test_ts
QUOTA 500K ON data_ts
TEMPORARY TABLESPACE temp_ts
PROFILE clerk;

GRANT CREATE SESSION TO Sarath;

When you specify the default tablespace for a user, also specify a quota on that tablespace.

In the following CREATE USER statement, the default tablespace for user Sarath data_ts, and his quota on that tablespace is500K:

CREATE USER Sarath IDENTIFIED BY password
DEFAULT TABLESPACE data_ts
QUOTA 100M ON test_ts
QUOTA 500K ON data_ts
TEMPORARY TABLE SPACE temp_ts
PROFILE clerk;

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.

The following CREATE USER statement assigns the following quotas for the test_ts and data_ts table spaces:

CREATE USER Sarath
IDENTIFIED BY password
DEFAULT TABLESPACE data_ts
QUOTA 100M ON test_ts
QUOTA 500K ON data_ts
TEMPORARY TABLESPACE temp_ts
PROFILE clerk;

You can assign a user either individual quotas for a specific amount of disk space in each tablespace or an unlimited amount of disk space in all tablespaces. Specific quotas prevent a user’s objects from using too much space in the database.

You can assign quotas to a user tablespace when you create the user, or add or change quotas later. (You can find existing user quotas by querying the USER_TS_QUOTAS view.) If a new quota is less than the old one, then the following conditions remain true:

  • 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.

In the following, the temporary table space of Asis temp_ts, a table space created explicitly to contain only temporary segments.

CREATE USER Sarath
IDENTIFIED BY password
DEFAULT TABLESPACE data_ts
QUOTA 100M ON test_ts
QUOTA 500K ON data_ts
TEMPORARY TABLESPACE temp_ts
PROFILE clerk;

To create a temporary tablespace, use the CREATE TEMPORARY TABLESPACE SQL statement.

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.

You can set the temporary table space for a user at user creation, and change it later using the ALTER USER statement. If you are logged in as user SYS, you can set a quota for the temporary table space, and other space allocations. (Only user SYS can do this, because all space in the temporary table space belongs to user SYS.) You can also establish table space groups instead of assigning individual temporary 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.

The following example demonstrates how to assign a user a profile.

CREATE USER as
IDENTIFIED BY password
DEFAULT TABLESPACE data_ts
QUOTA 100M ON test_ts
QUOTA 500K ON data_ts
TEMPORARY TABLE SPACE temp_ts
PROFILE clerk;

 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 subsequently granted to the user to be default roles. Use the ALTER USER statement to change the default roles for the user. For example:

GRANT USER As clerk_role;

 ALTER USER As DEFAULT ROLE clerk_role;

 Before a role can be made the default role for a user, that user must have been already granted the role.

About Altering User Accounts

Users can change their own passwords. However, to change any other option of a user security domain, you must have the ALTER USER system privilege. Security administrators are typically the only users that have this system privilege, as it allows a modification of any user security domain. This privilege includes the ability to set table space quotas for a user on any table space in the database, even if the user performing the modification does not have a quota for a specified table space.

Using the ALTER USER Statement to Alter a User Account

You can alter user security settings with the ALTER USER SQL statement. Changing user security settings affects the future user sessions, not current sessions.
Example 2-2 Altering a User Account

ALTER USER Sarath
IDENTIFIED EXTERNALLY
DEFAULT TABLESPACE data_ts
TEMPORARY TABLESPACE temp_ts
QUOTA 100M ON data_ts
QUOTA 0 ON test_ts
PROFILE clerk;

 The ALTER USER statement here changes the security settings for the user Sarath as follows:

  • Authentication is changed to use the operating system account of the user Sarath.
  • The default and temporary tablespaces are explicitly set for user SARATH.
  • The user Sarath is given a 100M quota for the DATA_TS tablespace.
  • The quota on the test_ts is revoked for the user Sarath.
  • The user Sarath is assigned the clerk profile.

CHANGING SYSUSER PASSWORD:

Example 2-3 Using ORAPWD to Change the SYS User Password

orapwd file=’orapworcl’

Enter password for SYS: new_password

 Managing Resources with Profiles

profile is a named set of resource limits and password parameters that restrict database usage and instance resources for a user. You can assign a profile to each user, and a default profile to all others. Each user can have only one profile, and creating a new one supersedes an earlier version.

You need to create and manage user profiles only if resource limits are a requirement of your database security policy. To use profiles, first categorize the related types of users in a database. Just as roles are used to manage the privileges of related users, profiles are used to manage the resource limits of related users. Determine how many profiles are needed to encompass all types of users in a database and then determine appropriate resource limits for each profile.

In general, the word profile refers to a collection of attributes that apply to a user, enabling a single point of reference for any of multiple users that share those exact attributes. User profiles in Oracle Internet Directory contain attributes pertinent to directory usage and authentication for each user. Similarly, profiles in Oracle Label Security contain attributes useful in label security user administration and operations management. Profile attributes can include restrictions on system resources. You can use Database Resource Manager to set these types of resource limits.

Profile resource limits are enforced only when you enable resource limitation for the associated database. Enabling this limitation can occur either before starting up the database (using the RESOURCE_LIMIT initialization parameter) or while it is open (using the ALTER SYSTEM statement).

Though password parameters reside in profiles, they are unaffected by RESOURCE_LIMIT or ALTER SYSTEM and password management is always enabled. In Oracle Database, Database Resource Manager primarily handles resource allocations and restrictions.

Creating Profiles

Any authorized database user can create, assign to users, alter, and drop a profile at any time (using the CREATE USER or ALTER USER statement). Profiles can be assigned only to users and not to roles or other profiles. Profile assignments do not affect current sessions, instead, they take effect only in subsequent sessions. To find information about current profiles, query the DBA_PROFILES view.

Dropping Profiles

To drop a profile, you must have the DROP PROFILE system privilege. You can drop a profile (other than the default profile) using the SQL statement DROP PROFILE.To successfully drop a profile currently assigned to a user, use the CASCADE option.

The following statement drops the profile clerk, even though it is assigned to a user:

DROP PROFILE clerk CASCADE;

 Any user currently assigned to a profile that is dropped is automatically assigned to the DEFAULT profile. The DEFAULT profile cannot be dropped. When a profile is dropped, the drop does not affect currently active sessions. Only sessions created after a profile is dropped use the modified pro file assignments.

Deleting User Accounts

When you drop a user account, Oracle Database removes the user account and associated schema from the data dictionary. It also immediately drops all schema objects contained in the user schema, if any.

 Notes:

  • If a user schema and associated objects must remain but the user must be denied access to the database, then revoke the CREATE SESSIONprivilege from the user.
  • Do not attempt to drop the SYSor SYSTEM Doing so corrupts your database.

A user that is currently connected to a database cannot be dropped. To drop a connected user, you must first terminate the user sessions using the SQL statement ALTER SYSTEM with the KILL SESSION clause. You can find the session ID (SID) by querying the V$SESSION view.

Example 2-4 shows how to query V$SESSION and displays the session ID, serial number, and user name for user SARATH.

Example 2-4 Querying V$SESSION for the Session ID of a User

SELECT SID, SERIAL#, USERNAME FROM V$SESSION;

SID           SERIAL#       USERNAME

——-   ————    —————

127              55234              SARATH

Example 2-5 shows how to stop the session for user Sarath.

Example 2-5 Killing a User Session

ALTER SYSTEM KILL SESSION ‘127, 55234’;

You can drop a user from a database using the DROP USER statement. To drop a user and all the user schema objects (if any), you must have the DROP USER system privilege. Because the DROP USER system privilege is powerful, a security administrator is typically the only type of user that has this privilege.

If the schema of the user contains any dependent schema objects, then use the CASCADE option to drop the user and all associated objects and foreign keys that depend on the tables of the user successfully. If you do not specify CASCADE and the user schema contains dependent objects, then an error message is returned and the user is not dropped.

Before dropping a user whose schema contains objects, thoroughly investigate which objects the schema contains and the implications of dropping them. You can find the objects owned by a particular user by querying the DBA_OBJECTS view.

Example 2-6 shows how to find the objects owned by user Sarath.

Example 2-6 Finding Objects Owned by a User

SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER LIKE ‘SARATH’;

 (Enter the user name in capital letters.) Pay attention to any unknown cascading effects. For example, if you intend to drop a user who owns a table, then check whether any views or procedures depend on that particular table.

Example 2-7 drops the user Sarath and all associated objects and foreign keys that depend on the tables owned by Sarath.

Example 2-7 Dropping a User Account

DROP USER Sarath CASCADE;

Using Data Dictionary Views to Find Information About Users and Profiles

Table 2-1 lists data dictionary views that contain information about database users and profiles.

View Description
ALL_OBJECTS Describes all objects accessible to the current user
ALL_USERS Lists users visible to the current user, but does not describe them
DBA_PROFILES Displays all profiles and their limits
DBA_TS_QUOTAS Describes tablespace quotas for users
DBA_OBJECTS Describes all objects in the database
DBA_USERS Describes all users of the database
DBA_USERS_WITH_DEFPWD Lists all user accounts that have default passwords
PROXY_USERS Describes users who can assume the identity of other users
RESOURCE_COST Lists the cost for each resource in terms of CPUs for each session, reads for each session, connection times, and SGA
USER_PASSWORD_LIMITS Describes the password profile parameters that are assigned to the user
USER_RESOURCE_LIMITS Displays the resource limits for the current user
USER_TS_QUOTAS Describes tablespace quotas for users
USER_OBJECTS Describes all objects owned by the current user
USER_USERS Describes only the current user
V$SESSION Lists session information for each current session, includes user name
V$SESSTAT Lists user session statistics
V$STATNAME Displays decoded statistic names for the statistics shown in the V$SESSTAT view

The following sections present examples of using these views. These examples assume that the following statements have been run:

CREATE PROFILE clerk LIMIT
SESSIONS_PER_USER 1
IDLE_TIME 30
CONNECT_TIME 600;

CREATE USER jfee
IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp_ts
QUOTA 500K ON users
PROFILE clerk;

CREATE USER dcranney
IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp_ts
QUOTA unlimited ON users;

CREATE USER user scott IDENTIFIED BY password;

Listing All Users and Associated Information

To find all users and their associated information as defined in the database, query the DBA_USERS view.

For example:

SELECT USERNAME, PROFILE, ACCOUNT_STATUS, AUTHENTICATION_TYPE FROM DBA_USERS;

 USERNAME            PROFILE               ACCOUNT_STATUS          AUTHENTICATION_TYPE

—————        —————           ————————-         ——————————

SYS                             DEFAULT                           OPEN                                         PASSWORD

SYSTEM                    DEFAULT                           OPEN                                        PASSWORD

USERSCOTT           DEFAULT                            OPEN                                        PASSWORD

JFEE                           CLERK                                 OPEN                                         GLOBAL

DCRANNEY             DEFAULT                           OPEN                                         EXTERNAL

 Listing All Tablespace Quotas

Use the DBA_TS_QUOTAS view to list all tablespace quotas specifically assigned to each user.

SELECT * FROM DBA_TS_QUOTAS;

 TABLESPACE       USERNAME         BYTES        MAX_BYTES       BLOCKS      MAX_BLOCKS

—————–       ————–         ———      —————-      ———–    —————-

USERS                               JFEE                      0                   512000                      0                    250

USERS                         DCRANNEY               0                        -1                            0                    -1

When specific quotas are assigned, the exact number is indicated in the MAX_BYTES column. This number is always a multiple of the database block size, so if you specify a tablespace quota that is not a multiple of the database block size, then it is rounded up accordingly. Unlimited quotas are indicated by -1.

Listing All Profiles and Assigned Limits

The DBA_PROFILE view lists all profiles in the database and associated settings for each limit in each profile.

SELECT * FROM DBA_PROFILES ORDER BY PROFILE;

 PROFILE             RESOURCE_NAME             RESOURCE_TYPE         LIMIT

————-        ————————-           ———————-     ————–

CLERK               COMPOSITE_LIMIT                           KERNEL               DEFAULT

CLERK               FAILED_LOGIN_ATTEMPTS        PASSWORD          DEFAULT

CLERK               PASSWORD_LIFE_TIME                PASSWORD           DEFAULT

To find the default profile values, run the following query:

SELECT * FROM DBA_PROFILES WHERE PROFILE = ‘DEFAULT’;

 

PROFILE             RESOURCE_NAME             RESOURCE_TYPE          LIMIT

————–   ————————-          ——————-      ————–

DEFAULT             COMPOSITE_LIMIT                  KERNEL                 UNLIMITED

DEFAULT             SESSIONS_PER_USER               KERNEL                 UNLIMITED

 Viewing Memory Use for Each User Session

To find the memory use for each user session, query the V$SESSION view. The following query lists all current sessions, showing the Oracle Database user and current User Global Area (UGA) memory use for each session:

SELECT USERNAME, VALUE || ‘bytes’ “Current UGA memory”

FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name

WHERE sess.SID = stat.SID

AND stat.STATISTIC# = name.STATISTIC#

AND name.NAME = ‘session uga memory’;

USERNAME                       Current UGA memory

——————–          ——————————

18636bytes

17464bytes

19180bytes

18364bytes

39384bytes

35292bytes

17696bytes

15868bytes

USERSCOTT                         42244bytes

SYS                                           98196bytes

SYSTEM                                 30648bytes

 11 rows selected.

 To see the maximum UGA memory allocated to each session since the instance started, replace ‘session uga memory’ in the preceding query with ‘session uga memory max’.

Read More:
PFILE & SPFILE
Oracle Architecture and Adminstration
ASM DISK Creations

Comments

comments

Check Also

DATA BASE LINKS

DATABASE LINKS: What are the db links? create a link from one database to another …

Leave a Reply

Your email address will not be published. Required fields are marked *