The Database Design Resource Center



Oracle access control

To perform Oracle access control, Oracle creates a number of default database users or schemas when a new database is created. Below are some of the most important users:

SYS/CHANGE_ON_INSTALL or INTERNAL

Oracle Data Dictionary/ Catalog

Created by: ?/rdbms/admin/sql.bsq and various cat*.sql scripts
Can password be changed: Yes (Do so right after the database was created)
Can user be dropped: NO

SYSTEM/MANAGER (please do not use SYS)
Created by: ?/rdbms/admin/sql.bsq
Can password be changed: Yes (Do so right after the database was created)
Can user be dropped: NO

OUTLN/OUTLN
Stored outlines for optimizer plan stability
Created by: ?/rdbms/admin/sql.bsq
Can password be changed: Yes (Do so right after the database was created)
Can user be dropped: NO

SCOTT/TIGER,
ADAMS/WOOD,
JONES/STEEL,
CLARK/CLOTH
and
BLAKE/PAPER.
Training/ demonstration users containing the popular EMP and DEPT tables
Created by: ?/rdbms/admin/utlsampl.sql
Can password be changed: Yes
Can user be dropped: YES - Drop users cascade from all production environments

HR/HR (Human Resources),
OE/OE (Order Entry),
SH/SH (Sales History).
Training/ demonstration users containing the popular EMPLOYEES and DEPARTMENTS tables
Created by: ?/demo/schema/mksample.sql
Can password be changed: Yes
Can user be dropped: YES - Drop users cascade from all production environments

CTXSYS/CTXSYS
Oracle interMedia (ConText Cartridge) administrator user
Created by: ?/ctx/admin/dr0csys.sql

TRACESVR/TRACE
Oracle Trace server
Created by: ?/rdbms/admin/otrcsvr.sql

DBSNMP/DBSNMP
Oracle Intelligent agent
Created by: ?/rdbms/admin/catsnmp.sql, called from catalog.sql
Can password be changed: Yes - put the new password in snmp_rw.ora file
Can user be dropped: YES - Only if you do not use the Intelligent Agents

ORDPLUGINS/ORDPLUGINS
Object Relational Data (ORD) User used by Time Series, etc.
Created by: ?/ord/admin/ordinst.sql

ORDSYS/ORDSYS
Object Relational Data (ORD) User used by Time Series, etc
Created by: ?/ord/admin/ordinst.sql

DSSYS/DSSYS
Oracle Dynamic Services and Syndication Server
Created by: ?/ds/sql/dssys_init.sql

MDSYS/MDSYS
Oracle Spatial administrator user
Created by: ?/ord/admin/ordinst.sql

AURORA$ORB$UNAUTHENTICATED/INVALID
Used for users who do not authenticate in Aurora/ORB
Created by: ?/javavm/install/init_orb.sql called from ?/javavm/install/initjvm.sql

PERFSTAT/PERFSTAT
Oracle Statistics Package (STATSPACK) that supersedes UTLBSTAT/

UTLESTAT
Created by: ?/rdbms/admin/statscre.sql

For Oracle access control, users normally use the "connect" statement to connect from one database user to another. However, DBAs can switch from one user to another without a password.

If you allow people to log in with OPS$ accounts from Windows Workstations, you cannot be sure who they really are. With terminals, you can rely on operating system passwords, with Windows, you cannot.

If you set REMOTE_OS_AUTHENT=TRUE in your init.ora file, Oracle assumes that the remote OS has authenticated the user.

If REMOTE_OS_AUTHENT is set to FALSE (recommended), remote users will be unable to connect without a password. IDENTIFIED EXTERNALLY will only be in effect from the local host.

Also, if you are using "OPS$" as your prefix, you will be able to log on locally with or without a password, regardless of whether you have identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY.

The next level of Oracle access control security is Fine Grained Auditing (DBMS_FGA) which allow auditing records to be generated when certain rows are selected from a table.

A list of defined policies can be obtained from DBA_AUDIT_POLICIES. Audit records are stored in DBA_FGA_AUDIT_TRAIL. Look at this example:

-- Add policy on table with autiting condition...

execute dbms_fga.add_policy('HR', 'EMP', 'policy1', 'deptno > 10');

-- Must ANALYZE, this feature works with CBO (Cost Based Optimizer)

analyze table EMP compute statistics;
select * from EMP where c1 = 11;
-- Will trigger auditing
select * from EMP where c1 = 09; -- No auditing

-- Now we can see the statments that triggered the auditing condition...

select sqltext from sys.fga_log$;
delete from sys.fga_log$;

A VPD offers Fine-Grained Access Control (FGAC) for secure separation of data.

This ensures that users only have access to data that pertains to them. Using this option, one could even store multiple companies' data within the same schema, without them knowing about it.

VPD configuration is done via the DBMS_RLS (Row Level Security) package. Select from SYS.V$VPD_POLICY to see existing VPD configuration.

Unlike the specific grant model where specific database privileges are granted to specific users, the grant execute model allows users to be granted execution access without having any database privileges.

In Oracle access control, this is called definer rights, and the end user takes on the database privileges of the definer of the stored procedure when the code is executed.

Definer rights have several important advantages over standard grant security, primarily the simplification of the Oracle access control rules.

In Oracle, you may also use invoker rights, by which the end user may only execute the stored procedure using privileges that are assigned using standard grant security. Invoker rights are more complicated (and less desirable to many Oracle designers) because Oracle grant security must also be implemented.

It should be apparent that the grant execute model requires careful up-front design. Developers must be forced to follow the design standards, creating Oracle stored procedures and functions that are then encapsulated into packages.

Procedurally, this requires the Oracle designer to predetermine the process code for the system and define a set of packages that perform all database access and processing.

For more details on Oracle access control and other DBA issues, read my section on Oracle DBA.

Return to installing the Oracle database


Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant


Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:


What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials



Database Normalization eBook:


Database Normalization eBook




Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.