ch10.htm

Teach yourself Oracle8 in 21 day

<P>If you choose the Create option, you will be presented with the same Create Profile
screen that you saw with Enterprise Manager, with the same options. Clicking one
of the profiles causes the profile properties to appear on the right side of Security
Manager screen, as shown in Figure 10.14.</P>
<P>You modify profiles under Security Manager by changing the parameters on the right
side of the screen. As you can see, there is also a Password tab here. You can click
this Password tab and see the same password parameters as with Enterprise Manager.</P>
<P>As will all the facilities available through Enterprise Manager and utilities
such as Security Manager, you can also create and modify a profile via the Server
Manager or scripts.</P>
<P><A NAME="14"></A><A HREF="14.htm"><B>Figure 10.14.</B></A></P>
<P><I>The Edit Profile screen offers a detailed view of the default profile.</I></P>
<P>
<H3><FONT COLOR="#000077"><BR>
<B>Managing Profiles with the </B><TT>CREATE PROFILE</TT><B> and </B><TT>ALTER</TT>
<TT>PROFILE</TT><B> Commands</B></FONT></H3>
<P>As with all the commands that you have seen so far, you can create and modify
profiles via a command-line utility. For the profile, these utilities are the <TT>CREATE
PROFILE</TT>, <TT>ALTER PROFILE</TT>, and <TT>DROP PROFILE</TT> commands. The syntax
of the <TT>CREATE PROFILE</TT> and <TT>ALTER PROFILE</TT> commands is identical except
that the first creates a profile and the other changes an already-existing profile.</P>
<P>The syntax of these commands is given in the Oracle8 documentation and really
does not need to be repeated here. Look in the Oracle8 Server SQL Reference Manual
for the exact syntax and how to use it. Another good way to learn some of the syntax
used in these commands is to take advantage of the Show SQL option available in many
Enterprise Manager utilities.</P>
<P>If you are setting up many different profiles and you find that the number you
have is quite large, it is a good idea to start putting them in a SQL script. If
you have it in a script, you can use it to rebuild the database if necessary, and
if you ever need to create an additional database for the same user community, it
will be much easier to set up the profiles.
<H2><FONT COLOR="#000077"><B>Managing Roles</B></FONT></H2>
<P>Roles are very important because they are the primary way of allocating system
privileges to the user community. Privileges can be allocated to a user either via
a role or directly, but directly can be quite time-consuming and tedious for the
DBA. By creating a role for a certain type of user and allocating system privileges
to that role, you can cut down on the amount of work that must be done and have an
easier way of tracking privileges.</P>
<P>A complete list of Oracle default roles and system privileges is given in Appendix
C, &quot;Oracle Roles and Privileges.&quot; Please see that appendix for answers
to questions on specific privileges.</P>
<P>You can create, modify, and assign roles to users via Enterprise Manager, Security
Manager, or the <TT>CREATE ROLE</TT>, <TT>ALTER ROLE</TT>, <TT>DROP ROLE</TT>, and
<TT>ALTER USER</TT> commands. In general, if you are just changing a few roles or
users, the graphical tools are very nice. If you are modifying a large number of
different roles or users, it might be better to use a script, where the SQL commands
can be saved and used again if necessary.
<H3><FONT COLOR="#000077"><B>Managing Roles with Enterprise Manager</B></FONT></H3>
<P>You can create and modify roles graphically via the Enterprise Manager utility.
Drill down into the database that you want to administer and right-click the Roles
entry. This will bring up the Create Role screen, which is shown in Figure 10.15.</P>
<P><A NAME="15"></A><A HREF="15.htm"><B>Figure 10.15.</B></A></P>
<P><I>The Create Role screen allows you to create a new role.</I></P>
<P>Here you can create a new role that, when created, will have no other roles or
privileges associated with it. To populate the role with other roles, simply drag
and drop a role onto the Granted Roles entry under the role that you have created
(see Figure 10.16). This will add that selected role as one for which your newly
created role has privileges. It is also possible to do this from system privileges.</P>
<P><A NAME="16"></A><A HREF="16.htm"><B>Figure 10.16.</B></A></P>
<P><I>Drag and drop roles to populate a new role with them.</I></P>
<P>After you have created the role and populated it with other roles or system privileges,
you might want to assign it to a user. With Enterprise Manager, it is possible simply
to drag this role onto the Granted Roles entry under the user to whom you want to
give it. This will automatically grant the role to the designated user. This drag-and-drop
operation is shown in Figure 10.17.</P>
<P><A NAME="17"></A><A HREF="17.htm"><B>Figure 10.17.</B></A></P>
<P><I>Dropping roles onto a user to assign that role to the user.</I></P>
<P>If you like the drag-and-drop features provided in Enterprise Manager, this may
be the best method for you. Security Manager provides this functionality plus a little
bit more, as you'll learn in the next section.
<H3><FONT COLOR="#000077"><B>Managing Roles with Security Manager</B></FONT></H3>
<P>Security Manager can also be used to create roles, grant them to users, and manage
them. If you prefer using a graphical tool but need a bit more functionality than
what is provided with Enterprise Manager, you will find Security Manager very useful.</P>
<P>To create a new role, use the same technique as with Enterprise Manager: Right-click
the Roles icon and select Create. This will bring up the same Create Role screen
that you saw earlier. It is shown here in Figure 10.18.</P>
<P><A NAME="18"></A><A HREF="18.htm"><B>Figure 10.18.</B></A></P>
<P><I>The Create Role screen in Security Manager is the same as the one used in Enterprise
Manager.</I></P>
<P>After you have created the role, right-click its name in the Security Manager
screen and you will see the option to Add Privileges to Roles.</P>
<P>From this screen, you can modify the new role by adding new roles (as shown in
Figure 10.19) or by adding system privileges (as shown in Figure 10.20). Both of
these are accomplished by selecting the roles or privileges you want and adding them
to the selected role or roles with the mouse.</P>
<P><A NAME="19"></A><A HREF="19.htm"><B>Figure 10.19.</B></A></P>
<P><I>Adding roles to a role.</I></P>
<P><A NAME="20"></A><A HREF="20.htm"><B>Figure 10.20.</B></A></P>
<P><I>Adding system privileges to a role.</I></P>
<P>Once nice benefit of Security Manager is the capability to expand the roles and
privileges to see what is available. Simply drill down into the different parameters
to see what is available or allocated. An example of this is shown in Figure 10.21.</P>
<P><A NAME="21"></A><A HREF="21.htm"><B>Figure 10.21.</B></A></P>
<P><I>This Security Manager screen allows you to view the roles and privileges associated
with the <TT>DOGS2</TT> role.</I></P>
<H3><FONT COLOR="#000077"><BR>
<B>Managing Roles from the Command Line</B></FONT></H3>
<P>Managing roles is a little different from working with some of the other commands
you have seen throughout the book because managing a role takes more than just one
command.</P>
<P>To create and maintain a role, you can use the <TT>CREATE ROLE</TT>, <TT>ALTER
ROLE</TT>, and <TT>DROP ROLE</TT> commands. The <TT>CREATE ROLE</TT> and <TT>ALTER
ROLE</TT> commands are used only to create the role and to manage the security on
the role; privileges and other roles are not assigned to roles via the <TT>ALTER
ROLE</TT> command. The <TT>CREATE ROLE</TT> and <TT>ALTER ROLE</TT> commands take
the following options:</P>
<PRE><FONT COLOR="#0066FF">CREATE ROLE role
NOT IDENTIFIED
Or IDENTIFIED BY password
Or IDENTIFIED EXTERNALLY
Or IDENTIFIED GLOBALLY
</FONT></PRE>
<P>These options simply have to do with the security of the role itself. To modify
a role by adding other roles or system privileges to it, you must use the <TT>GRANT</TT>
command.
<H4><FONT COLOR="#000077"><B>Using </B>GRANT<B> and </B>REVOKE</FONT></H4>
<P>You use <TT>GRANT</TT> to assign roles or system privileges to roles or users.
The same command works whether you are assigning these roles or system privileges
to an individual user or to a role that in turn can be assigned to many users.
<H4><FONT COLOR="#000077"><B>The Syntax for the </B>GRANT<B> Command</B></FONT></H4>
<P>The <TT>GRANT</TT> command takes the following syntax:</P>
<P><B>SYNTAX:</B></P>
<PRE><FONT COLOR="#0066FF">GRANT role or system privilege [, role or system privilege ]
TO user or role or PUBLIC [, user or role ]
[ WITH ADMIN OPTION ]
</FONT></PRE>
<P>The <TT>GRANT</TT> command can take any number of system privileges and roles
and assign them to any number of users or roles. By specifying that you want to grant
a role or system privilege to <TT>PUBLIC</TT>, you are specifying that you want that
role or privilege to be granted to all users in the system.</P>
<P>The <TT>REVOKE</TT> command is just the opposite of the <TT>GRANT</TT> command;
it will take a role or system privilege away from a user or role:</P>
<PRE><FONT COLOR="#0066FF">REVOKE role or system privilege [, role or system privilege ]
FROM user or role or PUBLIC [, user or role ]
</FONT></PRE>
<H4><FONT COLOR="#000077"><B>The </B>DROP<B> Command</B></FONT></H4>
<P>Finally, you can use the <TT>DROP </TT>command to completely take away a role
from the system. You should not have to be dropping roles very often if the roles
that you create are planned out in advance.
<H4><FONT COLOR="#000077"><B>The </B>ADMIN<B> Option</B></FONT></H4>
<P>The <TT>ADMIN</TT> option specifies that the grantee has the right to pass this
role or system privilege to any other user or role in the system. If the user is
granted the role with the <TT>ADMIN</TT> option, that user may also alter or drop
the role.
<H2><FONT COLOR="#000077"><B>Summary</B></FONT></H2>
<P>Today you learned how to manage users in your Oracle8 database. At the beginning
of the lesson you saw how to create user accounts using both graphical and command-line
utilities. This is the most basic building block of user administration.</P>
<P>After the user account has been created comes the task of administering that user
account. This might consist of adding and/or removing roles and system privileges,
or modifying some of the resource limitations placed on the user in the form of a
profile.</P>
<P>You also learned how to administer profiles, which are used to limit certain system
resources that the user session might be consuming. By using a profile, you can assign
these limits to a profile and then assign the profile to a class of users.</P>
<P>Finally, you learned how and why to use roles. Roles are very powerful devices.
By using roles, administrative tasks can be simplified by assigning a set of system
privileges to a class of user, and then handling exceptions as they arise.</P>
<P>Roles are very important and should be well documented. By documenting roles,
you can easily decide what role a new user should be assigned. As always, any changes
to the system, such as adding or modifying roles, should be logged in the system
journal.
<H2><FONT COLOR="#000077"><B>What's Next?</B></FONT></H2>
<P>In tomorrow's lesson, &quot;Managing Processes,&quot; you will start to see more
of how the Oracle processes work. You will learn how to find out what processes are
active in the system, and how to kill runaway processes. You will also see how the
Oracle multithreaded server works.
<H2><FONT COLOR="#000077"><B>Q&amp;A</B></FONT></H2>

<DL>
	<DD><B>Q What's the difference between a privilege and a role?</B>
	<P><B>A</B> A <I>privilege</I> is the authority to perform a certain act or command.
	A <I>role</I> is a collection of privileges and roles that can be assigned to a user.</P>
	<P><B>Q What is the </B><TT>DEFAULT</TT><B> profile?</B></P>
	<P><B>A</B> The <TT>DEFAULT</TT> profile was created by the system when the database
	was created. By default, all users have unlimited access to the system.</P>
	<P><B>Q Why do you use roles instead of just assigning system privileges?</B></P>
	<P><B>A</B> Assigning system privileges can be very time-consuming and complicated.
	By creating a role for each user type, such as accounting, finance, payroll, and
	so on, you can assign the privileges based on duties.</P>
	<P><B>Q Can a user have more than one role?</B></P>
	<P><B>A</B> Certainly. A user can have multiple roles, depending on that user's job
	and duties.
</DL>

<H2><FONT COLOR="#000077"><B>Workshop</B></FONT></H2>
<P>The workshop provides quiz questions to help you solidify your understanding of
the material covered and exercises to provide you with experience in using what you've
learned. Find the answers to the quiz questions in Appendix A, &quot;Answers.&quot;
<H3><FONT COLOR="#000077"><B>Quiz</B></FONT></H3>

<DL>
	<DD><B>1.</B> Should more than one user share a user account?
	<P><B>2.</B> How many accounts should each user have?</P>
	<P><B>3.</B> Where would you set the maximum amount of CPU per session?</P>
	<P><B>4.</B> Where would you set permissions to export data?</P>
	<P><B>5.</B> What is the <TT>ADMIN</TT> option?</P>
	<P><B>6.</B> How do you remove a role?</P>
	<P><B>7.</B> How do you remove a system privilege from a role?</P>
	<P><B>8.</B> Are quotas assigned via roles?</P>
	<P><B>9.</B> How are temporary tablespaces assigned?</P>
	<P><B>10.</B> What does the Create Like feature in Enterprise Manager or Security
	Manager do?
</DL>

<H3><FONT COLOR="#000077"><B>Exercises</B></FONT></H3>

<DL>
	<DD><B>1. </B>Create a user using Enterprise Manager.
	<P><B>2.</B> Modify that user using Security Manager.</P>
	<P><B>3.</B> Create a new profile.</P>
	<P><B>4.</B> Create a new role.</P>
	<P><B>5.</B> Add several roles and privileges to that new role.
</DL>

<CENTER>
<P>
<HR>
<A HREF="../ch09/ch09.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch11/ch11.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>