Home » Databases » Sybase » ASE » Ed Barlow Procedures: sp__groupprotect updated to show the group name properly for SAP Sybase ASE 15 and higher

Ed Barlow Procedures: sp__groupprotect updated to show the group name properly for SAP Sybase ASE 15 and higher

Ed Barlow createdSAP Sybase the sp__groupprotect stored procedure for reporting group permissions in Sybase ASE as part of the excellent Extended Stored Procedures by Ed Barlow (AKA the Ed Barlow Stored Procedures).

The problem is that the group name is truncated often making it difficult to determine exactly which group a particular line applies to:

 type grp             tot    sel    upd    del    ins    rev    exe
 ---- --------------- ------ ------ ------ ------ ------ ------ ------
 D    application_gro 253    0      0      0      0      0      0
 D    dtm_tm_role     253    0      0      0      0      0      0
 D    ha_role         253    0      0      0      0      0      0
 D    webservices_rol 253    0      0      0      0      0      0

I’ve updated the stored procedure to show the entire group name:

 type grp                tot    sel    upd    del    ins    rev    exe
 ---- ------------------ ------ ------ ------ ------ ------ ------ ------
 D    application_group  253    0      0      0      0      0      0
 D    dtm_tm_role        253    0      0      0      0      0      0
 D    ha_role            253    0      0      0      0      0      0
 D    webservices_role   253    0      0      0      0      0      0
$ diff groupprotect.sql groupprotect.15
23c23
< create procedure sp__groupprotect( @dont_format char(1) = NULL )
---
> create procedure dbo.sp__groupprotect( @dont_format char(1) = NULL )
26a27,36
> --------------------------------------------------------------------------------------------------
> -- Vers|   Date   |      Who           | DA | Description
> -------+----------+--------------------+----+-----------------------------------------------------
> -- 1.1 |11/18/2013|  Jason Froebe      |    | Fix formatting
> -- 1.0 |          |  Edward M Barlow   |    | Stored procedure reporting group permissions
> -------+----------+--------------------+----+-----------------------------------------------------
>
> declare @max_name_size varchar(3)
> declare @exec_str varchar(2000)
>
110,112c120,125
< select type,grp=convert(char(15),group_name),tot=convert(char(6),total),sel=convert(char(6),s),upd=convert(char(6),u),del=convert(char(6),d),ins=convert(char(6),i),rev=convert(char(6),r),exe=convert(char(6),e)
<         from #objects
<         order by type,group_name
---
>         select @max_name_size = convert(varchar(3), max(char_length(group_name))) from #objects
>         select @exec_str =
>             'select type,grp=convert(char(' + @max_name_size + '),group_name),tot=convert(char(6),total),sel=convert(char(6),s),upd=convert(char(6),u),del=convert(char(6),d),ins=convert(char(6),i),rev=convert(char(6),r),exe=convert(char(6),e)
>             from #objects
>             order by type,group_name'
>         exec (@exec_str)

Here’s the full stored procedure code:

/* Procedure copyright(c) 1995 by Edward M Barlow */

/******************************************************************************
**
** Name        : sp__groupprotect.sql
**
**   permissions by object type / user group vs sel/upd/ins/del
**
******************************************************************************/
:r database
go
:r dumpdb
go

if exists (select * from sysobjects
           where  name = "sp__groupprotect"
           and    type = "P")
begin
   drop proc sp__groupprotect
end
go

create procedure dbo.sp__groupprotect( @dont_format char(1) = NULL )
as
set nocount on

--------------------------------------------------------------------------------------------------
-- Vers|   Date   |      Who           | DA | Description
-------+----------+--------------------+----+-----------------------------------------------------
-- 1.1 |11/18/2013|  Jason Froebe      |    | Fix formatting
-- 1.0 |          |  Edward M Barlow   |    | Stored procedure reporting group permissions
-------+----------+--------------------+----+-----------------------------------------------------

declare @max_name_size varchar(3)
declare @exec_str varchar(2000)

select distinct type,uid=sysusers.uid,group_name=sysusers.name,total=0,s=0,u=0,d=0,i=0,r=0,e=0
into   #objects
from   sysusers,   sysobjects
where  sysusers.uid=sysusers.gid
and    sysobjects.uid=1
and    ( sysusers.uid>=16390 or sysusers.uid < 16000 )

select distinct action,id,uid,protecttype,type="  "
into  #p
from  sysprotects p

update  #p
set     type=o.type
from    sysobjects o
where   o.id=#p.id
and     o.uid=1

update #objects set total=(select count(*) from sysobjects o
                                where o.type=n.type
                                and    o.uid=1)
from #objects n

update  #objects
set     s=(select count(*)
                from    #p
                where   #p.action=193
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     u=(select count(*)
                from    #p
                where   #p.action=197
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     d=(select count(*)
                from    #p
                where   #p.action=196
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     i=(select count(*)
                from    #p
                where   #p.action=195
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     r=(select count(*)
                from    #p
                where   #p.protecttype=2
                and     #p.uid = #objects.uid
                and     #p.type = #objects.type
)
from #objects

update  #objects
set     e=(select count(*)
                from    #p
                where   #p.action=224
                and     #p.uid = #objects.uid
                and     #p.protecttype<=1
                and     #p.type = #objects.type
)
from #objects

if @dont_format is null
begin
        select @max_name_size = convert(varchar(3), max(char_length(group_name))) from #objects
        select @exec_str =
            'select type,grp=convert(char(' + @max_name_size + '),group_name),tot=convert(char(6),total),sel=convert(char(6),s),upd=convert(char(6),u),del=convert(char(6),d),ins=convert(char(6),i),rev=convert(char(6),r),exe=convert(char(6),e)
            from #objects
            order by type,group_name'
        exec (@exec_str)
end
else
begin
        select type,grp=group_name,tot=convert(char(6),total),sel=convert(char(6),s),upd=convert(char(6),u),del=convert(char(6),d),ins=convert(char(6),i),rev=convert(char(6),r),exe=convert(char(6),e)
        from #objects
        order by type,group_name
end

return (0)
go
grant execute on sp__groupprotect to public
go

groupprotect

Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect