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
10
PL/SQL Blocks and Roles
Roles with PL/SQL blocks, depends on whether it is an
anonymous block or a named block such as a stored procedure, function, package
or trigger; and whether it executes with definer’s rights or invoker’s rights.
Named Blocks with Definer's Rights
When a named PL/SQL block executes with Definer’s rights,
then all the roles are disabled. In a
procedure that runs with Definer’s rights, roles are not used for privilege
checking, and you can’t set roles either.
SQL> select * from session_roles;
ROLE
------------------------------
CONNECT
RESOURCE
CUSTOM_ROLE_001
SQL> show user
USER is "FRED"
If a named PL/SQL block that executes with Definer’s rights,
queries SESSION_ROLES, it will return no rows.
Anonymous Blocks with Invoker's Rights
Anonymous PL/SQL blocks and named PL/SQL blocks that
execute with invokers rights, are executed according to the privileges granted
through the enabled roles. You can set a
role in the session to add a group of privileges.
DDL Statements and Roles
In order to successfully execute a DDL statement, the user
is dependent on one or more system privileges.
For example CREATE VIEW system privilege or CREATE ANY VIEW system
privilege. When creating a view, the
user may also require the SELECT object privilege or the SELECT ANY TABLE
privilege.
The Oracle Database Server restricts the use of specific
privileges in certain DDL statements, according to a set of rules.
·
All of the system
privileges and schema object privileges are usable in the context of performing
DDL operations, when received through a role.
For example: CREATE TABLE, CREATE VIEW, CREATE PROCEDURE, and for a table
ALTER, INDEX. Exception: The REFERENCES
object privilege for a table cannot be used to define foreign keys if the
privilege is received through a role.
·
When a DDL statement
has a DML component to it then; all the system and object privileges that are
received through a role, are not usable for the DML side of the operation.
For example if a user receives the
SELECT ANY TABLE system privilege or the SELECT object privilege for a table
through a role, then the user cannot use the privilege to create a view on a
table that belongs to another user.
For example if the user was:
·
Receives the CREATE
VIEW system privilege through a granted role
·
Receive the SELECT
object privilege for the employees table from a granted role
·
Directly granted the
SELECT object privilege for the departments table
·
The user can issue
SELECT statements on both the employees table and the departments table.
So if the use were to take stock, the user has access to
the CREATE VIEW system privilege and the SELECT privilege on the employees
table. Yet when the user tries to create
a view on the employees table an error is generated. This is because the SELECT employees
privilege was granted through a role.
However
the user can successfully create a view on the departments table. This is because the user was directly granted
the SELECT privilege on the departments table, and the CREATE VIEW system
privilege remains as it was for the employees table.
Franz Devantier,
Need a database health check, or a security audit?
devantierf@gmail.com
Classifieds
No comments:
Post a Comment