RE: Granted privileges

        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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] 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

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l