Transcript OFFICE OF THE ACCOUNTANT GENERAL (A&E), KERALA

OFFICE OF THE
ACCOUNTANT GENERAL (A&E),
KERALA
Management of Security
in VLC Software
Security in VLC Software is administered by a user with
DBA privileges, VLCADMIN, and is managed through
the Administration Module.
The Security and Administration module provides
extensive security features to safeguard the data from
both unauthorised viewing and intentional tampering. In
addition, it maintains the users and defines users by
creating roles and functions, audits all deleted records
and checks SQL facilities. Security is provided by
granting specific privileges to roles and then assigning
roles to users. Roles are created by the administrator for
every module by granting the type of access, full or read
only, on related database objects depending on the
module requirement and the status and responsibilities of
the user.
It is invoked from the Modules drop down Menu.
The first screen of the Administration
Module offers the Menu for all
Administration activities.
The Main Menu item are
>Module other Administration
>Roles
>Users
>Report
>Exit
Before going into the finer
details of the sub menus
a detour for a brief look at
the security features in
Oracle
Database Security Concepts
• Confidentiality, integrity, and availability
are the hallmarks of database security.
• Securing your database involves not only
establishing strong password policy, but
also adequate access controls.
• Security is also about the weakest link.
Databases should not provide a point of
weakness.
Database Security Concepts (Contd…)
• Security in Oracle has several layers.
• The first layer of security is, of course, the RAW assignment and
management of users.
• The second layer is the high-level system grants that give the
user permission to create, alter, and use database objects such
as tables, indexes, and clusters.
• The third layer of security is the object-level grants that allow
users to interact with database objects.
• The fourth layer is the column grant layer that grants or
restricts access to the specific columns inside a database object.
• The fifth layer involves the use of policies and contexts to
control row level access. The final level of security deals with
controlling access to system resources such as CPUs and is
accomplished through the use of profiles and resource plans and
groups.
In VLC Software the
VLCADMIN user creates users
using the ‘User Creation and
Role Allocation’ sub menu
under the ‘Users’ menu item
– Data is protected by
 Defining the types/levels of users
 Defining area of work for each user
 Providing various tiers of security for each
