/****************************************************************************
** (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.sqc
**
** SAMPLE: How to grant, display, and revoke authorities at database level
**
** DB2 API USED:
** sqluadau -- Get Authorizations
**
** SQL STATEMENTS USED:
** GRANT (Database Authorities)
** SELECT INTO
** REVOKE (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 information on DB2 APIs, see the Administrative API Reference.
**
** For the latest information on programming, building, and running DB2
** applications, visit the DB2 Information Center:
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#include "utilemb.h"
int DbAuthGrant(void);
int DbAuthForAnyUserOrGroupDisplay(void);
int DbAuthForCurrentUserDisplay(void);
int DbAuthRevoke(void);
/* support function */
char *authStrVal(short);
EXEC SQL BEGIN DECLARE SECTION;
char granteetype[2];
char dbadmauth[2];
char createtabauth[2];
char bindaddauth[2];
char connectauth[2];
char nofenceauth[2];
char implschemaauth[2];
char loadauth[2];
EXEC SQL END DECLARE SECTION;
int main(int argc, char *argv[])
{
int rc = 0;
char dbAlias[SQL_ALIAS_SZ + 1];
char user[USERID_SZ + 1];
char pswd[PSWD_SZ + 1];
/* check the command line arguments */
rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd);
if (rc != 0)
{
return rc;
}
printf("\nTHIS SAMPLE SHOWS "
"HOW TO GRANT/DISPLAY/REVOKE AUTHORITIES AT DATABASE LEVEL.\n");
/* connect to the database */
rc = DbConn(dbAlias, user, pswd);
if (rc != 0)
{
return rc;
}
rc = DbAuthGrant();
rc = DbAuthForAnyUserOrGroupDisplay();
rc = DbAuthForCurrentUserDisplay();
rc = DbAuthRevoke();
/* disconnect from the database */
rc = DbDisconn(dbAlias);
if (rc != 0)
{
return rc;
}
return 0;
} /* end main */
char *authStrVal(short authShortVal)
{
if (authShortVal == 1)
{
return ("YES");
}
else
{
return ("NO");
}
} /* authStrVal */
int DbAuthGrant(void)
{
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" GRANT (Database Authorities)\n");
printf(" COMMIT\n");
printf("TO GRANT AUTHORITIES AT DATABASE LEVEL.\n");
/* grant user authorities at database level */
printf("\n GRANT CONNECT, CREATETAB, BINDADD ON DATABASE");
printf(" TO USER user1\n");
EXEC SQL GRANT CONNECT, CREATETAB, BINDADD ON DATABASE TO USER user1;
EMB_SQL_CHECK("user authorities at db. level -- grant");
printf(" COMMIT\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
return 0;
} /* DbAuthGrant */
int DbAuthForAnyUserOrGroupDisplay(void)
{
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENT:\n");
printf(" SELECT INTO\n");
printf("TO DISPLAY AUTHORITIES FOR ANY USER AT DATABASE LEVEL.\n");
printf("\n SELECT granteetype, dbadmauth, createtabauth, bindaddauth,\n"
" connectauth, nofenceauth, implschemaauth, loadauth\n"
" FROM syscat.dbauth\n"
" WHERE grantee = 'USER1'\n");
EXEC SQL SELECT granteetype, dbadmauth, createtabauth, bindaddauth,
connectauth, nofenceauth, implschemaauth, loadauth
INTO :granteetype, :dbadmauth, :createtabauth, :bindaddauth,
:connectauth, :nofenceauth, :implschemaauth, :loadauth
FROM syscat.dbauth
WHERE grantee = 'USER1';
EMB_SQL_CHECK("user authorities at database level -- get");
printf("\n Grantee Type = %c\n", granteetype[0]);
printf(" DBADM auth. = %c\n", dbadmauth[0]);
printf(" CREATETAB auth. = %c\n", createtabauth[0]);
printf(" BINDADD auth. = %c\n", bindaddauth[0]);
printf(" CONNECT auth. = %c\n", connectauth[0]);
printf(" NO_FENCE auth. = %c\n", nofenceauth[0]);
printf(" IMPL_SCHEMA auth. = %c\n", implschemaauth[0]);
printf(" LOAD auth. = %c\n", loadauth[0]);
return 0;
} /* DbAuthForAnyUserOrGroupDisplay */
int DbAuthForCurrentUserDisplay(void)
{
struct sqlca sqlca;
struct sql_authorizations currentUserAuthorities;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE DB2 API:\n");
printf(" sqluadau -- Get Authorizations\n");
printf("TO DISPLAY CURRENT USER AUTHORITIES AT DATABASE LEVEL:\n");
currentUserAuthorities.sql_authorizations_len = SQL_AUTHORIZATION_SIZE;
/* get current user authorities */
sqluadau(¤tUserAuthorities, &sqlca);
DB2_API_CHECK("current user authorities -- get");
printf("\n User DBADM authority : %s\n",
authStrVal(currentUserAuthorities.sql_dbadm_auth));
printf(" User CREATETAB authority : %s\n",
authStrVal(currentUserAuthorities.sql_createtab_auth));
printf(" User BINDADD authority : %s\n",
authStrVal(currentUserAuthorities.sql_bindadd_auth));
printf(" User CONNECT authority : %s\n",
authStrVal(currentUserAuthorities.sql_connect_auth));
printf(" User CREATE_NOT_FENC authority : %s\n",
authStrVal(currentUserAuthorities.sql_create_not_fenc_auth));
printf(" User IMPLICIT_SCHEMA authority : %s\n",
authStrVal(currentUserAuthorities.sql_implicit_schema_auth));
printf(" User LOAD authority : %s\n",
authStrVal(currentUserAuthorities.sql_load_auth));
printf("\n Group DBADM authority : %s\n",
authStrVal(currentUserAuthorities.sql_dbadm_grp_auth));
printf(" Group CREATETAB authority : %s\n",
authStrVal(currentUserAuthorities.sql_createtab_grp_auth));
printf(" Group BINDADD authority : %s\n",
authStrVal(currentUserAuthorities.sql_bindadd_grp_auth));
printf(" Group CONNECT authority : %s\n",
authStrVal(currentUserAuthorities.sql_connect_grp_auth));
printf(" Group CREATE_NOT_FENC authority: %s\n",
authStrVal(currentUserAuthorities.sql_create_not_fenc_grp_auth));
printf(" Group IMPLICIT_SCHEMA authority: %s\n",
authStrVal(currentUserAuthorities.sql_implicit_schema_grp_auth));
printf(" Group LOAD authority : %s\n",
authStrVal(currentUserAuthorities.sql_load_grp_auth));
return 0;
} /* DbAuthForCurrentUserDisplay */
int DbAuthRevoke(void)
{
struct sqlca sqlca;
printf("\n-----------------------------------------------------------");
printf("\nUSE THE SQL STATEMENTS:\n");
printf(" REVOKE (Database Authorities)\n");
printf(" COMMIT\n");
printf("TO REVOKE AUTHORITIES AT DATABASE LEVEL.\n");
/* revoke user authorities at database level */
printf("\n REVOKE CONNECT, CREATETAB, BINDADD ON DATABASE"
" FROM USER user1\n");
EXEC SQL REVOKE CONNECT, CREATETAB, BINDADD ON DATABASE FROM user1;
EMB_SQL_CHECK("user authorities at db. level -- revoke");
printf(" COMMIT\n");
EXEC SQL COMMIT;
EMB_SQL_CHECK("transaction -- commit");
return 0;
} /* DbAuthRevoke */