Users and Database Authorization for Oracle Database XE

Altering Users

Changing the characteristics of a user is accomplished by using the ALTER USER command. The syntax for ALTER USER is nearly identical to that ofCREATE USER, except thatALTER USERallows you to assign roles as well as grant rights to a middle-tier application to perform functions on behalf of the user.

In this example, we change userKELLYCto use a different default permanent tablespace:

SQL> alter user kellyc
  2  default tablespace php_apps quota 500m on php_apps;

User altered.

SQL>

Note that the userKELLYCstill can create objects in theUSERStablespace, but she must explicitly specifyUSERSin anyCREATE TABLEandCREATE INDEXcommands.

Dropping Users

Dropping users is very straightforward and is accomplished with the DROP USER command. The only parameters are the username to be dropped and the CASCADE option; any objects owned by the user must be explicitly dropped or moved to another schema if theCASCADEoption is not used. In the following example, the userQUEENBis dropped, and if there are any objects owned byQUEENB, they are automatically dropped as well:

SQL> drop user queenb cascade;
User dropped.

If any other schema objects, such as views or packages, rely on objects dropped when the user is dropped, the other schema objects are markedINVALIDand must be recoded to use other objects and then recompiled. In addition, any object privileges that were granted by the first user to a second user via theWITH GRANT OPTIONclause are automatically revoked from the second user if the first user is dropped.

Becoming Another User

To debug an application, a DBA sometimes needs to connect as another user to simulate the problem. Without knowing the actual plain-text password of the user, the DBA can retrieve the encrypted password from the database, change the password for the user, connect with the changed password, and then change back the password using an undocumented clause of the ALTER USER command. It is assumed that the DBA has access to the DBA_USERS table, along with theALTER USERprivilege. If the DBA has the DBA role, these two conditions are satisfied.

The first step is to retrieve the encrypted password for the user, which is stored in the tableDBA_USERS:

SQL> select password from dba_users where username = 'KELLYC';

PASSWORD
-------------------------
E18FBF6B825235F2

SQL>

Save this password using cut and paste in a GUI environment, or save it in a text file to retrieve later. The next step is to temporarily change the user’s password and then log in using the temporary password:

SQL> alter user kellyc identified by temppass;
User altered.
SQL> connect kellyc/temppass;
Connected.

At this point, you can debug the application fromKELLYC’s point of view. Once you are done debugging, change the password back using the undocumentedby valuesclause ofALTER USER:

SQL> alter user kellyc identified by values 'E18FBF6B825235F2';
User altered.

User-Related Data Dictionary Views

A number of data dictionary views contain information related to users and characteristics of users. Table 31-2 lists the most common views and tables.

Table 31-2. User-Related Data Dictionary Views and Tables