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.
Contact:
Franz
Security,
Privileges, Roles, Profiles and Resource Limitations - Part
5
Invoker's Rights
An invoker’s rights procedure is quite different to a
definer’s right procedure. The invoker’s
rights procedure executes with all the privileges of the invoker. Roles are enabled, unless the invoker’s
rights procedure was called directly or indirectly by a definer’s rights
procedure. So you can see that the user
of an invoker’s rights procedure needs privileges in order to execute the
procedure, either directly to the user or through a role on all the objects
that the procedure accesses. All the external
references in an invoker’s rights procedure must be resolved in the invoker’s
schema.
The invoker is going to need all the privileges at
run-time, in order to access the program and object references, that are
embedded in the DML or dynamic SQL statements that are included in the
procedure. Provision should be made,
with reference to object access, for the fact that the dynamic SQL statements
are effectively recompiled at run-time.
However other external references, such as direct PL/SQL
function calls, the owner’s privileges are checked at compile time, and no
run-time check is made. Therefore the
user of an invoker’s rights procedure only needs privileges on the objects
invoked, by the DML and dynamic SQL statements.
You can create a software bundle that consists of multiple
program units, some with definer’s rights and some with invoker’s rights; and
restrict the program entry points, in other words you have a controlled step-in
to the software bundle. Then a user who
has the privilege to execute an entry-point procedure can also execute internal
program units indirectly, but cannot directly call the internal programs.
System Privileges Needed to Create or Alter a Procedure
To create a procedure, user must have the CREATE PROCEDURE
system privilege. To alter a procedure
or manually recompile a procedure, a user must own the procedure. If the user is some sort of power-user, they
may have CREATE ANY PROCEDURE or ALTER ANY PROCEDURE system privileges. Alarm bells should be going off if they have
the “ANY” privilege, because that means they can create or change any procedure
in the database; look very carefully to see that these privileges do not
constitute a security threat.
The user who owns the procedure must also have privileges
for all the schema objects referenced in the procedure body. If you have obtained the required privileges
through roles, then you still have a problem.
All of the needed privileges need to have been granted directly to
you. You also need for example the
EXECUTE privilege granted directly to you for any procedures that are called
inside the procedure being created.
Triggers are similar, in that they also require that
privileges to the referenced objects be granted explicitly to the trigger
owner. There is an exception in the
anonymous PL/SQL blocks, the privileges can be granted explicitly or through a
role.
Packages and Package Objects
If a user has been granted the EXECUTE object privilege for a package, then that
user can execute any public procedure or function in the package, and access or
modify the value of any public package variable. You can’t grant EXECUTE privileges for
individual constructs within a package.
Therefore you need to establish security constructs that can handle the
requirement for different people being able to access different functionality.
If you grant EXECUTE privileges to a user for a package,
then the user has access to all the procedures within the package. So our design has to take into account the
separation of duties.
CREATE PACKAGE BODY employ_dismiss AS
PROCEDURE employ(…)
IS
BEGIN
INSERT INTO
employee…;
END employ;
--
PROCEDURE dismiss(…)
IS
BEGIN
DELETE FROM employee…;
END dismiss;
END employ_dismiss;
/
CREATE PACKAGE BODY increase_bonus AS
PROCEDURE increase(…)
IS
BEGIN
UPDATE employee
SET salnotch = …;
END increase;
--
PROCEDURE bonus(…)
IS
BEGIN
UPDATE employee
SET bonus = …;
END bonus;
END increase_bonus;
/
Now we:
GRANT EXECUTE ON employ_dismiss TO (Those managers that do
the employing and dismissing)
GRANT EXECUTE ON increase_bonus TO (Those in finance who adjust the salary’s)
So by simply separating the duties of various functions in
the organization, and granting access to those who need that functionality, we
have made things more secure. It would
now be unlikely that somebody could be accidentally or maliciously dismissed,
because the functionality to do this now resides with a very few people.
Still on this theme of separation of duties, we could have
put all of the functionality in one package, and only allow users to access
this package through properly controlled procedures and packages.
For example:
CREATE PACKAGE BODY personnel AS
PROCEDURE employ(…)
IS
BEGIN
INSERT INTO
employee…;
END employ;
--
PROCEDURE dismiss(…)
IS
BEGIN
DELETE FROM employee…;
END dismiss;
--
PROCEDURE increase(…)
IS
BEGIN
UPDATE employee
SET salnotch = …;
END increase;
--
PROCEDURE bonus(…)
IS
BEGIN
UPDATE employee
SET bonus = …;
END bonus;
END personnel;
/
Now that we have all of the functionality in one package,
we create separate procedures and packages to access the functionality.
CREATE PROCEDURE employ(…)
BEGIN
Personnel. employ(…);
END employ;
/
Only those users who will employ somebody will have access
to the procedure “employ”.
CREATE PROCEDURE dismiss(…)
BEGIN
Personnel. dismiss(…);
END employ;
/
Only those who will be allowed to dismiss people will have access
to the “dismiss” procedure.
CREATE PACKAGE BODY adjustsal AS
PROCEDURE incr(…) IS
BEGIN
Personnel. increase(…);
END incr;
--
PROCEDURE bon(…) IS
BEGIN
Personnel. bonus(…);
END bon;
END adjustsal;
/
Only those who are going to need to adjust the salary will
be given access to this functionality.
They will be able to raise an employees salary like this
SQL> execute adjustsal.incr(…);
They will be able to give a bonus like this:
SQL> execute adjustsal.bon(…);
In the above examples we have tightened up security by
applying the principal of the separation of duties. Effectively only those people who are
authorized to perform certain functions will have the privileges, and be able
to perform those functions.
Type Privileges
Type privileges falls under a few different areas of
interest.
·
System privileges for
named types
·
Object privileges
·
Method execution Model
·
Privileges required to
Create Types and Tables Using Types
·
Privileges on Type
Access and Object Access
·
Type Dependencies
System
Privileges for Named Types
Oracle
defines the system privileges in the table below for Object Types, VARRAY’s,
and Nested Tables:
Privilege
|
Detail of privilege
|
CREATE TYPE
|
Create named types in your own schemas
|
CREATE ANY TYPE
|
Create a named type in any schema
|
ALTER ANY TYPE
|
Alter a named type in any schema
|
DROP ANY TYPE
|
Drop a named type in any schema
|
EXECUTE ANY TYPE
|
Use and reference a named type in any schema
|
It is not good practice to grant the RESOURCE role to users
anymore. One of the reasons is that the
RESOURCE role includes the CREATE TYPE system privilege. The DBA role includes all of the TYPE
privileges.
Object Privileges
The only object privilege that is applicable to a named
type is the EXECUTE privilege. If a user
has the EXECUTE privilege on a Named Type, then the user can use the Named Type
to perform a few operations:
·
Define a table
·
Define a column in a
relational table
·
Declare a variable or
parameter of the named type
The EXECUTE privilege on a Named Type allows a user to
invoke the methods in the type, including the constructor. This works in a comparable way to the way
that the EXECUTE privilege works on a stored PL/SQL procedure.
Franz Devantier,
Need a database health check, or a security audit?
devantierf@gmail.com
Classifieds
No comments:
Post a Comment