RE: Granted privileges
- From: "Goulet, Richard" <Richard.Goulet@xxxxxxxxxxx>
- To: <cicciuxdba@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Mar 2009 10:49:41 -0400
Alan,
It has been years since I toyed with MySql. Regrettably I
really did not like the little monster and especially it's dual
licensing model. Anyway, I had to do some looking in Sql*Server,
PostgreSql, and the ANSI Standard to answer you (isn't the internet
wonderful, especially Google). Granting select access as MySql does
appears to be peculiar to that system (I hate to call MySql an RDBMS or
even a DBMS but more a file manager since each object is a file). It is
also contrary to the ANSI Standard in that:
"Each privilege is represented by a privilege descriptor. A privilege
descriptor contains:
- the identification of the table, column, domain, character
set,
collation, or translation that the descriptor describes;
- the <authorization identifier> of the grantor of the
privilege;
- the <authorization identifier> of the grantee of the
privilege;
- identification of the action that the privilege allows; and
- an indication of whether or not the privilege is grantable.
"
ISO/IEC 9075:1992, Database Language SQL- July 30, 1992
That being said, each DBMS vendor is allowed to add extensions to the
standard and some, in particular Microslop Sql*Server have diverged from
basic parts of the standard. In particular I find objection with
Oracle's interpretation of the DROP object statement. Most other DBMS's
reply with no error if the object to be dropped does not exist, but
Oracle does. Now the ANSI Standard does leave the handling of this to
the individual vendor by not specifying how to handle it.
Bottom line, each vendor has his/her own ways to handle some of these
things, depending on how compliant to the ANSI standard they want to be.
If I'm not mistaken, MySql is only "entry" level compliant whereas I
believe Oracle is "fully" compliant.
Dick Goulet
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Guillermo Alan Bort
Sent: Tuesday, March 10, 2009 8:27 AM
To: oracle-l
Subject: Granted privileges
Hey everyone,
Yesterday I was toying arround with a MySQL (and toying is about
everything I could do) and I found a rather interesting thing.
I created a database 'db1' and a user 'db1', obviously (as I'm an
oracle dba) user db1 had full privileges on database db1, however I
then created another user (to run the authentication side of the
application) and then granted just select on the tables of database
db1 (I then removed this privilege and added select just on the users
table). But the interesting thing I found is that I can grant a user
privileges on database level. In Oracle terms it would be like
granting a user privileges over a schema (not owned by it). This is
not possible in Oracle (afaik), object level privileges have to bee
granted for every object. I've found some workarrounds over the years
but never a simple solution like GRANT SELECT ON ANY TABLE FROM
<schema> TO <user>; which would be awesome ^_^ (yes, I know about
synonyms, triggers and everything, I've done it, it's just that It
seems a little too much work for something so simple).
so... I'd like to hear everyone's opinion on this (I know there are
some great DBAs arround this list and I always appreciate your
comments).
Have fun, life's too short
Alan Bort
Oracle Certified Professional
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
