 |
|
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:
- AURORA$ORB$UNAUTHENTICATED
- Jserver user
- 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:
