Hi All,

On II 9.2.0 (a64.lnx/143)NPTL + 13556...

How do you make a database private using an SQL command? Or an OS command?

And here's an interesting thing:

create a public database called bowtest, and then...
sql iidbdb <<SQL_END
grant noaccess on database bowtest to public;
\p\g
SQL_END

Now when I use accessdb to check on the databases access it says private. Pity the iidatabase catalog has access = 17.

It appears that if accessdb sees no access to public it says the database is private regardless of the setting of access field.

Martin Bowes

Despite what you might think from the name the "access" column isn't
telling you about whether a database is private or not. Here's the full
list of flags for that column:



i4 du_access; /* Database's accessibility
status. */

##define DU_GLOBAL 1 /* This is a global db. */

##define DU_RES_DDB 2 /* Unused, moved to dbservice

** as DU_1SER_DDB(alexc) */

##define DU_DESTROYDB 4 /* Db was/is in process of being

** destroyed. */

##define DU_CREATEDB 8 /* Db was/is in process of

** being created. */

##define DU_OPERATIVE 16 /* Db is operative, i.e. is

** accessible to users. */

##define DU_CONVERTING 32 /* This database was created in

** release 5 and has not yet

** been converted to r6

** compatibility. */

##define DU_UPGRADING 64 /* The database was created via

** an earlier version of r6 and

** is in the process of being

** upgraded to the current r6,

** or the upgradedb attempt was

** made and failed. */

##define DU_MLSDB 128 /* Database created with MLS
(B1) security */

##define DU_DESTROYDBNOWAIT 256 /* don't wait during destroydb
if

** the database is busy */

##define DU_PRODUCTION 512 /* Production mode */

##define DU_NOBACKUP 1024 /* No online checkpoints */

##define DU_RDONLY 2048 /* Database is readonly */

##define DU_MUSTLOG 4096 /* Database must be logged */



This is from src/common/hdr/hdr/dudbms.qsh - DU_DATABASE structure which
is the structure used for iidatabase. As you can see there's nothing in
there about public/private databases. DU_OPERATIVE means database is
generally available - when it's not set you get:



E_US0014 Database not available at this time.

o The database may be marked inoperable. This can occur if

CREATEDB failed.

o An exclusive database lock may be held by another session.

o The database may be open by an exclusive (/SOLE) DBMS server.



HTH

Paul



From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 13 September 2010 12:03
To: Ingres and related product discussion forum
Subject: [Info-Ingres] making a database private in SQL / Accessdb bug



Hi All,



On II 9.2.0 (a64.lnx/143)NPTL + 13556...



How do you make a database private using an SQL command? Or an OS
command?



And here's an interesting thing:



create a public database called bowtest, and then...

sql iidbdb <<SQL_END

grant noaccess on database bowtest to public;

\p\g

SQL_END



Now when I use accessdb to check on the databases access it says
private. Pity the iidatabase catalog has access = 17.



It appears that if accessdb sees no access to public it says the
database is private regardless of the setting of access field.



Martin Bowes

Hi Paul,

Whats DU_GLOBAL mean then? I've interpreted that to mean that if set the database is public, otherwise its private.

Marty

From: Paul Mason [mailto:Paul.Mason (AT) ingres (DOT) com]
Sent: 13 September 2010 12:35
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] making a database private in SQL / Accessdb bug

Despite what you might think from the name the "access" column isn't telling you about whether a database is private or not. Here's the full list of flags for that column:

i4 du_access; /* Database's accessibility status.*/
##define DU_GLOBAL 1 /* This is a global db. */
##define DU_RES_DDB 2 /* Unused, moved to dbservice
** as DU_1SER_DDB(alexc) */
##define DU_DESTROYDB 4 /* Db was/is in process of being
** destroyed. */
##define DU_CREATEDB 8 /* Db was/is in process of
** being created. */
##define DU_OPERATIVE 16 /* Db is operative, i.e. is
** accessible to users. */
##define DU_CONVERTING 32 /* This database was created in
** release 5 and has not yet
** been converted to r6
** compatibility. */
##define DU_UPGRADING 64 /* The database was created via
** an earlier version of r6 and
** is in the process of being
** upgraded to the current r6,
** or the upgradedb attempt was
** made and failed. */
##define DU_MLSDB 128 /* Database created with MLS (B1) security */
##define DU_DESTROYDBNOWAIT 256 /* don't wait during destroydb if
** the database is busy */
##define DU_PRODUCTION 512 /* Production mode */
##define DU_NOBACKUP 1024 /* No online checkpoints */
##define DU_RDONLY 2048 /* Database is readonly */
##define DU_MUSTLOG 4096 /* Database must be logged */

