permissions question

Please help on the following question: I have a database with a customer sensitive data. I also have a third party company who suppose to help us with reporting from this database connecting via ODBC. I want to let them to be able to see all the tables except one - customdata. If I grant "connect" to the "newuser" it's automatically gives me a select-update table-level privileges on all the tables in this database, and if I run: "revoke update, select from newuser" it would not let me do it on a specific table. How do I secure this table, please. Sincerely, Elena. e-mail: ekorol@styleclick.com

I suspect that PUBLIC (all users) has "Default access privileges" to your table. Unless your database is "ANSI Compliant", then PUBLIC will have "Default access privileges" (Select, Insert, Update, Delete, Under) on the table, unless these have been previously REVOKEd. Tables in ANSI databases grant no default privileges to PUBLIC. In a non ANSI database, defaultprivileges can be avoided if the environment variable NODEFDAC=yes is set before the table is created. To remove the default privileges from the "newuser" from the table, you could :- REVOKE ALL ON customdata FROM PUBLIC; BUT ... It is entirely possible that all of your other database users rely on PUBLIC access to this table to be able to Select, Insert, etc. this table. In that case you will need to either directly grant access privileges to all users requiring customdata, or do the same through a ROLE. BTW - check your syntax on the REVOKE statement. HTH Brett Randall Elena Korol wrote: > > Please help on the following question: > > I have a database with a customer sensitive data. > I also have a third party company who suppose to help us with reporting from > this database connecting via ODBC. > I want to let them to be able to see all the tables except one - customdata. > > If I grant "connect" to the "newuser" it's automatically gives me a > select-update table-level privileges on all the tables in this database, and > if I run: > "revoke update, select from newuser" it would not let me do it on a specific > table. > > How do I secure this table, please. > > Sincerely, > Elena. > > e-mail: ekorol@styleclick.com