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
3
Table Privileges
Schema object privileges for tables, enable table security
for Data Manipulation Language (DML), and Data Definition Language (DDL).
DML Operations
You can grant the privileges for SELECT, INSERT, UPDATE,
and DELETE DML operations on a table or view.
These privileges should only be granted to users and roles that need
these privileges to complete the required operations on the tables or views in
question.
You can restrict INSERT and UPDATE privileges for a table
to specific columns of the table. With
Selective INSERT, a privileged user can insert a row into a table with values
only for the selected columns. All the
other columns receive NULL values or default values. With Selective UPDATE, a user can update
only specific column values of a row.
Selective INSERT and UPDATE privileges are used to restrict user access
to sensitive data.
If you don’t want normal data entry users to be able to alter
the salary notch column of the SALARY table; then you can grant selective
INSERT or UPDATE privileges that exclude the salary notch column. A view which excludes the salary notch column
could also be used to provide an additional layer of security.
Here is how this works:
You want to grant update privileges for example on a
certain table, but only on a certain column to a user or a role. As user SAL_USER
SQL> grant update (column_01, column_2) on salary_tab to
fred;
Now user FRED can update this table with these two columns.
SQL> update SAL_USER.SALARY_TAB
Set column_01 = 505
Where salary_num = ‘00480’;
This update goes through without a hitch.
SQL> update SAL_USER.SALARY_TAB
Set column_03 = 505
Where salary_num = ‘00480’;
Results in an ORA-01031: insufficient privileges.
This is exactly what we wanted to achieve with the above
scenario.
View Privileges
A view is the result of a stored query, and shows the data
from one or more selected tables, and could even include other views. The data in a view can be queried, and it can
be changed, provided that the data represented is not summary data generated
from the underlying tables. Data in a
query can be UPDATED, DELETED, or INSERTED into. These operations will directly alter the
underlying tables on which the view is based, and will be subject to the
integrity constraints and triggers of the underlying tables. Of course if the user does not have access to
a column on a table, and the user attempts to update that column, then the
ORA-01031: insufficient privileges error will result.
Privileges Required to Create Views
You need the following privileges to create a view;
·
One of the following
system privileges must have been granted to you
o
CREATE VIEW system
privilege to create a view in your own schema
o
CREATE ANY VIEW system
privilege to create a view in any other user’s schema
·
One of the following
privileges, must have been granted explicitly to you
o
SELECT, INSERT,
UPDATE, OR DELETE object privileges on all the base objects underlying the view
o
SELECT ANY TABLE,
INSERT ANY TABLE, UPDATE ANY TABLE, or DELETE ANY TABLE system privileges
·
Also, in order to
grant other users access to your view, you must have received object privileges
to the base objects with the GRANT OPTION clause or appropriate system
privileges with the ADMIN OPTION clause; to enable grantees to access your
view.
Increasing Table Security with Views
To use a view the privileges are different than for when
you were creating the view. You require
the appropriate privileges for the view only to use a view. You do not require privileges on base objects
underlying the view.
Views add two more levels of security for tables; that is
column-level security, and value-based security.
·
Column-Level Security:
A view can provide access to selected columns of base tables. This means that columns containing sensitive
data are excluded from the view.
SQL> Select First_name,
Last_name, Employee_id, Pay_Date
from salary_table;
Notice how the salary details have
been left out of the view.
·
Value-Based
Security: A view can provide value based security for the information in a
table. A where clause in the definition
of the view displays only the rows that are needed. The where clause can also exclude rows with
especially sensitive data in them
SQL> CREATE VIEW entry_ones AS
SELECT * FROM salary_table
WHERE salary <10000;
In this case we have allowed all of the columns, but just limited
the amount of rows, to the rows that reflect criteria. This may expose some sensitive data.
We can also address the Column-Level security and the Value-Based
security in one view, by selecting only certain columns, and limiting the
amount of rows returned with a where clause.
SQL> CREATE VIEW my_salary AS
SELECT full_name, salary --
column-level security
From salary_table
Where last_name = username;
-- value-based security
Franz Devantier,
Need a database health check, or a security audit?
devantierf@gmail.com
Classifieds
No comments:
Post a Comment