This is from src/common/hdr/hdr/dudbms.qsh - DU_DATABASE structure which isthe structure used for iidatabase. As you can see there's nothing in thereabout public/private databases. DU_OPERATIVE means database is generally available - when it's not set you get:

E_US0014 Database not available at this time.
o The database may be marked inoperable. This can occur if
CREATEDB failed.
o An exclusive database lock may be held by another session.
o The database may be open by an exclusive (/SOLE) DBMS server.

HTH
Paul

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Martin Bowes
Sent: 13 September 2010 12:03
To: Ingres and related product discussion forum
Subject: [Info-Ingres] making a database private in SQL / Accessdb bug

Hi All,

On II 9.2.0 (a64.lnx/143)NPTL + 13556...

How do you make a database private using an SQL command? Or an OS command?

And here's an interesting thing:

create a public database called bowtest, and then...
sql iidbdb <<SQL_END
grant noaccess on database bowtest to public;
\p\g
SQL_END

Now when I use accessdb to check on the databases access it says private. Pity the iidatabase catalog has access = 17.

It appears that if accessdb sees no access to public it says the database is private regardless of the setting of access field.

Martin Bowes

You're right - that occurred to me when I was out at lunch. Apologies
for the mis-direct - my mistake.



I not sure that what accessdb shows is unreasonable though.



Cheers

Paul



From: Martin Bowes [mailto:martin.bowes (AT) ctsu (DOT) ox.ac.uk]
Sent: 13 September 2010 13:02
To: Ingres and related product discussion forum
Cc: Paul Mason
Subject: RE: [Info-Ingres] making a database private in SQL / Accessdb
bug



Hi Paul,



Whats DU_GLOBAL mean then? I've interpreted that to mean that if set the
database is public, otherwise its private.



Marty



From: Paul Mason [mailto:Paul.Mason (AT) ingres (DOT) com]
Sent: 13 September 2010 12:35
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] making a database private in SQL / Accessdb
bug



Despite what you might think from the name the "access" column isn't
telling you about whether a database is private or not. Here's the full
list of flags for that column:



i4 du_access; /* Database's accessibility
status. */

##define DU_GLOBAL 1 /* This is a global db. */

##define DU_RES_DDB 2 /* Unused, moved to dbservice

** as DU_1SER_DDB(alexc) */

##define DU_DESTROYDB 4 /* Db was/is in process of being

** destroyed. */

##define DU_CREATEDB 8 /* Db was/is in process of

** being created. */

##define DU_OPERATIVE 16 /* Db is operative, i.e. is

** accessible to users. */

##define DU_CONVERTING 32 /* This database was created in

** release 5 and has not yet

** been converted to r6

** compatibility. */

##define DU_UPGRADING 64 /* The database was created via

** an earlier version of r6 and

** is in the process of being

** upgraded to the current r6,

** or the upgradedb attempt was

** made and failed. */

##define DU_MLSDB 128 /* Database created with MLS
(B1) security */

##define DU_DESTROYDBNOWAIT 256 /* don't wait during destroydb
if

** the database is busy */

##define DU_PRODUCTION 512 /* Production mode */

##define DU_NOBACKUP 1024 /* No online checkpoints */

##define DU_RDONLY 2048 /* Database is readonly */

##define DU_MUSTLOG 4096 /* Database must be logged */



This is from src/common/hdr/hdr/dudbms.qsh - DU_DATABASE structure which
is the structure used for iidatabase. As you can see there's nothing in
there about public/private databases. DU_OPERATIVE means database is
generally available - when it's not set you get:



E_US0014 Database not available at this time.

o The database may be marked inoperable. This can occur if

CREATEDB failed.

o An exclusive database lock may be held by another session.

o The database may be open by an exclusive (/SOLE) DBMS server.



HTH

Paul



From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Martin Bowes
Sent: 13 September 2010 12:03
To: Ingres and related product discussion forum
Subject: [Info-Ingres] making a database private in SQL / Accessdb bug



Hi All,



On II 9.2.0 (a64.lnx/143)NPTL + 13556...



How do you make a database private using an SQL command? Or an OS
command?



And here's an interesting thing:



create a public database called bowtest, and then...

sql iidbdb <<SQL_END

grant noaccess on database bowtest to public;

\p\g

SQL_END



Now when I use accessdb to check on the databases access it says
private. Pity the iidatabase catalog has access = 17.



It appears that if accessdb sees no access to public it says the
database is private regardless of the setting of access field.



Martin Bowes