Dear Readers,
My name is Franz Devantier, creator of this blog. I am an Oracle Certified
Professional (OCP DBA 11g) Security DBA.
I will be sharing with you the basic duties of an Oracle DBA, and also
some of the undocumented, and not so well known tasks.
I will make a deal with you: If you refer me to a company that needs
database support, from a few hours per week to full time, and I am able to sign
a contract with them.
Then I will give you 10% of the monthly
contract or deal price every month. When
the contract ends, and we re-sign the contract, I will again give you 10% of
the monthly contract price. This will go
on until the company no longer employs or contracts me or my agents to look
after their databases.
I can do this, because that 10% is my
marketing budget. When we re-sign the
contract, in the future, it may depend on you giving the thumbs up again, and
that is worth 10% of the monthly contract price, to be given to you as
commission.
Security,
Privileges, Roles, Profiles and Resource Limitations - Part
4
Procedure
Privileges
EXECUTE is
the only schema object privilege for procedures. The same for standalone procedures, functions
and packages. You should grant this
privilege only to users who need to execute the procedure. You will also need to grant the privilege on
this procedure to users that will compile another procedure that calls this
procedure. It is important to create and
manage secure and effective use of the procedure privileges.
Procedure Execution and Security Domains
If a user has the EXECUTE object privilege for a specific
procedure, then the user can execute the procedure, or compile a program that
references the procedure. Important to
be aware that no run-time privilege check is made when the procedure is called. If a user has the EXECUTE ANY PROCEDURE
system privilege, then they can execute any procedure in the database. So don’t give the EXECUTE ANY PROCEDURE
system privilege out easily, because it is a security risk.
Privileges to execute procedures, can be granted to a user
through roles. The owner of the
procedure is called the definer. The
procedure definer, must have all the appropriate object privileges for the
referenced objects, in order to create the procedure without errors. When the definer grants EXECUTE on that
procedure to another user; then that user will exercise the privileges that
were granted to the definer, when they execute the procedure.
A user with the EXECUTE object privilege for a specific
procedure can execute the procedure or compile a program unit that references
the procedure. No run-time privilege check is made when the procedure is
called. A user with the EXECUTE ANY PROCEDURE system privilege can execute any
procedure in the database. Privileges to execute procedures can be granted to a
user through roles.
The owner of a procedure, called the definer, must
have all the necessary object privileges for referenced objects. If the owner
grants to another user the right to use that procedure, then the owner object
privileges for the objects referenced by the procedure apply to that user's
exercise of the procedure. These are termed "definer's rights."
The user of a procedure who is not its owner is called the
"invoker." Additional privileges on referenced objects are required
for invoker's rights procedures, but not for definer's rights procedures.
Definer's Rights
A user of a definer’s rights procedure requires only the
privilege to execute the procedure and does not require any privileges on the
underlying objects that the procedure accesses.
A Definer’s rights procedure operates under the security domain of the
user who owns the procedure; regardless of who is executing the procedure. So the owner of the definer’s rights
procedure has all the needed object privileges for all the referenced
objects. Fewer privileges need to be
granted to users of a definer’s rights procedure. This results in a more efficient and secure
control of database access.
You can use definer’s rights procedures to control access
to private database objects, and add an additional level of database
security. Instead of granting access to
a whole lot of objects that the user needs to access, you can create a definer’s
rights procedure, and only grant the EXECUTE privilege on the procedure to the
user. This way the user is forced to
access the referenced objects only through the procedure.
With a definer’s rights procedure, the privileges of the definer or owner of the procedure are
checked before the procedure is executed.
If a needed privilege on a referenced object has been revoked from the
owner of a definer’s rights procedure, then the procedure cannot be executed by
the owner, or any other user.
Trigger execution works in the same way as definer’s rights
procedures. The user executes SQL
statement which that user has the appropriate privileges to execute. The SQL statement causes a trigger to
fire; The statements in the trigger now
execute under the security domain of the owner of the trigger. So the user does not need to have any
privileges on the objects that the trigger accesses.
Franz Devantier,
Need a database health check, or a security audit?
devantierf@gmail.com
Classifieds
No comments:
Post a Comment