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.

Permission denied Updating through a View

6 posts in General Discussion Last posting was on 2003-06-24 05:03:46.0Z
Richard Posted on 2003-06-13 20:04:48.0Z
From: "Richard" <Richardr@ldsinc.com>
Subject: Permission denied Updating through a View
Date: Fri, 13 Jun 2003 15:04:48 -0500
Lines: 3040
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <evMOQgeMDHA.279@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: lds253.ldsinc.com 66.6.104.253
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1201
Article PK: 3451

I have an App where I am restricting the user's "table access" to NONE.
(what I mean is I am not giving any table access to any users) I have Stored
Procedures where I Add/Update/Delete from the tables and GRANT Exec access
to those SP's All of my conditions work except for when I update through a
VIEW with a Disconnected Recordset. The reason I am using the VIEW is
because ADO will not let you use a SP with a Disconnected Recordset. My
Error happens with the following code:

Set rstTemp.ActiveConnection = ADOConnect1.Connection
rstTemp.UpdateBatch <<<ERROR OCCURS HERE

[Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or access
violation: Permission denied: you do not have permission to update "MYTABLE"

My VIEW has GRANT ALL access. The Error goes away when I give Table Access
to the User for the underlying table. I have included a Request Level Log
for my errors.

Can someone please help!

Thanks
Richard


Nick Elson Posted on 2003-06-18 20:17:41.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
References: <evMOQgeMDHA.279@forums-2-dub>
Subject: Re: Permission denied Updating through a View
Date: Wed, 18 Jun 2003 16:17:41 -0400
Lines: 57
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <uw9Q2edNDHA.362@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: nicelson-xp.sybase.com 172.31.142.76
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1216
Article PK: 3463

One would probably need to know these facts first:

- version and ebf of the product
- who owns the view?
- what it's definition is?
- what is the definiton of the base table(s)?
- explicit permissions granted to owners

to answer this.

Most likely the answer will be that the owner of the view has not
been granted (explicitly) sufficient rights to permit that access
the base tables.


05/28 15:10:20.417 ** REQUEST conn: 26502088 STMT_PREPARE
"UPDATE "DBA"."Sec_Verus_User" SET "Security_Mask"=? WHERE
"Verus_User_Link_Code"=? AND "Security_Mask"=?"
05/28 15:10:20.417 ** DONE conn: 26502088 STMT_PREPARE
Stmt=65541
05/28 15:10:20.417 ** REQUEST conn: 26502088 STMT_EXECUTE
Stmt=65541
05/28 15:10:20.417 ** ERROR conn: 26502088 code: -121 "Permission
denied: you do not have permission to update "Sec_Verus_User""
05/28 15:10:20.418 ** DONE conn: 26502088 STMT_EXECUTE

"Richard" <Richardr@ldsinc.com> wrote in message
news:evMOQgeMDHA.279@forums-2-dub...
> I have an App where I am restricting the user's "table access" to NONE.
> (what I mean is I am not giving any table access to any users) I have
Stored
> Procedures where I Add/Update/Delete from the tables and GRANT Exec access
> to those SP's All of my conditions work except for when I update through a
> VIEW with a Disconnected Recordset. The reason I am using the VIEW is
> because ADO will not let you use a SP with a Disconnected Recordset. My
> Error happens with the following code:
>
> Set rstTemp.ActiveConnection = ADOConnect1.Connection
> rstTemp.UpdateBatch <<<ERROR OCCURS HERE
>
> [Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or access
> violation: Permission denied: you do not have permission to update
"MYTABLE"
>
> My VIEW has GRANT ALL access. The Error goes away when I give Table
Access
> to the User for the underlying table. I have included a Request Level Log
> for my errors.
>
> Can someone please help!
>
> Thanks
> Richard
>
>
>


Richard Posted on 2003-06-23 14:14:40.0Z
From: "Richard" <Richardr@ldsinc.com>
References: <evMOQgeMDHA.279@forums-2-dub> <uw9Q2edNDHA.362@forums-2-dub>
Subject: Re: Permission denied Updating through a View
Date: Mon, 23 Jun 2003 09:14:40 -0500
Lines: 150
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <uF4GbLZODHA.218@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: lds253.ldsinc.com 66.6.104.253
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1231
Article PK: 3475

Nick Sorry is has taken me so long to respond...

- version and ebf of the product

(8.0.2.4249)

- who owns the view?

DBA is then owner of the Views

- what it's definition is?

CREATE VIEW DBA.vw_Adm_UserFeatures ()

AS

SELECT vu.Verus_User_Link_Code,vu.Security_Mask,vu.UserName,

sc.Application_Name

FROM DBA.Sec_Verus_User as vu

JOIN DBA.Sec_Control sc ON sc.Sec_AppID=vu.Sec_AppID



- what is the definiton of the base table(s)?

CREATE TABLE Sec_Verus_User (

Verus_User_Link_Code AUTOLINKCODE PRIMARY KEY,

Sec_AppID INTEGER,

Date_Added DATEADDED,

Date_Edited DATEEDITED,

User_Added USERADDED,

User_Edited USEREDITED,

Secured_Features_Link_Code INTEGER NOT NULL,

UserName CHAR(254) NOT NULL,

Security_Mask INTEGER DEFAULT 0

);

CREATE TABLE Sec_Control (

Sec_AppID AUTOLINKCODE PRIMARY KEY,

Date_Added DATEADDED,

Date_Edited DATEEDITED,

Application_Name CHAR(254) NOT NULL

);



- explicit permissions granted to owners

GRANT CONNECT TO SecAdmin;

GRANT GROUP TO SecAdmin;

GRANT CONNECT TO vradm Identified BY "ldslds";

GRANT MEMBERSHIP IN GROUP SecAdmin TO vradm;

GRANT SELECT ON DBA.vw_Adm_UserFeatures TO SecAdmin;

GRANT UPDATE ON DBA.vw_Adm_UserFeatures TO SecAdmin;



-It works when I issue this permissions on the Table

GRANT UPDATE ON DBA.Sec_Verus_User TO SecAdmin;

GRANT SELECT ON DBA.Sec_Verus_User TO SecAdmin;

"Nick Elson" <no_spam_nicelson@sybase.com> wrote in message
news:uw9Q2edNDHA.362@forums-2-dub...
> One would probably need to know these facts first:
>
> - version and ebf of the product
> - who owns the view?
> - what it's definition is?
> - what is the definiton of the base table(s)?
> - explicit permissions granted to owners
>
> to answer this.
>
> Most likely the answer will be that the owner of the view has not
> been granted (explicitly) sufficient rights to permit that access
> the base tables.
>
>
> 05/28 15:10:20.417 ** REQUEST conn: 26502088 STMT_PREPARE
> "UPDATE "DBA"."Sec_Verus_User" SET "Security_Mask"=? WHERE
> "Verus_User_Link_Code"=? AND "Security_Mask"=?"
> 05/28 15:10:20.417 ** DONE conn: 26502088 STMT_PREPARE
> Stmt=65541
> 05/28 15:10:20.417 ** REQUEST conn: 26502088 STMT_EXECUTE
> Stmt=65541
> 05/28 15:10:20.417 ** ERROR conn: 26502088 code: -121 "Permission
> denied: you do not have permission to update "Sec_Verus_User""
> 05/28 15:10:20.418 ** DONE conn: 26502088 STMT_EXECUTE
> "Richard" <Richardr@ldsinc.com> wrote in message
> news:evMOQgeMDHA.279@forums-2-dub...
> > I have an App where I am restricting the user's "table access" to NONE.
> > (what I mean is I am not giving any table access to any users) I have
> Stored
> > Procedures where I Add/Update/Delete from the tables and GRANT Exec
access
> > to those SP's All of my conditions work except for when I update through
a
> > VIEW with a Disconnected Recordset. The reason I am using the VIEW is
> > because ADO will not let you use a SP with a Disconnected Recordset. My
> > Error happens with the following code:
> >
> > Set rstTemp.ActiveConnection = ADOConnect1.Connection
> > rstTemp.UpdateBatch <<<ERROR OCCURS HERE
> >
> > [Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or access
> > violation: Permission denied: you do not have permission to update
> "MYTABLE"
> >
> > My VIEW has GRANT ALL access. The Error goes away when I give Table
> Access
> > to the User for the underlying table. I have included a Request Level
Log
> > for my errors.
> >
> > Can someone please help!
> >
> > Thanks
> > Richard
> >
> >
> >
>
>


Nick Elson Posted on 2003-06-23 15:21:14.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
References: <evMOQgeMDHA.279@forums-2-dub> <uw9Q2edNDHA.362@forums-2-dub> <uF4GbLZODHA.218@forums-2-dub>
Subject: Re: Permission denied Updating through a View
Date: Mon, 23 Jun 2003 11:21:14 -0400
Lines: 251
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <#PrSmwZODHA.279@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: nicelson-xp.sybase.com 172.31.142.76
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1232
Article PK: 3476

Hmmm ....
I could see how explicit grants would work but . . . .

. . . this works for me in 8.0.2#4251 ... so you should
maybe try a more recent ebf of the ** SERVER **

My repro. (derived from your information):
= = = = = = = = = = = = = = = = = = = =

-- clean up if required:
revoke SELECT on DBA.vw_Adm_UserFeatures from SecAdmin;
revoke UPDATE on DBA.vw_Adm_UserFeatures from SecAdmin;
revoke MEMBERSHIP IN GROUP SecAdmin from vradm;
revoke CONNECT from vradm;
revoke GROUP from SecAdmin;
revoke CONNECT from SecAdmin;
drop view vw_Adm_UserFeatures;
drop table Sec_Verus_User;
drop table Sec_Control;
checkpoint;

-- what is the definiton of the base table(s)?
-- note the datatypes substituted for UDTs

CREATE TABLE Sec_Verus_User (
Verus_User_Link_Code integer PRIMARY KEY,
Sec_AppID INTEGER,
Date_Added datetime,
Date_Edited datetime,
User_Added datetime,
User_Edited datetime,
Secured_Features_Link_Code INTEGER NOT NULL,
UserName CHAR(254) NOT NULL,
Security_Mask INTEGER DEFAULT 0
);

CREATE TABLE Sec_Control (
Sec_AppID integer PRIMARY KEY,
Date_Added datetime,
Date_Edited datetime,
Application_Name CHAR(254) NOT NULL
);

-- what is the view's definition
CREATE VIEW DBA.vw_Adm_UserFeatures ()
AS
SELECT vu.Verus_User_Link_Code,vu.Security_Mask,vu.UserName,
sc.Application_Name
FROM DBA.Sec_Verus_User as vu
JOIN DBA.Sec_Control sc ON sc.Sec_AppID=vu.Sec_AppID;

-- explicit permissions granted to owners
GRANT CONNECT TO SecAdmin;
GRANT GROUP TO SecAdmin;
GRANT CONNECT TO vradm Identified BY "ldslds";
GRANT MEMBERSHIP IN GROUP SecAdmin TO vradm;
GRANT SELECT ON DBA.vw_Adm_UserFeatures TO SecAdmin;
GRANT UPDATE ON DBA.vw_Adm_UserFeatures TO SecAdmin;

-- add some data so an update is possible
insert into DBA.Sec_Verus_User values (
1,
1,
'2003-06-23 12:00:00.000',
'2003-06-23 12:00:00.000',
'2003-06-23 12:00:00.000',
'2003-06-23 12:00:00.000',
1,
'vradm',
0
);
insert into Sec_Control values(
1,
'2003-06-23 12:00:00.000',
'2003-06-23 12:00:00.000',
'vradm'
);
commit;

-- verify data with "select * from DBA.vw_Adm_UserFeatures;"
-- execute a rollback; to release locks before proceeding


-- login into another session of dbisql as vradm\ldslds and run this

select * from DBA.vw_Adm_UserFeatures;
-- pause here a note value before continuing

update DBA.vw_Adm_UserFeatures set security_mask=1 where
security_mask=0;
--note: how the value changes after you continue
-- SO no permission problems! ?

"Richard" <Richardr@ldsinc.com> wrote in message
news:uF4GbLZODHA.218@forums-2-dub...
> Nick Sorry is has taken me so long to respond...
>
> - version and ebf of the product
>
> (8.0.2.4249)
>
> - who owns the view?
>
> DBA is then owner of the Views
>
> - what it's definition is?
>
> CREATE VIEW DBA.vw_Adm_UserFeatures ()
>
> AS
>
> SELECT vu.Verus_User_Link_Code,vu.Security_Mask,vu.UserName,
>
> sc.Application_Name
>
> FROM DBA.Sec_Verus_User as vu
>
> JOIN DBA.Sec_Control sc ON sc.Sec_AppID=vu.Sec_AppID
>
>
>
> - what is the definiton of the base table(s)?
>
> CREATE TABLE Sec_Verus_User (
>
> Verus_User_Link_Code AUTOLINKCODE PRIMARY KEY,
>
> Sec_AppID INTEGER,
>
> Date_Added DATEADDED,
>
> Date_Edited DATEEDITED,
>
> User_Added USERADDED,
>
> User_Edited USEREDITED,
>
> Secured_Features_Link_Code INTEGER NOT NULL,
>
> UserName CHAR(254) NOT NULL,
>
> Security_Mask INTEGER DEFAULT 0
>
> );
>
> CREATE TABLE Sec_Control (
>
> Sec_AppID AUTOLINKCODE PRIMARY KEY,
>
> Date_Added DATEADDED,
>
> Date_Edited DATEEDITED,
>
> Application_Name CHAR(254) NOT NULL
>
> );
>
>
>
> - explicit permissions granted to owners
>
> GRANT CONNECT TO SecAdmin;
>
> GRANT GROUP TO SecAdmin;
>
> GRANT CONNECT TO vradm Identified BY "ldslds";
>
> GRANT MEMBERSHIP IN GROUP SecAdmin TO vradm;
>
> GRANT SELECT ON DBA.vw_Adm_UserFeatures TO SecAdmin;
>
> GRANT UPDATE ON DBA.vw_Adm_UserFeatures TO SecAdmin;
>
>
>
> -It works when I issue this permissions on the Table
>
> GRANT UPDATE ON DBA.Sec_Verus_User TO SecAdmin;
>
> GRANT SELECT ON DBA.Sec_Verus_User TO SecAdmin;
>
> "Nick Elson" <no_spam_nicelson@sybase.com> wrote in message
> news:uw9Q2edNDHA.362@forums-2-dub...
> > One would probably need to know these facts first:
> >
> > - version and ebf of the product
> > - who owns the view?
> > - what it's definition is?
> > - what is the definiton of the base table(s)?
> > - explicit permissions granted to owners
> >
> > to answer this.
> >
> > Most likely the answer will be that the owner of the view has not
> > been granted (explicitly) sufficient rights to permit that access
> > the base tables.
> >
> >
> > 05/28 15:10:20.417 ** REQUEST conn: 26502088 STMT_PREPARE
> > "UPDATE "DBA"."Sec_Verus_User" SET "Security_Mask"=? WHERE
> > "Verus_User_Link_Code"=? AND "Security_Mask"=?"
> > 05/28 15:10:20.417 ** DONE conn: 26502088 STMT_PREPARE
> > Stmt=65541
> > 05/28 15:10:20.417 ** REQUEST conn: 26502088 STMT_EXECUTE
> > Stmt=65541
> > 05/28 15:10:20.417 ** ERROR conn: 26502088 code: -121 "Permission
> > denied: you do not have permission to update "Sec_Verus_User""
> > 05/28 15:10:20.418 ** DONE conn: 26502088 STMT_EXECUTE
> > "Richard" <Richardr@ldsinc.com> wrote in message
> > news:evMOQgeMDHA.279@forums-2-dub...
> > > I have an App where I am restricting the user's "table access" to
NONE.
> > > (what I mean is I am not giving any table access to any users) I have
> > Stored
> > > Procedures where I Add/Update/Delete from the tables and GRANT Exec
> access
> > > to those SP's All of my conditions work except for when I update
through
> a
> > > VIEW with a Disconnected Recordset. The reason I am using the VIEW is
> > > because ADO will not let you use a SP with a Disconnected Recordset.
My
> > > Error happens with the following code:
> > >
> > > Set rstTemp.ActiveConnection = ADOConnect1.Connection
> > > rstTemp.UpdateBatch <<<ERROR OCCURS HERE
> > >
> > > [Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or access
> > > violation: Permission denied: you do not have permission to update
> > "MYTABLE"
> > >
> > > My VIEW has GRANT ALL access. The Error goes away when I give Table
> > Access
> > > to the User for the underlying table. I have included a Request Level
> Log
> > > for my errors.
> > >
> > > Can someone please help!
> > >
> > > Thanks
> > > Richard
> > >
> > >
> > >
> >
> >
>
>


Richard Posted on 2003-06-23 22:46:49.0Z
From: "Richard" <Richardr@ldsinc.com>
References: <evMOQgeMDHA.279@forums-2-dub> <uw9Q2edNDHA.362@forums-2-dub> <uF4GbLZODHA.218@forums-2-dub> <#PrSmwZODHA.279@forums-2-dub>
Subject: Re: Permission denied Updating through a View
Date: Mon, 23 Jun 2003 17:46:49 -0500
Lines: 269
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <uhtNnpdODHA.362@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: lds253.ldsinc.com 66.6.104.253
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1240
Article PK: 3484

Nick,

Just a quick reply...I am out of office for a couple of days so I won't be
able to look into your other responses/solutions until later in the week.
In response to Version EBF 8.0.2#4251 we have clients that are also on
Version 7.0 so I am trying to find a solution that will port to 7.0 and
8.0...
Thanks
Richard

"Nick Elson" <no_spam_nicelson@sybase.com> wrote in message
news:%23PrSmwZODHA.279@forums-2-dub...
> Hmmm ....
> I could see how explicit grants would work but . . . .
>
> . . . this works for me in 8.0.2#4251 ... so you should
> maybe try a more recent ebf of the ** SERVER **
>
> My repro. (derived from your information):
> = = = = = = = = = = = = = = = = = = = =
>
> -- clean up if required:
> revoke SELECT on DBA.vw_Adm_UserFeatures from SecAdmin;
> revoke UPDATE on DBA.vw_Adm_UserFeatures from SecAdmin;
> revoke MEMBERSHIP IN GROUP SecAdmin from vradm;
> revoke CONNECT from vradm;
> revoke GROUP from SecAdmin;
> revoke CONNECT from SecAdmin;
> drop view vw_Adm_UserFeatures;
> drop table Sec_Verus_User;
> drop table Sec_Control;
> checkpoint;
>
> -- what is the definiton of the base table(s)?
> -- note the datatypes substituted for UDTs
>
> CREATE TABLE Sec_Verus_User (
> Verus_User_Link_Code integer PRIMARY KEY,
> Sec_AppID INTEGER,
> Date_Added datetime,
> Date_Edited datetime,
> User_Added datetime,
> User_Edited datetime,
> Secured_Features_Link_Code INTEGER NOT NULL,
> UserName CHAR(254) NOT NULL,
> Security_Mask INTEGER DEFAULT 0
> );
>
> CREATE TABLE Sec_Control (
> Sec_AppID integer PRIMARY KEY,
> Date_Added datetime,
> Date_Edited datetime,
> Application_Name CHAR(254) NOT NULL
> );
>
> -- what is the view's definition
> CREATE VIEW DBA.vw_Adm_UserFeatures ()
> AS
> SELECT vu.Verus_User_Link_Code,vu.Security_Mask,vu.UserName,
> sc.Application_Name
> FROM DBA.Sec_Verus_User as vu
> JOIN DBA.Sec_Control sc ON sc.Sec_AppID=vu.Sec_AppID;
>
> -- explicit permissions granted to owners
> GRANT CONNECT TO SecAdmin;
> GRANT GROUP TO SecAdmin;
> GRANT CONNECT TO vradm Identified BY "ldslds";
> GRANT MEMBERSHIP IN GROUP SecAdmin TO vradm;
> GRANT SELECT ON DBA.vw_Adm_UserFeatures TO SecAdmin;
> GRANT UPDATE ON DBA.vw_Adm_UserFeatures TO SecAdmin;
>
> -- add some data so an update is possible
> insert into DBA.Sec_Verus_User values (
> 1,
> 1,
> '2003-06-23 12:00:00.000',
> '2003-06-23 12:00:00.000',
> '2003-06-23 12:00:00.000',
> '2003-06-23 12:00:00.000',
> 1,
> 'vradm',
> 0
> );
> insert into Sec_Control values(
> 1,
> '2003-06-23 12:00:00.000',
> '2003-06-23 12:00:00.000',
> 'vradm'
> );
> commit;
>
> -- verify data with "select * from DBA.vw_Adm_UserFeatures;"
> -- execute a rollback; to release locks before proceeding
>
>
> -- login into another session of dbisql as vradm\ldslds and run this
>
> select * from DBA.vw_Adm_UserFeatures;
> -- pause here a note value before continuing
>
> update DBA.vw_Adm_UserFeatures set security_mask=1 where
> security_mask=0;
> --note: how the value changes after you continue
> -- SO no permission problems! ?
>
>
> "Richard" <Richardr@ldsinc.com> wrote in message
> news:uF4GbLZODHA.218@forums-2-dub...
> > Nick Sorry is has taken me so long to respond...
> >
> > - version and ebf of the product
> >
> > (8.0.2.4249)
> >
> > - who owns the view?
> >
> > DBA is then owner of the Views
> >
> > - what it's definition is?
> >
> > CREATE VIEW DBA.vw_Adm_UserFeatures ()
> >
> > AS
> >
> > SELECT vu.Verus_User_Link_Code,vu.Security_Mask,vu.UserName,
> >
> > sc.Application_Name
> >
> > FROM DBA.Sec_Verus_User as vu
> >
> > JOIN DBA.Sec_Control sc ON sc.Sec_AppID=vu.Sec_AppID
> >
> >
> >
> > - what is the definiton of the base table(s)?
> >
> > CREATE TABLE Sec_Verus_User (
> >
> > Verus_User_Link_Code AUTOLINKCODE PRIMARY KEY,
> >
> > Sec_AppID INTEGER,
> >
> > Date_Added DATEADDED,
> >
> > Date_Edited DATEEDITED,
> >
> > User_Added USERADDED,
> >
> > User_Edited USEREDITED,
> >
> > Secured_Features_Link_Code INTEGER NOT NULL,
> >
> > UserName CHAR(254) NOT NULL,
> >
> > Security_Mask INTEGER DEFAULT 0
> >
> > );
> >
> > CREATE TABLE Sec_Control (
> >
> > Sec_AppID AUTOLINKCODE PRIMARY KEY,
> >
> > Date_Added DATEADDED,
> >
> > Date_Edited DATEEDITED,
> >
> > Application_Name CHAR(254) NOT NULL
> >
> > );
> >
> >
> >
> > - explicit permissions granted to owners
> >
> > GRANT CONNECT TO SecAdmin;
> >
> > GRANT GROUP TO SecAdmin;
> >
> > GRANT CONNECT TO vradm Identified BY "ldslds";
> >
> > GRANT MEMBERSHIP IN GROUP SecAdmin TO vradm;
> >
> > GRANT SELECT ON DBA.vw_Adm_UserFeatures TO SecAdmin;
> >
> > GRANT UPDATE ON DBA.vw_Adm_UserFeatures TO SecAdmin;
> >
> >
> >
> > -It works when I issue this permissions on the Table
> >
> > GRANT UPDATE ON DBA.Sec_Verus_User TO SecAdmin;
> >
> > GRANT SELECT ON DBA.Sec_Verus_User TO SecAdmin;
> >
> > "Nick Elson" <no_spam_nicelson@sybase.com> wrote in message
> > news:uw9Q2edNDHA.362@forums-2-dub...
> > > One would probably need to know these facts first:
> > >
> > > - version and ebf of the product
> > > - who owns the view?
> > > - what it's definition is?
> > > - what is the definiton of the base table(s)?
> > > - explicit permissions granted to owners
> > >
> > > to answer this.
> > >
> > > Most likely the answer will be that the owner of the view has not
> > > been granted (explicitly) sufficient rights to permit that access
> > > the base tables.
> > >
> > >
> > > 05/28 15:10:20.417 ** REQUEST conn: 26502088 STMT_PREPARE
> > > "UPDATE "DBA"."Sec_Verus_User" SET "Security_Mask"=? WHERE
> > > "Verus_User_Link_Code"=? AND "Security_Mask"=?"
> > > 05/28 15:10:20.417 ** DONE conn: 26502088 STMT_PREPARE
> > > Stmt=65541
> > > 05/28 15:10:20.417 ** REQUEST conn: 26502088 STMT_EXECUTE
> > > Stmt=65541
> > > 05/28 15:10:20.417 ** ERROR conn: 26502088 code: -121 "Permission
> > > denied: you do not have permission to update "Sec_Verus_User""
> > > 05/28 15:10:20.418 ** DONE conn: 26502088 STMT_EXECUTE
> > > "Richard" <Richardr@ldsinc.com> wrote in message
> > > news:evMOQgeMDHA.279@forums-2-dub...
> > > > I have an App where I am restricting the user's "table access" to
> NONE.
> > > > (what I mean is I am not giving any table access to any users) I
have
> > > Stored
> > > > Procedures where I Add/Update/Delete from the tables and GRANT Exec
> > access
> > > > to those SP's All of my conditions work except for when I update
> through
> > a
> > > > VIEW with a Disconnected Recordset. The reason I am using the VIEW
is
> > > > because ADO will not let you use a SP with a Disconnected Recordset.
> My
> > > > Error happens with the following code:
> > > >
> > > > Set rstTemp.ActiveConnection = ADOConnect1.Connection
> > > > rstTemp.UpdateBatch <<<ERROR OCCURS HERE
> > > >
> > > > [Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or
access
> > > > violation: Permission denied: you do not have permission to update
> > > "MYTABLE"
> > > >
> > > > My VIEW has GRANT ALL access. The Error goes away when I give Table
> > > Access
> > > > to the User for the underlying table. I have included a Request
Level
> > Log
> > > > for my errors.
> > > >
> > > > Can someone please help!
> > > >
> > > > Thanks
> > > > Richard
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>


Nick Elson Posted on 2003-06-24 05:03:46.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
References: <evMOQgeMDHA.279@forums-2-dub> <uw9Q2edNDHA.362@forums-2-dub> <uF4GbLZODHA.218@forums-2-dub> <#PrSmwZODHA.279@forums-2-dub> <uhtNnpdODHA.362@forums-2-dub>
Subject: Re: Permission denied Updating through a View
Date: Tue, 24 Jun 2003 01:03:46 -0400
Lines: 328
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Message-ID: <eoHuO8gODHA.218@forums-2-dub>
Newsgroups: ianywhere.public.general
NNTP-Posting-Host: vpn-dub-065.sybase.com 10.22.120.65
Path: forums-1-dub!forums-master.sybase.com!forums-2-dub.sybase.com
Xref: forums-1-dub ianywhere.public.general:1241
Article PK: 3486

Just as quick a response ...

*** consider treating this as a bug ***
*** or a broken database ***
*** or a failed upgrade (a.k.a. "") ***

Clearly from the documentation for ASA 7 and the same for ASA 8
state that this should work for many reasons:

"The owner of the view must be either:
a.. a DBA.

b.. a non-DBA, but also the owner of all the base table(s) referred to by
the view.

c.. a non-DBA, and not the owner of some or all of the base table(s)
referred to by the view. However, the view owner has SELECT permission WITH
GRANT OPTION on the base table(s) not owned and any other required
permission(s) WITH GRANT OPTION on the base table(s) not owned for the
operation.

Instead of the owner having permission(s) WITH GRANT OPTION on the base
table(s), permission(s) may have been granted to PUBLIC. This includes
SELECT permission on system tables."


Your explicit grants for DBA (temporarily setting DBA's special role aside
for
the moment) affirms the last condition but that should not have been
necessary,
due to the first and second conditions already being met.

ASA 7 reference: ASA Adaptive Server Anywhere User's Guide
PART 5. Database Administration and
Advanced Use
CHAPTER 23. Managing User IDs and
Permissions
Managing individual user IDs and
permissions
"Granting permissions on
views"

ASA 8 reference: Adaptive Server Anywhere Database Administration Guide
12. Managing User IDs and Permissions
Managing individual user IDs and
permissions
"Granting permissions on
views"

"Richard" <Richardr@ldsinc.com> wrote in message
news:uhtNnpdODHA.362@forums-2-dub...
> Nick,
>
> Just a quick reply...I am out of office for a couple of days so I won't be
> able to look into your other responses/solutions until later in the week.
> In response to Version EBF 8.0.2#4251 we have clients that are also on
> Version 7.0 so I am trying to find a solution that will port to 7.0 and
> 8.0...
> Thanks
> Richard
>
> "Nick Elson" <no_spam_nicelson@sybase.com> wrote in message
> news:%23PrSmwZODHA.279@forums-2-dub...
> > Hmmm ....
> > I could see how explicit grants would work but . . . .
> >
> > . . . this works for me in 8.0.2#4251 ... so you should
> > maybe try a more recent ebf of the ** SERVER **
> >
> > My repro. (derived from your information):
> > = = = = = = = = = = = = = = = = = = = =
> >
> > -- clean up if required:
> > revoke SELECT on DBA.vw_Adm_UserFeatures from SecAdmin;
> > revoke UPDATE on DBA.vw_Adm_UserFeatures from SecAdmin;
> > revoke MEMBERSHIP IN GROUP SecAdmin from vradm;
> > revoke CONNECT from vradm;
> > revoke GROUP from SecAdmin;
> > revoke CONNECT from SecAdmin;
> > drop view vw_Adm_UserFeatures;
> > drop table Sec_Verus_User;
> > drop table Sec_Control;
> > checkpoint;
> >
> > -- what is the definiton of the base table(s)?
> > -- note the datatypes substituted for UDTs
> >
> > CREATE TABLE Sec_Verus_User (
> > Verus_User_Link_Code integer PRIMARY KEY,
> > Sec_AppID INTEGER,
> > Date_Added datetime,
> > Date_Edited datetime,
> > User_Added datetime,
> > User_Edited datetime,
> > Secured_Features_Link_Code INTEGER NOT NULL,
> > UserName CHAR(254) NOT NULL,
> > Security_Mask INTEGER DEFAULT 0
> > );
> >
> > CREATE TABLE Sec_Control (
> > Sec_AppID integer PRIMARY KEY,
> > Date_Added datetime,
> > Date_Edited datetime,
> > Application_Name CHAR(254) NOT NULL
> > );
> >
> > -- what is the view's definition
> > CREATE VIEW DBA.vw_Adm_UserFeatures ()
> > AS
> > SELECT vu.Verus_User_Link_Code,vu.Security_Mask,vu.UserName,
> > sc.Application_Name
> > FROM DBA.Sec_Verus_User as vu
> > JOIN DBA.Sec_Control sc ON sc.Sec_AppID=vu.Sec_AppID;
> >
> > -- explicit permissions granted to owners
> > GRANT CONNECT TO SecAdmin;
> > GRANT GROUP TO SecAdmin;
> > GRANT CONNECT TO vradm Identified BY "ldslds";
> > GRANT MEMBERSHIP IN GROUP SecAdmin TO vradm;
> > GRANT SELECT ON DBA.vw_Adm_UserFeatures TO SecAdmin;
> > GRANT UPDATE ON DBA.vw_Adm_UserFeatures TO SecAdmin;
> >
> > -- add some data so an update is possible
> > insert into DBA.Sec_Verus_User values (
> > 1,
> > 1,
> > '2003-06-23 12:00:00.000',
> > '2003-06-23 12:00:00.000',
> > '2003-06-23 12:00:00.000',
> > '2003-06-23 12:00:00.000',
> > 1,
> > 'vradm',
> > 0
> > );
> > insert into Sec_Control values(
> > 1,
> > '2003-06-23 12:00:00.000',
> > '2003-06-23 12:00:00.000',
> > 'vradm'
> > );
> > commit;
> >
> > -- verify data with "select * from DBA.vw_Adm_UserFeatures;"
> > -- execute a rollback; to release locks before proceeding
> >
> >
> > -- login into another session of dbisql as vradm\ldslds and run this
> >
> > select * from DBA.vw_Adm_UserFeatures;
> > -- pause here a note value before continuing
> >
> > update DBA.vw_Adm_UserFeatures set security_mask=1 where
> > security_mask=0;
> > --note: how the value changes after you continue
> > -- SO no permission problems! ?
> >
> >
> > "Richard" <Richardr@ldsinc.com> wrote in message
> > news:uF4GbLZODHA.218@forums-2-dub...
> > > Nick Sorry is has taken me so long to respond...
> > >
> > > - version and ebf of the product
> > >
> > > (8.0.2.4249)
> > >
> > > - who owns the view?
> > >
> > > DBA is then owner of the Views
> > >
> > > - what it's definition is?
> > >
> > > CREATE VIEW DBA.vw_Adm_UserFeatures ()
> > >
> > > AS
> > >
> > > SELECT vu.Verus_User_Link_Code,vu.Security_Mask,vu.UserName,
> > >
> > > sc.Application_Name
> > >
> > > FROM DBA.Sec_Verus_User as vu
> > >
> > > JOIN DBA.Sec_Control sc ON sc.Sec_AppID=vu.Sec_AppID
> > >
> > >
> > >
> > > - what is the definiton of the base table(s)?
> > >
> > > CREATE TABLE Sec_Verus_User (
> > >
> > > Verus_User_Link_Code AUTOLINKCODE PRIMARY KEY,
> > >
> > > Sec_AppID INTEGER,
> > >
> > > Date_Added DATEADDED,
> > >
> > > Date_Edited DATEEDITED,
> > >
> > > User_Added USERADDED,
> > >
> > > User_Edited USEREDITED,
> > >
> > > Secured_Features_Link_Code INTEGER NOT NULL,
> > >
> > > UserName CHAR(254) NOT NULL,
> > >
> > > Security_Mask INTEGER DEFAULT 0
> > >
> > > );
> > >
> > > CREATE TABLE Sec_Control (
> > >
> > > Sec_AppID AUTOLINKCODE PRIMARY KEY,
> > >
> > > Date_Added DATEADDED,
> > >
> > > Date_Edited DATEEDITED,
> > >
> > > Application_Name CHAR(254) NOT NULL
> > >
> > > );
> > >
> > >
> > >
> > > - explicit permissions granted to owners
> > >
> > > GRANT CONNECT TO SecAdmin;
> > >
> > > GRANT GROUP TO SecAdmin;
> > >
> > > GRANT CONNECT TO vradm Identified BY "ldslds";
> > >
> > > GRANT MEMBERSHIP IN GROUP SecAdmin TO vradm;
> > >
> > > GRANT SELECT ON DBA.vw_Adm_UserFeatures TO SecAdmin;
> > >
> > > GRANT UPDATE ON DBA.vw_Adm_UserFeatures TO SecAdmin;
> > >
> > >
> > >
> > > -It works when I issue this permissions on the Table
> > >
> > > GRANT UPDATE ON DBA.Sec_Verus_User TO SecAdmin;
> > >
> > > GRANT SELECT ON DBA.Sec_Verus_User TO SecAdmin;
> > >
> > > "Nick Elson" <no_spam_nicelson@sybase.com> wrote in message
> > > news:uw9Q2edNDHA.362@forums-2-dub...
> > > > One would probably need to know these facts first:
> > > >
> > > > - version and ebf of the product
> > > > - who owns the view?
> > > > - what it's definition is?
> > > > - what is the definiton of the base table(s)?
> > > > - explicit permissions granted to owners
> > > >
> > > > to answer this.
> > > >
> > > > Most likely the answer will be that the owner of the view has not
> > > > been granted (explicitly) sufficient rights to permit that access
> > > > the base tables.
> > > >
> > > >
> > > > 05/28 15:10:20.417 ** REQUEST conn: 26502088 STMT_PREPARE
> > > > "UPDATE "DBA"."Sec_Verus_User" SET "Security_Mask"=? WHERE
> > > > "Verus_User_Link_Code"=? AND "Security_Mask"=?"
> > > > 05/28 15:10:20.417 ** DONE conn: 26502088 STMT_PREPARE
> > > > Stmt=65541
> > > > 05/28 15:10:20.417 ** REQUEST conn: 26502088 STMT_EXECUTE
> > > > Stmt=65541
> > > > 05/28 15:10:20.417 ** ERROR conn: 26502088 code: -121
"Permission
> > > > denied: you do not have permission to update "Sec_Verus_User""
> > > > 05/28 15:10:20.418 ** DONE conn: 26502088 STMT_EXECUTE
> > > > "Richard" <Richardr@ldsinc.com> wrote in message
> > > > news:evMOQgeMDHA.279@forums-2-dub...
> > > > > I have an App where I am restricting the user's "table access" to
> > NONE.
> > > > > (what I mean is I am not giving any table access to any users) I
> have
> > > > Stored
> > > > > Procedures where I Add/Update/Delete from the tables and GRANT
Exec
> > > access
> > > > > to those SP's All of my conditions work except for when I update
> > through
> > > a
> > > > > VIEW with a Disconnected Recordset. The reason I am using the
VIEW
> is
> > > > > because ADO will not let you use a SP with a Disconnected
Recordset.
> > My
> > > > > Error happens with the following code:
> > > > >
> > > > > Set rstTemp.ActiveConnection = ADOConnect1.Connection
> > > > > rstTemp.UpdateBatch <<<ERROR OCCURS HERE
> > > > >
> > > > > [Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or
> access
> > > > > violation: Permission denied: you do not have permission to update
> > > > "MYTABLE"
> > > > >
> > > > > My VIEW has GRANT ALL access. The Error goes away when I give
Table
> > > > Access
> > > > > to the User for the underlying table. I have included a Request
> Level
> > > Log
> > > > > for my errors.
> > > > >
> > > > > Can someone please help!
> > > > >
> > > > > Thanks
> > > > > Richard
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>