***********************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM, for the purpose of
** assisting you in the development of your applications.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
***********************************************************************
**
** SOURCE FILE NAME: dbauth.sqb
**
** SAMPLE: How to grant and display authorities on a database
**
** SQL STATEMENTS USED:
** BEGIN DECLARE SECTION
** END DECLARE SECTION
** CONNECT
** GRANT (Database Authorities)
**
**
***********************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, compiling, and running
** DB2 applications, visit the DB2 Information Center:
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
***********************************************************************
Identification Division.
Program-Id. "dbauth".
******************************************************************
* DATA DIVISION *
******************************************************************
DATA DIVISION.
******************* Working-Storage ***************************
Working-Storage Section.
copy "sqlenv.cbl".
copy "sql.cbl".
copy "sqlca.cbl".
* ------ general variables ------------------------------------
77 errloc pic x(80).
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 dbalias.
49 dbalias-len pic s9(4) comp-5 value 0.
49 dbalias-buf pic x(9) value " ".
01 userid.
49 userid-len pic s9(4) comp-5 value 0.
49 userid-buf pic x(128) value " ".
01 pswd.
49 pswd-len pic s9(4) comp-5 value 0.
49 pswd-buf pic x(19).
EXEC SQL END DECLARE SECTION END-EXEC.
******************************************************************
* PROCEDURE DIVISION *
******************************************************************
PROCEDURE DIVISION.
******************* Main *************************************
Main Section.
display " ".
display "DATABASES: HOW TO GRANT AND DISPLAY AUTHORITIES".
display " AT DATABASE LEVEL".
display " ".
* ----------------------------------------------------------------
display "Enter database alias( 'sample' by default): "
with no advancing.
accept dbalias-buf.
if dbalias-buf = spaces
move "sample" to dbalias-buf.
inspect dbalias-buf
tallying dbalias-len for characters before initial " ".
display "Enter user id( current by default) : "
with no advancing.
accept userid-buf.
inspect userid-buf
tallying userid-len for characters before initial " ".
if userid-buf not equal spaces
display "Enter password : "
with no advancing
accept pswd-buf.
inspect pswd-buf
tallying pswd-len for characters before initial " ".
display " ".
* ----------------------------------------------------------------
if userid-buf = spaces
EXEC SQL CONNECT TO :dbalias END-EXEC
else
EXEC SQL
CONNECT TO :dbalias USER :userid USING :pswd
END-EXEC.
* ----------------------------------------------------------------
perform Db-Auth-Grant.
* ----------------------------------------------------------------
End-Main. stop run.
******************** Db-Auth-Grant *****************************
Db-Auth-Grant Section.
display "USE THE SQL STATEMENTS:".
display "-GRANT (Database Authorities)".
display "-COMMIT".
display "TO GRANT AUTHORITIES AT DATABASE LEVEL.".
display " ".
display " GRANT CONNECT, CREATETAB, BINDADD".
display " ON DATABASE".
display " TO USER user1".
EXEC SQL
GRANT CONNECT, CREATETAB, BINDADD
ON DATABASE
TO USER user1
END-EXEC.
move "user authorities at db. level -- grant" to errloc.
call "checkerr" using SQLCA errloc.
if sqlcode is less than 0 go to End-Db-Auth-Grant.
End-Db-Auth-Grant.