Ed Barlow’s sp__helprotect updated for SAP Sybase ASE 15.7 Solved

I needed to copy the permissions from one database to another but the sp__helprotect stored procedure in Ed Barlow’s Extended System Stored Procedure Library for Sybase and Sql Server wasn’t producing output with “GRANT” and “REVOKE”. It also had problems with DBCC privileges. Here you go:

sp__helprotect.sql

/* Procedure copyright(c) 1995 by Edward M Barlow */
/* Updated for v15.7 by Jason Froebe */

/******************************************************************************
**
** Name : sp__helpprotect.sql
**
******************************************************************************/
if exists (select * from sysobjects
where name = “sp__helprotect”
and type = “P”)
begin
drop proc sp__helprotect
end
go

create procedure sp__helprotect
@parameter varchar(30) = NULL /* name of object or user to check */,
@do_system_tables char(1) = null, /* if not null will include system tbls */
@dont_format char(1) = null,
@groups_only char(1) = null
as
declare @type char(2), @uid int, @msg varchar(255), @objid int

if @parameter is NULL
select @objid=null
else
select @objid = object_id(@parameter)

/* define our table */
select id,uid,action,protecttype,columns,grantor,
column_name = ” ”
,action_text = ” ”
,protecttype_text = ” ”
,ending = ” ”
into #protects
from sysprotects
where 1=2

/* Either a passed object or all objects */
if @objid is not null or @parameter is null
begin

select uid,gid into #groups from sysusers

if @groups_only is not null
delete #groups
where uid != gid

/* IT IS AN OBJECT */
insert #protects
select id,p.uid,action,protecttype,columns,grantor,””,””,””,””
from sysprotects p, #groups g
where id=isnull(@objid,id)
and p.uid = g.uid

/* REVOKES ON COLUMNS */
insert #protects
select id,p.uid,action,2,columns,grantor,
“(“+col_name(p.id,c.number)+”)”,””,””,””
from sysprotects p, master.dbo.spt_values c, #groups g
where p.columns is not null
and convert(tinyint,substring(p.columns,c.low,1)) & c.high=0
and c.type = “P”
and c.number < = 255 and c.number>0
and c.low>1
and col_name(p.id,c.number) is not null
and id=isnull(@objid,id)
and p.uid=g.uid

if @do_system_tables is null and @objid is null
delete #protects
from #protects p, sysobjects o
where p.id = o.id
and o.type = ‘S’
end
else
begin

/* IS IT A USER */
select @uid = uid from sysusers where name=@parameter
if @@rowcount = 0 or @uid is null
begin
print “No User Or Object Found”
return (1)
end

insert #protects
select distinct id,uid,action,protecttype,columns,grantor,””,””,””,””
from sysprotects p
where uid=@uid
/* and isnull( p.columns,0x01 ) = 0x01 */

/* REVOKES ON COLUMNS */
insert #protects
select id,uid,action,2,columns,grantor,
“(“+col_name(p.id,c.number)+”)”, “”,””,””
from sysprotects p, master.dbo.spt_values c
where isnull( p.columns,0x01 ) != 0x01
and convert(tinyint, substring(p.columns, c.low, 1)) & c.high = 0
and c.type = “P”
and c.number < = 255 and c.number>0
and c.low>1
and col_name(p.id,c.number) is not null
and uid=@uid

if @do_system_tables is null
delete #protects
from #protects p, sysobjects o
where p.id = o.id
and o.type = ‘S’
end

/* References etc */
delete #protects
where action in(151,207,222,233,236)

update #protects
set action_text = name
from master.dbo.spt_values v
where v.type=’T’
and v.number = #protects.action

update #protects
set protecttype_text = name
from master.dbo.spt_values v
where v.type=’T’
and v.number = #protects.protecttype +204

— protecttype column can contain these values: 0 for grant with grant. 1 for grant. 2 for revoke
update #protects
set protecttype_text =
case
when protecttype = 0
then “GRANT”
when protecttype = 1
then “GRANT”
when protecttype = 2
then “REVOKE”
end

update #protects
set ending = ” WITH GRANT OPTION”
where protecttype = 0

declare @max_len int
select @max_len = max(char_length( rtrim(protecttype_text)+” “+rtrim(action_text)+” on “+rtrim(object_name(id))+column_name+” to “+rtrim(user_name(uid))+ending))
from #protects

if @max_len < 60 select substring( rtrim(protecttype_text) + " " + rtrim(action_text) + case when id = 0 then " " else " on " end + rtrim(object_name(id)) + column_name + " to " + rtrim(user_name(uid)) + ending ,1,59) from #protects where rtrim(action_text) != "" order by object_name(id),protecttype_text else if @max_len < 80 select substring( rtrim(protecttype_text) + " " + rtrim(action_text) + case when id = 0 then " " else " on " end + rtrim(object_name(id)) + column_name + " to " + rtrim(user_name(uid)) + ending ,1,79) from #protects where rtrim(action_text) != "" order by object_name(id),protecttype_text else if @max_len < 132 select substring( rtrim(protecttype_text) + " " + rtrim(action_text) + case when id = 0 then " " else " on " end + rtrim(object_name(id)) + column_name + " to " + rtrim(user_name(uid)) + ending ,1,131) from #protects where rtrim(action_text) != "" order by object_name(id),protecttype_text return (0) go grant execute on sp__helprotect to public go[/sql]

Share Button

Want a free ebook? Java Application Development on Linux

Nikesh Jauhari over on the Linux Poison blog, posted about the free eBook – Java Application Development on Linux, a 599 page ebook from informIT. Yeah, when you download the book, you will need to give informIT your contact info. If you don’t want to, you could probably just use an email address that you don’t use except for such registration things.

Share Button

PowerBuilder: Download your PowerBuilder patches for v11.x, 12.x before April 15th! Sybase is taking them away!

Download the ebfs (patches) for PowerBuilder 11x and 12x now before Sybase removes them from the my.sybase.com!

Announcement from SAP/Sybase:

April 11, 2013
Dear Valued Sybase Customer:

This letter provides notification of the end of sale for the following versions of Sybase PowerBuilder.

Product Platform End of Sale Migration Path
PowerBuilder 12.x Windows 04/15/2013 PowerBuilder 12.5.2
PowerBuilder 12.5.x Windows 04/15/2013 PowerBuilder 12.5.2

Please note that EBFs for PowerBuilder 11.x, 11.5.x, 12.x and 12.5.x (except for PowerBuilder 12.5.2) will no longer be available as of April 15, 2013.

If you have any questions, please call your local Sybase Technical Support Center. One of our Customer Service representatives will respond to your questions or direct your call to someone who can. For the number of your closest Technical Support Center, please go to: www.sybase.com/contactus/support.

Thank you for your cooperation in this product transition. We regret any inconvenience that discontinuing the sale of this product may cause your organization. Our support staff is available to assist you in any way possible.

Sincerely,
Sue Dunnell
PowerBuilder Product Manager
Sybase, an SAP Company

Share Button

Howto Perl: Crypt::CBC module with the blowfish encryption cipher

I came up with the following example a few years back. Crypt::CBCperl is quite easy to use but can be confusing to new users of it. I prefer to use the subroutines encrypt_hex and decrypt as the encoded string is hexidecimal not in binary format. This allows me to work with it as if it was a normal string, such as sending it in a tweet or email or possibly embed it in an image.

use warnings;
use strict;
use Crypt::CBC;

our $cipher = Crypt::CBC->new(
  -key => 'g0oB3r__g0oB3r',
  -cipher => 'Blowfish'
);

# Encrypts a string and returns it.
sub encrypt {
  return( $cipher->encrypt_hex($_) );
}

# Decrypts an encrypted string and returns it.
sub decrypt {
  return( $cipher->decrypt( pack("H*", $_) ) );
}

#########

my $encrypted_string = encrypt('happy')
my $decrypted_string = decrypt($encrypted_string);

printf "'happy' -> encrypted as '%s' -> decrypted to '%s'\n", $encrypted_string, $decrypted_string;
Share Button

“Why the perl community is no boy’s club” by tinita

If you haven’t read tinita‘s blog post, please do so.  As males we often don’t see the harm we do when we are “just joking around” or “teasing” or whatever our justification is for making crude statements.  I know I am not innocent either, most commonly because I wasn’t aware that <insert some comment> was offensive to another person.  We deserve to treat each other with respect.

First, the reason for this post: There was this answer in the recent survey:
“None – I refuse to acknowledge the term man hours, you patriarchical pig. But I have many person-hours. And let me tell you…”

You can discuss if this is discriminating feminists or not. It’s a matter of perception, if you know the author or not. It seems that there are people who find this offensive. And I also think that it shouldn’t be on a perlmonks poll, while in a group of friends it might be funny.

Read the rest of her post on tinita’s blog

Share Button

New SAP/Sybase White Papers for Sybase IQ 16.0

SAP Sybase IQ 16 Product Overview – Outlines the new features and benefits of SAP Sybase IQ 16, along with what motivated the build of this product version.

Sybase
Sybase

SAP Sybase IQ 16 In-Database Analytics Option –  This is a technical overview of the SAP Sybse IQ In-Database Analytics Option. This options delivers fast, accurate insights quickly to help make the decisions needed to win in today’s competitive environment.

SAP Sybase IQ 16 Advanced Security Option – SAP Sybase IQ 16 Advanced Security Option Technical Overview. This option allows advanced protection of your valuable data assets without sacrificing flexibility.

SAP Sybase IQ 16 Very Large Database Option – This is a technical overview for the SAP Sybase IQ 16 Very Large Database Option. This option maximizes the value of your growing data assets.

SAP Sybase IQ 16 Unstructured Data Analytics Option – This option allows for the storage and retrieval of unstructured data including full text search and analysis.

SAP Sybase IQ 16 Multiplex Grid Option – The option delivers elastic capacity for high performance Enterprise Data Warehouses.

Lesson 1: Introduction to Sybase IQ Welcome to “Getting started with Sybase IQ Column Store Analytics Server” This book is set up in a way that will put the subject areas most pertinent to you right at your fingertips. This is not a reference book. Instead, it is a how-to book where the information is presented with the goal of getting you started with Sybase IQ as quickly as possible.

Lesson 2: Product Installation and Database Creation – This chapter discusses installing Sybase IQ, verifying the installation, starting up the server and using client tools to connect to it. It will show you how to create a Sybase IQ database. It will also describe the TPCH schema that you will be creating in your Sybase IQ database. This schema will be the basis for the work you will be doing in subsequent chapters of this book.

Lesson 3: Create Schema and Load Data – This chapter discusses creating users in the database you created in the previous chapter, installing the TPCH schema, and loading data into the tables.

Lesson 4: Optimize a Query Using the Sybase IQ Query Plan – In this chapter, you will learn how Sybase IQ executes queries, and how to interpret the Sybase IQ query plan. The focus will be on query optimization on a single IQ server, not distributed query processing in a Multiplex. That is addressed in another lesson, “Scaling Out with Multiplex and Distributed Query Processing”. In this lesson, you will execute a SQL query, and perform some analysis to optimize the query.

Lesson 5: Monitoring Sybase IQ with Sybase Control Center – Once you have your database up and running, there are administrative tasks that you need to know how to perform in order to insure the health – responsiveness and longevity – of your system. This lesson is going to focus on Sybase Control Center – a graphical monitoring and administration tool for all types of Sybase servers, including Sybase IQ.

Lesson 6: Scaling Out with Multiplex and Distributed Query Processing – If your analytics workload becomes too large for a single machine, Sybase IQ can be configured in a clustered configuration called a “Multiplex” that scales to accommodate more users and more complex queries.

Lesson 7: Pattern Matching with a C++ in-Database UDF – When you want to perform analysis on the data in your Sybase IQ database, you normally will write a SQL query or stored procedure. But Sybase IQ has taken analytics to another level with its in-database analytics feature. Sybase IQ has created an API for extending the functionality of the Sybase IQ database server with user defined functions called UDFs.

Share Button

Elie Wiesel: Universal Lessons of the Holocaust

Share Button

Sybase Control Center and SAP

Now that SAP is exerting control over the policies and procedures within Sybase, will Sybase Control Center be

Sybase
Sybase
redesigned to be usable?  I mean, it appears to be only partially implemented, provides no historical information, limited performance metrics, no ability to create reports, allows only one ‘user’ at a time, et cetera.  I know in the past, a certain Sybase Partner had a temper tantrum when Sybase demonstrated SCC at TechWave.  (I was in the room when one of the reps had the fit), so will SAP be able to push back and design/develop Sybase Control Center into something useful?

Share Button

Drobo Mini

I really want a Drobo Mini 🙂

Drobo Usage

Share Button

Best Practices for SAP/Sybase IQ 15

Many of us are still running SAP/Sybase IQ 15x.

This document discusses Best Practices for IQ 15. It is intended to provide a starting point for optimizing your implementation of IQ 15. It includes an introduction to new features, suggested methods for using and configuring important features of IQ, and some precautions for avoiding common problems. As a general purpose document, it is not intended to be a comprehensive guide for every environment.

It is always a good idea to be aware of any best practices for a piece of software regardless of whether you intend to follow them verbatim or a subset of them.

Share Button