Two databases, remote synonyms and granting select (read-only) permissions

Two databases, remote synonyms and granting select (read-only) permissions

Post by MADS » Wed, 26 Aug 2009 10:12:41


Hi everybody!

We have an IDS 10 production database with hundreds of tables.

We created a new database for a read-only web application.

We created synonyms in the new database, pointing to the production
database, for the few tables that will be used in the web application.

And, we want to grant only select permissions to those synonyms for a
new "readonly" user.

We tried to grant select permissions to the synonym, and got:

556: Cannot create, drop, or modify an object that is external to
current database.

So we the granted the select permissions for the user in the original
tables, but no connect permission to the original database.

The problem is that when the user accesses the tables, he gets:

387: No connect permission.
111: ISAM error: no record found.

We don't want the user to connect to the productive database...

So, any ideas?
 
 
 

Two databases, remote synonyms and granting select (read-only) permissions

Post by Superboe » Wed, 26 Aug 2009 16:28:00


> 56: Cannot create, drop, or modify an object that is external to> > current database.> >> > So we the granted the select permissions for the user in the original> > tables, but no connect permission to the original database.> >> > The problem is that when the user accesses the tables, he gets:> >> > 87: No connect permissio>.
> 11: ISAM error: o record fo>nd>
>
> We don't want the user to connect to the productive databas>..>
>
> So, any ideas?

i guess the user needs to be able to logon.....
if that is not wanted you could set up ER or something or set up HDR
and have them connect to the readonly
secondary or???

Superboer.

 
 
 

Two databases, remote synonyms and granting select (read-only) permissions

Post by Ian Michae » Wed, 26 Aug 2009 21:33:20

Very quickly.

If you want to use a synonym, you're going to have to create connect permissions.
You're also going to have a *bit* of overhead and delays from using a remote database via a synonym. Meaning that performance will vary based on hardware, networking... and it may not be a good idea. (Assuming the database is on a different machine. If on the same machine, again performance will vary.)

If you want, create a *special* user for the web app, and only grant connect and select privileges for the web app? This accomplishes the same thing without having the additional overhead.

If you *need* to create a separate *read only* database, then ER/HDR would be the better way to go.

So if you're using the 'cheap'(er)* version of IDS that doesn't offer ER/HDR, then create the second database, grant connect and select permissions to the web app, and then do the same for your main database. I'd put the database on a different server because of the overhead. You don't say anything about your network or options, but if you can afford a high speed network card (2 of them), put one each in the database servers, create your own 10.x.x.x network segment, and use that only for database to database connection.
(1GB cards are common enough these days). This might reduce your overhead.

If you've got one of those 2 socket 8 core 5500 series Xeon chips, then split the box to two virtual servers and then you'd have less issues with the overhead.

The interesting thing is that you could subnet your databases and your external web server so that it can only see your secondary server. There are lots of ways to slice and dice this given enough of a budget. ;-)

* Note: Workgroup vs Enterprise. Workgroup is *much* cheaper if you can live within its limitations. (Many can!)

But hey!
What do I know? I still think that hydrencephali is a precursor to becoming an exec within IBM.
This would explain IBM's love affair with polysorbate 80.
(Hint: You can google the terms, but if you have to, then you don't know your underground comics from the 70's .)

-G



_________________________________________________________________
Windows Live: Make it easier for your friends to see what youe up to on Facebook.
http://windowslive.com/Campaign/SocialNetworking?ocid=PID23285::T:WLMTAGL:ON:WL:en-US:SI_SB_facebook:082009
<html>
<head>
<style>
..hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Verdana
}
</style>
</head>
<body class='hmmessage'>
Very quickly.<br><br>If you want to use a synonym, you're going to have to create connect permissions.<br>You're also going to have a *bit* of overhead and delays from using a remote database via a synonym. Meaning that performance will vary based on hardware, networking... and it may not be a good idea. (Assuming the database is on a different machine. If on the same machine, again performance will vary.)<br><br>If you want, create a *special* user for the web app, and only grant connect and select privileges for the web app? This accomplishes the same thing without having the additional overhead.<br><br>If you *need* to create a separate *read only* database, then ER/HDR would be the better way to go.<br><br>So if you're using the 'cheap'(er)* version of IDS that doesn't offer ER/HDR, then create the second database, grant connect and select permissions to the
 
 
 

