Edit D:\rfid\database\database\doc\server.112\e10897\users_secure.htm
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html lang="en" xml:lang="en" xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=us-ascii" /> <meta http-equiv="Content-Language" content="en" /> <meta http-equiv="Content-Style-Type" content="text/css" /> <meta http-equiv="Content-Script-Type" content="text/javascript" /> <meta name="robots" content="all" scheme="http://www.robotstxt.org/" /> <meta name="generator" content="Oracle DARB XHTML Converter (Mode = document) - Version 5.1" /> <meta name="Date" content="2009-09-09T22:35:13Z" /> <meta name="doctitle" content="Oracle® Database 2 Day DBA 11g Release 2 (11.2)" /> <meta name="partno" content="E10897-02" /> <meta name="docid" content="ADMQS" /> <link rel="Start" href="../../index.htm" title="Home" type="text/html" /> <link rel="Copyright" href="../../dcommon/html/cpyr.htm" title="Copyright" type="text/html" /> <link rel="Stylesheet" href="../../dcommon/css/blafdoc.css" title="Default" type="text/css" /> <script type="text/javascript" src="../../dcommon/js/doccd.js"> </script> <link rel="Contents" href="toc.htm" title="Contents" type="text/html" /> <link rel="Index" href="index.htm" title="Index" type="text/html" /> <link rel="Prev" href="storage.htm" title="Previous" type="text/html" /> <link rel="Next" href="schema.htm" title="Next" type="text/html" /> <link rel="alternate" href="../e10897.pdf" title="PDF version" type="application/pdf" /> <title>Administering User Accounts and Security</title> </head> <body> <div class="header"> <div class="zz-skip-header"><a name="top" id="top" href="#BEGIN">Skip Headers</a></div> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <tr> <td align="left" valign="top"><b>Oracle® Database 2 Day DBA<br /> 11<i>g</i> Release 2 (11.2)</b><br /> Part Number E10897-02</td> <td valign="bottom" align="right"> <table class="icons oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="245"> <tr> <td align="center" valign="top"><a href="../../index.htm"><img width="24" height="24" src="../../dcommon/gifs/doclib.gif" alt="Go to Documentation Home" /><br /> <span class="icon">Home</span></a></td> <td align="center" valign="top"><a href="../../nav/portal_booklist.htm"><img width="24" height="24" src="../../dcommon/gifs/booklist.gif" alt="Go to Book List" /><br /> <span class="icon">Book List</span></a></td> <td align="center" valign="top"><a href="toc.htm"><img width="24" height="24" src="../../dcommon/gifs/toc.gif" alt="Go to Table of Contents" /><br /> <span class="icon">Contents</span></a></td> <td align="center" valign="top"><a href="index.htm"><img width="24" height="24" src="../../dcommon/gifs/index.gif" alt="Go to Index" /><br /> <span class="icon">Index</span></a></td> <td align="center" valign="top"><a href="../../dcommon/html/feedback.htm"><img width="24" height="24" src="../../dcommon/gifs/feedbck2.gif" alt="Go to Feedback page" /><br /> <span class="icon">Contact Us</span></a></td> </tr> </table> </td> </tr> </table> <hr /> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <tr> <td align="left" valign="top"> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="98"> <tr> <td align="center" valign="top"><a href="storage.htm"><img width="24" height="24" src="../../dcommon/gifs/leftnav.gif" alt="Go to previous page" /><br /> <span class="icon">Previous</span></a></td> <td align="center" valign="top"><a href="schema.htm"><img width="24" height="24" src="../../dcommon/gifs/rightnav.gif" alt="Go to next page" /><br /> <span class="icon">Next</span></a></td> </tr> </table> </td> <td align="right" valign="top" style="font-size: 90%"><a href="../e10897.pdf">View PDF</a></td> </tr> </table> <a name="BEGIN" id="BEGIN"></a></div> <div class="IND"><!-- End Header --><a id="CHDJBHHI" name="CHDJBHHI"></a><a id="ADMQS007" name="ADMQS007"></a> <h1 class="chapter"><span class="secnum">7</span> Administering User Accounts and Security</h1> <p><a id="sthref359" name="sthref359"></a><a id="sthref360" name="sthref360"></a>This chapter describes how to create and manage user accounts. It contains the following sections:</p> <ul> <li> <p><a href="#CHDDDEBI">About User Accounts</a></p> </li> <li> <p><a href="#CHDEBFCC">About User Privileges and Roles</a></p> </li> <li> <p><a href="#CHDFFCIG">About Administrative Accounts and Privileges</a></p> </li> <li> <p><a href="#CHDBHDCF">Administering Roles</a></p> </li> <li> <p><a href="#CHDEBHDE">Administering Database User Accounts</a></p> </li> <li> <p><a href="#CHDDADDJ">Setting the Database Password Policy</a></p> </li> <li> <p><a href="#CFHGEBDE">Users: Oracle By Example Series</a></p> </li> </ul> <a id="CHDDDEBI" name="CHDDDEBI"></a><a id="ADMQS071" name="ADMQS071"></a> <div class="sect1"> <h2 class="sect1">About User Accounts</h2> <p>For users to access your database, you must create user accounts and grant appropriate database access privileges to those accounts. A user account is identified by a user name and defines the attributes of the user, including the following:</p> <ul> <li> <p>Authentication method</p> </li> <li> <p>Password for database authentication</p> </li> <li> <p>Default tablespaces for permanent and temporary data storage</p> </li> <li> <p>Tablespace quotas</p> </li> <li> <p>Account status (locked or unlocked)</p> </li> <li> <p>Password status (expired or not)</p> </li> </ul> <p>When you create a user account, you must not only assign a user name, a password, and default tablespaces for the account, but you must also do the following:</p> <ul> <li> <p>Grant the appropriate system privileges, object privileges, and roles to the account.</p> </li> <li> <p>If the user will be creating database objects, then give the user account a space usage quota on each tablespace in which the objects will be created.</p> </li> </ul> <p><a id="sthref361" name="sthref361"></a>Oracle recommends that you grant each user just enough privileges to perform his job, and no more. For example, a database application developer needs privileges to create and modify tables, indexes, views, and stored procedures, but does not need (and should not be granted) privileges to drop (delete) tablespaces or recover the database. You can create user accounts for database administration, and grant only a subset of administrative privileges to those accounts.</p> <p>In addition, you may want to create user accounts that are used by applications only. That is, nobody logs in with these accounts; instead, applications use these accounts to connect to the database, and users log in to the applications. This type of user account avoids giving application users the ability to log in to the database directly, where they could unintentionally cause damage. See <a href="#CHDEBFCC">"About User Privileges and Roles"</a> for more information.</p> <p><a id="sthref362" name="sthref362"></a>When you create a user account, you are also implicitly creating a schema for that user. A <span class="bold">schema</span> is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user. For example, <code>hr.employees</code> refers to the table named <code>employees</code> in the <code>hr</code> schema. (The <code>employees</code> table is owned by <code>hr</code>.) The terms <span class="italic">database object</span> and <span class="italic">schema object</span> are used interchangeably.</p> <p>When you delete a user, you must either simultaneously delete all schema objects of that user, or you must have previously deleted the schema objects in separate operations.</p> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a class="olink CNCPT111" href="http://www.oracle.com/pls/db112/lookup?id=CNCPT111"></a><span class="italic">Oracle Database Concepts</span> for an overview of schema objects</p> </li> </ul> </div> <a id="ADMQS12179" name="ADMQS12179"></a> <p class="subhead2">Predefined User Accounts</p> <p>In addition to the user accounts that you create, the database includes several user accounts that are automatically created upon installation.</p> <p>All databases include the administrative accounts <code>SYS</code>, <code>SYSTEM</code>, <code>SYSMAN</code>, and <code>DBSNMP</code>. <span class="bold">Administrative accounts</span> are highly privileged accounts, and are needed only by individuals authorized to perform administrative tasks such as starting and stopping the database, managing database memory and storage, creating and managing database users, and so on. You log in to Oracle Enterprise Manager Database Control (Database Control) with <code>SYS</code>, <code>SYSTEM</code>, or <code>SYSMAN</code>. The Management Agent of Database Control uses the <code>DBSNMP</code> account to monitor and manage the database. You assign the passwords for these accounts when you create the database with Oracle Database Configuration Assistant (DBCA). You must not delete these accounts.</p> <p>All databases also include <span class="bold">internal accounts</span>, which are automatically created so that individual Oracle Database features or components such as Oracle Application Express can have their own schemas. To protect these accounts from unauthorized access, they are initially locked and their passwords are expired. (A <span class="bold">locked account</span> is an account for which login is disabled.) You must not delete internal accounts, and you must not use them to log in to the database.</p> <p>Your database may also include <span class="bold">sample schemas</span>, which are a set of interlinked schemas that enable Oracle documentation and Oracle instructional materials to illustrate common database tasks. These schemas also provide a way for you to experiment without endangering production data.</p> <p>Each sample schema has a user account associated with it. For example, the <code>hr</code> user account owns the <code>hr</code> schema, which contains a set of simple tables for a human resources application. The sample schema accounts are also initially locked and have an expired password. As the database administrator, you are responsible for unlocking these accounts and assigning passwords to these accounts.</p> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a class="olink TDPSG20030" href="http://www.oracle.com/pls/db112/lookup?id=TDPSG20030"><span class="italic">Oracle Database 2 Day + Security Guide</span></a> for a list of predefined user accounts</p> </li> <li> <p><a href="#CHDCCDGE">"Locking and Unlocking User Accounts"</a></p> </li> <li> <p><a href="#CHDFFCIG">"About Administrative Accounts and Privileges"</a></p> </li> <li> <p><a href="#CHDEBHDE">"Administering Database User Accounts"</a></p> </li> <li> <p><a class="olink COMSC" href="http://www.oracle.com/pls/db112/lookup?id=COMSC"><span class="italic">Oracle Database Sample Schemas</span></a> for a description of the sample schemas</p> </li> <li> <p><a class="olink CNCPT123" href="http://www.oracle.com/pls/db112/lookup?id=CNCPT123"></a><span class="italic">Oracle Database Concepts</span> for an overview of database security</p> </li> </ul> </div> </div> <!-- class="sect1" --> <a id="CHDEBFCC" name="CHDEBFCC"></a><a id="ADMQS12001" name="ADMQS12001"></a> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2 class="sect1">About User Privileges and Roles</h2> <p>User privileges provide a basic level of database security. They are designed to control user access to data and to limit the kinds of SQL statements that users can execute. When creating a user, you grant privileges to enable the user to connect to the database, to run queries and make updates, to create schema objects, and more.</p> <p>The main types of user privileges are as follows<a id="sthref363" name="sthref363"></a><a id="sthref364" name="sthref364"></a>:</p> <ul> <li> <p><span class="bold">System privileges</span>—A sys<a id="sthref365" name="sthref365"></a><a id="sthref366" name="sthref366"></a>tem privilege gives a user the ability to perform a particular action, or to perform an action on any schema objects of a particular type. For example, the system privilege <code>CREATE</code> <code>TABLE</code> permits a user to create tables in the schema associated with that user, and the system privilege <code>CREATE</code> <code>USER</code> permits a user to create database users.</p> </li> <li> <p><span class="bold">Object privileges</span>—An obje<a id="sthref367" name="sthref367"></a><a id="sthref368" name="sthref368"></a>ct privilege gives a user the ability to perform a particular action on a specific schema object. Different object privileges are available for different types of schema objects. The privilege to select rows from the <code>EMPLOYEES</code> table or to delete rows from the <code>DEPARTMENTS</code> table are examples of object privileges.</p> </li> </ul> <p>Mana<a id="sthref369" name="sthref369"></a><a id="sthref370" name="sthref370"></a>ging privileges is made easier by using <span class="bold">roles</span>, which are named groups of related privileges. You create roles, grant system and object privileges to the roles, and then grant roles to users. You can also grant roles to other roles. Unlike schema objects, roles are not contained in any schema.</p> <p><a href="#CHDHGCGH">Table 7-1</a> lists three widely used roles that are predefined in Oracle Database. You can grant these roles when you create a user or at any time thereafter.</p> <div class="tblhruleformal"><a id="ADMQS12341" name="ADMQS12341"></a><a id="sthref371" name="sthref371"></a><a id="CHDHGCGH" name="CHDHGCGH"></a> <p class="titleintable">Table 7-1 Oracle Database Predef<a id="sthref372" name="sthref372"></a>ine<a id="sthref373" name="sthref373"></a>d Roles</p> <table class="HRuleFormal" title="Oracle Database Predefined Roles" summary="This table lists the three roles that administrators can assign to users." dir="ltr" border="1" width="100%" frame="hsides" rules="rows" cellpadding="3" cellspacing="0"> <col width="19%" /> <col width="*" /> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t4">Role Name</th> <th align="left" valign="bottom" id="r1c2-t4">Description</th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t4" headers="r1c1-t4"> <p><code>CON<a id="sthref374" name="sthref374"></a><a id="sthref375" name="sthref375"></a>NECT</code></p> </td> <td align="left" headers="r2c1-t4 r1c2-t4"> <p>Enables a user to connect to the database. Grant this role to any user or application that needs database access. If you create a user using Database Control, then this role is automatically granted to the user.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t4" headers="r1c1-t4"> <p><code>RESO<a id="sthref376" name="sthref376"></a><a id="sthref377" name="sthref377"></a>URCE</code></p> </td> <td align="left" headers="r3c1-t4 r1c2-t4"> <p>Enables a user to create, modify, and delete certain types of schema objects in the schema associated with that user. Grant this role only to developers and to other users that must create schema objects. This role grants a subset of the create object system privileges. For example, it grants the <code>CREATE</code> <code>TABLE</code> system privilege, but does not grant the <code>CREATE</code> <code>VIEW</code> system privilege. It grants only the following privileges: <code>CREATE</code> <code>CLUSTER</code>, <code>CREATE</code> <code>INDEXTYPE</code>, <code>CREATE</code> <code>OPERATOR</code>, <code>CREATE</code> <code>PROCEDURE</code>, <code>CREATE</code> <code>SEQUENCE</code>, <code>CREATE</code> <code>TABLE</code>, <code>CREATE</code> <code>TRIGGER</code>, <code>CREATE</code> <code>TYPE</code>. In addition, this role grants the <code>UNLIMITED</code> <code>TABLESPACE</code> system privilege, which effectively assigns a space usage quota of <code>UNLIMITED</code> on all tablespaces in which the user creates schema objects.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t4" headers="r1c1-t4"> <p><code>DB<a id="sthref378" name="sthref378"></a><a id="sthref379" name="sthref379"></a>A</code></p> </td> <td align="left" headers="r4c1-t4 r1c2-t4"> <p>Enables a user to perform most administrative functions, including creating users and granting privileges; creating and granting roles; creating, modifying, and deleting schema objects in any schema; and more. It grants all system privileges, but does not include the privileges to start or shut down the database instance. It is by default granted to users <code>SYS</code> and <code>SYSTEM</code>.</p> </td> </tr> </tbody> </table> <br /></div> <!-- class="tblhruleformal" --> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CHDBHDCF">"Administering Roles"</a></p> </li> <li> <p><a href="#CHDEBHDE">"Administering Database User Accounts"</a></p> </li> <li> <p><a href="schema.htm#CFHJAHAA">Chapter 8, "Managing Schema Objects"</a></p> </li> <li> <p><a class="olink TDPSG30000" href="http://www.oracle.com/pls/db112/lookup?id=TDPSG30000"><span class="italic">Oracle Database 2 Day + Security Guide</span></a> for more information about privileges and roles</p> </li> <li> <p><a class="olink SQLRF01603" href="http://www.oracle.com/pls/db112/lookup?id=SQLRF01603"><span class="italic">Oracle Database SQL Language Reference</span></a> for tables of system privileges, object privileges, and predefined roles</p> </li> <li> <p><a class="olink CNCPT123" href="http://www.oracle.com/pls/db112/lookup?id=CNCPT123"></a><span class="italic">Oracle Database Concepts</span> for an overview of database security</p> </li> </ul> </div> </div> <!-- class="sect1" --> <a id="CHDFFCIG" name="CHDFFCIG"></a><a id="ADMQS12002" name="ADMQS12002"></a> <div class="sect1"><!-- infolevel="all" infotype="Concept" --> <h2 class="sect1">About Administrative Accounts and Privileges</h2> <p><a id="sthref380" name="sthref380"></a><a id="sthref381" name="sthref381"></a><a id="sthref382" name="sthref382"></a>Administrative accounts and privileges enable you to perform <a id="sthref383" name="sthref383"></a><a id="sthref384" name="sthref384"></a>administrative functions such as managing users, managing database memory, and starting up and shutting down the database.</p> <p>This section contains the following topics:</p> <ul> <li> <p><a href="#CHDBJCHH">SYS and SYSTEM Users</a></p> </li> <li> <p><a href="#CHDECEBA">SYSDBA and SYSOPER System Privileges</a></p> </li> </ul> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CHDDDEBI">"About User Accounts"</a></p> </li> <li> <p><a href="#CHDEBFCC">"About User Privileges and Roles"</a></p> </li> <li> <p><a href="#CHDEBHDE">"Administering Database User Accounts"</a></p> </li> </ul> </div> <a id="CHDBJCHH" name="CHDBJCHH"></a><a id="ADMQS12003" name="ADMQS12003"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">SYS and SY<a id="sthref385" name="sthref385"></a><a id="sthref386" name="sthref386"></a>STEM Users</h3> <p>The following administrative user accounts are automatically created when you install Oracle Database. They are both created with the password that you supplied upon installation, and they are both automatically granted the <code>DBA</code> role.</p> <ul> <li> <p><code>SYS</code></p> <p><a id="sthref387" name="sthref387"></a>This account can perform all administrative functions. All base (underlying) tables and views for the database data dictionary are stored in the <code>SYS</code> schema. These base tables and views are critical for the operation of Oracle Database. To maintain the integrity of the data dictionary, tables in the <code>SYS</code> schema are manipulated only by the database. They should never be modified by any user or database administrator. You must not create any tables in the <code>SYS</code> schema.</p> <p>The <code>SYS</code> user is granted the <code>SYSDBA</code> privilege, which enables a user to perform high-level administrative tasks such as backup and recovery.</p> </li> <li> <p><code>SYSTEM</code></p> <p>This account can perform all administrative functions except the following:</p> <ul> <li> <p>Backup and recovery</p> </li> <li> <p>Database upgrade</p> </li> </ul> <p>While this account can be used to perform day-to-day administrative tasks, Oracle strongly recommends creating named users account for administering the Oracle database to enable monitoring of database activity.</p> </li> </ul> </div> <!-- class="sect2" --> <a id="CHDECEBA" name="CHDECEBA"></a><a id="ADMQS12004" name="ADMQS12004"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">SYSD<a id="sthref388" name="sthref388"></a><a id="sthref389" name="sthref389"></a><a id="sthref390" name="sthref390"></a>BA and SYSOPER System Privileges</h3> <p><code>SYSDBA</code> and <code>SYSOPER</code> are administrative privileges required to perform high-level administrative operations such as creating, starting up, shutting down, backing up, or recovering the database. The <code>SYSDBA</code> system privilege is for fully empowered database administrators and the <code>SYSOPER</code> system privilege allows a user to perform basic operational tasks, but without the ability to look at user data.</p> <p>The <code>SYSDBA</code> and <code>SYSOPER</code> system privileges allow access to a database instance even when the database is not open. Control of these privileges is therefore completely outside of the database itself. This control enables an administrator who is granted one of these privileges to connect to the database instance to start the database.</p> <p>You can also think of the <code>SYSDBA</code> and <code>SYSOPER</code> privileges as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other way. For example, if you have the <code>SYSDBA</code> privilege, then you can connect to the database using <code>AS SYSDBA</code>.</p> <p>The <code>SYS</code> user is automatically granted the <code>SYSDBA</code> privilege upon installation. When you log in as user <code>SYS</code>, you must connect to the database as <code>SYSDBA</code> or <code>SYSOPER</code>. Connecting as a <code>SYSDBA</code> user invokes the <code>SYSDBA</code> privilege; connecting as <code>SYSOPER</code> invokes the <code>SYSOPER</code> privilege. Oracle Enterprise Manager Database Control does not permit you to log in as user <code>SYS</code> without connecting as <code>SYSDBA</code> or <code>SYSOPER</code>.</p> <p>When you connect with the <code>SYSDBA</code> or <code>SYSOPER</code> privilege, you connect with a default schema, not with the schema that is generally associated with your user name. For <code>SYSDBA</code> this schema is <code>SYS</code>; for <code>SYSOPER</code> the schema is <code>PUBLIC</code>.</p> <div class="infoboxnote"> <p class="notep1">Caution:</p> When you connect as user <code>SYS</code>, you have unlimited privileges on data dictionary tables. Be certain that you do not modify any data dictionary<a id="sthref391" name="sthref391"></a> tables.</div> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a class="olink ADMIN11048" href="http://www.oracle.com/pls/db112/lookup?id=ADMIN11048"><span class="italic">Oracle Database Administrator's Guide</span></a> for the operations authorized with the <code>SYSDBA</code> and <code>SYSOPER</code> privileges</p> </li> </ul> </div> </div> <!-- class="sect2" --></div> <!-- class="sect1" --> <a id="CHDBHDCF" name="CHDBHDCF"></a><a id="ADMQS072" name="ADMQS072"></a> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2 class="sect1">Administering Roles</h2> <p><a id="sthref392" name="sthref392"></a><span class="bold">Roles</span> are named groups of related system and object privileges. You create roles and then assign them to users and to other roles.</p> <p>This section contains the following topics:</p> <ul> <li> <p><a href="#CHDIGAFJ">Viewing Roles</a></p> </li> <li> <p><a href="#g1101992">Example: Creating a Role</a></p> </li> <li> <p><a href="#CHDGCEGB">Example: Modifying a Role</a></p> </li> <li> <p><a href="#BGBEABDI">Deleting a Role</a></p> </li> </ul> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CHDEBFCC">"About User Privileges and Roles"</a></p> </li> <li> <p><a class="olink TDPSG30000" href="http://www.oracle.com/pls/db112/lookup?id=TDPSG30000"><span class="italic">Oracle Database 2 Day + Security Guide</span></a> for more information about administering user security, roles, and privileges</p> </li> </ul> </div> <a id="CHDIGAFJ" name="CHDIGAFJ"></a><a id="ADMQS0724" name="ADMQS0724"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Viewing Roles</h3> <p><a id="sthref393" name="sthref393"></a>You view roles on the Roles page of Oracle Enterprise Manager Database Control (Database Control).</p> <p class="orderedlisttitle">To view roles: </p> <ol> <li> <p>Go to the Database Home page, logging in with a user account that has privileges to manage roles. An example of such a user account is <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Server</span> to view the Server subpage.</p> </li> <li> <p>In the Security section, click <span class="bold">Roles</span>.</p> <p>The Roles page appears.</p> <img width="721" height="450" src="img/roles.gif" alt="Description of roles.gif follows" title="Description of roles.gif follows" longdesc="img_text/roles.htm" /><br /> <a id="sthref395" name="sthref395" href="img_text/roles.htm">Description of the illustration roles.gif</a><br /> <br /></li> <li> <p>To view the details of a particular role, in the <span class="bold">Select</span> column, select the name of the role you want to view, and then click <span class="bold">View</span>.</p> <p>If you do not see the role, then it may be on another page. In this case, do one of the following:</p> <ul> <li> <p>Just above the list of roles, click <span class="bold">Next</span> to view the next page. Continue clicking <span class="bold">Next</span> until you see the desired role.</p> </li> <li> <p>Use the Search area of the page to search for the desired role. In the <span class="bold">Object Name</span> field, enter the first few letters of the role, and then click <span class="bold">Go</span>.</p> <p>You can then select the role and click <span class="bold">View</span>.</p> </li> </ul> <p>The View Role page appears. In this page, you can see all the privileges and roles granted to the selected role.</p> </li> </ol> </div> <!-- class="sect2" --> <a id="g1101992" name="g1101992"></a><a id="ADMQS0725" name="ADMQS0725"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Example: Creating a Role</h3> <p>Suppose you want to create a role called <code>APPDEV</code> for application developers. Because application developers must be able to create, modify, and delete the schema objects that their applications use, you want the <code>APPDEV</code> role to include the system privileges shown in <a href="#BGBBIBHI">Table 7-2</a>.</p> <div class="tblformal"><a id="ADMQS12342" name="ADMQS12342"></a><a id="sthref396" name="sthref396"></a><a id="BGBBIBHI" name="BGBBIBHI"></a> <p class="titleintable">Table 7-2 System Privileges Granted to the APPDEV Role</p> <table class="Formal" title="System Privileges Granted to the APPDEV Role" summary="APPDEV privs" dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <col width="25%" /> <col width="*" /> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t10">Privilege</th> <th align="left" valign="bottom" id="r1c2-t10">Description</th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t10" headers="r1c1-t10"> <p><code>CREATE</code> <code>TABLE</code></p> </td> <td align="left" headers="r2c1-t10 r1c2-t10"> <p>Enables a user to create, modify, and delete tables in his schema.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t10" headers="r1c1-t10"> <p><code>CREATE</code> <code>VIEW</code></p> </td> <td align="left" headers="r3c1-t10 r1c2-t10"> <p>Enables a user to create, modify, and delete views in his schema.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t10" headers="r1c1-t10"> <p><code>CREATE</code> <code>PROCEDURE</code></p> </td> <td align="left" headers="r4c1-t10 r1c2-t10"> <p>Enables a user to create, modify, and delete procedures in his schema.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r5c1-t10" headers="r1c1-t10"> <p><code>CREATE</code> <code>TRIGGER</code></p> </td> <td align="left" headers="r5c1-t10 r1c2-t10"> <p>Enables a user to create, modify, and delete triggers in his schema.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r6c1-t10" headers="r1c1-t10"> <p><code>CREATE</code> <code>SEQUENCE</code></p> </td> <td align="left" headers="r6c1-t10 r1c2-t10"> <p>Enables a user to create, modify, and delete sequences in his schema.</p> </td> </tr> <tr align="left" valign="top"> <td align="left" id="r7c1-t10" headers="r1c1-t10"> <p><code>CREATE</code> <code>SYNONYM</code></p> </td> <td align="left" headers="r7c1-t10 r1c2-t10"> <p>Enables a user to create, modify, and delete synonyms in his schema.</p> </td> </tr> </tbody> </table> <br /></div> <!-- class="tblformal" --> <p class="orderedlisttitle">To create the APPDEV role: </p> <ol> <li> <p>Go to the Roles page, as described in <a href="#CHDIGAFJ">"Viewing Roles"</a>.</p> </li> <li> <p>Click <span class="bold">Create</span>.</p> <p>The Create Role page appears.</p> </li> <li> <p>In the <span class="bold">Name</span> field, enter <code>APPDEV</code>.</p> </li> <li> <p>Click <span class="bold">System Privileges</span> to go to the System Privileges subpage.</p> <img width="715" height="205" src="img/create_role.gif" alt="Description of create_role.gif follows" title="Description of create_role.gif follows" longdesc="img_text/create_role.htm" /><br /> <a id="sthref398" name="sthref398" href="img_text/create_role.htm">Description of the illustration create_role.gif</a><br /> <br /> <p>The table of system privileges for this role contains no rows yet.</p> </li> <li> <p>Click <span class="bold">Edit List</span>.</p> <p>The Modify System Privileges page appears.</p> </li> <li> <p>In the Available System Privileges list, double-click privileges to add them to the Selected System Privileges list.</p> <p>The privileges to add are listed in <a href="#BGBBIBHI">Table 7-2</a>.</p> <img width="714" height="305" src="img/modify_system_privs.gif" alt="Description of modify_system_privs.gif follows" title="Description of modify_system_privs.gif follows" longdesc="img_text/modify_system_privs.htm" /><br /> <a id="sthref399" name="sthref399" href="img_text/modify_system_privs.htm">Description of the illustration modify_system_privs.gif</a><br /> <br /> <div class="infoboxnote"> <p class="notep1">Note:</p> Double-clicking a privilege is a shortcut. You can also select a privilege and then click the <span class="bold">Move</span> button. To select multiple privileges, hold down the Shift key while selecting a range of privileges, or press the Ctrl key and select individual privileges, then click <span class="bold">Move</span> after you have selected the privileges.</div> </li> <li> <p>Click <span class="bold">OK</span>.</p> <p>The System Privileges subpage returns, showing the system privileges that you selected. At this point, you could click <span class="bold">Roles</span> to assign other roles to the <code>APPDEV</code> role, or click <span class="bold">Object Privileges</span> to assign object privileges to the <code>APPDEV</code> role.</p> </li> <li> <p>Click <span class="bold">OK</span> to return to the Roles page.</p> <p>The <code>APPDEV</code> role now appears in the table of database roles.</p> </li> </ol> </div> <!-- class="sect2" --> <a id="CHDGCEGB" name="CHDGCEGB"></a><a id="ADMQS0726" name="ADMQS0726"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Example: Modifying a Role</h3> <p>Suppose your applications make use of Oracle Streams Advanced Queuing, and you determine that developers must be granted the roles <code>AQ_ADMINISTRATOR_ROLE</code> and <code>AQ_USER_ROLE</code> to develop and test their applications. You must edit the <code>APPDEV</code> role to grant it these two Advanced Queuing roles.</p> <p class="orderedlisttitle">To modify the APPDEV role: </p> <ol> <li> <p>Go to the Roles page, as described in <a href="#CHDIGAFJ">"Viewing Roles"</a>.</p> </li> <li> <p>In the Select column, click <span class="bold">APPDEV</span> role, and then click <span class="bold">Edit</span>.</p> <p>The Edit Role page appears.</p> </li> <li> <p>Click <span class="bold">Roles</span> to navigate to the Roles subpage.</p> </li> <li> <p>Click <span class="bold">Edit List</span>.</p> <p>The Modify Roles page appears.</p> </li> <li> <p>In the Available Roles list, double-click the roles <code>AQ_ADMINISTRATOR_ROLE</code> and <code>AQ_USER_ROLE</code> to add them to the Selected Roles list.</p> </li> <li> <p>Click <span class="bold">OK</span>.</p> <p>The Roles subpage returns, showing that the roles that you selected were granted to the <code>APPDEV</code> role.</p> </li> <li> <p>Click <span class="bold">Apply</span> to save your changes.</p> <p>An update message appears, indicating that the role <code>APPDEV</code> was modified successfully.</p> </li> </ol> </div> <!-- class="sect2" --> <a id="BGBEABDI" name="BGBEABDI"></a><a id="ADMQS0727" name="ADMQS0727"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Deleting a Role</h3> <p>Use caution when deleting a role, because Database Control deletes a role even if that role is currently granted to one or more users. Before deleting a role, you may want to determine if the role has any grantees. Dropping (deleting) a role automatically removes the privileges associated with that role from all users that had been granted the role.</p> <p class="orderedlisttitle">To determine if a role has any grantees: </p> <ol> <li> <p>Go to the Roles page as described in <a href="#CHDIGAFJ">"Viewing Roles"</a>.</p> </li> <li> <p>In the Select column, click the desired role.</p> <p>If you do not see the desired role, then it may be on another page. In this case, do one of the following:</p> <ul> <li> <p>Just above the list of roles, click <span class="bold">Next</span> to view the next page. Continue clicking <span class="bold">Next</span> until you see the desired role.</p> </li> <li> <p>Use the Search area of the page to search for the desired role. In the <span class="bold">Object Name</span> field, enter the first few letters of the role, and then click <span class="bold">Go</span>.</p> </li> </ul> <p>You can then select the role.</p> </li> <li> <p>In the Actions list, select <span class="bold">Show Grantees</span>, and then click <span class="bold">Go</span>.</p> <p>A report appears, listing the users that are granted the selected role.</p> </li> <li> <p>Click <span class="bold">Cancel</span> to return to the Roles page.</p> </li> </ol> <p class="orderedlisttitle">To delete a role: </p> <ol> <li> <p>If you are not there, then go to the Roles page, as described in <a href="#CHDIGAFJ">"Viewing Roles"</a>.</p> </li> <li> <p>In the Select column, click the desired role, and then click <span class="bold">Delete</span>.</p> <p>A confirmation page appears.</p> </li> <li> <p>Click <span class="bold">Yes</span>.</p> <p>A confirmation message indicates that the role has been deleted successfully.</p> </li> </ol> </div> <!-- class="sect2" --></div> <!-- class="sect1" --> <a id="CHDEBHDE" name="CHDEBHDE"></a><a id="ADMQS074" name="ADMQS074"></a> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2 class="sect1">Administering Database User Accounts</h2> <p><a id="sthref403" name="sthref403"></a><a id="sthref404" name="sthref404"></a>This section provides instructions for creating and managing user accounts for the people and applications that use your database. It contains the following topics:</p> <ul> <li> <p><a href="#CHDJEBDG">Viewing User Accounts</a></p> </li> <li> <p><a href="#CHDDCIGA">Example: Creating a User Account</a></p> </li> <li> <p><a href="#CHDJGCHH">Creating a New User Account by Duplicating an Existing User Account</a></p> </li> <li> <p><a href="#CHDBDBGI">Example: Granting Privileges and Roles to a User Account</a></p> </li> <li> <p><a href="#CHDGIACA">Example: Assigning a Tablespace Quota to a User Account</a></p> </li> <li> <p><a href="#CHDFGCCA">Example: Modifying a User Account</a></p> </li> <li> <p><a href="#CHDCCDGE">Locking and Unlocking User Accounts</a></p> </li> <li> <p><a href="#CHDJCIFG">Expiring a User Password</a></p> </li> <li> <p><a href="#CHDHHGAF">Example: Deleting a User Account</a></p> </li> </ul> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CHDDDEBI">"About User Accounts"</a></p> </li> </ul> </div> <a id="CHDJEBDG" name="CHDJEBDG"></a><a id="ADMQS12005" name="ADMQS12005"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Viewing User Accounts</h3> <p><a id="sthref405" name="sthref405"></a><a id="sthref406" name="sthref406"></a>You view user accounts on the Users page of Oracle Enterprise Manager Database Control (Database Control).</p> <p class="orderedlisttitle">To view users: </p> <ol> <li> <p>Go to the Database Home page, logging in with a user account that has privileges to manage users, for example, <code>SYSTEM</code>.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Server</span> to view the Server subpage.</p> </li> <li> <p>In the Security section, click <span class="bold">Users</span>.</p> <p>The Users page appears.</p> <img width="794" height="579" src="img/users_page.gif" alt="Users page." title="Users page." longdesc="img_text/users_page.htm" /><br /> <a id="sthref408" name="sthref408" href="img_text/users_page.htm">Description of the illustration users_page.gif</a><br /> <br /></li> <li> <p>To view the details of a particular user, in the <span class="bold">Select</span> column, click the user, and then click <span class="bold">View</span>.</p> <p>If you do not see the user, then it may be on another page. In this case, do one of the following:</p> <ul> <li> <p>Just above the list of users, click <span class="bold">Next</span> to view the next page. Continue clicking <span class="bold">Next</span> until you see the desired user.</p> </li> <li> <p>Use the Search area of the page to search for the desired user. In the <span class="bold">Object Name</span> field, enter the first few letters of the user name, and then click <span class="bold">Go</span>.</p> </li> <li> <p>Click a table column to change the sort order of the data in the table. For example, to list the users in reverse alphabetical order, click the UserName column heading.</p> </li> </ul> <p>You can then select the user and click <span class="bold">View</span>.</p> <p>The View User page appears, and displays all user attributes.</p> </li> </ol> </div> <!-- class="sect2" --> <a id="CHDDCIGA" name="CHDDCIGA"></a><a id="ADMQS0741" name="ADMQS0741"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Example: Creating a User Account</h3> <p><a id="sthref409" name="sthref409"></a><a id="sthref410" name="sthref410"></a>Suppose you want to create a user account for a database application developer named Nick. Because Nick is a developer, you want to grant him the database privileges and roles that he requires to build and test his applications. You also want to give Nick a 10 MB quota on his default tablespace so that he can create schema objects in that tablespace.</p> <p class="orderedlisttitle">To create the user Nick: </p> <ol> <li> <p>Go to the Users page, as described in <a href="#CHDJEBDG">"Viewing User Accounts"</a>.</p> </li> <li> <p>On the Users page, click <span class="bold">Create</span>.</p> <p>The Create User page appears, displaying the General subpage.</p> <img width="765" height="451" src="img/create_user.gif" alt="Create User General Page" title="Create User General Page" longdesc="img_text/create_user.htm" /><br /> <a id="sthref412" name="sthref412" href="img_text/create_user.htm">Description of the illustration create_user.gif</a><br /> <br /></li> <li> <p>In the <span class="bold">Name</span> field, enter <code>NICK</code>.</p> </li> <li> <p>In the Profile list, accept the value <code>DEFAULT</code>.</p> <p>This setting assigns the default password policy to user Nick.</p> <p>See <a href="#CHDDADDJ">"Setting the Database Password Policy"</a>.</p> </li> <li> <p>Accept the default value <code>Password</code> in the Authentication list.</p> <p>For information about other more advanced methods to authenticate users, see <a class="olink DBSEG003" href="http://www.oracle.com/pls/db112/lookup?id=DBSEG003"><span class="italic">Oracle Database Security Guide</span></a>.</p> </li> <li> <p>In the <span class="bold">Enter Password</span> and <span class="bold">Confirm Password</span> fields, enter a password that is secure.</p> <p><a class="olink DBSEG33223" href="http://www.oracle.com/pls/db112/lookup?id=DBSEG33223"></a>Create a password that is secure. See <span class="italic">Oracle Database Security Guide</span> for more information.</p> </li> <li> <p>Do not select <span class="bold">Expire password now</span>. If the account status is set to expired, then the user or the database administrator must change the password before the user can log in to the database.</p> </li> <li> <p>(Optional) Next to the <span class="bold">Default Tablespace</span> field, click the flashlight icon, select the <span class="bold">USERS</span> tablespace, and then click <span class="bold">Select</span>.</p> <p>All schema objects that Nick creates will then be created in the <code>USERS</code> tablespace unless he specifies otherwise. If you leave the Default Tablespace field blank, then Nick is assigned the default tablespace for the database, which is <code>USERS</code> in a newly installed database. For more information about the <code>USERS</code> tablespace, see <a href="storage.htm#CACDCJHD">"About Tablespaces"</a>.</p> </li> <li> <p>(Optional) Next to the <span class="bold">Temporary Tablespace</span> field, click the flashlight icon, select the <span class="bold">TEMP</span> tablespace, and then click <span class="bold">Select</span>.</p> <p>If you leave the Temporary Tablespace field blank, then Nick is assigned the default temporary tablespace for the database, which is <code>TEMP</code> in a newly installed database. For more information about the <code>TEMP</code> tablespace, see <a href="storage.htm#CACDCJHD">"About Tablespaces"</a>.</p> </li> <li> <p>For the Status option, accept the default selection of <span class="bold">Unlocked</span>.</p> <p>You can later lock the user account to prevent users from logging in with it. To temporarily deny access to a user account, locking the user account is preferable to deleting it, because deleting it also deletes all schema objects owned by the user.</p> </li> <li> <p>Grant roles, system privileges, and object privileges to the user, as described in <a href="#CHDBDBGI">"Example: Granting Privileges and Roles to a User Account"</a>.</p> <div class="infoboxnote"> <p class="notep1">Note:</p> Do not click OK in Step <a href="#CHDBJCHB">13</a> of <a href="#CHDBDBGI">"Example: Granting Privileges and Roles to a User Account"</a>. Instead, skip that step and continue with Step <a href="#CHDHEGHH">12</a> in this procedure.</div> </li> <li><a id="CHDHEGHH" name="CHDHEGHH"></a> <p>Assign a 10 MB quota on the <code>USERS</code> tablespace, as described in <a href="#CHDGIACA">"Example: Assigning a Tablespace Quota to a User Account"</a>.</p> </li> <li> <p>If you did not click OK while assigning the tablespace quota (previous step), then click <span class="bold">OK</span> now to create the user.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="em_manage.htm#CACFCDBE">"Creating Database Control Administrative Users"</a></p> </li> <li> <p><a class="olink TDPSG20000" href="http://www.oracle.com/pls/db112/lookup?id=TDPSG20000"><span class="italic">Oracle Database 2 Day + Security Guide</span></a>.</p> </li> </ul> </div> </div> <!-- class="sect2" --> <a id="CHDJGCHH" name="CHDJGCHH"></a><a id="ADMQS0742" name="ADMQS0742"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Creating a New User Account by Duplicating an Existing User Account</h3> <p><a id="sthref413" name="sthref413"></a><a id="sthref414" name="sthref414"></a><a id="sthref415" name="sthref415"></a>To create a user account that is similar in attributes to an existing user account, you can duplicate the existing user account.</p> <p class="orderedlisttitle">To create a new user account by duplicating an existing user account: </p> <ol> <li> <p>Go to the Users page, as described in <a href="#CHDJEBDG">"Viewing User Accounts"</a>.</p> </li> <li> <p>In the <span class="bold">Select</span> column, click the user to duplicate.</p> </li> <li> <p>In the Actions list, select <span class="bold">Create Like</span>, and then click <span class="bold">Go</span>.</p> <p>The Create User page appears. This page displays a new user with the same attributes as the duplicated user.</p> </li> <li> <p>Enter a user name and password, modify the user attributes or privileges if desired, and then click <span class="bold">OK</span> to save the new user.</p> </li> </ol> <p>The Actions list also provides shortcuts for other actions, and provides a way to display the SQL command used to create a user.</p> </div> <!-- class="sect2" --> <a id="CHDBDBGI" name="CHDBDBGI"></a><a id="ADMQS12040" name="ADMQS12040"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Example: Granting Privileges and Roles to a User Account</h3> <p><a id="sthref417" name="sthref417"></a><a id="sthref418" name="sthref418"></a><a id="sthref419" name="sthref419"></a><a id="sthref420" name="sthref420"></a>Suppose you are creating or modifying a user account named Nick. Because Nick is a database application developer, you want to grant him the <code>APPDEV</code> role, which enables him to create database objects in his own schema. (You created the <code>APPDEV</code> role in <a href="#g1101992">"Example: Creating a Role"</a>.) Because you want Nick to be able to create tables and views in other schemas besides his own, you want to grant him the <code>CREATE</code> <code>ANY</code> <code>TABLE</code> and <code>CREATE</code> <code>ANY</code> <code>VIEW</code> system privileges. In addition, because he is developing a human resources application, you want him to be able to view the tables in the <code>hr</code> sample schema and use them as examples. You therefore want to grant him the <code>SELECT</code> object privilege on those tables. Finally, you want Nick to be able to log in to Database Control so that he can use the graphical user interface to create and manage his database objects. You therefore want to grant him the <code>SELECT_CATALOG_ROLE</code> role. The following table summarizes the privileges and roles to grant to Nick.</p> <div class="inftblinformal"> <table class="Informal" title="Privileges and Roles to Grant to User Nick" summary="This table summarizes the roles and privileges that you are granting to use Nick for an example. It has 2 columns and 3 rows. The first column is entitled "Grant Type". The second column is "Privilege or Role Names"" dir="ltr" border="1" width="100%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"> <col width="31%" /> <col width="*" /> <thead> <tr align="left" valign="top"> <th align="left" valign="bottom" id="r1c1-t15">Grant Type</th> <th align="left" valign="bottom" id="r1c2-t15">Privilege or Role Name</th> </tr> </thead> <tbody> <tr align="left" valign="top"> <td align="left" id="r2c1-t15" headers="r1c1-t15">System privileges</td> <td align="left" headers="r2c1-t15 r1c2-t15"><code>CREATE</code> <code>ANY</code> <code>TABLE</code>, <code>CREATE</code> <code>ANY</code> <code>VIEW</code></td> </tr> <tr align="left" valign="top"> <td align="left" id="r3c1-t15" headers="r1c1-t15">Object privileges</td> <td align="left" headers="r3c1-t15 r1c2-t15"><code>SELECT</code> on all tables in the <code>hr</code> schema</td> </tr> <tr align="left" valign="top"> <td align="left" id="r4c1-t15" headers="r1c1-t15">Roles</td> <td align="left" headers="r4c1-t15 r1c2-t15"><code>APPDEV</code>, <code>SELECT_CATALOG_ROLE</code></td> </tr> </tbody> </table> <br /></div> <!-- class="inftblinformal" --> <p>The following example assumes that you are in the process of creating the user account for Nick or editing the account. Either you have accessed the Create User page and have entered all required fields on the General subpage (see <a href="#CHDDCIGA">"Example: Creating a User Account"</a>), or you have accessed the Edit User page for Nick (see <a href="#CHDFGCCA">"Example: Modifying a User Account"</a>). The example also assumes that you have not yet granted any privileges or roles to Nick.</p> <p class="orderedlisttitle">To grant privileges and roles to the user Nick: </p> <ol> <li> <p>Toward the top of the Create User or Edit User page, click <span class="bold">Roles</span> to display the Roles subpage.</p> <p>The Roles subpage shows that the <code>CONNECT</code> role is assigned to Nick. Database Control automatically assigns this role to all users that you create. (The selected Default check box indicates that the <code>CONNECT</code> role is a <span class="bold">default role</span> for Nick, which means that it is automatically enabled whenever Nick logs in.)</p> </li> <li> <p>Click <span class="bold">Edit List</span>.</p> <p>The Modify Roles page appears.</p> <img width="674" height="305" src="img/modify_roles.gif" alt="Description of modify_roles.gif follows" title="Description of modify_roles.gif follows" longdesc="img_text/modify_roles.htm" /><br /> <a id="sthref422" name="sthref422" href="img_text/modify_roles.htm">Description of the illustration modify_roles.gif</a><br /> <br /></li> <li> <p>In the Available Roles list, locate the <code>APPDEV</code> role, and double-click it to add it to the Selected Roles list. Do the same with the <code>SELECT_CATALOG_ROLE</code> role and then click <span class="bold">OK</span>.</p> <p>The Create User or Edit User page returns, showing that the <code>CONNECT</code>, <code>APPDEV</code>, and <code>SELECT_CATALOG_ROLE</code> roles are granted to Nick.</p> <div class="infoboxnote"> <p class="notep1">Note:</p> Double-clicking a role is a shortcut. You can also select the role and then click the <span class="bold">Move</span> button. To select multiple privileges, hold down the Shift key while selecting a range of privileges, or press the Ctrl key and select individual privileges.</div> </li> <li> <p>Toward the top of the page, click <span class="bold">System Privileges</span> to select the System Privileges subpage.</p> </li> <li> <p>Click <span class="bold">Edit List</span>.</p> <p>The Modify System Privileges page appears.</p> </li> <li> <p>In the Available System Privileges list, scroll to locate the <code>CREATE</code> <code>ANY</code> <code>TABLE</code> and <code>CREATE</code> <code>ANY</code> <code>VIEW</code> privileges, double-click each to add them to the Selected System Privileges list, and then click <span class="bold">OK</span>.</p> <p>The Create User or Edit User page returns, showing the newly added system privileges.</p> <div class="infoboxnote"> <p class="notep1">Note:</p> <a id="sthref423" name="sthref423"></a><a id="sthref424" name="sthref424"></a><a id="sthref425" name="sthref425"></a><a id="sthref426" name="sthref426"></a>To revoke a role, double-click it in the Selected Roles list on the Modify Roles page. To revoke a system privilege, double-click it in the Selected System Privileges list on the Modify System Privileges page.</div> </li> <li> <p>Toward the top of the page, click <span class="bold">Object Privileges</span> to select the Object Privileges subpage.</p> </li> <li> <p>In the Select Object Type list, select <span class="bold">Table</span> and then click <span class="bold">Add</span>.</p> <p>The Add Table Object Privileges page appears.</p> <img width="552" height="475" src="img/add_object_privileges.gif" alt="Description of add_object_privileges.gif follows" title="Description of add_object_privileges.gif follows" longdesc="img_text/add_object_privileges.htm" /><br /> <a id="sthref427" name="sthref427" href="img_text/add_object_privileges.htm">Description of the illustration add_object_privileges.gif</a><br /> <br /></li> <li> <p>Click the flashlight icon next to the Select Table Objects list.</p> <p>The Select Table Objects dialog box appears.</p> </li> <li> <p>In the Schema list, select <code><span class="codeinlinebold">HR</span></code>, and then click <span class="bold">Go</span>.</p> <p>All tables in the <code>hr</code> schema are displayed.</p> <img width="596" height="471" src="img/select_table_objects.gif" alt="Description of select_table_objects.gif follows" title="Description of select_table_objects.gif follows" longdesc="img_text/select_table_objects.htm" /><br /> <a id="sthref428" name="sthref428" href="img_text/select_table_objects.htm">Description of the illustration select_table_objects.gif</a><br /> <br /></li> <li> <p>Click <span class="bold">Select All</span>, and then click the <span class="bold">Select</span> button.</p> <p>The Select Table Objects dialog box closes, and the names of all tables in the <code>hr</code> schema appear in the Select Table Objects field on the Add Table Object Privileges page.</p> </li> <li> <p>In the Available Privileges list, double-click the <code><span class="codeinlinebold">SELECT</span></code> privilege to move it to the Selected Privileges list, and then click <span class="bold">OK</span>.</p> <p>The Create User or Edit User page returns, showing that the <code>SELECT</code> object privilege for all <code>hr</code> tables is granted to user Nick.</p> <div class="infoboxnote"> <p class="notep1">Note:</p> To revoke an object privilege, select it on the Create User or Edit User page (Object Privileges subpage), and then click <span class="bold">Delete</span>.</div> </li> <li><a id="CHDBJCHB" name="CHDBJCHB"></a> <p>Do one of the following to save the role and privilege grants:</p> <ul> <li> <p>If you are creating a user account, then click <span class="bold">OK</span> to save the new user account.</p> </li> <li> <p>If you are modifying a user account, then click <span class="bold">Apply</span> to save the changes for the user account.</p> </li> </ul> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CHDEBFCC">"About User Privileges and Roles"</a></p> </li> <li> <p><a class="olink TDPSG30038" href="http://www.oracle.com/pls/db112/lookup?id=TDPSG30038"><span class="italic">Oracle Database 2 Day + Security Guide</span></a></p> </li> </ul> </div> </div> <!-- class="sect2" --> <a id="CHDGIACA" name="CHDGIACA"></a><a id="ADMQS12041" name="ADMQS12041"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Example: Assigning a Tablespace Quota to a User Account</h3> <p><a id="sthref429" name="sthref429"></a><a id="sthref430" name="sthref430"></a>Suppose you are creating or modifying a user account named Nick. You want to assign Nick a space usage quota of 10 MB on his default tablespace.</p> <p>You must assign Nick a tablespace quota on his default tablespace before he can create objects in that tablespace. (This is also true for any other tablespace in which Nick wants to create objects.) After a quota is assigned to Nick for a particular tablespace, the total space used by all of his objects in that tablespace cannot exceed the quota. You can also assign a quota of <code>UNLIMITED</code>.</p> <p>The following example assumes that you are in the process of creating the user account for Nick or editing the account. Either you have accessed the Create User page and have entered all required fields on the General subpage (see <a href="#CHDDCIGA">"Example: Creating a User Account"</a>), or you have accessed the Edit User page for Nick (see <a href="#CHDFGCCA">"Example: Modifying a User Account"</a>). The example also assumes that Nick has not yet been assigned a quota on any tablespaces.</p> <p class="orderedlisttitle">To assign a tablespace quota to user Nick: </p> <ol> <li> <p>Toward the top of the Create User or Edit User page, select the <span class="bold">Quotas</span> subpage.</p> <p>The Quotas subpage appears, showing that user Nick does not have a quota assigned on any tablespace.</p> <img width="772" height="366" src="img/quotas.gif" alt="Description of quotas.gif follows" title="Description of quotas.gif follows" longdesc="img_text/quotas.htm" /><br /> <a id="sthref432" name="sthref432" href="img_text/quotas.htm">Description of the illustration quotas.gif</a><br /> <br /></li> <li> <p>In the <span class="bold">Quota</span> column for tablespace <code>USERS</code>, select <span class="bold">Value</span> from the list.</p> </li> <li> <p>In the <span class="bold">Value</span> column for tablespace <code>USERS</code>, enter <code><span class="codeinlinebold">10</span></code>.</p> </li> <li> <p>Do one of the following to save the new quota assignment:</p> <ul> <li> <p>If you are creating a user account, then click <span class="bold">OK</span> to save the new user account.</p> </li> <li> <p>If you are modifying a user account, then click <span class="bold">Apply</span> to save changes for the user account.</p> </li> </ul> </li> </ol> </div> <!-- class="sect2" --> <a id="CHDFGCCA" name="CHDFGCCA"></a><a id="ADMQS0743" name="ADMQS0743"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Example: Modifying a User Account</h3> <p><a id="sthref433" name="sthref433"></a><a id="sthref434" name="sthref434"></a>Suppose you want to remove the quota limitations for the user Nick on his default tablespace, <code>USERS</code>. To do so, you must modify his user account.</p> <p class="orderedlisttitle">To modify the user Nick: </p> <ol> <li> <p>Go to the Users page, as described in <a href="#CHDJEBDG">"Viewing User Accounts"</a>.</p> </li> <li> <p>In the <span class="bold">Select</span> column, select the user account Nick, and then click <span class="bold">Edit</span>.</p> <p>If you do not see user Nick, then he may be on another page. In this case, do one of the following:</p> <ul> <li> <p>Just above the list of user accounts, click <span class="bold">Next</span> to view the next page. Continue clicking <span class="bold">Next</span> until you see the user account for Nick.</p> </li> <li> <p>Use the Search area of the page to search for his account. In the <span class="bold">Object Name</span> field, enter the letters <span class="bold">NI</span>, and then click <span class="bold">Go</span>.</p> </li> </ul> <p>You can then select the user account for Nick and click <span class="bold">Edit</span>.</p> <p>The Edit User page appears, and displays the general attributes for Nick.</p> </li> <li> <p>Toward the top of the page, select the <span class="bold">Quotas</span> subpage.</p> </li> <li> <p>In the <span class="bold">Quota</span> column for tablespace <code>USERS</code>, select <span class="bold">Unlimited</span> from the list, and then click <span class="bold">Apply</span>.</p> <p>A message appears, indicating that user Nick was modified successfully.</p> </li> </ol> </div> <!-- class="sect2" --> <a id="CHDCCDGE" name="CHDCCDGE"></a><a id="ADMQS12042" name="ADMQS12042"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Locking and Unlocking User Accounts</h3> <p><a id="sthref436" name="sthref436"></a><a id="sthref437" name="sthref437"></a>To temporarily deny access to the database for a particular user account, you can lock the user account. If the user then attempts to connect, then the database displays an error message and does not allow the connection. You can unlock the user account when you want to permit database access again for that user.</p> <p class="orderedlisttitle">To lock or unlock a user account: </p> <ol> <li> <p>Go to the Users page, as described in <a href="#CHDJEBDG">"Viewing User Accounts"</a>.</p> </li> <li> <p>In the <span class="bold">Select</span> column, click the desired user account.</p> <p>If you do not see the desired user account, then it may be on another page. In this case, use the <span class="bold">Next</span> button to view additional pages or use the Search area of the page to search for the desired user account.</p> </li> <li> <p>Do one of the following:</p> <ul> <li> <p>To lock the account, select <span class="bold">Lock User</span> from the Actions list, and then click <span class="bold">Go</span>.</p> </li> <li> <p>To unlock the account, select <span class="bold">Unlock User</span> from the Actions list, and then click <span class="bold">Go</span>.</p> </li> </ul> <p>A confirmation message appears.</p> </li> <li> <p>Click <span class="bold">Yes</span>.</p> </li> </ol> </div> <!-- class="sect2" --> <a id="CHDJCIFG" name="CHDJCIFG"></a><a id="ADMQS12043" name="ADMQS12043"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Expiring a User Password</h3> <p>When you expir<a id="sthref439" name="sthref439"></a><a id="sthref440" name="sthref440"></a><a id="sthref441" name="sthref441"></a>e a user password, the user is prompted to change his or her password the next time that user logs in. Reasons to expire a password include the following:</p> <ul> <li> <p>A user password becomes compromised.</p> </li> <li> <p>You have a security policy in place that requires users to change their passwords on a regular basis.</p> <div class="infoboxnote"> <p class="notep1">Note:</p> You can automate the automatic expiring of user passwords after a certain interval. See <a href="#CHDDADDJ">"Setting the Database Password Policy"</a>.</div> </li> <li> <p>A user has forgotten his or her password.</p> <p>In this third case, you modify the user account, assign a new temporary password, and expire the password. The user then logs in with the temporary password and is prompted to choose a new password.</p> </li> </ul> <p class="orderedlisttitle">To expire a user password: </p> <ol> <li> <p>Go to the Users page, as described in <a href="#CHDJEBDG">"Viewing User Accounts"</a>.</p> </li> <li> <p>In the <span class="bold">Select</span> column, click the desired user account.</p> <p>If you do not see the desired user account, then it may be on another page. In this case, do one of the following:</p> <ul> <li> <p>Just above the list of user accounts, click <span class="bold">Next</span> to view the next page. Continue clicking <span class="bold">Next</span> until you see the desired user account.</p> </li> <li> <p>Use the Search area of the page to search for the desired user account. In the <span class="bold">Object Name</span> field, enter the first few letters of the user account name, and then click <span class="bold">Go</span>.</p> </li> </ul> <p>You can then select the user account.</p> </li> <li> <p>To expire the passwords for all users, select the <span class="bold">Multiple</span> option, then click <span class="bold">Select All</span>.</p> </li> <li> <p>Select <span class="bold">Expire Password</span> from the Actions list, and then click <span class="bold">Go</span>.</p> <p>A confirmation message appears.</p> </li> <li> <p>Click <span class="bold">Yes</span> to complete the task.</p> </li> </ol> </div> <!-- class="sect2" --> <a id="CHDHHGAF" name="CHDHHGAF"></a><a id="ADMQS0744" name="ADMQS0744"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Example: Deleting a User Account</h3> <p><a id="sthref443" name="sthref443"></a><a id="sthref444" name="sthref444"></a>Suppose user Nick has moved to another department. Because it is no longer necessary for him to have access to the database, you want to delete his user account.</p> <p>You must use caution when deciding to deleting a user account, because this action also deletes all schema objects owned by the user. To prevent a user from logging in to the database while keeping the schema objects intact, lock the user account instead. See <a href="#CHDCCDGE">"Locking and Unlocking User Accounts"</a>.</p> <p class="orderedlisttitle">To delete user Nick: </p> <ol> <li> <p>Go to the Users page, as described in <a href="#CHDJEBDG">"Viewing User Accounts"</a>.</p> </li> <li> <p>In the <span class="bold">Select</span> column, select the user account Nick, and then click <span class="bold">Delete</span>.</p> <p>If you do not see the user account Nick, then it may be on another page. In this case, do one of the following:</p> <ul> <li> <p>Just above the list of user accounts, click <span class="bold">Next</span> to view the next page. Continue clicking <span class="bold">Next</span> until you see the user account for Nick.</p> </li> <li> <p>Use the Search area of the page to search for the user account. In the <span class="bold">Object Name</span> field, enter the letters <span class="bold">NI</span>, and then click <span class="bold">Go</span>.</p> </li> </ul> <p>You can then select the user account for Nick and click <span class="bold">Delete</span>.</p> <p>A confirmation page appears.</p> </li> <li> <p>Click <span class="bold">Yes</span> to confirm the deletion of the user account.</p> </li> </ol> </div> <!-- class="sect2" --></div> <!-- class="sect1" --> <a id="CHDDADDJ" name="CHDDADDJ"></a><a id="ADMQS076" name="ADMQS076"></a> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2 class="sect1">Setting the Database Password Policy</h2> <p><a id="sthref446" name="sthref446"></a><a id="sthref447" name="sthref447"></a>This section provides background information and instructions for setting the password policy for all user accounts in the database. It contains the following topics:</p> <ul> <li> <p><a href="#CHDBAFJD">About Password Policies</a></p> </li> <li> <p><a href="#CHDIJGHE">Modifying the Default Password Policy</a></p> </li> </ul> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CHDEBHDE">"Administering Database User Accounts"</a></p> </li> <li> <p><a class="olink TDPSG20029" href="http://www.oracle.com/pls/db112/lookup?id=TDPSG20029"><span class="italic">Oracle Database 2 Day + Security Guide</span></a></p> </li> </ul> </div> <a id="CHDBAFJD" name="CHDBAFJD"></a><a id="ADMQS12044" name="ADMQS12044"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">About Password Policies</h3> <p><a id="sthref448" name="sthref448"></a><a id="sthref449" name="sthref449"></a>When you create a user account, a default password policy is assigned to that user account. The default password policy for a newly installed database includes these directives:</p> <ul> <li> <p>The password for the user account expires automatically in 180 days.</p> </li> <li> <p>The user account is locked 7 days after password expiration.</p> </li> <li> <p>The user account is locked for 1 day after 10 failed login attempts.</p> </li> </ul> <p><a id="sthref450" name="sthref450"></a>The default password policy is assigned to user accounts through a database object called a <span class="italic">profile</span>. Each user account is assigned a profile, and the profile has several attributes that describe a password policy. The database comes with a default profile (named <code>DEFAULT</code>), and unless you specify otherwise when you create a user account, the default profile is assigned to the user account.</p> <p>For better database security, you may want to impose a more strict password policy. For example, you may want passwords to expire every 70 days, and you may want to lock user accounts after three failed login attempts. (A failed login attempt for a user account occurs when a user enters an incorrect password for the account.) You may also want to require that passwords be complex enough to provide reasonable protection against intruders who try to break into the system by guessing passwords. For example, you might specify that passwords must contain at least one number and one punctuation mark.</p> <p>You change the password policy for every user account in the database by modifying the password-related attributes of the <code>DEFAULT</code> profile.</p> <div class="infoboxnote"> <p class="notep1">Note:</p> It is possible to have different password policies for different user accounts. You accomplish this by creating multiple profiles, setting password-related attributes differently for each profile, and assigning different profiles to different user accounts. This scenario is not addressed in this section.</div> </div> <!-- class="sect2" --> <a id="CHDIJGHE" name="CHDIJGHE"></a><a id="ADMQS12045" name="ADMQS12045"></a> <div class="sect2"><!-- infolevel="all" infotype="General" --> <h3 class="sect2">Modifying the Default Password Policy</h3> <p>You modify the default password policy for every database user account by modifying the password-related attributes of the profile named <code>DEFAULT</code>.</p> <p class="orderedlisttitle">To modify the default password policy: </p> <ol> <li> <p>Go to the Database Home page.</p> <p>See <a href="em_manage.htm#BABHJAGE">"Accessing the Database Home Page"</a>.</p> </li> <li> <p>At the top of the page, click <span class="bold">Server</span> to view the Server subpage.</p> </li> <li> <p>In the Security section, click <span class="bold">Profiles</span>.</p> <p>The Profiles page appears.</p> </li> <li> <p>In the <span class="bold">Select</span> column, select the profile named <code><span class="codeinlinebold">DEFAULT</span></code>, and then click <span class="bold">Edit</span>.</p> <p>The Edit Profile page appears.</p> </li> <li> <p>Toward the top of the page, select the <span class="bold">Password</span> subpage.</p> <img width="725" height="484" src="img/edit_profile.gif" alt="Description of edit_profile.gif follows" title="Description of edit_profile.gif follows" longdesc="img_text/edit_profile.htm" /><br /> <a id="sthref452" name="sthref452" href="img_text/edit_profile.htm">Description of the illustration edit_profile.gif</a><br /> <br /></li> <li> <p>Change field values as required. Click the flashlight icon next to each field to view a list of choices. (Click <span class="bold">Help</span> on this page for a description of the fields.)</p> </li> <li> <p>Click <span class="bold">Apply</span> to save your changes.</p> </li> </ol> <div class="infoboxnotealso"> <p class="notep1">See Also:</p> <ul> <li> <p><a href="#CHDBAFJD">"About Password Policies"</a></p> </li> <li> <p><a class="olink TDPSG20022" href="http://www.oracle.com/pls/db112/lookup?id=TDPSG20022"><span class="italic">Oracle Database 2 Day + Security Guide</span></a></p> </li> </ul> </div> </div> <!-- class="sect2" --></div> <!-- class="sect1" --> <a id="CFHGEBDE" name="CFHGEBDE"></a><a id="ADMQS075" name="ADMQS075"></a> <div class="sect1"><!-- infolevel="all" infotype="General" --> <h2 class="sect1">Users: Oracle By Example Series</h2> <p>Oracle By Example (OBE) has a series on the <span class="italic">Oracle Database 2 Day DBA</span> guide. This OBE steps you through the tasks in this chapter and includes annotated screenshots.</p> <p>To view the Users OBE, in your browser, enter the following URL:</p> <p><code><a href="http://www.oracle.com/technology/obe/11gr2_2day_dba/users/users.htm">http://www.oracle.com/technology/obe/11gr2_2day_dba/users/users.htm</a></code></p> </div> <!-- class="sect1" --></div> <!-- class="ind" --> <div class="footer"> <hr /> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%"> <col width="33%" /> <col width="*" /> <col width="33%" /> <tr> <td align="left"> <table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="98"> <tr> <td align="center" valign="top"><a href="storage.htm"><img width="24" height="24" src="../../dcommon/gifs/leftnav.gif" alt="Go to previous page" /><br /> <span class="icon">Previous</span></a></td> <td align="center" valign="top"><a href="schema.htm"><img width="24" height="24" src="../../dcommon/gifs/rightnav.gif" alt="Go to next page" /><br /> <span class="icon">Next</span></a></td> </tr> </table> </td> <td style="font-size: 90%" align="center" class="copyrightlogo"><img width="144" height="18" src="../../dcommon/gifs/oracle.gif" alt="Oracle" /><br /> Copyright © 2004, 2009, Oracle and/or its affiliates. All rights reserved.<br /> <a href="../../dcommon/html/cpyr.htm">Legal Notices</a></td> <td align="right"> <table class="icons oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="245"> <tr> <td align="center" valign="top"><a href="../../index.htm"><img width="24" height="24" src="../../dcommon/gifs/doclib.gif" alt="Go to Documentation Home" /><br /> <span class="icon">Home</span></a></td> <td align="center" valign="top"><a href="../../nav/portal_booklist.htm"><img width="24" height="24" src="../../dcommon/gifs/booklist.gif" alt="Go to Book List" /><br /> <span class="icon">Book List</span></a></td> <td align="center" valign="top"><a href="toc.htm"><img width="24" height="24" src="../../dcommon/gifs/toc.gif" alt="Go to Table of Contents" /><br /> <span class="icon">Contents</span></a></td> <td align="center" valign="top"><a href="index.htm"><img width="24" height="24" src="../../dcommon/gifs/index.gif" alt="Go to Index" /><br /> <span class="icon">Index</span></a></td> <td align="center" valign="top"><a href="../../dcommon/html/feedback.htm"><img width="24" height="24" src="../../dcommon/gifs/feedbck2.gif" alt="Go to Feedback page" /><br /> <span class="icon">Contact Us</span></a></td> </tr> </table> </td> </tr> </table> </div> <noscript> <p>Scripting on this page enhances content navigation, but does not change the content in any way.</p> </noscript> </body> </html>
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de