user
Types of Users :
- Data Entry Operators
- Supervisors
- Management
•VLC Software incorporates an
added security feature.
•All sub menu actions are protected
from unauthorised users.
•Each action calls for a separate log
on thus preventing unauthorised
users from adding/deleting or
modifying Users, Roles or Modules.
• The Administration module is so
designed that first a module master is
created. It is followed by the creation
of Roles.
• A user can be created only after
creation of Modules and Roles.
• This ensures effective implementation
of the Oracle Security feature of user
grants and privileges.
User Grants and Privileges
> Privileges specify the type of Data
Manipulation Language (DML)
operations, which the user can perform
upon data.
> Two distinct categories of privileges
within a database are :
• System Privileges
• Schema Object Privileges
User Grants and Privileges(Contd…)
• System Privileges
System privileges allow users to perform a
particular systemwide action or a particular
action on a particular type of schema object.
For example, the privileges to create a
tablespace, table or to insert or delete the
rows of any table in the database are system
privileges. Many system privileges are
available only to administrators and
application developers because the privileges
are very powerful.
User Grants and Privileges(Contd…)
• Schema Object Privileges
Schema object privileges for tables allow table
security at the level of data manipulation language
(DML) and data dictionary language (DDL)
operations.
Object privileges allow a user to perform a
particular action on a specific object including
tables, views, sequences, procedures, functions, and
packages. For example, the privilege to insert rows
into a particular table is an object privilege. Object
privilege grants always include the name of the
object for which the privilege is granted.
Managing System and Object Privileges
• The user’s ability to supply a valid username
and password can be used as a first level of
authorization for a user to access a database
or specific database tables.
• Additional techniques that can be used to
further manage system and object privileges
are:
> Roles
> Stored Procedures
> Network Facilities
> Views
Using Roles to Manage Privileges
• A Role mechanism can be used to
provide authorisation. A single person
or a group of people can be granted a
role or a group of roles. One role can
be granted in turn to other roles. By
defining different types of roles,
administrators can manage access
privileges much more easily.
Use of Roles
• Roles are collections of system,
object, and row grants. This allows
the DBA to collect all related grants
for a specific application function
under one object that can then be
easily granted to your users with a
single command. Using roles has
several benefits:
Use of Roles (Contd…)
> Reduces the number of grants and
thereby makes it easier to manage
security
> Dynamically changes the privileges
for many users with a single
grant/revoke
> Can be selectively enabled/disabled
depending upon the application
Use of Roles (Contd…)
> Roles can be used for most system
and object privileges. Privileges
granted through a role cannot be used
for creating a stored object (views,
packages, triggers, java, procedures,
and function). You need to grant
privileges directly to the user for this
At user level the privileges are assigned in
VLC software as given below:
The privileges for.
– Data Entry operator (Select + Insert +
Update + Delete),
– Supervisors (select + Update Check Flag),
- Management (Select)
Area of work for each user
• Each user would be working on a particular
module or a part within a module. This is
determined by providing privileges to roles
Overall security is managed in VLC
Software in 3 security tiers
+ Tier I: Log on Level- Through
Password protection
+ Tier II: Menu level. The user can
operate that Module which is
assigned to him - Through Roles.
+ Tier III: Form/Report Level. Only
those tasks that the role permits
-Through Modules.
User Administration - Passwords
Oracle supports a number of passwords features.
You need to consider all of the following when you
set password standards:
> Passwords aging and expiration: To help ensure that a
password will not be compromised, passwords should
be changed on a system at least every three months.
The decision of whether to enforce password aging and
expiration should be identified in the security plan.
The longer a password remains in effect for an account,
the greater the possibility that the password can be
compromised.
User Administration - Passwords (Contd….)
> Password reuse : If a password will be permitted to be
reused, restrict its use to no more frequently than every
seventh password cycle. A better approach would be to
completely exclude a password that has been used from
being used by that person again.
> Failed login attempts : The number of failed login
attempts that will be tolerated on a system should be
defined in the security plan. You must also determine
the actions that should be taken when the number of
failed login attempts has been exceeded.
User Administration - Passwords (Contd….)
> Account locking and unlocking : If
account locking is going to be
enabled, you can define the personnel
who will be in charge of performing
the account unlocking.
> Passwords in Oracle can now be aged
and expired.
In Oracle one can manage passwords through
profiles. Some of the parameters one can
control are:
• FAILED_LOGIN_ATTEMPTS - failed login
attempts before the account is locked
• PASSWORD_LIFE_TIME - limits the number
of days the same password can be used for
authentication
• PASSWORD_REUSE_TIME - number of days
before a password can be reused
• PASSWORD_REUSE_MAX - number of
password changes required before the current
password can be reused
One can manage passwords through profiles. Some of the
parameters one can control are: (Contd…)
• PASSWORD_LOCK_TIME - number of days
an account will be locked after maximum failed
login attempts
• PASSWORD_GRACE_TIME - number of days
after the grace period begins during which a
warning is issued and login is allowed
• PASSWORD_VERIFY_FUNCTION password complexity verification script
VLC Software incorporates the Oracle Security features
through its menus. A detailed look at the software will clarify
how it is implemented in the VLC Software
A Detailed look at Security and
Administration Module of VLC
Software
The first step in the
implementation of the Security
features is the creation of the
Module Master
• VLCADMIN user has the privilege to create
this Master table. Login as VLCADMIN user to
use this menu item.
• Each module in the Software is given a Module
Code.
• The relevant Forms and Reports are attached
to the module through this form.
• The Form/Report Status flag is set to valid for
the Forms/Reports to be available to users.
• If a Form/Report is to be made inaccessible to
all users this flag is set to invalid.
• The next step is the creation of
Roles
• Roles are module dependent
• As users are of 3 types, Roles too
have 3 types of classification.
• The 3 types are:
• Data Entry Operators identified by D.
• Supervisors identified by S and
• Managers identified by M.
+ Again, depending on the type of access to
objects, a further classification is made
– ALL
– Select
– Insert etc.
– The Role name is thus a combination of Module and
the two classifications like AC_D_ALL1
Login as the owner of objects to create Roles
• After creation of the role, objects necessary to run
the functions for that role are attached to the role.
• Depending on the groups of users, who will be
assigned this role the privileges are checked in the
check box. It could be All or a combination of
Select, Update, Insert and Delete.
• If All is checked, checking any of the other items
will throw an error message.
• And if one of the rest is checked, checking All will
throw an error message.
In the VLC Software, VLC is the owner of
all objects. Therefore, even after attaching
the objects to the respective roles ORA00942 error will be thrown up when a user
tries to work with Forms/Reports in this
module. This is overcome by creation of
Public Synonyms for all objects.
Clicking ‘Create Synonyms’ button creates
synonyms for all objects in one go.
Once the appropriate Roles are
created we are ready for
creation of users
Login as VLCADMIN to create
users
You can choose the user type from the
List, type the user name, userID
Password, Section, Default tablespace,
Temporary tablespace and Profile.
The user account status is set as ‘valid’
by default.
If you want to temporarily block a
user this flag can be set to invalid.
The Role/Roles allocated to the user is
attached in the lower block.
SQL*PLUS Facilities
# By default all SQL*PLUS privileges
are disabled for all users.
# Login as System to grant SQL*PLUS
facilities.
# The SQL*PLUS attributes can be
selectively granted to a user by the
System user.
A user’s password usage and resources can be
controlled through profiles.
The Administrative module has a form to
create/modify profiles.
We can limit the numbers of sessions for a user,
and set values for idle time, failed login
attempts, password reuse time, password lock
time, connect time, password life time, password
reuse maximum and password grace time.
Use of profiles is an easy and efficient method of
enforcing password control .
Among other things, it will ensure that users
will change passwords regularly.
After a user is created, the DBA communicates the
userid and password (same as the userid, to begin
with) to the user with instructions to change the
password immediately.
Passwords are changed using the following form:The user logs in using the original password to this
form.
The new password is typed in the two text boxes.
The program rejects passwords that are shorter
than 6 characters.
This, in fact, overcomes one of the vulnerabilities of
Oracle in respect of length of password.
Press the “Change Password” button and exit by
pressing the “Exit” button.
Restriction on Forms and Reports
• The ‘Forms and Reports for users’ sub
menu under Users menu item is used to
restrict use of Forms and Reports.
• Only those Forms and Reports attached
through this menu will be accessible to
the user.
Restriction on Forms and Reports
(Contd…)
• Even from among those Forms and
Reports attached to a user, the DBA can
restrict usage through the ‘User Form
Status’ Flag.
• Only those Form/Reports whose status is
‘Valid’ is accessible to the user.
• By setting status to ‘Invalid’, access can
be denied to any Form/Report,
notwithstanding the fact that those
Forms/Reports are attached to the user.
Audit of Deleted Records
• The Security and Administration
Module provides auditing of deleted
records.
• It keeps track of all deleted records.
• You can view the list of deleted records
on screen.
• This is accessed through the ‘List of
deleted records’ submenu under
‘Module Other Administration’ menu.
• You can also generate hard copies of
the list of deleted records.
• That was the preview of the report.
• The Report is generated using the
‘List of deleted records’ submenu
under the ‘Reports’ Menu.
• Details are shown user-wise with
date in the descending order.
Relation between
Supervisors and DEOs
• VLC Software includes a feature whereby a
supervisory user, with whom the software
has established a supervisor-DEO
relationship, is given the privilege to update,
or delete records entered by DEOs.
• These relationships are created through the
‘Supervisor DEO Master’ sub menu under
the ‘Module Other Administration’ Menu.
Relation between
Supervisors and DEOs (Contd…)
• Userids and usernames of all DEOs under a
Supervisor are attached through this form.
• Once the relationship is established the
supervisor gets the privilege to update or
delete records entered by DEOs supervised
by him/her.
• This is helpful in making corrections when
the Supervisory user checks data entered by
the DEOs.
Need for enhancement through
Backend Privilege Management
• Oracle has a few more facilities
for privilege management like
– Stored procedures
– Views and
– Virtual Private Database (VPD)
Stored Procedures
• Through stored procedures you can restrict the
database operations that users can perform.
• You can allow them to access data only through
procedures and functions that execute with the
definers privileges.
• For example you can grant users access to a
procedure that updates a table, but not grant
them access to the table itself.
• Users who have only privilege to execute the
procedure can invoke the procedure but they
cannot manipulate table data in any other way.
Views
• A view can limit access to only selected
columns of the base table.
• A view can provide view based security
for the information in a table.
• A where clause in the definition of a view
restricts the display to only selected rows
of the base table.
• To use a view requires privileges only for
the view itself and not on the base
table/object underlying the view.
Row Level Security
* Row level Access to data is a granular form of
data access.
* Access to particular rows is based on such
considerations as the department to which the
user belongs, his job responsibility and other
significant factors.
* Views are used to implement row level security.
* There are two more effective approaches to row
level security.
> They are : Virtual Private Database (VPD) and
Label Based Access Control.
Virtual Private Database (VPD)
• VPD is the ability to perform query modification
based on security policy you have defined in a
package.
• It is associated with a table, view or a synonym.
• VPD provides Fine Grained Access Control.
FGAC is data driven, context-dependent and
row-based.
• VPD enables one to store data belonging to other
sections/offices in the same schema, without their
knowing about it.
Label Security
(Label Based Access Control)
• Label Security uses the VPD feature of
Oracle8i to implement row level security.
• Access to rows are restricted according to a
user’s security sensitivity tag or label to data
rows.
• Data is also marked with appropriate
sensitivity label.
• Label security is configured, controlled and
managed by the Policy Manager utility.
Label Security
(Label Based Access Control) (Contd…)
• Implementation of such fine grained control
of privileges enhances security and prevents
unauthorised access to a very large extent.
• The modular structure of VLC software
segregates data and imposes access
restriction.
• However, enhancement of privilege
management through backend further fine
tunes security.
Thank you