Subject Re: SQL names for user and role
--- In IB-Architect@y..., Larry Carter <lcarter_97132@y...> wrote:
> I have been following this thread somewhat and wanted
> to throw out a small idea. If this has already been
> discussed in the past then forgive me for the wasted
> bandwidth.
> I don't know what the SQL92/99 spec says,

[That's OK, you are hardly alone.]

> but what
> about Directory Services capabilities as a security
> level option for Firebird? Base LDAP would probably
> be sufficient I would think. This would give users
> the option of DB Server level security or DS based
> security. Most of the ROLES aspects etc. wouldn't
> have to change since we don't neccessarily have to
> extend the schema just use it for base authentication
> (i.e. OS user level).

If you are just thinking of using LDAP as an alternative to using OS
level authentication, then that could probably fit relatively easily
with existing SQL standards.

[Jaws drop ... she speaks something other than SQL, The One True

Existing SQL standards, as far as I know, leave the mechanism for
identifying users, or rather for mapping external users to users known
within the SQL database, entirely up to the database vendor.
Authentication via LDAP vs. OS authentication vs. Authentication via
Tarot cards is all the same, as far as the SQL standards are

Have I misunderstood where you thought LDAP would fit? If so, skip
the rest of this message ...

Here's what SQL92 says:

First, as a refresher, the SQL standard <connect statement> looks like
CONNECT TO <SQL-server name>
[ AS <connection name> ]
[ USER <user name> ] <==== that's the interesting bit

From SQL92 Concepts section, subclause 4.30, SQL-Sessions:
"An SQL-session has an <authorization identifier> that is initially
set to an implementation-defined value when the SQL-session is
started, unless the SQL-session is started as a result of successful
execution of a <connect statement>, in which case the <authorization
identifier> of the SQL-session is set to the value of the implicit or
explicit <user name> contained in the <connect statement>."

From subclause 15.1, <connect statement>:

"1) If <user name> is not specified, then an implementation-defined
<user name> for the SQL-connection is implicit."

Translating loosely, "Woo hoo!" According to the SQL standard, if you
establish a SQL session (basically, a connection to a database)
without using the SQL connect statement, then it's up to the vendor to
determine which SQL user you are; and if you establish a connection
using a connect statement but don't specify USER explicitly, then
again it's up to the vendor to determine which SQL user you are, using
any mechanism the vendor thinks might fly.

As far as I know.

> I am fairly new to Directory Services with M$ ADS
> where I have started but they are way cool. We use
> ADS for our authentication to MS SQL Server and I
> would never go back to DB level security again.
> Just a though.
> Larry
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Auctions - buy the things you want at great prices