Two databases, remote synonyms and granting select (read-only) permissions

Post by MADS » Thu, 27 Aug 2009 03:30:04

ummer! Both databases are in the same server...

I'll investigate to try to replicate only the few tables needed with
(sorry for the SQL Server slang, it the only db that I've replicated)
standard transactional replication (meaning the subscribers updates
won't replicate to the server).

Thank you all for your advice!

Manuel Daponte

On Aug 25, 8:33m, Ian Michael Gumby < XXXX@XXXXX.COM > wrote:
> > > 11: ISAM error: o record>fo>n>.> >
> > > We don't want the user to connect to the productive data>as>.>.> >
> > > So, any>id>a>?
>
> > i guess the user needs to be able to log>n>....
> > if that is not wanted you could set up ER or something or set>u> HDR
> > and have them connect to the r>a>only
> > secondar> o>?>?
>
> > Sup>r>oer.
> > ________________________________________>_>____
> > Informix-list maili>g>list
> > Informix-l...@i>u>.org
> >http://www.iiug.org/mailman/listinfo/inform>x->ist
>
> __________________________________________________________>______
> Windows Live: Make it easier for your friends to see what youe up to on Facebook.http://windowslive.com/Campaign/SocialNetworking?ocid=PID23285::T:WLM...- Hide quoted>te>t -
>
> - Show quoted text -

 
 
 

Two databases, remote synonyms and granting select (read-only) permissions

Post by MADS » Thu, 27 Aug 2009 03:31:35

an, is it Zippy?

In Spain he was "Zippy Cocopera"...

On Aug 25, 8:33m, Ian Michael Gumby < XXXX@XXXXX.COM > wrote:
> > > 11: ISAM error: o record>fo>n>.> >
> > > We don't want the user to connect to the productive data>as>.>.> >
> > > So, any>id>a>?
>
> > i guess the user needs to be able to log>n>....
> > if that is not wanted you could set up ER or something or set>u> HDR
> > and have them connect to the r>a>only
> > secondar> o>?>?
>
> > Sup>r>oer.
> > ________________________________________>_>____
> > Informix-list maili>g>list
> > Informix-l...@i>u>.org
> >http://www.iiug.org/mailman/listinfo/inform>x->ist
>
> __________________________________________________________>______
> Windows Live: Make it easier for your friends to see what youe up to on Facebook.http://windowslive.com/Campaign/SocialNetworking?ocid=PID23285::T:WLM...- Hide quoted>te>t -
>
> - Show quoted text -

 
 
 

Two databases, remote synonyms and granting select (read-only) permissions

Post by Ian Michae » Thu, 27 Aug 2009 04:00:38


[SNIP]


Why yes it is!

Zippy in any language is still a pinhead!

Based on what IBM is currently doing, one has to give serious credence that all IBM execs have to be pinheads or just don't care about what the company will be left with in a couple of years.

Its definitely not your father's IBM.



_________________________________________________________________
With Windows Live, you can organize, edit, and share your photos.
http://www.yqcomputer.com/
<html>
<head>
<style>
..hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Verdana
}
</style>
</head>
<body class='hmmessage'>
<br><br>> From: XXXX@XXXXX.COM <br>> Subject: Re: Two databases, remote synonyms and granting select (read-only) permissions<br>> Date: Tue, 25 Aug 2009 11:31:35 -0700<br>> To: XXXX@XXXXX.COM <br>> <br>> Ian, is it Zippy?<br>> <br>> In Spain he was "Zippy Cocopera"...<br>> <br>[SNIP]<br>> > But hey!<br>> > What do I know? I still think that hydrencephali is a precursor to becoming an exec within IBM.<br>> > This would explain IBM's love affair with polysorbate 80.<br>> > (Hint: You can google the terms, but if you have to, then you don't know your underground comics from the 70's .)<br>> ><br><br><br>Why yes it is!<br><br>Zippy in any language is still a pinhead!<br><br>Based on what IBM is currently doing, one has to give serious credence that all IBM execs have to be pinheads or just don't care about what the company will be left with in a couple of years.<br><br>Its definitely not your father's IBM.<br><br><br><br /><hr />With Windows Live, you can organize, edit, and share your photos. <a href=' http://www.yqcomputer.com/ ' target='_new'>Click here.</a></body>
</html>