Oracle access controlTo 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
SYSTEM/MANAGER (please do not use SYS)
HR/HR (Human Resources),
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;
-- Now we can see the statments that triggered the auditing condition...
select sqltext 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.
Free eBookSubscribe 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.Read more Testimonials
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."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / 2019
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
The name Oracle is a trademark of Oracle Corporation.