Core Role Based Access Control _RBAC_ mechanism for MySQL

Document Sample
Core Role Based Access Control _RBAC_ mechanism for MySQL Powered By Docstoc
					Core Role Based Access Control (RBAC)
        mechanism for MySQL

Ian Molloy       Radu Dondera                    Umang Sharan

             CS541 Project Report

              Under the Guidance of
               Prof. Elisa Bertino

                        With the
             Department of Computer Science
             Purdue University, West Lafayette
   RBAC or Role-Based Access Control is an approach to restrict system access to
authorized users and help in implementing a secure access control for larger databases.
MySQL is a popular open source relational database management system (RDBMS)
which currently implements MAC and DAC access control mechanisms. We extend the
access control policies in MySQL by adding the Core RBAC functionality to it.

1 Introduction                                                                                                            3

2 Role-Based Access Control                                                                                               3
  2.1 RBAC model and Functional Specification          .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   4
      2.1.1 Core RBAC . . . . . . . . . . . . .       .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   4
      2.1.2 Hierarchical RBAC . . . . . . . . .       .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   5
      2.1.3 Protection Policies . . . . . . . . .     .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   5
  2.2 Handling Sessions . . . . . . . . . . . . . .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   .   6

3 Introduction to MySQL                                                                                                   6
  3.1 System R Authorization Model . . . . . . . . . . . . . . . . . . . . . . .                                          6
  3.2 Access Control in MySQL . . . . . . . . . . . . . . . . . . . . . . . . . .                                         7

4 Coding and Schema                                                                                                       8

1     Introduction
RBAC or Role-Based Access Control is an access control mechanism that enables bet-
ter access security management, restricting system access to authorized users [4] [3].
With role-based access control, access decisions are based on the roles that individual
users have as part of a group or organization. The use of roles is an effective way for
developing and enforcing enterprise-specific security policies. The process of defining
roles should be based on a thorough analysis of how the organization operates and
thus how the roles would help in granting/restricting access to authorized/unautho-
rized users. There have been several attempts to propose a well defined model for
RBAC [1]. Consider the following example [6] to understand RBAC better: A hospital
has several types of employees - Doctors, nurses, tellers, CMO, etc. Now every member
of the hospital staff cannot be assigned the same privileges to the hospital resources
- a doctor may have access to personal patient files while a teller may not and so on.
One method is to use DAC throughout and specify all the necessary permissions on
every piece of hospital equipment that every member has. But this is a wastage since
on most of the resources, groups of employees (say the doctors or the nurses) tend to
have the same set of permissions.
Under the RBAC framework, users are granted membership into roles based on their
competencies and responsibilities in the organization. The operations that a user is
permitted to perform are based on the user’s role. User membership into roles can
be revoked easily and new memberships established as job assignments dictate. Role
associations can be established when new operations are instituted and old operations
can be deleted as organizational functions change and evolve. This simplifies the ad-
ministration and management of privileges. Further, roles can be updated without
updating the privileges for every user on an individual basis.
Role-based access mechanisms have been present in commercial databases [7] like In-
formix Online Dynamic Server, Oracle Enterprise Server and Sybase Adaptive Server
for quite some time now but MySQL uses only DAC and rudimentary MAC for access
control [5]. We extend the MySQL database management system to incorporate RBAC
access control policies too.

2     Role-Based Access Control
The concept of Role-Based Access Control (RBAC) has been used in applications for
decades but it has matured as a full-fledged access control model such as Mandatory
Access Control (MAC) or Discretionary Access Control (DAC) over the past decade.
RBAC policies resemble those of group access privileges in UNIX systems, privilege
groupings in databasee mangaement systems, etc. The modern concept of RBAC in-
cludes such notions in a single access control model based on role and role hierarchies,
role activation, user/role interactions. The principal motivation behind RBAC is the

ability to articulate and enforce enterprise-specific security policies and the ease with
which security management can be streamlined.
From a functional perspective, RBAC’s central notion is that of operations representing
actions associated with roles and users that are made members of roles based on their
job assignments and access privileges. These relationships are typically many-to-many
that is to say that a user may have several roles and each role can be assigned to
several users. Similarly, an action may be assigned to several roles while a role may
have several actions. The operations that are associated with roles constrain members
of the role to a specified set of actions.
An important feature of the RBAC model is the protection policies. These include
- the specification of competency to perform specific tasks; the enforcement of Least
Privilege for administrators and general users and the specification, as well as the en-
forcement, of conflicts of interest rules, which may entail duty assignment and dynamic
and static separation of duties. These policies can be enforced at the time operations
are authorized for a role, at the time users are authorized as members of a role, at the
time of role activation (like when a role is established as part of a user’s active session)
or when a user attempts to perform an operation on an object. Such decisions are
implementation specific and are made based on the functionality required.

2.1     RBAC model and Functional Specification
RBAC policies are described in terms of users, subjects, roles, role hierarchies, opera-
tions, and protected objects. To perform an operation on an object controlled under
RBAC, a user must be active in some role. Before a user can be active in a role, that
user must first have been authorized as a member of the role by a security admin-
istrator. RBAC provides administrators with the capability to place constraints on
role authorization, role activation, and operation execution. These constraints have a
variety of forms. Constraints include cardinality and mutual exclusivity rules which
can be applied on a role-by-role basis. In addition, constraints can be placed on the
authorization of an operation to a role and on operations being performed on objects
(i.e. time and location constraints). Let us overview the different components of RBAC
model in the following sections.

2.1.1    Core RBAC
Core RBAC includes the essential features of RBAC. The basic concept of RBAC is
that users are assigned to roles and permissions are assigned to roles, and users ac-
quire permissions by being members of roles. Core RBAC includes requirements that
user-role and permission-role assignment can be many-to-many. Thus the same user
can be assigned to many roles and a single role can have many users. Similarly, for
permissions, a single permission can be assigned to many roles and a single role can be
assigned to many permissions. Core RBAC includes requirements for user-role review

whereby the roles assigned to a specific user can be determined as well as users assigned
to a specific role. A similar requirement for permission-role review is imposed as an
advanced review function. Core RBAC also includes the concept of user sessions, which
allows selective activation and deactivation of roles. Finally, Core RBAC requires that
users be able to simultaneously exercise permissions of multiple roles. Thus, it is ev-
ident that Core RBAC captures the aspects of traditional group-based access control
used in UNIX.
We have added the Core RBAC functionality to MySQL RDBMS. Core RBAC em-
bodies the essential and most important feature set of RBAC models. Implementing
Core RBAC was a natural starting point - extensions to the RBAC model like role
hierarchies can be built upon our implementation easily.

2.1.2   Hierarchical RBAC
Roles can have overlapping capabilities i.e., users belonging to different roles may be
assigned common permissions. Furthermore, within many organizations there are a
number of general permissions that are performed by a large number of users. Hi-
erarchical RBAC adds requirements for supporting role hierarchies. A hierarchy is
mathematically a partial order defining a seniority relation between roles, whereby se-
nior roles acquire the permissions of their juniors, and junior roles acquire the user
membership of their seniors. The role hierarchy may be of two types:
   • General Hierarchical RBAC : In this case, there is support for an arbitrary partial
     order to serve as the role hierarchy, to include the concept of multiple inheritance
     of permissions and user membership among roles.
   • Limited Hierarchical RBAC : Some systems may impose restrictions on the role
     hierarchy. Most commonly, hierarchies are limited to simple structures such as
     trees or inverted trees.

2.1.3   Protection Policies
Apart from the hierarchy of roles and user-role assignments, the RBAC model also
encapsulates several protection policies for static and dynamic seperation of duty re-
lations to ensure conflict resolution during privilege evaluation. For example, we may
want to take care during role assignment that if a user is being assigned to multiple
roles then their privileges are mutually exclusive and don’t contradict each other. Sim-
ilarly, we might want to restrict the number of roles a user can activate per session and
so on.

2.2      Handling Sessions

3      Introduction to MySQL
MySQL is an open-source multiuser relational database management system (RDBMS).
At present, MySQL implements DAC and rudimentary MAC access control policies.

3.1      System R Authorization Model
System R [2] is the seminal work on relational database management systems on which
MySQL (and many others) are built. As a result, it is important to look at the
authorization model of System R, and then consider how MySQL deviates from this
    System R is a multi-level discretionary access control system. When a user creates
a table, they are the owner of table, and may grant privileges (access at various levels)
to other users. These privileges consist of the standard operations one may perform on
the table, such as select, insert, update, delete, etc. and an additional grant permission.
If a user has been given the grant permission (typically known as the grant option)
they may further grant any or all of his privileges on the tables other users. After
a sequence of grant operations, the result is a “directed graphs of granted privileges
originating from the table creator,” [2]. Views are handled in a similar manner, and
a user may only grant permissions on a view which they hold on the underlying table
(with the grant option).
    Revocation is slightly more complicated, and several design and implementation
decisions can be made.
    1. The first option is to revoke the privilege from just a single user. Such a revocation
       is non-cascading and non-recursive; users who obtained the privilege from the
       revoked principal will maintain their privilege.
    2. The second option is to recursively revoke the permission from all users who
       (transitively) obtained the privilege from the revoked principal. This requires the
       system to store a “grantor” field for the purpose of revocation.
    3. A third option is to recursively revoke the permission from all users who (tran-
       sitively) obtained the privilege from the revoked principal, but to return the
       grant-graph to a consistent state if the revoked principal had never been granted
       the privilege. This requires the system to store timestamp to all grant operations.
This should provide one with enough background knowledge to being to understand
how MySQL handles authorization, which we will discuss next.

3.2    Access Control in MySQL
The primary aim of the access control mechanism in MySQL or MySQL Privilege
System as it is called is to authenticate a user based on his username, host and password
and to associate the user with privileges on different databases and tables such as
Select, Update, Insert, etc. The MySQL Privilege System ensures that a user is allowed
access to inly those operations to which it has sufficient privileges. The user identity
is determined by using the username as well as the hostname. MySQL access control
involves two stages when you run a client program that connects to the server: The
server checks whether it should allow you to connect. Thereafter, assuming that you
can connect, the server checks each statement you issue to determine whether you have
sufficient privileges to perform it. If your privileges are changed (either by yourself or
someone else) while you are connected, those changes do not necessarily take effect
immediately for the next statement that you issue.
The first thing to mention when discussing MySQL authorization is its concept of the
principal. A principal in MySQL is a pair consisting of the username and originating
host of the request. This is an attempt to restrict permissions on less trusted hosts,
and allows an administrator restrict root to localhost access only. A user authenticates
using any number of methods, such as password (which may be different for each host),
SSL, and X509 certificates.
    A principal is then granted privileges, such as select, insert, update, create on
the system. When a user performs an operation, MySQL asserts that the user has
sufficient privileges to perform the operation before it is executed. If their privileges
do not dominate the required privileges, the access is denied. We state the granted
privileges must dominate the required privileges for several reasons. First, privileges
may be granted at several granularities. A user may be granted global permissions
over the system (including what are referred to as “super user” privileges), which may
be applied to any database stored within the DBMS. Second, a user may be granted
privileges at the database and host level. Within each database privileges may be
assigned at the table, and even column granularity. Access decisions may then be
made at the column level, and a user’s privileges on a column are defined by

                    Global ∨ (Database ∧ Host) ∨ T able ∨ Column                     (1)

   The privileges are stored in table in MySQL as:
   It should be noted that no access control decisions query these tables directly. The
access control tables are loaded into main memory when the system initializes and
when a user issues the flish privileges command. No grant or revoke operations will
take affect until one of these two events takes place.
   Privileges are granted to a user using the grant and revoke mechanisms from System
R, with a few exceptions.
   • MySQL is not a discretionary access control system. A user who creates a table

                               Scope          Table
                               Global         mysql.user
                               Databse        mysql.db
                               Table          mysql.tables priv
                               Column         mysql.columns priv
                               Procedures     mysql.procs priv

     Table 1: Storage Location for Privileges at Varying Granularities in MySQL

                       Field   Type         Null       Key   Default   Extra
                       User    char(16)     NO         PRI
                       Host    char(60)     NO         PRI
                       Role    char(64)     NO         PRI

                  Table 2: rbac ua: User-Role Assignment Relationship

          is not the table’s owner, and may have no privileges on the table they created.
          Consider the following listing:

                 Listing 1: Non Discretionary Access Control in MySQL
1        R V K ALL ON db . ∗ F O u s e r ;
          E O E                    R M
2        GRANT c r e a t e ON db . ∗ TO u s e r ;
3        −− Log i n as u s e r
4                                                 H R(
         CREATE TABLE db . t a b l e ( column C A 2 0 ) ) ;
5        SELECT ∗ F O db . t a b l e ;
                     R M
6        −− Access Denied

        • On revocation, MySQL will choose the simplest strategy, and will not perform
          any cascading revocation. The grant tables do contain columns for both grantor
          and timespams, and cascading revocation is in the works for a future version.

    4     Coding and Schema
    We added the following tables:

    Installation The mysql install scripts were modified to automatically create the
    rbac * tables if necessary. (See mysql create system tables)

Field                   Type            Null   Key   Default   Extra
Role                    char(64)        NO     PRI
Select priv             enum(’N’,’Y’)   NO           N
Insert priv             enum(’N’,’Y’)   NO           N
Update priv             enum(’N’,’Y’)   NO           N
Delete priv             enum(’N’,’Y’)   NO           N
Create priv             enum(’N’,’Y’)   NO           N
Drop priv               enum(’N’,’Y’)   NO           N
Reload priv             enum(’N’,’Y’)   NO           N
Shutdown priv           enum(’N’,’Y’)   NO           N
Process priv            enum(’N’,’Y’)   NO           N
File priv               enum(’N’,’Y’)   NO           N
Grant priv              enum(’N’,’Y’)   NO           N
References priv         enum(’N’,’Y’)   NO           N
Index priv              enum(’N’,’Y’)   NO           N
Alter priv              enum(’N’,’Y’)   NO           N
Show db priv            enum(’N’,’Y’)   NO           N
Super priv              enum(’N’,’Y’)   NO           N
Create tmp table priv   enum(’N’,’Y’)   NO           N
Lock tables priv        enum(’N’,’Y’)   NO           N
Execute priv            enum(’N’,’Y’)   NO           N
Repl slave priv         enum(’N’,’Y’)   NO           N
Repl client priv        enum(’N’,’Y’)   NO           N
Create view priv        enum(’N’,’Y’)   NO           N
Show view priv          enum(’N’,’Y’)   NO           N
Create routine priv     enum(’N’,’Y’)   NO           N
Alter routine priv      enum(’N’,’Y’)   NO           N
Create user priv        enum(’N’,’Y’)   NO           N

           Table 3: rbac pa: Global Role Permissions

              Field                   Type              Null   Key    Default   Extra
              Db                      char(64)          NO     PRI
              Role                    char(64)          NO     PRI
              Select priv             enum(’N’,’Y’)     NO            N
              Insert priv             enum(’N’,’Y’)     NO            N
              Update priv             enum(’N’,’Y’)     NO            N
              Delete priv             enum(’N’,’Y’)     NO            N
              Create priv             enum(’N’,’Y’)     NO            N
              Drop priv               enum(’N’,’Y’)     NO            N
              Grant priv              enum(’N’,’Y’)     NO            N
              References priv         enum(’N’,’Y’)     NO            N
              Index priv              enum(’N’,’Y’)     NO            N
              Alter priv              enum(’N’,’Y’)     NO            N
              Create tmp table priv   enum(’N’,’Y’)     NO            N
              Lock tables priv        enum(’N’,’Y’)     NO            N
              Create view priv        enum(’N’,’Y’)     NO            N
              Show view priv          enum(’N’,’Y’)     NO            N
              Create routine priv     enum(’N’,’Y’)     NO            N
              Alter routine priv      enum(’N’,’Y’)     NO            N
              Execute priv            enum(’N’,’Y’)     NO            N

                         Table 4: rbac db: Database Privilege Table

Field           Type                                                              Null    Key     Default   Extra
Db              char(64)                                                          NO      PRI
Role            char(64)                                                          NO      PRI
Table name      char(64)                                                          NO      PRI
Table priv      set(’Select’,’Insert’,’Update’,’Delete’,’Create’,’Drop’,
                ’Grant’,’References’,’Index’,’Alter’,’Create View’,’Show view’)   NO
Column priv     set(’Select’,’Insert’,’Update’,’References’)                      NO

                       Table 5: rbac tables priv: Table Privilege Table

  Field             Type                                           Null   Key   Default   Extra
  Db                char(64)                                       NO     PRI
  Role              char(64)                                       NO     PRI
  Table name        char(64)                                       NO     PRI
  Column name       char(64)                                       NO
  Column priv       set(’Select’,’Insert’,’Update’,’References’)   NO

                    Table 6: rbac columns priv: Column Privilege Table

Field            Type                                      Null   Key    Default   Extra
Db               char(64)                                  NO     PRI
Role             char(64)                                  NO     PRI
Routine name     char(64)                                  NO     PRI
Routine type     enum(’FUNCTION’,’PROCEDURE’)              NO     PRI
Proc priv        set(’Execute’,’Alter Routine’,’Grant’)    NO

                Table 7: rbac proc priv: Procedure Privilege Table

Broad access control Broader access control is handled by the user, host and db
databases. There are loaded into memory on system load by the acl init function
and reloaded after the flush privileges cal by acl reload. We have written RBAC
counterparts rbac init and rbac reload which are called upon successfully reloading
the standard MySQL tables, and load the rbac ua, rbac pa and rbac db tables. Much
like the standard tables, our tables are loaded into memory, and sorted by attributes
(db, host, role, and user), where more explicit attributes come before less explicit
    The three main tables are searched for access rights when an operation is tested
by the acl get function. For our implementation, we wrote an equivalent rbac get
function, which loops through a list of activated roles, and returns the disjunction of
all permissions belonging to that role, and the permissions belonging to the user.

Finer Access Control Finer grained access control decisions (table, column and
procedure level) are loaded by very similar methods in a function called grant init
(with [re]laods as well), to which we added a family of rbac grant * functions.
For the finer grained controls, there are methods check grant to check for table per-
missions, and store (within the tables) the structures holding the permissions at the
column level. We augmented these functions load (again, by disjunction) the permis-
sions available to all the roles, and store a list of pairs {Role,ColumnStructure} for the
column level permissions.
    When checking permission, MySQL calls a series of check functions depending on
the type of access that is requested, and the privileges still required (not inherited
from a higher level). These functions include check grant, check grant column,
check column grant in table ref, check grant routine, check grant db, , and
have been overloaded to call RBAC equivalents.
    For the SQL command show grants; MySQL has defined get table grant and
get column grants which are used in conjunction with acl get to determine all of the
privileges granted to a user. By augmenting the appropriate functions, the privileges
a user has via activated roles are also displayed.

[1] Database security-concepts, approaches, and challenges. IEEE Trans. Dependable
    Secur. Comput., 2(1):2–19, 2005. Fellow-Elisa Bertino and Fellow-Ravi Sandhu.
[2] M. M. Astrahan and M. W. Blasgen. System r.: relational approach to database
    management. ACM Trans. Database Syst., 1(2):97–137, 1976.
[3] S. Gavrila D.Ferraiolo, R.Sandhu. Proposed nist standard for role-based access
    control. In ACM Transactions On Information and System Security, pages 224–
    274, 2001.
[4] D. Ferraiolo and R. Kuhn. Role-based access controls. In 15th NIST-NCSC National
    Computer Security Conference, pages 554–563, 1992.
[5] MySQL. The mysql access privilege system.
[6] NIST. An introduction to role-based access control. 1995.
[7] C. Ramaswamy and R. Sandhu. Role-based access control features in commercial
    database management systems. In Proc. 21st NIST-NCSC National Information
    Systems Security Conference, pages 503–511, 1998.


Shared By: