Making a database readonly
Can anybody help me here - there must be a simple command to make a database readonly. Thanks L Lee Aholima, Database Administrator Thames-Coromandel District Council Ph 07 868 6025 Email lee.aholima@tcdc.govt.nz Mobile 0274333915 The contents of this e-mail maybe CONFIDENTIAL OR LEGALLY PRIVILEGED, & is intended only for the persons named above. If this e-mail is not addressed to you, you must not use, read, distribute or copy this document. If you have received this document by mistake, please call us and destroy the original. Thank you.
The only way to make a database readonly is to revoke all permissions
for all non-system tables, then grant select to public (or whomever) on all
your tables.
You can get a list of all your tables by performing the following select:
select tabname from systables where tabid > 99
Save that output to a file, modify the file to perform the revokes, save as
revoke.sql, then modify that file to perform the grant statements and save as
grant.sql. Dbaccess the files in that order and, poof, readonly database.
Take care.
Clifton
Lee Aholima <lee.aholima@tcdc.govt.nz> wrote:
Can anybody help me here - there must be a simple command to make a
database readonly.
Thanks
L
Lee Aholima,
Database Administrator
Thames-Coromandel District Council
Ph 07 868 6025
Email lee.aholima@tcdc.govt.nz
Mobile 0274333915
The contents of this e-mail maybe CONFIDENTIAL OR LEGALLY PRIVILEGED, &
is intended only for the persons named above. If this e-mail is not
addressed to you, you must not use, read, distribute or copy this
document. If you have received this document by mistake, please call us
and destroy the original. Thank you.
Lee Aholima said: > Can anybody help me here - there must be a simple command to make a > database readonly. Stick it on optical media? There is no simple command -- log a feature request with Tech Support. > The contents of this e-mail maybe CONFIDENTIAL OR LEGALLY PRIVILEGED, & > is intended only for the persons named above. If this e-mail is not > addressed to you, you must not use, read, distribute or copy this > document. If you have received this document by mistake, please call us > and destroy the original. Thank you. This is a totally unenforceable waste of bandwidth. Still, at least it's not MIME. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche "I'm trying to see things your way, but I can't get my head up my ass" - JCH "Ogni uomo mi guarda come se fossi una testa di cazzo" - Marco http://www.catb.org/~esr/faqs/smart-questions.html
--0__=09BBE584DFD9852A8f9e8a93df938690918c09BBE584DFD9852A
Content-type: multipart/alternative;
Boundary="1__=09BBE584DFD9852A8f9e8a93df938690918c09BBE584DFD9852A"
--1__=09BBE584DFD9852A8f9e8a93df938690918c09BBE584DFD9852A
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: quoted-printable
Or doing somthing like..
t.sh -------------
#!/bin/ksh
##
##
DB=3D$1
echo 'select tabname from systables where tabid > 99 and tabtype =3D "T=
" ' \\\\
| dbaccess -e ${DB} \\\\
| awk '{ if ($1 =3D=3D "tabname")
{ printf("revoke all on %s from public;\\
", $2)
printf("grant select on %s to public;\\
", $2)
}}' \\\\
| dbaccess -e ${DB}
and then invoking it by t.sh <dbname>
=
"Clifton M. Bean" =
<cmbean@sbcglobal =
.net> =
To
Sent by: ids@iiug.org =
forum.subscriber@ =
cc
iiug.org =
Subj=
ect
Re: Making a database readonly =
09/22/2004 12:50 [3457] =
AM =
=
=
=
=
=
The only way to make a database readonly is to revoke all permissions f=
or
all non-system tables, then grant select to public (or whomever) on all=
your tables.
You can get a list of all your tables by performing the following selec=
t:
select tabname from systables where tabid > 99
Save that output to a file, modify the file to perform the revokes, sav=
e as
revoke.sql, then modify that file to perform the grant statements and s=
ave
as grant.sql. Dbaccess the files in that order and, poof, readonly
database.
Take care.
Clifton
Lee Aholima <lee.aholima@tcdc.govt.nz> wrote:
Can anybody help me here - there must be a simple command to make a
database readonly.
Thanks
L
Lee Aholima,
Database Administrator
Thames-Coromandel District Council
Ph 07 868 6025
Email lee.aholima@tcdc.govt.nz
Mobile 0274333915
The contents of this e-mail maybe CONFIDENTIAL OR LEGALLY PRIVILEGED, &=
is intended only for the persons named above. If this e-mail is not
addressed to you, you must not use, read, distribute or copy this
document. If you have received this document by mistake, please call us=
and destroy the original. Thank you.
=
--1__=09BBE584DFD9852A8f9e8a93df938690918c09BBE584DFD9852A
Content-type: text/html; charset=US-ASCII
Content-Disposition: inline
Content-transfer-encoding: quoted-printable
<html><body>
<p>Or doing somthing like..<br>
<br>
t.sh -------------<br>
#!/bin/ksh<br>
##<br>
##<br>
<br>
DB=3D$1<br>
echo 'select tabname from systables where tabid > 99 and tabtype =3D=
"T" ' \\\\<br>
| dbaccess -e ${DB} \\\\<br>
| awk '{ if ($1 =3D=3D "tabname")<br>
{ printf("revoke all on %s from public;\\
", $2)<b=
r>
printf("grant select on %s to public;\\
", $2)<b=
r>
}}' \\\\<br>
| dbaccess -e ${DB}<br>
<br>
and then invoking it by t.sh <dbname><br>
<br>
<br>
<br>
<img src=3D"cid:10__=3D09BBE584DFD9852A8f9e8a93df938@us.ibm.com" width=3D=
"16" height=3D"16" alt=3D"Inactive hide details for "Clifton M. Be=
an" <cmbean@sbcglobal.net>">"Clifton M. Bean" <=
cmbean@sbcglobal.net><br>
<br>
<br>
<table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">=
<tr valign=3D"top"><td style=3D"background-image:url(cid:20__=3D09BBE58=
4DFD9852A8f9e8a93df938@us.ibm.com); background-repeat: no-repeat; " wid=
th=3D"40%">
<ul>
<ul>
<ul>
<ul><b><font size=3D"2">"Clifton M. Bean" <cmbean@sbcgloba=
l.net></font></b><font size=3D"2"> </font><br>
<font size=3D"2">Sent by: forum.subscriber@iiug.org</font>
<p><font size=3D"2">09/22/2004 12:50 AM</font></ul>
</ul>
</ul>
</ul>
</td><td width=3D"60%">
<table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">=
<tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3=
0__=3D09BBE584DFD9852A8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"=
1" width=3D"58" alt=3D""><br>
<div align=3D"right"><font size=3D"2">To</font></div></td><td width=3D"=
100%"><img src=3D"cid:30__=3D09BBE584DFD9852A8f9e8a93df938@us.ibm.com" =
border=3D"0" height=3D"1" width=3D"1" alt=3D""><br>
<font size=3D"2">ids@iiug.org</font></td></tr>
<tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3=
0__=3D09BBE584DFD9852A8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"=
1" width=3D"58" alt=3D""><br>
<div align=3D"right"><font size=3D"2">cc</font></div></td><td width=3D"=
100%"><img src=3D"cid:30__=3D09BBE584DFD9852A8f9e8a93df938@us.ibm.com" =
border=3D"0" height=3D"1" width=3D"1" alt=3D""><br>
</td></tr>
<tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3=
0__=3D09BBE584DFD9852A8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"=
1" width=3D"58" alt=3D""><br>
<div align=3D"right"><font size=3D"2">Subject</font></div></td><td widt=
h=3D"100%"><img src=3D"cid:30__=3D09BBE584DFD9852A8f9e8a93df938@us.ibm.=
com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><br>
<font size=3D"2">Re: Making a database readonly [3457]</font></td></t=
r>
</table>
<table border=3D"0" cellspacing=3D"0" cellpadding=3D"0">
<tr valign=3D"top"><td width=3D"58"><img src=3D"cid:30__=3D09BBE584DFD9=
852A8f9e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=
=3D""></td><td width=3D"336"><img src=3D"cid:30__=3D09BBE584DFD9852A8f9=
e8a93df938@us.ibm.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><=
/td></tr>
</table>
</td></tr>
</table>
<br>
<tt>The only way to make a database readonly is to revoke all permissio=
ns for all non-system tables, then grant select to public (or whomever)=
on all your tables.<br>
<br>
You can get a list of all your tables by performing the following selec=
t:<br>
<br>
select tabname from systables where tabid > 99<br><br>
Save that output to a file, modify the file to perform the revokes, sav=
e as revoke.sql, then modify that file to perform the grant statements =
and save as grant.sql. Dbaccess the files in that order and, poof=
, readonly database.<br>
<br>
Take care.<br>
Clifton<br>
<br>
Lee Aholima <lee.aholima@tcdc.govt.nz> wrote:<br>
Hi, I use the following selects:
select 'revoke all on ' || tabname || ' from public;'
from systables
where owner = 'dba' AND tabtype = 'T'AND tabid > 99
Execute this output. After that, execute this query:
select 'grant select on ' || tabname || ' to public;'
from systables
where owner = 'dba' AND tabtype = 'T'AND tabid > 99
After that execute this output.
Good luck
Paola
Mensaje citado por "Clifton M. Bean" <cmbean@sbcglobal.net>:
> The only way to make a database readonly is to revoke all permissions for all
> non-system tables, then grant select to public (or whomever) on all your
> tables.
>
> You can get a list of all your tables by performing the following select:
>
> select tabname from systables where tabid > 99>
> Save that output to a file, modify the file to perform the revokes, save as
> revoke.sql, then modify that file to perform the grant statements and save as
> grant.sql. Dbaccess the files in that order and, poof, readonly database.
>
> Take care.
> Clifton
>
> Lee Aholima <lee.aholima@tcdc.govt.nz> wrote:
> Can anybody help me here - there must be a simple command to make a
> database readonly.
>
> Thanks
> L
>
> Lee Aholima,
> Database Administrator
> Thames-Coromandel District Council
> Ph 07 868 6025
> Email lee.aholima@tcdc.govt.nz
> Mobile 0274333915
>
> The contents of this e-mail maybe CONFIDENTIAL OR LEGALLY PRIVILEGED, &
> is intended only for the persons named above. If this e-mail is not
> addressed to you, you must not use, read, distribute or copy this
> document. If you have received this document by mistake, please call us
> and destroy the original. Thank you.
>
>
>
>
>
>
>