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

¹ Only MySQL support has been tested.

² 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.

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.

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
SomeWeb.SomeOtherTopic
SomeWeb.UnlistedTopic

Without inheritance

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

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

Now let's say that allow_query inherits from allow_do.

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

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

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