OFFICE OF THE ACCOUNTANT GENERAL (AE), KERALA

Document Sample
OFFICE OF THE ACCOUNTANT GENERAL (AE), KERALA Powered By Docstoc
					     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 ORA-00942 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