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 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 matters only and it's 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.

More about permissions read 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 the configure script. 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 for an action might be somewhat tricky as there're three layers of control are involved:

  1. On the top level Foswiki checks user's permissions to read or modify a topic. This layer we better consider when deal with a plugin like DBIQueryPlugin. Still, we mention it here as it's a crucial part of the whole security issue.
  2. DatabaseContrib provides (but doesn't use) the middle layer check mechanism which can be used by a plugin or any other Perl code.
  3. Finally, the lowest layer is database server side permissions implied thru Foswiki user to DB user mappings mentionted above.

Foswiki to database user mapping.

Mapping of Foswiki users into database server user accounts is done by means of the usermap key in the configuration setting (see Database definition above). The check is done using the following rules:

  1. Check if Foswiki user has an enty in usermap.
  2. Check if Foswiki user is a member of a group that has an entry in usermap.
  3. Use user and password keys of the database definition.

db_connect fails if no valid mapping is found.

Access checks.

Access checks are performed by access_allowed method. Checks are based upon special keys in a database definition. The keys are mapping topics into Foswiki users and groups. A key is related to a kind of action allowed for a user if topic maps into this user or into a group this user belongs to. Key names are not predefined and their meanings are specified by a plugin performing an access check.

For example, DBIQueryPlugin relies upon allow_do and allow_query keys to determine if %DBI_DO% and %DBI_QUERY% are respectively accessible by a user. TIP Read DBIQueryPlugin documention to find out more about access checks.

There are no special restrictions on access key names though except that they mist differ from the standard keys used by DatabaseContrib. Though it is highly recommended that name shall be prepended with allow_ prefix as this way it will never get into a confilict with possible future development of this contrib.

An access rule is a hash of the following form:

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

In simple words it means that WikiUser and memebers of SomeGroup are allowed to do something within topic !SomeWeb.SomeTopic. Topic names has to be in their full form, including web name. No variable or macros expansion is done for the purpose of better readability and consequently for better control over permissions.

Instead of a topic name special key default could be used to define permissions to topics not individually listed for a paticular allow_ key. In the last example it means that WikiUser is allowed for SomeWeb.SomeTopic but not for SomeWeb.SomeOtherTopic and not for any other topic of this Foswiki unless he is a member of MightGroup.

ALERT! It is important to remember that default key is used only and only when no individual entry for a topic is present in an allow_ hash. What it means is that neither SomeMightyUser nor members of MightyGroup are allowed for SomeWeb.SomeOtherTopic unless they're members of SomeGroup. In real life I would suggest making life simplier for AdminGroup by including it into the default key. But it still has to be mentioned for any other topic where memers of the group require access.

Permissions inheritance.

Quite often permissions are organized hierarchically meaning that if a user allowed for one kind of access then he is allowed for more restrictive kind of access just by definition. For example, DBIQueryPlugin postulates that users having allow_do permission doesn't need to be specifically listed in allow_query for the same topic.

db_access_allowed() subroutine implements this functionality by defining permissions inheritance. Let's add one more allow_ key to the example above:

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

Assume that allow_somemore inherits from allow_something. Then the following table displays what users a granted with allow_somemore for a topic.

ALERT! Note: We suppose that SomeMightUser is a member of MightyGroup only; WikiUser isn a member of SomeGroup only. ScumBag isn't a member of any group.

!SomeWeb.SomeTopic ALERT! DONE DONE
SomeWeb.SomeOtherTopic ALERT! ALERT! DONE
SomeWeb.UnlistedTopic DONE ALERT! ALERT!
SomeMightUser WikiUser ScumBag

Technically, inheritance is implemented in a very simple way: if there is no match for a user in an allow_ key then db_access_allowed is been called against the key it inherits from.

ALERT! Note: For the moment there is no way to define custom inheritance. It is hardcoded to implement DBIQueryPlugin allow_query MOVED TO... allow_do relations. Though it's only a matter to find out which implementations is better.

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.02
Release 1.02
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
NewsFlash
Topic attachments
I Attachment Action Size Date Who Comment
DatabaseContrib.md5md5 DatabaseContrib.md5 manage 168 bytes 15 Oct 2015 - 22:11 VadimBelman  
DatabaseContrib.sha1sha1 DatabaseContrib.sha1 manage 192 bytes 15 Oct 2015 - 22:11 VadimBelman  
DatabaseContrib.tgztgz DatabaseContrib.tgz manage 13 K 15 Oct 2015 - 22:10 VadimBelman  
DatabaseContrib.zipzip DatabaseContrib.zip manage 16 K 15 Oct 2015 - 22:10 VadimBelman  
DatabaseContrib_installerEXT DatabaseContrib_installer manage 4 K 15 Oct 2015 - 22:10 VadimBelman  
Topic revision: r3 - 15 Oct 2015, VadimBelman - This page was cached on 30 Jun 2016 - 03:29.

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