Granting access to tables

When you create SQL tables from within Dexterity, the user who created the tables will be the only person able to access them. The same situation occurs if you create tables outside of Dexterity using a utility such as ISQL. You must grant access privileges to the tables and associated stored procedures for other users to access data in your application.

You can use a tool like ISQL to manually grant privileges to each table and its associated stored procedures, though this could be tedious for larger applications. A more automated method is to use a stored procedure to grant access privileges to a table. The SQL statements create a stored procedure that grants access privileges to the members of DEXGRP for the specified table.

To create the amAutoGrant stored procedure, copy the code shown here and paste it into a utility such as ISQL that can execute SQL statements. When you execute the SQL statements shown here, the amAutoGrant stored procedure is created. Then you can create a prototype procedure like the one shown following the SQL statements below and use the call sproc statement to call the amAutoGrant stored procedure from within your Dexterity application. Calling stored procedures is described in Stored Procedures.

/* amAutoGrant
   This procedure grants permissions on the passed table and its 
   associated zDP_* stored procedures
*/
SET QUOTED_IDENTIFIER OFF
if exists (select * from sysobjects where id = object_id('amAutoGrant'))
	drop procedure amAutoGrant
go

if exists (select * from sysobjects where id = object_id('autotemp'))
	drop table autotemp
go

create table autotemp(name char(150))
go

create procedure amAutoGrant
	@tablename char(150) output
as
set nocount on

DECLARE @command varchar(255)

/* Do the table grant*/
SELECT @command = 'grant SELECT,INSERT,DELETE,UPDATE on '+rtrim(@tablename)+' to DEXGRP'
EXEC  (@command)


/* Set up the procedure select */
DELETE FROM autotemp
SELECT @command = "insert into autotemp select name from sysobjects where name like 'zDP_"+rtrim(@tablename)+"%'"
EXEC  (@command) 

/* Declare the cursor */
DECLARE TheCursor CURSOR FOR
  select  'grant EXECUTE on '+ rtrim(name) + " to DEXGRP" from autotemp

/* Open the cursor */
OPEN TheCursor

WHILE @@FETCH_STATUS = @@FETCH_STATUS	--  That is, 'WHILE TRUE'.
BEGIN
  FETCH NEXT FROM TheCursor INTO @command

  IF @@FETCH_STATUS = -2				 --  Row has been deleted.
	CONTINUE
  IF @@FETCH_STATUS = -1				 --  All rows processed.
	BREAK

	/* Execute the select for each proc */
  EXEC (@command)  
END

/* Close and deallocate the cursor */
CLOSE TheCursor
DEALLOCATE TheCursor
GO

SET NOCOUNT off
GO

grant execute on amAutoGrant to DEXGRP
go

The following is the prototype procedure required to call this stored procedure.

sproc returns long Ret_Code;
inout string table_physical_name;

{This is the prototype procedure for the amAutoGrant stored procedure. Note that you must supply the table physical name.}

call sproc "amAutoGrant", Ret_Code, table_physical_name;

Documentation Feedback

---