Home arrow Oracle arrow Users and Database Authorization for Oracle Database XE

Users and Database Authorization for Oracle Database XE

In this fourth part of a ten-part series on securing Oracle Database XE, we'll delve more deeply into handling users and look at the various methods of database authorization. This article is excerpted from chapter 31 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

  1. Users and Database Authorization for Oracle Database XE
  2. Understanding Database Authorization Methods
By: Apress Publishing
Rating: starstarstarstarstar / 1
December 30, 2010

print this article



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.


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';



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;

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

Data Dictionary View



Usernames, encrypted passwords, account status, and default tablespaces.


Disk space usage and limits by user and tablespace, for users who have quotas that are not UNLIMITED.


Profiles that can be assigned to users with resource limits assigned to the profiles.


Password history with usernames, encrypted passwords, and date stamps. Used to enforce password reuse rules.

>>> More Oracle Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: