Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle user security management

Oracle Tips by
Steve Karam
Oracle ACE, Oracle Certified Master

In the rapidly shifting world of database technology, one fact has always been, and will always remain, true: a great database is no good if it is not secure. A faulty security plan is not just vulnerable to hackers; it opens your company to data theft, corruption, and legal action.

Oracle has made the claim of being Unbreakable, and it truly can be unbreakable with the proper know-how, patches and experience. The built-in security of Oracle, both 9i and 10g, is without a doubt some of the best in the industry. Through proper usage, auditing, and documentation of security practices using Oracle's built in tools, you too can possess a truly unbreakable system.

Oracle user security

The fastest database in existence would have one control file, two redo log groups with one member each, and no users.

If that sentence didn't catch your eye the first time, read it again. A database must have users. Without them, there is no profit. But having users connecting to your database presents a whole range of potential problems. You, the DBA, must give privileges to access other objects on the database. An open database is a hackable database.

There are three levels of users in the average Oracle system:

  • Administrators
  • Developers
  • End users/ Vendors/ Customers/ Viewers

The article you are reading now is stored in a database, and is being delivered by a web application to your computer screen. At this moment, you are an end user. When you go to the bank and use an ATM, you are a user of a database. The act of putting your ATM card into a system and entering your PIN is your login to the application. From there, you are a customer and viewer, able to select (view balance), insert (make a transaction), and update (deposit/withdraw).

The main thing that you, the DBA must apply to your users is the principle of least privilege. Here are some ideas for making this principle work:

  • Do not give your users more abilities than they need to get the job done
  • Revoke unnecessary privileges from the PUBLIC pseudo-user
  • Expire and lock unnecessary users
  • Do not give your users more abilities than they need

This is the foundation of the principle of least privilege. Give your users only the rights they need to do their job, and no more. Do not give DBA to your users just to make life easier on you. Even simple roles such as CONNECT and RESOURCE can pose a security threat to your system. If you are not careful, a user can easily crash your database using just the CONNECT role.

To comply with auditing standards and to protect your database, you should create separate roles for other DBAs, developers, and other database users. Create your roles according to job roles. If your company has sales clerks and sales managers, create the SALES_CLERK and SALES_MANAGER roles. If you have a junior DBA, create a JRDBA role. This makes it easy to maintain security through changing business rules and employees. It also will help you if you are audited. View the example below:

create role cust_serv_clerk;
grant select on customers to cust_serv_clerk;
grant select, insert, update on issue_track to cust_serv_clerk;

In this example, we create a role called CUST_SERV_CLERK. To this role we grant the ability to select from the CUSTOMERS table, and select, insert, and update the ISSUE_TRACK table. The role now has the privileges of a customer service clerk.

grant cust_serv_clerk to bob;

The above command gave a user named 'bob' the role. Bob now has all of the privileges granted to the role.

create role cust_serv_mgr;
grant cust_serv_clerk to cust_serv_mgr;
grant insert, update, delete on customers to cust_serv_mgr;
grant delete on issue_track to cust_serv_mgr;
grant cust_serv_mgr to mary;

Now we have created a role called CUST_SERV_MGR. To this role we have granted the CUST_SERV_CLERK role. Along with it, we grant the privileges it leaves out: insert, update, and delete on CUSTOMERS, and delete on ISSUE_TRACK. Lastly, we grant this newly created role to the user named 'mary.'

Use roles and privileges to enable your users to do the work they need. However, if you grant too much to your users, it will come back to bite you. Any user has the ability to see the privileges that are granted to them. All it takes is one clever user to figure out how to exploit those privileges - and in the DBA world, a mistake like that could cost you your credibility, if not your job.

Revoke unnecessary privileges from the PUBLIC pseudo-User

In Oracle, there is a pseudo-user named PUBLIC who is everyone and no one. A privilege or role granted to PUBLIC will be given to every user - past, present, and future. Never, under any circumstances, grant privileges to PUBLIC that you do not wish every user to have. Even if you want to do this, make sure there is a pressing business reason for doing so. It is not good enough to argue that it makes life easier for you.

The PUBLIC user comes with several execute privileges on key Oracle packages. Some of these packages include:

  • DBMS_RANDOM - Used to generate random numbers, often used as encryption seeds
  • DBMS_OBFUSCATION_TOOLKIT - Oracle's encryption toolkit
  • UTL_FILE - Utilities to read and write to the file system
  • UTL_SMTP - Used to send mail from PL/SQL
  • UTL_TCP - Network functionality from PL/SQL

Look again at the descriptions of the above packages. The thought of every database user having these privileges should terrify you. You can very easily revoke privileges from public, and then grant them only to the users that need them - like this:

revoke execute on dbms_obfuscation_toolkit from public;
grant execute on dbms_obfuscation_toolkit to security_user;

To find out what is granted to your PUBLIC user, you can use the following query:

select privilege from dba_sys_privs where grantee = 'PUBLIC';

Be warned that you should NOT revoke every privilege granted to PUBLIC. Look for key packages and use your common sense to find critical ones that should be revoked.

Changing passwords, expiring and locking inactive users

There are many users on a full installation of Oracle, most of which you probably won't need. To lock a user, you can use the following command:

alter user username account lock;

To unlock the user, simply replace 'lock' with 'unlock.'

Oracle comes with a few default accounts that should never be locked or dropped. These include: SYS, SYSTEM, SYSMAN (Oracle 10g), OUTLN. However, you do want always to change the password for these users. The default password for SYS is change_on_install. It is important that you follow these directions. To change the password for a user:

alter user username identified by new_password;

For non-critical users, you can always lock and expire the account. We can change the password for, lock, and expire the account CTXSYS all at once:

alter user ctxsys identified by 0bscur3 account lock password expire;

This will ensure that CTXSYS password has been changed from the self evident default of CTXSYS; on a successful login the password will have to be changed (because it is expired); and because it is locked, he account can't be logged into under any circumstances.

To aid you in this task, here is a list of users that come with a full database installation that are safe to expire and lock if you are not using their functionality:

  • BI - demo user
  • CTXSYS - Oracle Text/interMedia administrator
  • DBSNMP - Oracle Intelligent Agent
  • DSSYS - Dynamic Services and Syndication Server
  • HR - demo user
  • MDSYS - Spatial administrator
  • ORDSYS/ORDPLUGIN - Object Relational Data user
  • OE - demo user
  • PERFSTAT - Statspack administrative user
  • SCOTT - demo user
  • SH - demo user
  • TRACESVR - Oracle trace server
  • WKSYS - Ultrasearch administrator

The best rule of thumb is to install only the features you need when you install Oracle. If you are not going to use Spatial, interMedia, or UltraSearch, don't install them. Then you will not have to worry about those three users.

Conclusion on Oracle user security

While there is more to user management than this article details, these three principles form the foundation of user security. Without them, your database is easily accessible to data theft, corruption, and disruption. Never make the mistake of thinking that hackers would not be interested in breaking into your database. All it takes is one lapse, and you could lose everything you work for.

Also see:


If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.