On Tue, 2006-06-06 at 10:43, Michael Bayer wrote:
> On Jun 5, 2006, at 11:50 PM, William K. Volkman wrote:
>
> > Users of the tables, particularly web applications, are
> > only granted the minimum SQL privileges necessary for their
> > tasks. The capability to execute DDL operations would
> > specifically be prohibited. It is only in small personal
> > type projects or, at most, initial development that that type
> > of interaction would occur. For most deployed applications
> > the "db.create(table)" would/should never (for security
> > reasons) be possible.
>
> you understand that the issue is not involving DDL operations at all,
> it merely involves that a user, who has select access to a particular
> table, should be able to view information about the columns and
> constraints that comprise that table, right ?
Actually, from a security stand point, possibly/probably not.
>From the deployed application stand point, being able to
query table attributes also doesn't make much sense.
Only from the generic database interface application does
it make sense, and those types of applications tend to
be niche ones. Once an application is designed, the tables
and columns don't change much, why do they need to be able
to queried? If suddenly the arglbargle column is added
to a table would the deployed application really be able
to do something meaningful with it?
> it makes no sense that
> information_schema will reveal the names and datatypes of a
> particular table's columns to a user, but not its primary key
> constraints. are you saying this is by design ?
In theory the primary key and other constraints should be of
no interest to most application programs. The fact that
the database engine can change them at any time, even during
the duration of a long running application, applications
are required to not depend on that information.
The fact that they've gone to the trouble to constrain certain
fields to only be visible to the table owner
indicates that they've done it on purpose. I.E. As Owner:
sat=# select column_name, column_default
from information_schema.columns
where table_name = 'departments';
column_name | column_default
-----------------+-------------------------------------------------------
department_id | nextval('public.departments_department_id_seq'::text)
department_name |
As a non-owner:
column_name | column_default
-----------------+----------------
department_id |
department_name |
One reasoning probably lies in commercial deployment of the
database. Don't permit the users to reverse engineer the
database or give out information that could be used to break
it.
btw. I'm playing devil's advocate here, from a agile programming
perspective we're interested in these things. However stepping
back from a incremental development perspective to the finished
application one, there is no longer the need to dynamically
query this information. Plus we have the problem of when
to query and how often to update it.
Consider this, if my application at start up retrieves the
default values for a column and then explicitly executes
those defaults instead of letting the DB backend do it,
then in a long running application the DBA has lost capability.
Perhaps the default value for a column changes based on
time of day.
Web applications exacerbate the problem, something that
starts up, does something, then shuts down. Do we really
want/need the overhead of fetching table and column
information each time?
Cheers,
William.
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
