INSTALLATION

   Drop all users apart from root@localhost
   create database securich;

   tar -zxf securich-<version>.tar.gz

   source db/securich.sql
   source db/data.sql

   source procedures/*.sql

WEB

   http://www.securich.com


DOCUMENTATION

###########################################################################################
#####################################  PLEASE NOTE!!  #####################################
###########################################################################################
##                                                                                       ##
## STRICTLY USE ROOT TO GRANT REVOKE OR RECONCILE.                                       ##
## PLEASE NOTE THAT ONLY ROOT SHOULD HAVE ACCESS TO THE SECURITY DATABASE AS             ##
## OTHERWISE TABLE SYNC IS IN JEOPARDY CAUSING HAVOC IN THE SECURITY TABLES.             ##
##                                                                                       ##
## GRANTING PRIVILEGES ON ANY OF MYSQL OR SECURITY DATABASES IS A SECURITY RISK.         ##
## THIS SHOULD BE AVOIDED AT ALL COSTS BUT IS STILL PERMITTED THROUGH THE STORED         ##
## PROCEDURES JUST IN CASE ANYONE NEEDS THAT FUNCTIONALITY.                              ##
##                                                                                       ##
## FURTHER INFORMATION ABOUT MYSQL PRIVILEGES CAN BE FOUND AT:                           ##
## http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_show-databases   ##
##                                                                                       ##
###########################################################################################
###########################################################################################


THE ONLY TWO STORED PROCEDURES WHICH USERS MIGHT BE ALLOWED TO CALL UPON ARE set_password AND my_privileges.
ALL OTHER STORED PROCEDURES ARE VERY SENSITIVE AND POTENTIALLY DANGEROUS.


The stored procedures currently included are:

1) check_roles();
-- Run the above in order to check which roles are available

2) check_user_list();
-- Run the above in order to obtain a list of user@host present in the system

3) create_update_role('rolename','privilege');
-- Run the above in order to create/update roles at will. Note that updates in role privileges will reflect on users having the updated role on the system.

4) check_role_privileges('rolename');
-- Shows a list of privileges belonging to a particular role

5) check_user_entries('username');
-- Checks the roles assigned to a particular user and on which database and from which host those privileges can be used

6) check_full_user_entries('username');
-- Checks the roles assigned to a particular user and on which database, table and from which host those privileges can be used

7) check_user_privileges('username','hostname','databasename','rolename');
-- Lets the administrator check the privileges a user has on that database or place 'all' instead of 'rolename' in order to have a look at all the privileges on that particular combination.

8) grant_privileges('username','hostname','databasename','tablename','tabletype','rolename','emailaddress');
-- Used to create a user with any particular combination/privileges. The tablename should be left empty if database / global level privileges are to be assigned. Note that rolename can not be substituted with all in this case. The limitations on length of each field are:
FIELD           MAX LENGTH
username      - 16
hostname      - 60
databasename  - 64
tablename     - 64
tabletype     - 16
rolename      - 60
emailaddress  - 50

Failure to abide to the limitations will cause truncation of any of the above parameters.

table type / tablename can be:
tabletype           -   tablename            -   description
all                 -                        -   all database         -   db.*                         -   used generally like `grant privilege on db.* to 'user'@'hostname';`
alltables           -                        -   all tables           -   db.tb1 db.tb2 db.tb3 etc     -   for all tables separately (used when there is a need to grant on all and revoke on a few tables)
singletable         -   tablename            -   single table         -   tb1                          -   for individual tables `grant privilege on db.tb1 to 'user'@'hostname';`
regexp              -   regular expression   -   regexp               -   tb1 2tb but not table3       -   this uses regexp ***
storedprocedure     -   procedure name       -   single procedurure   -   pr1                          -   for individual procedures
*** note that for regexp usage, if tables needed have a common prefix the best way would be to add a ^ in front of the prefix i.e. ^prefix

9) revoke_privileges('username','hostname','databasename','tablename','tabletype','rolename','terminateconnections');
-- Revokes a privilege for a particular combination of username / hostname / databasename / tablename / role. The terminateconnectionsy is there to kill all threads for a particular user if set to Y which is revoked. Should you not want to cut off the user, just substitute it with n and the user won't be able to connect next time round but current connections remain intact. - tabletype should either be table (for a table) and storedprocedure (for a stored proc).

10) set_password('username','hostname','newpassword');
-- Changes password for any user (if current user is root), otherwise changes own password if current user is not root. can change the password up to 11times in 1 day and stores the last 5 passwords which were not changed for at least 24hrs. Does not permit the new password to be the same as any of the old passwords. Resets update count if more than 24hrs passed from last first update of the day. Password must be longer than 10 characters but that is configurable.

11) reconciliation('value');
-- This list ignores root privileges as well as the privilege 'usage'. It caters both for database privileges as well as for global privileges and supplies the difference between the the securich package privileges and those actually in the mysql system. Using parameter 'list' provides the differences explaining where a particular grant is found thus implying where it is not found (MySQL meaning it is found in MySQL database and not in securich database and vice versa). The parameter 'sync' can be used to re-synchronize the two systems thus obtaining a consistent state.

12) check_privilege_users('privilege') (version 0.1.1);
-- Shows a list of users who have been granted a particular privilege
    check_privilege_users('privilege','databasename','tablename') (version 0.1.2);
-- Shows a list of users who have been granted a particular privilege on a particular table in a particular database.

13) update_databases_tables_storedprocedures_list();
-- Updates the tables and databases tables (sec_tables, sec_databases, sec_storecprocedures and their relationship table sec_db_tb and sec_db_sp) with the full list of tables / databases / storedprocedures.

14) my_privileges('dbname');
-- This script is to be executed by any user (if grant has been permitted by the dba to run it) thus letting any user know what privileges he / she has. It is not totally recommended but might be helpful in development, qa and uat environments. The user can either type in a dbname he likes or '*' to get a full detailed list of privileges he/she got on individual tables, stored procedures / databases.

15) clone_user('sourceusernanme','sourcehostname','destusername','desthostname','destemailaddress');
-- If you have a particular user in a team already set up and you just want to create other users likewise, why not just clone them? The new user will of course have a different password which is supplied to the creator upon creation.

16) rename_user('oldusername','newusername','newemailaddress');
-- Renames an old user to the new username leaving all privileges intact and changing only the password and the email address.

passwordrotate.sh
-- Cronned script which checks for users having passwords older than 30 days, alerting the users to change the password. This script only runs for users type user not users type applications (further user types can be added in the future).
