Category : Files from Magazines
Archive   : DBMS0690.ZIP
Filename : BUTLER4

Listing 4.


* Set up array of permissions
s_per[1,1] = 'Create Database'
s_per[2,1] = 'Create Default'
s_per[3,1] = 'Create Procedure'
s_per[4,1] = 'Create Rule'
s_per[5,1] = 'Create Table'
s_per[6,1] = 'Create View'
s_per[7,1] = 'Dump Database'
s_per[8,1] = 'Dump Transaction'

* Clear row pointers for all columns so dBSQL is set to start
* at the top row of the answer table from SQL Server
call dbsql with 'clrcol 1'

* Make a corresponding move to the top of the Dbase table
* of permissions we're constructing
go top
call dbsql with 'setvar c 1 6', 'c 2 20', 'c 3 15, ,;
'c 4, 10', sql_stu
call dbsql with 'query sp_helprotect ' + lname, sql_stu,;
sql_row, sql_msg
if sql_stu > 1
do chkstu && error handling routine
endif
do while sql_stu = 1
p_obj = space (15)
p_col = space (10)

* read next row and store protection information to memvars
call dbsql with "nextrow 1 2 3", sql_stu, sql_row, sql_msg,;
p_type, p_act, p_obj
if p_type = 'Grant'
pact = .T.
else
pact = .F.
endif
if sql_stu = 1
call dbsql with "getvar 4", p_col
p_col = trim (p_col)
if p_col = 'All' &&if p-col is all we can ignore columns
p_obj = trim (p_obj)
*If object not defined, we're on a global permission
* such as "Create Procedure" that we stored in the array
if len (p_obj) = 0 .or. substr(p_obj, 1,1) = ' '
rc = 0 && set counter to 0

* loop through 8 times or until you
* match the action
do while rc < 8
rc = rc + 1
if p_act = s_per[rc,1]
store pact to s_per[rc,2],s_per[rc,3];
&& store logical
exit
endif
enddo

*If object exists, we we need to capture
*delete/insert/select/update/execute
*permissions into Dbase table
else
scan all for trim(object) = p_obj
do case
?object
case p_act = 'Delete'
replace del with pact, o_d with pact
case p_act = 'Insert'
replace ins with pact, o_i with pact
case p_act = 'Select'
replace sel with pact, o_s with pact
case p_act = 'Update'
replace upd with pact, o_u with pact
case p_act = 'Execute'
replace exe with pact, o_e with pact
endcase
endscan
endif &&end the "
else &&if the columns are not marked "ALL"
scan all for trim(object)= p_obj .and.;
trim(column)= p_col
?column
do case
case p_act = 'Select'
replace sel with pact, o_s with pact
case p_act = 'Update'
replace upd with pact, o_u with pact
endcase
endscan
endif
loop
else && if we get here that means we have an error
if sql_stu > 1
do chkstu
else
exit
endif
endif
enddo