Security bug report?

[Belu33fr]

Hi,
My issue is on the view with View_metadata. Access 2003, Sql 2000 and 2005.
Lasted SP.
Because I had some problems of read only with this type of view (only in
Access), I were looking for a solution. I founded that this type of view must
have all primary key of each table selected and the view could be a delete,
read, insert and update view. I were surprise of this because it was only in
Access.
Becaue of this, it was a possible whole of security and I decide to make
some tests.
I made a database with 3 tables. I made a view with 2 tables and with
VIEW_METADATA. I created all triggers on this view. I try it. All is fine. I
made an user without access on table but grant access on the view (except
DRI). I try it on the database. This user can not view and access directly to
the table. The only possible thing is use the view. It's fine, he can create,
modify and delete data in the database. The security is ok, the user can not
see the real database and the scheme of it.
Unfortunately, I open the view in design mode ... and surprise: I can read
all about the query, see the definitions of the tables. Of course I can not
modify the query but I can see ALL. I thought that something was wrong in
security access. Nothing directly, member of public standard group (we can
not exclude somebody from this group), nothing special in the public group
security.
Question: How msaccess can read this scheme because it has nothing granted
execpt the use of this view? Now, I stop because I dont want say more how use
this question.
Why I did not tell this directly to MS and post on this forum? Because, two
times that I told to MS the security problems (1 for Vista and another for
XP), they answered: First, you did not use a special code to do this and you
are connected to the attacked network, we can not say that it is serious.
Second, because it is by design, we can not consider that it is a security
bug from coding! For all this reason, we can not study this cases.
Yes, I agree with them: a secutity design not allowed access is not a bug of
security !!! LOL

 

[Mary Chipman [MSFT]]

If I understand you correctly, you are complaining about the fact that
Access can read the schema of the underlying view/table even though
you have revoked permissions to the data. The reason for this is that
Access must be able to read the metadata in order to pass the
appropriate SQL statements through ODBC to T-SQL in order for the
server to execute them. If this is unacceptable, you will need to
create parameterized stored procedures and execute them using
pass-through queries and/or ADO code, which is a lot more work. To
understand the problem more fully, create a Profiler trace and examine
the statements Access passes to the server for execution. HTH,

--Mary

 

[Belu33fr]

I understand well you. You are right on many points. It is normal that Access
needs some informations about data used. It is normal that Access needs
construct a correct request but do you think that Access can read schema of
all table? With access granted or not?
The problem is not that Access can read the metadata of the view (if I use
"Select N_Person " it is normal that Access can read all informations of
N_person), but Access can read the metadata of all table in the database!!!
For example, just you need put at the end of the FROM clause: "cross join
name_table" and you have access to this table. Is it normal? Why Access can
not read the table in the section table but can read it in the section view?
It is really strange! Do you want more? Do you think that it is normal that I
can not access to a table, but I can read the table schema by a view. Is it
normal? Use of procedures is not a solution, because with the view I can
access to the tables!!! Exemple SELECT * FROM dbo.nametable gives all
informations in the view designer but the dbo.nametable is not use in any
view. Strange, very strange.

 

[Mary Chipman [MSFT]]

What you see on the client (Access) depends on how you have secured
the tables and views on the database. Access cannot override any
permissions that you have revoked or denied; If Access can see schema
elements that you don't want it to, then you have overlooked some
aspect of securing those objects on the server.

--Mary

 

[Belu33fr]

I open a login with the login name of somebody who is into the public group
only. This person has no specific access on database.
On the client Acces I see nothing: no table, no schema, only the view on test.

I can open, read and write data with the view. It is the same with a form
linked with this view.

I can open this view in design mode. Strange because I dont have this access
granded. If I try this from the form, I can not (access refused). If I try
this from the design manager of SQL (with public account), I can not access.

Already in access view design, If I want add a table, access is denied but I
can see all tables and definitions: just into the sql text I need put a
"cross join tablename" !!!

Is it normal?

--Luc

 

[Mary Chipman [MSFT]]

What you see in Access depends on what permissions are defined on the
server, so it's impossible to know whether something is "normal" for
your environment or not. You can put a Profiler trace on the calls
between Access and SQL server and the design manager (SSMS?) so you
can see the login credentials being used for the two connections. HTH,

Mary