CHAPTER 8
User Management and Data Loading
The woods are lovely, dark and deep.
But I have promises to keep,
And miles to go before I sleep,
And miles to go before I sleep.
“Stopping by Woods on a Snowy Evening”by Robert Frost
Your job does not end when you create a database; you still have to get the data into it and ensure that those who need to use it can do so. This chapter discusses how to control users and how to get large amounts of data in and out of databases. User management and data loading are two common chores performed by database administrators.
Schemas
Every object in a database is explicitly owned by a single owner, and the owner of an object must explicitly authorize its use by somebody else. The collection of objects owned by a user is called a schema. To illustrate, Listing 8-1 shows a summary of the contents of the HR schema, one of the sample schemas that Oracle provides for educational purposes. Note that the terms user, schema, schema owner, and account are used interchangeably; for example, youcan speak of either the HR user or the HR schema.
The object types shown in Listing 8-1 are very common. Tables are the containers for your data; indexes help you find the data; sequences are continuously incrementing counters that generate unique identification numbers for data records; procedures are blocks of application logic that are stored in the database; triggers are specialized blocks of application logic that are triggered by specific events, such as a data record being added to a table; and views are “virtual tables” that can combine data from multiple tables.
Notice that you found the information you needed in a view called DBA_OBJECTS. It is a dictionary view in the SYS schema. Because Oracle Database strives to follow the principles of the relational model, it is no surprise that Oracle Database manages objects owned by users using its own tables, indexes, sequences, procedures, triggers, and views; these are stored in the SYS schema. For example, Listing 8-2 shows the contents of the SYS schema; notice the wide variety of object types.
Tip Because SQLDeveloper is a graphical user interface (GUI), it is the tool of choice to get information about objects in your database. However, you should also practice using and SQL*Plus and SQL to query the dictionary views, because there will be times when SQLDeveloper does not go far enough.
Tip Definitions of Oracle terms and links to the relevant sections of the reference manuals can be found in the Master Glossary available at www.oracle.com/pls/db121/homepage. Try using the Master Glossary to find the meanings of the terms you see in Listing 8-2.
Some of the objects in a schema, such as tables and indexes, represent blocks of storage; the others are just definitions that are stored as rows in tables. For example, the view DBA_OBJECTS is simply a definition stored as rows in the VIEW$ table in the SYS schema. An object that represents blocks of storage is called a segment . Listing 8-3 shows that only 11 of the object types displayed in Listing 8-2 actually represent blocks of storage; the others are only definitions stored as rows in tables.
You can find more information about each type of object in the appropriate dictionary view. Listing 8-4 is a list of the columns found in the DBA_TABLES view; you can do the same for any table or view using the DESCRIBE command. Coincidentally, a complete list of dictionary views can be found in a view called DICTIONARY; the actual SQL definitions of the views can be found in another view called DBA_VIEWS. You’ll explore additional data dictionary views in the exercises at the end of this chapter, and Chapter 9 discusses the topic further.
Tip Explanations of the information contained in the dictionary views can be found in the Oracle Database Reference available at www.oracle.com/pls/db121/homepage. Try using it to figure out the meaning of each of the columns in DBA_TABLES.
Ordinary users don’t have access to the dictionary views whose names begin with DBA_, because that would give them the ability to obtain information about other users. Instead, they have access to special sets of views whose names begin with USER_ and ALL_. The views whose names begin with USER_show only those objects the current user owns. As illustrated in Listing 8-5, Oracle displays an error message when user hr tries to query the DBA_OBJECTS view. When user hr queries the USER_OBJECTS view instead, Oracle displays the same information you saw in Listing 8-1. And, interestingly enough, when user hr queries the ALL_OBJECTS table, the user has access to tables in several other schemas.
User Management
As you might expect, nobody can store data in the database or retrieve data from it unless they are properly authorized to do so. The following sections explore the five commands required for user management: CREATE USER, ALTER USER, DROP USER, GRANT, and REVOKE. Only users with the appropriate privileges can execute these commands. For example, only a user with the Create User privilege can execute the CREATE USER command. Database administrators typically perform user-management tasks using the SYSTEM account.
Creating Users
The CREATE USER command is used to specify the alias by which the database knows a user.A typical convention is to use the first character of the user’s first name and the first seven characters of the person’s last name—for example, ifernand in the case of a user named Iggy Fernandez. Listing 8-6 shows a minimalist example of the CREATE USER command; the IDENTIFIED BY clause is used to specify the password that the user must specify to gain access to the database.
The CREATE USER statement should typically specify a value for DEFAULT TABLESPACE—the tablespace where the user’s tables and indexes are automatically placed if another tablespace is not explicitly specified—and TEMPORARY TABLESPACE—the tablespace used for sorting operations and other operations that require temporary space. If you do not specify values, DEFAULT TABLESPACE and TEMPORARY TABLESPACE are automatically set to the values listed in the DATABASE_PROPERTIES view. This example also neglects to assign a value to the user’s profile; this is automatically set to the value DEFAULT. Listing 8-7 shows some information from the DBA_USERS view; note especially the attributes of user ifernand.
Listing 8-7 is interesting because it also showsyou just how many schemas are automatically created when a database is created. The creation date provides a clue as to the origin of these schemas; the creation date of August 30, 2005 tells me that those schemas were part of the database template I used to create my database. The creation date of February 21, 2008 is when I installed the sample schemas in the database, soon after creating the database.
The user’s profile specifies a number of behaviors and quotas that apply to the user. For example, IDLE_TIME specifies the length of time after which an idle session is automatically disconnected in order to conserve system resources, and CPU_PER_SESSION specifies the maximum number of CPU cycles that any single session may utilize.You can find a detailed description of these behaviors and quotas in the CREATE PROFILE section of the Oracle Database 12c SQL Language Reference. You can customize the default profile using the ALTER PROFILE command, and you can create custom profiles using the CREATE PROFILE command. Listing 8-8 shows the definition of the DEFAULT profile.
Giving Permissions to Users
Oracle givesyou tight control over what users are permitted to do; the GRANT command is the main tool for this purpose. User ifernand may be known to Oracle, but he won’t be able to start an interactive session.Here’s what happens when he tries to do so:
SQL> CONNECT ifernand
Enter password:
ERROR:
ORA-01045: user IFERNAND lacks CREATE SESSION privilege; logon denied
To give the database administrator more flexibility in managing users, the ability to start a session is not automatically available to database users, even if they own objects in the database. The ability to start a session can be granted when necessary, and revoking a user’s ability to start a session does not remove any objects owned by the user. Let’s give user ifernand the ability to start a session:
SQL> CONNECT system
Enter password:
Connected.
SQL>GRANT CREATE SESSION TO ifernand;
Grant succeeded.
User ifernand is now able to connect, but Oracle does not let him create any tables:
SQL> CONNECT ifernand
Enter password:
Connected.
SQL> CREATE TABLE TEST AS
2 SELECT * FROM DUAL;
SELECT * FROM DUAL
*
ERROR at line 2:
ORA-01031: insufficient privileges
You must explicitly give user ifernand permission to create tables; this is done with the GRANT command. Here’s an example:
SQL> CONNECT system
Enter password:
Connected.
SQL>GRANT CREATE TABLE TO ifernand;
Grant succeeded.
User ifernand encounters another problem when he tries to create a table—he does not have a quota of space in any tablespace:
SQL> CONNECT ifernand
Enter password:
Connected.
SQL> CREATE TABLE TEST AS
2 SELECT * FROM DUAL;
SELECT * FROM DUAL
*
ERROR at line 2:
ORA-01950: no privileges on tablespace ’USERS’
You must explicitly give user ifernand a quota of space in at least one tablespace. You do so with the ALTER USER command:
SQL> CONNECT system
Enter password:
Connected.
SQL>ALTER USER ifernand QUOTA 128 m ON users;
User altered.
User ifernandcan now create a table in his default tablespace, USERS, but not in any other tablespace. Here’s what happens when he tries to create a table in another tablespace:
SQL> CONNECT ifernand
Enter password:
Connected.
SQL> CREATE TABLE TEST (dummy VARCHAR2(1))
2 STORAGE (INITIAL 128 m)
3 TABLESPACE example;
CREATE TABLE TEST (dummy VARCHAR2(1))
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ’EXAMPLE’
As shown next, user ifernandcan only successfully create tables in the USERS tablespace. If he does not explicitly specify a tablespace when creating a table (or index), it is automatically created in the USERS tablespace, because that is his default tablespace:
SQL> CONNECT ifernand
Enter password:
Connected.
SQL> CREATE TABLE TEST (dummy VARCHAR2(1))
2 STORAGE (INITIAL 128 m);
Table created.
SQL> SELECT tablespace:name
2 FROM user_segments
3 WHERE segment_name = ’TEST’;
TABLESPACE_NAME
------------------------------
USERS
Once user ifernand exhausts his quota of space, his tables cannot grow any more, and he cannot create new tables (or indexes):
SQL> CONNECT ifernand
Enter password:
Connected.
SQL> ALTER TABLE TEST ALLOCATE EXTENT (SIZE 64 k);
ALTER TABLE TEST ALLOCATE EXTENT (SIZE 64 k)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ’USERS’
SQL> CREATE INDEX test_i1 ON TEST(dummy);
CREATE INDEX test_i1 ON TEST(dummy)
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ’USERS’
SQL> SELECT tablespace:name,
2 BYTES / 1048576 AS mb
3 FROM user_ts_quotas;
TABLESPACE_NAME MB
------------------------------ ----------
USERS 128
Note that other users cannot retrieve or modify the contents of tables owned by user ifernand unless he explicitly gives them the necessary privileges. Listing 8-9 shows some examples of granting table privileges to users—the word PUBLIC denotes all users of the database.
TABLE, SYSTEM, AND ROLE PRIVILEGES
A recurring theme in this chapter’s discussion is that Oracle gives you the ability to control tightly what users are permitted to do. Object privileges are privileges to perform operations on objects; examples include SELECT, INSERT, UPDATE, and DELETE privileges on tables. System privileges are privileges that do not apply to specific objects; examples are CREATE SESSION and CREATE TABLE. Roles are collections of privileges that are created for convenience; all the privileges in a collection can be assigned to a user with a single command. For example, if you want to give a user the ability to perform database administration functions, you can give them theDBA role—it includes such privileges as CREATE USER and DROP USER.
The permissions granted to each user of the database are tracked in the dba_sys_privs, dba_role_privs, and dba_tab_privs views.Here are their definitions:
SQL> CONNECT system
Enter password:
Connected.
SQL> DESCRIBE dba_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
SQL> DESCRIBE dba_role_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
SQL> DESCRIBE dba_tab_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
You explore these views in the exercises at the end of this chapter, where you are asked to audit the privileges of users.
Revoking Permissions Granted to Users
The REVOKE command can be used to revoke a privilege granted by a GRANT command. In Listing 8-10, user ifernand is revoking the privileges on the table test that he previously granted to various users. In the exercises at the end of this chapter, you are asked to review the permissions granted to PUBLIC and revoke those that pose a security risk.
Modifying User Attributes
You can use the ALTER USER command to change any of the attributes specified by the CREATE USER command. One very common use of the command is to change a user’s password.This particular use is not restricted to database administrators—users can use the ALTER USER command to change their own passwords, as shown in Listing 8-11. As you saw in the previous section, ALTER USER can also be used to give users a quota of space in a tablespace.
Removing Users
The final command in the suite of user-management commands is DROP USER.It removes all trace of the user from the database. As shown in Listing 8-12, if the user has created any objects with the database, then either those objects must first be removed manually or the CASCADE option must be specified when using the DROP USER command. CASCADE automatically removes all objects owned by the user being removed from the database.
Data Loading
Any user with the requisite privileges can insert data into tables, but database administrators are routinely called on to help with bulk loading of data.This typically happens when a new software application is deployed, but it can happen on a routine basis in data marts and data warehouses. The simplest way to load data is to use scripts containing INSERT commands—one INSERT command per row—but this approach is only useful for small amounts of data. The sections that follow discuss some of the tools and techniques provided by Oracle for data loading.
The Export and Import Utilities
The exp and imp utilities—used for exporting data out of and importing data into a database, respectively—were once the workhorses of the Oracle world. The data pump utilities, which were introduced in Oracle Database 10g, have more features but also have the drawback of using PL/SQL routines for reading and writing; as a result, they can only read and create server-side files. On the other hand, the exp and imp utilities read and create client-side files such as those located on a user’s laptop. Also, the data pump utilities do not work with magnetic tapes and standby databases. As a result of limitations like these, the exp and imp utilities have not been fully supplanted by the new utilities. I discuss them here for completeness even though their use is now expressly discouraged by Oracle.1
The exp and imp utilities are rich in features; you can list them with the help=y clause. Listing 8-13 shows the features of the exp utility—the imp utility has very similar features.
Listing 8-14 shows an example of the use of the exp utility. You export the contents of the hr sample schema into a file called hr.dmp.Using the consistent=y clause ensures that all the contents of the file accurately represent a single point in time.
Next, let’s import the data into another schema using the imp utility, as illustrated in Listing 8-15.You can take advantage of the fromuser and touser clauses.
Notice the warnings produced by the utility; it reports compilation errors for two triggers. This is because, in the absence of an explicit prohibition, the exp utility exported not only the data but also the definitions of indexes, constraints, triggers, and grants associated with the tables as well as the table and index statistics. The triggers on the employee table refer to stored procedures that do not exist in the destination schema.
The Data Pump Utilities
The expdp (Data Pump Export) and impdp (Data Pump Import) utilities were introduced in Oracle Database 10g with support for parallelism, compression, and encryption, among other things. They were intended to supplant the exp and imp utilities but could not do so completely, for the reasons already stated. The invocation syntax for expdp and impdp is fairly similar to that for the exp and imp utilities; you can use the help=y clause to list their features.
Listing 8-16 illustrates use the expdp and impdp utilities to export and import the same tables referenced in Listing 8-14 and Listing 8-15. Notice the reference to a special directory called data_pump_dir and how intervention by the database administrator becomes necessary to give users permission to use this directory.
SQL*Loader
The SQL*Loader utility (sqlldr) is used to import data from sources located in other Oracle databases.It is similar to the DB2 load utility and the SQL Server bcp utility. It imports data from one or more data files whose structure is described in a SQL*Loader control file.
SQL*Loader offers tremendous flexibility in dealing with data files. For example:
- Fixed-length data items and variable-length (delimited) data items are both supported.
- Header records can be skipped.
- Bad records can be discarded.
- Data items from multiple lines can be combined.
- Data items can be selectively imported.
- Data records can be selectively imported.
- Data items from the same row of data can be inserted into multiple tables.
Listing 8-17 shows the contents of a simple SQL*Loader control file that describes how to load three columns of information from a comma-delimited fileinto a table. The INFILE * clause indicates that there is no separate data file or infile; that is, the data is part of the control file itself.
This control file belongs to the first in a series of 11 case studies included with the Oracle software.The control files (ulcase*.ctl) and data files (ulcase*.dat) can be found in the ORACLE_HOME dbmsdemo directory. SQL scripts for creating the necessary tables are also provided (ulcase*.sql). Complete instructions for testing the examples can be found in the comments section of each control file; they are also discussed in Chapter 6 of Oracle Database 12c Utilities, available at www.oracle.com/pls/db121/homepage. Listing 8-18 shows the results of performing the first case study.
Tip Oracle does not provide a “SQL*Unloader” utility to complement the SQL*Loader utility—this is possibly the longest-standing gap in the Oracle Database product suite.
Summary
Here are some of the key points touched on in this chapter:
- Every object in a database is explicitly owned by a single owner, and the owner of an object must explicitly authorize its use by anybody else. The collection of objects owned by a user is called a schema.Oracle manages the contents of a database using its own tables, indexes, sequences, procedures, triggers, and views; these are stored in the SYS schema.
- The fivecommands required for user management are CREATE USER, ALTER USER, DROP USER, GRANT, and REVOKE.
- Object privileges are privileges to perform operations on objects; examples include SELECT, INSERT, UPDATE, and DELETE privileges on tables. System privileges are privileges that do not apply to specific objects; examples are CREATE SESSION and CREATE TABLE. Roles are collections of privileges that are created for convenience; all the privileges in a collection can be assigned to a user with a single command.
- The exp and imp utilities used to be the workhorses of the Oracle world; they are used to transfer data from one Oracle database to another. They are not as sophisticated as the data pump utilities introduced in Oracle Database 10g, but their advantage is that they can work with client-side files, named pipes, magnetic tapes, and standby databases (when opened in read-only mode).
- The expdp (Data Pump Export) and impdp (Data Pump Import) utilities were introduced in Oracle Database 10g with support for parallelism, compression, and encryption, among other things.
- The SQL*Loader utility is used to import data from sources in other Oracle databases; it is similar to the DB2 load utility and the SQL Server bcp utility. A complementary “SQL*Unloader” utility is not available.
Footnotes
1The following language appears in Oracle Database 11g Utilities: “Original export is desupported for general use as of Oracle Database 11g. The only supported use of Original Export in 11g is backward migration of XMLType data to a database version 10g release 2 (10.2) or earlier. Therefore, Oracle recommends that you use the new Data Pump Export and Import utilities, except in the following situations which require Original Export and Import:
You want to import files that were created using the original Export utility (exp).).
You want to export files that will be imported using the original Import utility (imp).). An example of this would be if you wanted to export data from Oracle Database 10g and then import it into an earlier database release.”