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 or array 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.

ALERT! Context and user/group mapping allows for use of '*' as a wildcard. There is no limits as to how many wildcards are allowed or where they could be placed.

Any*Context
AnyGivenContext, AnyOtherContext
*aGroup
SantaGroup, KindaGroup
S*U*r
StrangeUser, SuperUberDriver

If necessary, the wildcard itself can be escaped with '\' (backslash). Actually, '\' escapes any following character and expands into the char. It means that:

  • \* => *
  • \\ => * \a => a

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.

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 ) ],
    'ATasksWeb.Task*' => [ qw( MightyGroup Mighty* Admin*Group ) ],
    default => [ qw( SomeMightyUser MightyGroup ) ]
}

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

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

If the context is !ATasksWeb.Task00001 or any other topic in ATasksWeb which name starts with Task prefix then members of MightyGroup are granted access; any user whose name starts with Mighty, or belongs to groups either prefixed with Mighty or starting with Admin and ending with Group.

If the context is not !SomeWeb.SomeTopic or !SomeWeb.SomeOtherTopic then only !SomeMightyUser and members of !MightyGroup are permitted access. All others are denied. Removal of the 'default' key would result in denying access to all.

IDEA! If there is no entry in the configuration for an operation then *no access controls are applied*; all users are granted access to an 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 he is 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.

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

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. To overcome this situation the usermap key could be defined as an array:

usermap => [
   FrankGroup => {
       user => 'guest',
       password => 'doesntmatter',
   },
   DonaldTrump => {
       user => 'potus',
       password => 'pass'
   },
   SenateOversightGroup => {
       user => 'fireguard',
       password => 'chocolate',
   }
]

In this case there is no 'user first' ordering implied. Instead, the entries are checked as they're defined in the list. So, if 'DonaldTrump' happens to be a member of 'FrankGroup' then he is mapped into the 'guest' DB user. If he is not then DB user 'potus' would serve the purpose.

Basically, the array form of the key is preferred over the hash variant as it is more specific about the credentials used for a WikiUser. Though one might correctly assume that the hash version would be somewhat faster over the array but the difference could only be seen on really huge lists.

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. "Extensions Operation and Maintenance" Tab -> "Install, Update or Remove extensions" Tab. Click the "Search for Extensions" button. Enter part of the extension name or description and press search. Select the desired extension(s) and click install. If an extension is already installed, it will not show up in the search results.

You can also install from the shell by running the extension installer as the web server user: (Be sure to run as the webserver user, not as root!)
cd /path/to/foswiki
perl tools/extension_installer <NameOfExtension> 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 https://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 1.04.01
Release 10 Jan 2018
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
I Attachment Action Size Date Who Comment
DatabaseContrib.md5md5 DatabaseContrib.md5 manage 168 bytes 11 Jan 2018 - 03:08 VadimBelman  
DatabaseContrib.sha1sha1 DatabaseContrib.sha1 manage 192 bytes 11 Jan 2018 - 03:08 VadimBelman  
DatabaseContrib.tgztgz DatabaseContrib.tgz manage 15 K 11 Jan 2018 - 03:07 VadimBelman  
DatabaseContrib.zipzip DatabaseContrib.zip manage 18 K 11 Jan 2018 - 03:07 VadimBelman  
DatabaseContrib_installerEXT DatabaseContrib_installer manage 4 K 11 Jan 2018 - 03:07 VadimBelman  
Topic revision: r7 - 11 Jan 2018, VadimBelman
The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy