Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

How to read permissions from a program?

6 posts in General Discussion Last posting was on 2011-05-13 13:50:54.0Z
George Brink Posted on 2011-05-10 17:52:30.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: How to read permissions from a program?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dc97b5e$1@forums-1-dub>
Date: 10 May 2011 10:52:30 -0700
X-Trace: forums-1-dub 1305049950 10.22.241.152 (10 May 2011 10:52:30 -0700)
X-Original-Trace: 10 May 2011 10:52:30 -0700, vip152.sybase.com
Lines: 23
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30158
Article PK: 72336

I need to get a list of tables which are selectable by 'me' (a user who
is connected to database).

The problem is that sysprotects stores in the uid field ids of groups.
And simple statement like this:
----------------------------------
select o.name
from sysobjects o
join sysprotects p on o.id=p.id and
p.action=193 and
p.protecttype<2 and
p.uid=user_id()
----------------------------------
returns an empty resultset, since none of the tables are given directly
to me, they are given to groups and I have access to those tables as a
member of a group (or groups).
To make a problem more complicated, DB has tables from which I can
select since they are selectable by 'public', or I can be an owner of
the table and do not have an explicit record in sysprotect...

sp_helprotect does not really help...

Any suggestions?


"Mark A. Parsons" <iron_horse Posted on 2011-05-10 18:09:46.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to read permissions from a program?
References: <4dc97b5e$1@forums-1-dub>
In-Reply-To: <4dc97b5e$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dc97f6a@forums-1-dub>
Date: 10 May 2011 11:09:46 -0700
X-Trace: forums-1-dub 1305050986 10.22.241.152 (10 May 2011 11:09:46 -0700)
X-Original-Trace: 10 May 2011 11:09:46 -0700, vip152.sybase.com
Lines: 31
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30159
Article PK: 72337

You'll probably want to look at 3 or 4 different queries UNION'd together:

- select from sysobjects where uid = user_id() (objects owned by you)
- select from sysobjects/sysprotects (for access granted to user_id(), ie, your proposed query)
- select from sysobjects/sysprotects/sysusers (for access granted to groups you are assigned to + access granted to the
public group; use sysusers to look up groups you belong to)

Keep in mind that if your environment uses rolls then this will require some more coding.

On 05/10/2011 13:52, George Brink wrote:
> I need to get a list of tables which are selectable by 'me' (a user who is connected to database).
>
> The problem is that sysprotects stores in the uid field ids of groups. And simple statement like this:
> ----------------------------------
> select o.name
> from sysobjects o
> join sysprotects p on o.id=p.id and
> p.action=193 and
> p.protecttype<2 and
> p.uid=user_id()
> ----------------------------------
> returns an empty resultset, since none of the tables are given directly to me, they are given to groups and I have
> access to those tables as a member of a group (or groups).
> To make a problem more complicated, DB has tables from which I can select since they are selectable by 'public', or I
> can be an owner of the table and do not have an explicit record in sysprotect...
>
> sp_helprotect does not really help...
>
> Any suggestions?


George Brink Posted on 2011-05-11 18:15:28.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to read permissions from a program?
References: <4dc97b5e$1@forums-1-dub> <4dc97f6a@forums-1-dub>
In-Reply-To: <4dc97f6a@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dcad240$1@forums-1-dub>
Date: 11 May 2011 11:15:28 -0700
X-Trace: forums-1-dub 1305137728 10.22.241.152 (11 May 2011 11:15:28 -0700)
X-Original-Trace: 11 May 2011 11:15:28 -0700, vip152.sybase.com
Lines: 58
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30160
Article PK: 72343

Well, my last attempt to get a list of tables with some specific field
in them and selectable by 'me' - resulted in this:
--------------------
use @some_db
select Owner=u.name, Table_Name=o.name
from syscolumns c
join sysobjects o on o.id=c.id and o.type='U'
join sysusers u on o.uid=u.uid
join sysusers g on g.uid=user_id()
join sysprotects p on (o.id=p.id and p.action=193 and p.protecttype<2
and (p.uid=g.gid or p.uid=user_id() or p.uid=0)) or o.uid=user_id()
where c.name=@some_field
--------------------

Looks scary but works.

On 5/10/2011 2:09 PM, Mark A. Parsons wrote:
> You'll probably want to look at 3 or 4 different queries UNION'd together:
>
> - select from sysobjects where uid = user_id() (objects owned by you)
> - select from sysobjects/sysprotects (for access granted to user_id(),
> ie, your proposed query)
> - select from sysobjects/sysprotects/sysusers (for access granted to
> groups you are assigned to + access granted to the public group; use
> sysusers to look up groups you belong to)
>
> Keep in mind that if your environment uses rolls then this will require
> some more coding.
>
>
>
> On 05/10/2011 13:52, George Brink wrote:
>> I need to get a list of tables which are selectable by 'me' (a user
>> who is connected to database).
>>
>> The problem is that sysprotects stores in the uid field ids of groups.
>> And simple statement like this:
>> ----------------------------------
>> select o.name
>> from sysobjects o
>> join sysprotects p on o.id=p.id and
>> p.action=193 and
>> p.protecttype<2 and
>> p.uid=user_id()
>> ----------------------------------
>> returns an empty resultset, since none of the tables are given
>> directly to me, they are given to groups and I have
>> access to those tables as a member of a group (or groups).
>> To make a problem more complicated, DB has tables from which I can
>> select since they are selectable by 'public', or I
>> can be an owner of the table and do not have an explicit record in
>> sysprotect...
>>
>> sp_helprotect does not really help...
>>
>> Any suggestions?


Cory Sane [TeamSybase] Posted on 2011-05-13 06:35:19.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.general
References: <4dc97b5e$1@forums-1-dub> <4dc97f6a@forums-1-dub> <4dcad240$1@forums-1-dub>
In-Reply-To: <4dcad240$1@forums-1-dub>
Subject: Re: How to read permissions from a program?
Lines: 66
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18197
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18263
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dccd127$1@forums-1-dub>
Date: 12 May 2011 23:35:19 -0700
X-Trace: forums-1-dub 1305268519 10.22.241.152 (12 May 2011 23:35:19 -0700)
X-Original-Trace: 12 May 2011 23:35:19 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30166
Article PK: 72345

I confused by your use of syscolumns... You appear to be searching base on a column name.
The code looks interesting.

--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

"George Brink" <siberianowl@yahoo.com> wrote in message news:4dcad240$1@forums-1-dub...
> Well, my last attempt to get a list of tables with some specific field
> in them and selectable by 'me' - resulted in this:
> --------------------
> use @some_db
> select Owner=u.name, Table_Name=o.name
> from syscolumns c
> join sysobjects o on o.id=c.id and o.type='U'
> join sysusers u on o.uid=u.uid
> join sysusers g on g.uid=user_id()
> join sysprotects p on (o.id=p.id and p.action=193 and p.protecttype<2
> and (p.uid=g.gid or p.uid=user_id() or p.uid=0)) or o.uid=user_id()
> where c.name=@some_field
> --------------------
>
> Looks scary but works.
>
>
>
> On 5/10/2011 2:09 PM, Mark A. Parsons wrote:
>> You'll probably want to look at 3 or 4 different queries UNION'd together:
>>
>> - select from sysobjects where uid = user_id() (objects owned by you)
>> - select from sysobjects/sysprotects (for access granted to user_id(),
>> ie, your proposed query)
>> - select from sysobjects/sysprotects/sysusers (for access granted to
>> groups you are assigned to + access granted to the public group; use
>> sysusers to look up groups you belong to)
>>
>> Keep in mind that if your environment uses rolls then this will require
>> some more coding.
>>
>>
>>
>> On 05/10/2011 13:52, George Brink wrote:
>>> I need to get a list of tables which are selectable by 'me' (a user
>>> who is connected to database).
>>>
>>> The problem is that sysprotects stores in the uid field ids of groups.
>>> And simple statement like this:
>>> ----------------------------------
>>> select o.name
>>> from sysobjects o
>>> join sysprotects p on o.id=p.id and
>>> p.action=193 and
>>> p.protecttype<2 and
>>> p.uid=user_id()
>>> ----------------------------------
>>> returns an empty resultset, since none of the tables are given
>>> directly to me, they are given to groups and I have
>>> access to those tables as a member of a group (or groups).
>>> To make a problem more complicated, DB has tables from which I can
>>> select since they are selectable by 'public', or I
>>> can be an owner of the table and do not have an explicit record in
>>> sysprotect...
>>>
>>> sp_helprotect does not really help...
>>>
>>> Any suggestions?


George Brink Posted on 2011-05-13 13:50:54.0Z
From: George Brink <siberianowl@yahoo.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.17) Gecko/20110414 Thunderbird/3.1.10
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: How to read permissions from a program?
References: <4dc97b5e$1@forums-1-dub> <4dc97f6a@forums-1-dub> <4dcad240$1@forums-1-dub> <4dccd127$1@forums-1-dub>
In-Reply-To: <4dccd127$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dcd373e$1@forums-1-dub>
Date: 13 May 2011 06:50:54 -0700
X-Trace: forums-1-dub 1305294654 10.22.241.152 (13 May 2011 06:50:54 -0700)
X-Original-Trace: 13 May 2011 06:50:54 -0700, vip152.sybase.com
Lines: 44
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30168
Article PK: 72347


On 5/13/2011 2:35 AM, Cory Sane [TeamSybase] wrote:
> I confused by your use of syscolumns... You appear to be searching base
> on a column name.
> The code looks interesting.
>

Ok. My background:
On the server we have a set a databases. Those databases contain
information about clients. Tables are split into DBs by functional type
or a group the information belongs to.
My ultimate goal:
To make a 'client-explorer' application. Client-side GUI app which would
scan all relevant DBs for tables which holds some info about a client
(by client_id) and allow developers to easily investigate what is
actually written in the database vs what normal users see in their
applications.
For that, I need to read which tables have client_id column (in
different DBs) and a user (developer) who runs the program need to have
access to those tables.
Views are.... forgotten for a while :)


Right now I have this:
-----
select OwnerName = u.name, TableName=o.name
from %1.dbo.syscolumns c
join %1.dbo.sysobjects o on o.id=c.id and o.type='U'
join %1.dbo.sysusers u on o.uid=u.uid
join %1.dbo.sysusers su on
su.suid=suser_id() or
su.suid=(select altsuid
from %1.dbo.sysalternates where suid=suser_id()) or
su.suid=-1
join %1.dbo.sysusers g on g.uid=su.uid
join %1.dbo.sysprotects p on
o.id=p.id and p.action=193 and
p.protecttype<2 and
(p.uid=g.gid or p.uid=su.uid or p.uid=0)
where c.name='client_id'
---------------------------
%1 here is replaced by database name by my application.
The code is database independent.


Cory Sane [TeamSybase] Posted on 2011-05-13 06:36:42.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.general
References: <4dc97b5e$1@forums-1-dub> <4dc97f6a@forums-1-dub> <4dcad240$1@forums-1-dub>
In-Reply-To: <4dcad240$1@forums-1-dub>
Subject: Re: How to read permissions from a program?
Lines: 66
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18197
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18263
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4dccd17a$1@forums-1-dub>
Date: 12 May 2011 23:36:42 -0700
X-Trace: forums-1-dub 1305268602 10.22.241.152 (12 May 2011 23:36:42 -0700)
X-Original-Trace: 12 May 2011 23:36:42 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30167
Article PK: 72346

You may also want to include views in this search... a view would give you access to a column that you might not be able to see
in a table.

--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

"George Brink" <siberianowl@yahoo.com> wrote in message news:4dcad240$1@forums-1-dub...
> Well, my last attempt to get a list of tables with some specific field in them and selectable by 'me' - resulted in this:
> --------------------
> use @some_db
> select Owner=u.name, Table_Name=o.name
> from syscolumns c
> join sysobjects o on o.id=c.id and o.type='U'
> join sysusers u on o.uid=u.uid
> join sysusers g on g.uid=user_id()
> join sysprotects p on (o.id=p.id and p.action=193 and p.protecttype<2
> and (p.uid=g.gid or p.uid=user_id() or p.uid=0)) or o.uid=user_id()
> where c.name=@some_field
> --------------------
>
> Looks scary but works.
>
>
>
> On 5/10/2011 2:09 PM, Mark A. Parsons wrote:
>> You'll probably want to look at 3 or 4 different queries UNION'd together:
>>
>> - select from sysobjects where uid = user_id() (objects owned by you)
>> - select from sysobjects/sysprotects (for access granted to user_id(),
>> ie, your proposed query)
>> - select from sysobjects/sysprotects/sysusers (for access granted to
>> groups you are assigned to + access granted to the public group; use
>> sysusers to look up groups you belong to)
>>
>> Keep in mind that if your environment uses rolls then this will require
>> some more coding.
>>
>>
>>
>> On 05/10/2011 13:52, George Brink wrote:
>>> I need to get a list of tables which are selectable by 'me' (a user
>>> who is connected to database).
>>>
>>> The problem is that sysprotects stores in the uid field ids of groups.
>>> And simple statement like this:
>>> ----------------------------------
>>> select o.name
>>> from sysobjects o
>>> join sysprotects p on o.id=p.id and
>>> p.action=193 and
>>> p.protecttype<2 and
>>> p.uid=user_id()
>>> ----------------------------------
>>> returns an empty resultset, since none of the tables are given
>>> directly to me, they are given to groups and I have
>>> access to those tables as a member of a group (or groups).
>>> To make a problem more complicated, DB has tables from which I can
>>> select since they are selectable by 'public', or I
>>> can be an owner of the table and do not have an explicit record in
>>> sysprotect...
>>>
>>> sp_helprotect does not really help...
>>>
>>> Any suggestions?