If you're interested in building only Personal Oracle applications, learning the ins and outs of Oracle user administration isn't essential. However, a basic assumption of this book is that many Personal Oracle developers intend to upsize their applications to Oracle servers, which support many simultaneous users. Even though Personal Oracle7 isn't meant to support many users, this version of Oracle still has all the user administration functionality of its bigger brethren--;the Oracle Workgroup Server and the Oracle Enterprise Server.
You cannot use Personal Oracle as a server--;providing database services for users on other computers--;but you can define an account for each user that you want to support. In other words, more than one person can use a single computer running Personal Oracle but, obviously, not at the same time.
Personal Oracle enables you to create a database role--;a set of Oracle privileges that are designed to match a functional role in an organization. For example, a data entry clerk should be able to insert and update rows in a table but should not be allowed to delete any rows. To enforce this security scheme, you would create a database role named DATA_ENTRY, which would possess these object privileges. On the other hand, the data entry manager should be able to insert, update, and delete rows in the tables. You would create a separate role named DATA_ENTRY_MANAGER to implement this security policy. Once a database role is created, it can be granted to an individual user as needed. Database roles not only help to ensure appropriate security but also simplify database administration.
Chapter 3, "Personal Oracle7 for Windows 95: Database Administration Tools," briefly touched on the use of the Navigator in managing Oracle users and roles. Using the Navigator, you can create a new user in the Local Database, define a new database role, assign privileges to a database role, and assign a database role to a specific user. In addition, the Navigator lets you manage users and roles in remote databases through a database connection. In Personal Oracle7 for Windows 3.11, these capabilities were provided in the User Manager program.
Personal Oracle for Windows is equipped with a database administration tool--;User Manager--;that can create new users and assign database roles to them. Although User Manager is a Windows-based tool and is easy to use, SQL*Plus is generally a more convenient tool for managing users, roles, and their privileges. Managing a significant number of Oracle users via SQL*Plus scripts is less tedious than managing many users and roles in an interactive mode. Later in this chapter, I describe the SQL commands that create and modify database users, roles, and privileges.
To understand the concepts in this chapter, imagine that you are the database designer, application developer, database administrator, and all-around guru for St. Somewhere, a public hospital in the Northeast. To simplify the administration of the Oracle database, you've created a separate user named ST_SOMEWHERE that is the owner of the database tables used for storing patient information. In addition, you've created a set of public synonyms that point to ST_SOMEWHERE's tables, thereby hiding the ownership of the tables and simplifying the task of managing users and roles:
SQL> create public synonym Patient 2 for St_Somewhere.Patient; Synonym created.
Oracle gives you three ways to create a new user:
Managing users with the Navigator is really quite simple. To see the existing users in the Local Database, just double-click the User folder. A list of the existing users appears on the right side of the main window. To create a new user, select the User folder and right-click New. The Navigator prompts you for the user name, the password, and the password confirmation. (See Figure 27.1.)
The new user appears in the list of existing users in the Local Database. (See Figure 27.2.)
If you want to change the user's password, select the icon representing the user and right-click Properties. A window will appear containing two tab folders--;General and Role/Privilege. On the General tab folder, you need to enter the password twice--;once to specify the password and a second time to confirm. (See Figure 27.3.)
Creating a new Oracle user with User Manager is very simple. Double-click the User Manager icon, which resides in the Personal Oracle7 program group. The first thing you'll need to do is connect to Oracle. User Manager displays a dialog box in which you enter your Oracle username and password. (See Figure 27.4.) If you're creating an Oracle user for the first time, type in SYSTEM for the username and MANAGER for the password. If you've already created an Oracle user with the DBA role, enter that username and password.
Next, click the Create button. User Manager displays a dialog box that contains two radio buttons, one for User and the other for Role. Select the User button and click OK. (See Figure 27.5.)
User Manager displays another screen on which you enter the user's name, the password, and a confirmation of the password. Type in the username as SDALI and type in the password and its confirmation as LINCOLN. (See Figure 27.6.)
After you click the OK button, User Manager creates the new user and redisplays the User Manager screen, showing the new user on the list of existing Oracle users. (See Figure 27.7.)
When you create a new user with User Manager, the new user automatically receives the connect privilege. (It is actually a role, which I explain later in this chapter.) To grant another privilege to SDALI, click the Privileges button. User Manager displays a screen showing all privileges that can be granted to SDALI. (See Figure 27.8.)
Select the resource privilege by clicking it. Once the privilege has been selected, the Grant button is enabled. Click the Grant button to grant SDALI the privilege of creating tables, views, indexes, and other database objects. After you click the Grant button, the privilege appears on the left side of the screen. (See Figure 27.9.)
You can also use SQL*Plus or SQL*DBA to create a new Oracle user. The syntax for creating a new user is
CREATE USER user-name IDENTIFIED BY password [DEFAULT TABLESPACE default-tablespace-name] [TEMPORARY TABLESPACE temporary-tablespace-name] [QUOTA disk-quota ON tablespace-name] [PROFILE profile-name]
where the following variables are defined: user-name is the name of the new user, subject to normal Oracle naming restrictions.
password is the new user's password.
default-tablespace-name is the default tablespace for storing tables and indexes created by user-name.
temporary-tablespace-name is the tablespace for storing temporary tables created by Oracle on behalf of user-name.
disk-quota is an optional quota on the disk space used in tablespace-name (for example, 100K, 2M, or UNLIMITED).
profile-name is an optional resource profile to be assigned to user-name.
To illustrate the use of the CREATE USER statement, here is how you would create a new Oracle account for Mary Gordon, a software developer.
SQL> create user MGordon identified by Marys_password 2 default tablespace User_Data 3 temporary tablespace Temporary_Data 4 quota unlimited on User_Data; User created.
When you use the CREATE USER statement, you can set a user's default tablespace to a tablespace other than USER_DATA. Choosing your own default tablespace is one advantage of using SQL*Plus, rather than User Manager, to create a new user. If you don't specify a default tablespace, the SYSTEM tablespace will be used by default. You should avoid this situation; the SYSTEM tablespace should be reserved for storing the Oracle data dictionary.
In addition to providing a quota on the use of storage in a tablespace, Oracle also provides other system-level quotas that can be established for a user. These quotas include the following:
The mechanism that you use for specifying these quotas is the profile. For example, you might want to create a profile for a casual user that limits his or her connect time to two hours or less. In addition, you might also want to limit a casual user's idle time to 10 minutes. On top of that, you might also fear that a casual user--;particularly, one who has access to an ad hoc query tool--;could inadvertently construct queries that produce a Cartesian product of several tables. By setting a fairly high limit on LOGICAL_READS_PER_CALL, you can prevent the user's query from consuming memory and CPU time needed by other users. With the CREATE PROFILE statement, you would issue the following statement to define the profile:
SQL> create profile Casual_User 2 limit 3 connect_time 120 4 idle_time 10 5 logical_reads_per_call 10000; Profile created.
To enforce the resource limits specified in a user's profile, you must perform either one of the following tasks:
You might be asking, "What good are database roles?" For a system environment that has many tables (more than seven), many users (more than seven), and distinct functional roles, the implementation and maintenance of a security scheme is a major task. If you, as an Oracle DBA, grant privileges directly to individual users, you are very likely to make a mistake: you might forget to give some people a privilege they need to do their work, or you might give others a superfluous privilege. Also, if a change to the database application requires that the users' privileges also change, you'll have the enormous task of propagating the new privileges to each user.
By defining a database role for each functional role that exists in the organization, you significantly reduce the task of managing users and their privileges. For instance, suppose you have 10 database users--;three belong to the marketing department, and the other seven are in the engineering department. You would create two database roles, Marketer and Engineer, and grant the appropriate privileges to each role; the Marketer role needs access to one set of tables, whereas the Engineer role requires access to a different set. You would then grant the appropriate role to each user. If the database changes--;for instance, you add a table--;you simply update the privileges of the role, not the user.
When you use the Navigator, database roles are easy to manage. To see the existing roles in the Local Database, just double-click the Role folder. A list of the existing roles appears on the right side of the main window. To create a new role, select the Role folder and right-click New. The Navigator prompts you for the role name and an optional password for the role. (See Figure 27.10.)
Creating a new database role with User Manager is also straightforward. Assuming that User Manager is already connected to Personal Oracle, you start by clicking the Create button. User Manager displays a dialog box that contains two radio buttons, one for User and the other for Role. Select the Role button and click OK. (See Figure 27.11.)
User Manager displays another screen on which you enter the name of the new database role and its password. A password is not mandatory for a database role. Type in PURCHASING_AGENT for the new role and leave the password and confirmation empty. (See Figure 27.12.)
After you click the OK button, User Manager creates the new role and redisplays the User Manager screen, showing the new role on the list of existing database roles. (See Figure 27.13.)
You can create a new role by issuing the following SQL command with SQL*Plus:
CREATE ROLE role-name; where role-name is the database role to be created.
As the DBA for the St. Somewhere patient information system, you create a new role--;via SQL*Plus--;for the hospital admitting manager.
SQL> create role Admitting_Manager; Role created.
After you've created the new role with SQL*Plus, the role is part of the list of roles displayed by User Manager. (See Figure 27.14.)
Personal Oracle is installed with the following five predefined roles:
NoteThe historical reason for these predefined roles is that Oracle version 6 had only three privileges: connect, resource, and DBA. Because Oracle redefined these privileges as roles in Oracle7, SQL*Plus scripts developed for Oracle version 6 can be processed in Oracle7.
A role isn't useful unless it has been granted at least one privilege. Oracle has two categories of privileges.
You can grant many different system privileges to a role or user--;in fact, Oracle has more than 80 system privileges. One system privilege that almost every Oracle user needs is CREATE SESSION--;the capability to establish an Oracle session.
To grant a privilege to a database role with the Navigator, select the role to which you want to grant the privilege and right-click Properties. In the window that appears, click on the tab folder named Role/Privilege. Notice the two radio buttons near the top of the window--;Roles and Privileges. By default, the Roles radio button is selected (see Figure 27.15), which illustrates that a database role can also be granted other database roles.
To grant a privilege to a database role, select the Privileges radio button. A list of all system privileges appears on the right side of the window. To grant a system privilege to a role, select the privilege and click <. For example, in Figure 27.16 the CREATE TRIGGER system privilege is granted to the TECHNICIAN database role.
NoteYou can't use the Navigator to directly grant object privileges to a role. However, you could grant the object privileges to the role in SQL*Plus and then use the Navigator to grant the role to a user.
To revoke a system privilege from a role, select the privilege on the left side of the window and click >. To revoke all system privileges from a role, click >>. To grant all system privileges to a role, click <<. Once you click OK, the role will possess the system privileges that were granted. Consequently, any user who possesses that role will possess those system privileges.
The syntax for granting a system privilege to a role is
GRANT system-privilege TO role-name;
where system-privilege is a system privilege such as CREATE SESSION or ALTER USER and role-name is the database role receiving the privilege.
For example, you can grant the following system privileges to an Application_Developer role.
SQL> grant Create Session, Create Table, Create Procedure, Create View 2 to Application_Developer; Grant succeeded.
Similarly, the syntax for granting an object privilege to a role is
GRANT object-privilege ON [owner.]object-name TO role-name;
where object-privilege is an object privilege such as select or delete, object-name is the object of interest such as a table or view, owner is the Oracle account that owns object-name, and role-name is the database role receiving the privilege.
For instance, to give the database role Admitting_Manager the capability to select, insert, update, and delete rows from the Patient table, you would use the following Oracle SQL statement:
SQL> grant select, insert, update, delete on Patient 2 to Admitting_Manager; Grant succeeded.
The syntax to grant a role to a user is
GRANT role-name TO user-name;
where role-name is the database role to be granted and user-name is the Oracle user receiving the granted role.
For instance, you grant the role of Admitting_Manager to Henrietta Gomez in the following way:
SQL> grant Admitting_Manager to HGomez; Grant succeeded.
NoteSuppose an Oracle user has no object privileges for a table owned by another Oracle account. If the user tries to reference that table in a SQL statement, Oracle returns an error indicating that the table doesn't exist. The user can't determine whether the table exists or whether he simply doesn't have any privileges to access the table. However, if the user has at least one privilege--;say the select privilege--;and submits a SQL statement for which he doesn't have the necessary privilege, Oracle returns an error message informing the user that he doesn't have the necessary privilege.
To grant a database role to a user with the Navigator, select the user to which you want to grant a role and right-click Properties. In the next window, click on the tab folder named Role/Privilege. A list of the roles that the user currently has appears on the left side of the window; a list of the roles that can be granted to the user appears on the right. (See Figure 27.17.)
To grant a specific role, select the role and click < to assign it to the user. To assign all roles to a user, click <<. To revoke a specific role from a user, select the role to be revoked from the left side of the window and click >. To revoke all roles from a user, click >>. However, realize that revoking all roles from a user also revokes the CONNECT role from the user and the user will be unable to connect to the database.
You may also have to revoke a privilege from a database role from time to time. For example, a change in the corporate security policy might reduce the functional roles that are enabled to update a particular category of information. To revoke a privilege from a database role, the syntax is
REVOKE privilege from role-name;
where privilege is the privilege to be revoked and role-name is the name of the database role for which privilege is being revoked.
For example, to revoke the delete privilege for the Patient table from the Admitting_Manager role, you could issue the following statement in SQL*Plus:
SQL> revoke Delete on Patient 2 from Admitting_Manager; Revoke succeeded.
However, if you try to revoke a privilege that hasn't been granted, Oracle returns an error message.
SQL> revoke Alter on Patient 2 from Admitting_Manager; revoke Alter on Patient * ERROR at line 1: ORA-01927: cannot REVOKE privileges you did not grant
You can also use the REVOKE statement to revoke a database role that had been granted to an Oracle user. The syntax is
REVOKE role-name from user-name;
where role-name is the database role to be revoked and user-name is the user for whom the role is revoked.
For instance, if Henrietta Gomez is reassigned to a different position at St. Somewhere, you should revoke the database role of Admitting_Manager from her Oracle account.
SQL> revoke Admitting_Manager from HGomez; Revoke succeeded.
Every Oracle database, including Personal Oracle, has two special users: SYS and SYSTEM. The SYS account owns the Oracle data dictionary tables and associated database objects. The SYSTEM user owns tables that are used by Oracle application development tools such as Oracle Forms or Reports. You should not create any application objects such as tables or indexes while connected as SYS or SYSTEM. *
Tip
After you've installed an Oracle database, the passwords for the SYS and SYSTEM accounts remain as CHANGE_ON_INSTALL and MANAGER, respectively. For two reasons, I urge you to change these passwords after the Oracle database installation is complete. First, these passwords are widely known. Second, both of these accounts have been granted the DBA role and anyone who can connect as SYS or SYSTEM has full access to the Oracle database.
Change the passwords by using the ALTER USER statement:
ALTER USER SYSTEM IDENTIFIED BY new_password;
Both versions of Personal Oracle--;for Windows 95 and Windows 3.11--;will support multiple connections. For example, SQL*Plus, the Navigator, and PowerBuilder could be connected simultaneously to Personal Oracle. Seeing all the open connections to Personal Oracle can sometimes be very useful.
In Personal Oracle7 for Windows, a tool named Session Manager allowed you to see who was currently connected to an Oracle database. Personal Oracle7 for Windows 95 does not include Session Manager, but you can use SQL*DBA to see the same information.
First, you'll need to open an MS-DOS window--;SQL*DBA is not a Windows-based tool. Second, invoke SQL*DBA by typing sqldba mode=screen at the DOS prompt.
A window that contains a set of menu items will appear. You can use the mouse to select a menu item or press F4 if you want to use the keyboard to select a menu item. (See Figure 27.18.)
The first thing you'll want to do is to connect to the Personal Oracle7 database. Select Session [vb] Connect. In the window that appears, type the username and password and click OK. (See Figure 27.19.)
Once you've connected to the Personal Oracle7 database, a message in the window states that you are indeed connected. (See Figure 27.20.)
To see the users who are currently connected, select Monitor [vb] Session from the menu. SQL*DBA will display a window in which you can specify specific users that you want to display. To display all connected users, click Start. (See Figure 27.21.) The display will be updated every few seconds with the current list of connected users.
Session Manager is an Oracle Database Administration Tool for viewing the users who are connected to an Oracle database. It is really designed to be used for an Oracle instance that supports multiple concurrent users. Even so, you can see how Session Manager functions with Personal Oracle. Figure 27.22 shows the Session Manager screen, which reveals two users: SCOTT, who is connected via SQL*Plus, and FRAYED_WIRES, who is connected via SQL*DBA. The SYS user, who is running Session Manager, is shown as the active user; the other two users are inactive.
If you click FRAYED_WIRES, the user is highlighted and the Disconnect button is enabled. (See Figure 27.23.)
If you click the Disconnect button, Session Manager prompts you for a confirmation. (See Figure 27.24.)
If you click the Yes button, Session Manager displays the list of connected users without Frayed_Wires. (See Figure 27.25.)
This chapter covers the following principles regarding the management of Oracle users, database roles, and privileges: