TIP DatabaseContrib is not installed on Foswiki.org.

DatabaseContrib

Provides subroutines useful in writing plugins that access a SQL database.

Summary of Contents

This contrib provides an API that come in handy when accessing a SQL database. The API implemented using two different approaches: OO and procedural. While OO is the default, procedural is been kept for compatibility only and its use is strongly discouraged.

This contrib is used among others, by Foswiki:Extensions.DBIQueryPlugin. The hope is that we can consolidate the many different database connection schemes currently in use into this single contrib.

Detailed Documentation

This plugin uses the database independent access methods in CPAN:DBI to facilitate access to the SQL database. In the following, $dbh refers to the database handle abstraction of CPAN:DBI.

Conventions

The following conventions are used across this documentation:

Notations
[ $some_argument ] Subroutine aguments enclosed in square brackets are optional
... 0 or more arguments of the same kind as the one preceding the triple-dot element
=(X Y Z)= For return values means that method would return one of the listed values.
Typical arguments or variables
$dbname Database name defined in Database Definition
$dbh Database handle as used in CPAN:DBI documentation
$dbc DatabaseContrib object
$user Foswiki user name. In most cases might be in any valid form: long, short or login name - unless otherwise specified in the documentation
$topic Foswiki topic name. May or may not include web named part unless otherwise specified in the documentation

Object Oriented API

new ( attribute => value, ... ) -> $dbc

Creates a new Foswiki::Contrib::DatabaseContrib object and initializes it. attribute/value pairs are object configuration parameters being passed over to init() method.

Returns: reference to a newly created object or undef on failure.

init( attribute => value, ... ), reinit( attribute => value, ... ) -> $dbc

Initializes a Foswiki::Contrib::DatabaseContrib object using optional attribute/value pairs.

For the moment the only attribute accepted by the method is acl_inheritance hashref.

This method does not rely on previous object state and could be used anytime throughout the object's life cycle. This is what reinit() method is here for: it's a pure alias for init() but it makes the code a little bit more readable.

Returns: undef on soft errors, $dbc on success.

Throws Error::Simple exception on hard errors.

connect( $dbname ) -> $dbh

Tries to connect to a database defined by $dbname.

Returns: a CPAN:DBI database handle on success, undef otherwise.

disconnect( [ $dbname, ... ] )

Disconnects from databases defined by the argument list. Disconnects all opened connections if the list is empty.

access_allowed( $dbname, $topic, $access_type [, $user] ) -> $entity

Verifies user's permissions of type $access_type for a database defined by $dbname in a topic defined by $topic. If $user argument is omitted then permissions are checked for the currently logged in user.

Returns: undef if $access_type is not permitted. Otherwise it's Foswiki's group or user name to which $access_type is granted.

Read more about permissions in Access Control.

connected( $dbname ) -> (0|1)

Checks if there is initialized $dbh for database defined by $dbname.

Returns: undef if database defined by $dbname is missing in database definitions; true value if there is initialized $dbh handle for the database; false otherwise.

dbh( $dbname ) -> $dbh

Returns: $dbh handle for a connected database, undef for a disconnected or non-existing one.

Procedural API

db_init ( )

Initializes the module. Must be called before any other subroutines or if content of $Foswiki::cfg{Extensions}{DatabaseContrib}{connections} has been changed. The latter is mostly related to writing test units.

db_connect ( $dbname ) -> ( $dbh )

See connect() method.

db_connected ( $dbname ) -> ( undef|0|1 )

See connected() method.

db_disconnect ( [ $dbname, ... ] )

See disconnect() method.

db_access_allowed ( $dbname, $topic, $access_type [, $user ] )

See access_allowed() method.

Database Configuration

The databases that one may connect to are defined through configure. The connection information is inserted in the DatabaseContrib section.

Example:
   message_board => {
       user => 'dbuser',
       password => 'dbpasswd',
       driver => 'mysql',
       driver_attributes => {
           mysql_unicode => 1,
       },
       codepage => 'utf8',
       database => 'message_board',
       host => 'localhost',
       init => 'SET ...',
       allow_do => {
           default => [qw(AdminGroup)],
           'Sandbox.CommonDiscussion' => [qw(Guest)],
       },
       allow_query => {
           'Sandbox.SomeInfo' => [qw(SomeGroup OrUser)],
       },
       usermap => {
           SomeGroup => {
               user => 'somedbuser',
               password => 'somedbpassword',
           },
       },
   }

This example defines a database message_board and the necessary information to access this database. Additional databases can be added, as a comma-separated list of Perl hash refs.

The following parameters can be used to specify a database. The first level key are the database names used in the above functions. Each database has its own set of parameters defined in the hash.

Key Description Default Required
database Database name on the server. none required
user Default database account name. "" optional
password Default database account password. "" optional
driver CPAN:DBI driver used to access the server, (such as Pg, mysql, sqlite).1 none required
driver_attributes Additional DBD driver specific attributes to be passed as fourth argument to DBI->connect() call.
ALERT! Attributes RaiseError, PrintError and FetchHashKeyName are used intrnally by DatabaseContrib and will be ignored.
none optinal
dsn Complete dsn string to be used when creating the connection. See your DBD driver documentation.
ALERT! With this key defined both database and driver keys are ignored.
none optional
init Initialization command to be sent to the database server just after the connection is initiated. none optional
host DB server hostname. localhost optional
codepage Client-side codepage of this connection.2 none optional
usermap Hash ref mapping Foswiki users or groups to database accounts. See Access control below. none optional
allow_* Additional topic-level access control support (see Access control below). none optional

1 Only MySQL support has been tested.

2 Only MySQL and PosgreSQL support provided for this feature. Support for other servers is not implemented yet.

Access Control

Finding out if a user is permitted to perform a database action involves three layers of control:
  1. Foswiki ACLs that control access to the topic
  2. DatabaseContrib provides, but does not enforce, a simple access control mechanism which can be used by calling code
  3. Database server side permissions implied through wiki user to DB user mapping

Foswiki ACLs are discussed in depth elsewhere. We will focus on the seconds two layers of access control.

Access checks

Callers can perform access checks using the access_allowed method. Checks are based on:
  1. The operation being performed e.g. query, do
  2. The context of which the access occurs (usually a web.topic name)
The checks are defined in the database definition.

Operations are defined by callers using a name of the form allow_*, for example, allow_query, allow_do. These operation names are mapped in the database configuration to an access control hash.

The access control hash maps individual contexts to Foswiki user and/or group names. If the current context is mapped to the user's identity, or to a group that the user belongs to, then access is granted. If the current topic name is not found in the map then the default key is used. If the context is not mapped and there is no default key, then access is denied.

It is important to remember that callers define the interpretation of both the context and the operation. DatabaseContrib simply provides the generic mechanism for checking them. Note especially that there is no concept of wildcards in operatoin and context names; they must match exactly.

There are no special restrictions on operation names other than they must differ from the other configuration keys used by DatabaseContrib. It is highly recommended that operation names have an allow_ prefix to avoid any future conflicts.

For example, an access control hash for the allow_query operation as used by the DBIQueryPlugin might looks as follows. DBIQueryPlugin uses the complete web.topic name where the database operation is being performed as the context for the access check.

allow_do => {
    'SomeWeb.SomeTopic' => [ qw( WikiUser SomeGroup ) ],
    'SomeWeb.SomeOtherTopic' => [ qw( SomeOtherGroup ScumBag ) ],
    default => [ qw( SomeMightyUser MightyGroup ) ]
}

In this case if the context is !SomeWeb.SomeTopic then !WikiUser and members of !SomeGroup are allowed access. Everyone else is denied.

If the context is !SomeWeb.SomeOtherTopic then !ScumBag and members of 'SomeOtherGroup are allowed access. All other are denied.

If the context is not !SomeWeb.SomeTopic or !SomeWeb.SomeOtherTopic then only !SomeMightyUser and members of !MightyGroup are permitted access. All others are denied.

IDEA! If there is no entry in the configuration for an operation then no access controls are applied; all users can access the operation in all contexts. Note however that user mappings still apply.

Inheritance

Access controls are often organized hierarchically, meaning that if a user is permitted one kind of access then they are implicitly permitted a more restrictive kind of access as well.

Let's add another operation to the example above:

allow_query => {
    'SomeWeb.SomeTopic' => [ qw( ScumBag ) ]
},

Without inheritance, this restricts the allow_query operation in context !SomeWeb.SomeTopic to user !ScumBag only (ignoring group membership in the example for the sake of clarity).

Can they query? !SomeMightyUser !WikiUser !ScumBag
SomeWeb.SomeTopic ALERT! DONE DONE
SomeWeb.SomeOtherTopic ALERT! ALERT! DONE
SomeWeb.UnlistedTopic DONE ALERT! ALERT!
Without inheritance
Can they query? !SomeMightyUser !WikiUser !ScumBag
SomeWeb.SomeTopic ALERT! ALERT! DONE
SomeWeb.SomeOtherTopic ALERT! ALERT! ALERT!
SomeWeb.UnlistedTopic ALERT! ALERT! ALERT!

Can they do? !SomeMightyUser !WikiUser !ScumBag
SomeWeb.SomeTopic ALERT! DONE ALERT!
SomeWeb.SomeOtherTopic ALERT! ALERT! DONE
SomeWeb.UnlistedTopic DONE ALERT! ALERT!

Now let's say that allow_query inherits from allow_do.

Can they query? !SomeMightyUser !WikiUser !ScumBag
SomeWeb.SomeTopic ALERT! DONE DONE
SomeWeb.SomeOtherTopic ALERT! ALERT! DONE
SomeWeb.UnlistedTopic DONE ALERT! ALERT!

Can they do? !SomeMightyUser !WikiUser !ScumBag
SomeWeb.SomeTopic ALERT! DONE ALERT!
SomeWeb.SomeOtherTopic ALERT! ALERT! DONE
SomeWeb.UnlistedTopic DONE ALERT! ALERT!

DatabaseContrib implements this functionality using the add_acl_inheritance method. Callers can use this to define an inheritance relation between pairs of operations. For example,
add_acl_inheritance( allow_query => 'allow_do' )
specifies the inheritance relation discussed above. Inheritance relations can allso be specified during construction, thus:
    $dbc =
      Foswiki::Contrib::DatabaseContrib->new(
        acl_inheritance => { allow_query => 'allow_do', }, )

Foswiki to database user mapping.

The lowest level of database access control involves mapping the user's wiki identity into a database server user account. This step is optional, and only really useful if the database supports multiple user accounts. The mapping can be performed for individual Foswiki user accounts, or implied through membership of a wiki group.

Mapping is done by means of the usermap key in the configuration setting (see Database definition above). If a user has an explicit entry in the usermap, then that mapping is used. Otherwise group membership is tested for each group listed in the usermap. For example,
usermap => {
   DonaldTrump => {
       user => 'potus',
       password => 'pass'
   },
   SenateOversightGroup => {
       user => 'fireguard',
       password => 'chocolate',
   }
}
If user 'DonaldTrump' attempts an operation on this database, they are mapped to DB user 'potus'. If another user attempts access and they are a member of the wiki group 'SenateOversightGroup' they are mapped to DB user 'fireguard'. As many mappings as necessary can be defined.

ALERT! there is no ordering implied other than 'check user first, then check group'. So if your user is a member of several matching groups, it is random which group mapping will be used to access the DB.

If the user is not mapped by the usermap, or no usermap is given, then the default database user will be used.

Settings

Settings are stored as preferences variables. To reference a setting write %<plugin>_<setting>%, e.g. %DATABASECONTRIB_DEBUG%

  • One line description:
    • Set SHORTDESCRIPTION = Provides subroutines useful in writing plugins that access a SQL database

Installation Instructions

Note: You do not need to install anything on the browser to use this module. The following instructions are for the administrator who installs the module on the Foswiki server.

  • For an automated installation, run the configure script and follow "Find More Extensions" in the in the Extensions section.

  • Or, follow these manual installation steps:
    • Download the ZIP file from the Plugins home (see below).
    • Unzip DatabaseContrib.zip in your twiki installation directory. Content:
      File: Description:
      data/Foswiki/DatabaseContrib.txt Contrib topic
      lib/Foswiki/Contrib/DatabaseContrib.pm Contrib Perl module
      lib/Foswiki/Contrib/DatabaseContrib/Config.spec Configuration specification
    • Set the ownership of the extracted directories and files to the webserver user.

  • Contrib configuration and testing:
    • Verify access and ownership settings for the new scripts.
    • Edit your .htaccess file to require a valid user for the savesection script (if needed).

Installation

You do not need to install anything in the browser to use this extension. The following instructions are for the administrator who installs the extension on the server.

Open configure, and open the "Extensions" section. Use "Find More Extensions" to get a list of available extensions. Select "Install".

If you have any problems, or if the extension isn't available in configure, then you can still install manually from the command-line. See http://foswiki.org/Support/ManuallyInstallingExtensions for more help.

Info

Dependencies:
NameVersionDescription
Carp>=1.33Error messages
DBI>=1.5Database independent interface
CGI>4.0CGI interface
Exporter>=0May be required for lib/Foswiki/Contrib/DatabaseContrib.pm
Foswiki::Func>=0Basic API
Time::HiRes>=0HiRes time value
Storable>=0Required for cloning data structures
Clone>=0Required for fast cloning of simple structures
Change History:  
1.0.0 (XX Mmm 20XX): Initial version

PackageForm edit

Author VadimBelman
Version v1.03
Release 24 March 2017
Description Provides subroutines useful in writing plugins that access a SQL database.
Copyright © %$CREATEDYEAR%, VadimBelman, All Rights Reserved
License GPL (GNU General Public License)
Home http://foswiki.org/Extensions/DatabaseContrib
Support http://foswiki.org/Support/DatabaseContrib
Repository https://github.com/foswiki/DatabaseContrib
ExtensionClassification Data and Files
ExtensionType ContribPackage
Compatibility
IncompatibleWith
ImageUrl
DemoUrl http://
SupportUrl DatabaseContrib
ModificationPolicy PleaseFeelFreeToModify
Topic attachments
I Attachment Action Size Date Who Comment
DatabaseContrib.md5md5 DatabaseContrib.md5 manage 168 bytes 24 Mar 2017 - 14:09 CrawfordCurrie  
DatabaseContrib.sha1sha1 DatabaseContrib.sha1 manage 192 bytes 24 Mar 2017 - 14:09 CrawfordCurrie  
DatabaseContrib.tgztgz DatabaseContrib.tgz manage 13 K 24 Mar 2017 - 14:08 CrawfordCurrie  
DatabaseContrib.zipzip DatabaseContrib.zip manage 16 K 24 Mar 2017 - 14:08 CrawfordCurrie  
DatabaseContrib_installerEXT DatabaseContrib_installer manage 4 K 24 Mar 2017 - 14:08 CrawfordCurrie  
Topic revision: r5 - 24 Mar 2017, CrawfordCurrie - This page was cached on 28 Mar 2017 - 14:23.

The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License