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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Assigning Oracle user security roles

Oracle Tips by
Steve Karam, OCM

 

Also see our notes on:

Assigning Oracle user security roles

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.

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

  1. Administrators
  2. Developers
  3. 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 enteriing 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:

  1. Do not give your users more abilities than they need to get the job done
  2. Revoke unnecessary privileges from the PUBLIC pseudo-user
  3. 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 carefull, 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:

  1. DBMS_RANDOM - Used to generate random numbers, often used as encryption seeds
  2. DBMS_OBFUSCATION_TOOLKIT - Oracle's encryption toolkit
  3. UTL_FILE - Utilities to read and write to the file system
  4. UTL_SMTP - Used to send mail from PL/SQL
  5. 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.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of my 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 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational