[SGVLUG] Database security for "sql" databases

[Emerson, Tom]

> -----Original Message-----
> Behalf Of John Riehl
> 
> Emerson, Tom wrote:
> > I'm trying to determine and/or validate that the proper 
> > permissions have been "granted" 
> 
> It depends on the database.

heh heh heh -- that much I know :)

> usually, there is a command to show all the information (e.g. show). 
> Check your manual.

FWIW: here is the output of the "help" command that lists all of the commands available.

isql=> help;

HELP is available for the following:

===== SQL Statements =====
Commands marked by * can NOT be executed from ISQL.

   ADD DBEFILE                EXECUTE IMMEDIATE
   ADD TO GROUP               EXECUTE PROCEDURE
  *ADVANCE                   *FETCH
   ALTER DBEFILE             *GOTO
   ALTER TABLE                GRANT
  *BEGIN                     *IF
   BEGIN ARCHIVE             *INCLUDE
  *BEGIN DECLARE SECTION      INSERT
   BEGIN WORK                 LOCK TABLE
   CHECKPOINT                 LOG COMMENT
  *CLOSE                     *OPEN
   COMMIT ARCHIVE             PREPARE
   COMMIT WORK               *PRINT
   CONNECT                    RAISE ERROR
   CREATE DBEFILE            *REFETCH
   CREATE DBEFILESET          RELEASE
   CREATE GROUP               REMOVE DBEFILE
   CREATE INDEX               REMOVE FROM GROUP
   CREATE PARTITION           RESET
   CREATE PROCEDURE          *RETURN
   CREATE RULE                REVOKE
   CREATE SCHEMA              ROLLBACK WORK
   CREATE TABLE               SAVEPOINT
   CREATE TEMPSPACE           SELECT
   CREATE VIEW                SET CONNECTION
  *DECLARE                    SET CONSTRAINTS
  *DECLARE CURSOR             SET DEFAULT DBEFILESET
   DELETE                     SET DML ATOMICITY
  *DELETE WHERE CURRENT       SET MULTITRANSACTION
  *DESCRIBE                   SET PRINTRULES
   DISABLE AUDIT LOGGING      SET SESSION
   DISABLE RULES              SET TRANSACTION
   DISCONNECT                 SET USER TIMEOUT
   DROP DBEFILE               SETOPT
   DROP DBEFILESET           *SQLEXPLAIN
   DROP GROUP                 START DBE
   DROP INDEX                 START DBE NEW
   DROP MODULE                START DBE NEWLOG
   DROP PARTITION             STOP DBE
   DROP PROCEDURE             TERMINATE USER
   DROP RULE                  TRANSFER OWNERSHIP
   DROP TABLE                 TRUNCATE TABLE
   DROP TEMPSPACE             UPDATE
   DROP VIEW                  UPDATE STATISTICS
   ENABLE AUDIT LOGGING      *UPDATE WHERE CURRENT
   ENABLE RULES               VALIDATE
  *END DECLARE SECTION       *WHENEVER
   EXECUTE                   *WHILE

                            
===== ISQL Commands =====

   CHANGE           GENPLAN          LIST HISTORY     SET
   DO               HELP             LIST INSTALL     SQLGEN
   EDIT             HOLD             LIST SET         SQLUTIL
   END              INFO             LOAD             START
   ERASE            INPUT            RECALL           STORE
   EXIT             INSTALL          REDO             SYSTEM
   EXTRACT          LIST FILE        RENAME           UNLOAD

(Enter name of command) isql HELP=> //

isql=> 
> 
> almost all sql databases store this information in its own internal 
> tables.  the names of these internal tables vary from db to 
> db. select * from table.....


This is also known as "meta" data, or data/tables that describe the structure of the data rather than (being) the data itself.  After a bit of digging, I *think* I've found the table, but "even as the creator", I don't seem to have "select" access to the particular table:

isql=> SELECT * FROM HPRDBSS.TABAUTH;
User MGR at COREDEV does not have SELECT authority on HPRDBSS.TABAUTH.  
(DBERR 2301)
isql=> 

there are, however, some "views" called CATALOG.<whatever> and SYSTEM.<likewise> that I think are views within the HPRDBSS tables  (this figures in later -- see below)
 
> all sql databases that I know allow a admin to dump out the database 
> into a stream of sql statements that recreates it.  (e.g. mysqldump 
> --all-databases ).  if you pipe that to a file, then browse it, you 
> should be able to discern the table that is used to store 
> privledges, as well as all the data in it.

Actually, the system DOES have a utility for recreating the STRUCTURE, but not one that "dumps the data" as SQL statements to re-load the data (the files that house the data are typically stored and restored to/from "tape" as complete files, not discrete data)  However, as part of the structure, the "authority" information is exported as GRANT/REVOKE statements as such:

>> gen tabauth

ALLBASE/SQL Command Generator for Table Authority

Schema File Name or '//' to STOP command >> 

Please enter Owner Names.  Type @ for all, ? for a list of  
Owner Names, or RETURN to quit.

Owner Name >> tomsdb
Do you wish to specify Table Names for each Owner (n/y)? y

Enter Table Names for Owner TOMSDB 
Type @ for all, ? for a list of Table Names, or RETURN to quit.

Table Name for Owner TOMSDB >> users


GRANT SELECT
  ON TOMSDB.USERS TO TOMSDB_10
  BY MGR at COREDEV;

GRANT SELECT,INSERT,DELETE,UPDATE
  ON TOMSDB.USERS TO TOMSDB_20
  BY MGR at COREDEV;

GRANT SELECT,INSERT,DELETE,UPDATE
  ON TOMSDB.USERS TO TOMSDB_64
  BY MGR at COREDEV;

REVOKE ALL
  ON TOMSDB.USERS
  FROM PUBLIC;

==================

This is all well and good, but it turns out what I need to verify is access to the meta-data tables in the first place!  Perhaps a *tiny* bit more background will make sense (and bore the rest of you -- sorry...)

Basically, from my "front end" programs I'm opening this database as an "odbc" data source.  When configuring the data source on the SERVER side, I need to specify what "tables" the front-end clients can see or access.  In this DB environment, there are some 400+ tables (and views), but the user only needs to access a handful of them.  (relatively speaking -- I think the actual number of tables is still over 100)  Also, rather than explicitly name each one, the system allows the DBA to specify an "@" to mean "all tables", and, out of laziness or lack of understanding, "that's the way we've always done it".

The downside is that when you open a connection to the database, the server looks at this "@" and (internally?) gathers information about each and every table that the user can access -- this takes a noticable amount of time on a loaded server (10 seconds or more...) -- and the "optimization" that the maker of the ODBC server suggested was to explicitly note each table in the server config file.

When I do that, my front-end program chokes on a "select * from..." statement because it has to retrieve the column names from from one of these "meta" tables [this happens actually at the server side -- because the server does not have the authority, by it's own definition of the database, it just falls over...]  when an "@" is used, these "meta" tables are in the list and everything works [so it isn't entirely laziness/misunderstanding...]  Furthermore, even if these meta tables are explicitly defined, without the "@" access it still falls over, so there is still some sort of disconnect between theory and reality... ;)