Databases Reference
In-Depth Information
application, you can create stored procedures that provide access to the tables and grant
access to the stored procedures instead of the tables. For example, instead of granting
INSERT privileges for the EMPLOYEE table, you might create and grant access to a
stored procedure called HIRE_EMPLOYEE that accepts as parameters all the data for
a new employee.
When you run a stored procedure normally, the procedure has the access rights that
were granted to the owner of the procedure; that owner is the schema in which the
procedure resides. If a particular schema has access to a particular database object, all
stored procedures that reside in that schema have the same rights as the schema. When
any user calls one of those stored procedures, that user has the same access rights to the
underlying data objects that the procedure does.
For example, suppose there is a schema called HR_REP. This schema has write access
to the EMP table. Any stored procedure in the HR_REP schema also has write access
to the EMP table. Consequently, if you grant a user access to a stored procedure in the
HR_REP schema, that user will also have write access to the EMP table regardless of
her personal level of security privilege. However, she will have access only through the
stored procedures in the schema.
One small but vitally important caveat applies to access through stor‐
ed procedures: the security privilege must be
directly
granted to the
schema, not granted by means of a role.
If you attach the keyword AUTHID CURRENT_USER to a stored procedure when it
is compiled, security restrictions will be enforced based on the username of the user
invoking the procedure, rather than the schema that owns the stored procedure (the
definer of the procedure). If a user has access to a particular database object with a
particular privilege, that user will have the same access through stored procedures
compiled with the AUTHID CURRENT_USER.
Oracle Database 12
c
adds a whole new concept called Real Application Security, which
is similar to a VPD but more flexible. With Real Application Security, you can define
users outside the context of a database user and assign security privileges based on that
user identity. This separation allows for more flexibility, as you do not need to have a
database user defined for every application user, as well as the added flexibility of policy-
based security definition. Real Application Security maps to a security architecture
where data security is enforced in the database, and users are defined and authenticated
in the context of the application.
Distributed Database and Multitier Security
All the security features available for standard Oracle Databases are also available for
the distributed database environment, which is covered in
Chapter 13
. However, the
