Oracle access control

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