Revoking access rights

Database
Enthusiast

Revoking access rights

Hello,

 

Is there any solution to remove user rights from an object(table) if they were granted at the database level?

 

Thanks

5 REPLIES
Senior Apprentice

Re: Revoking access rights

Hi,

Not directly, because the dbms treats database level and table level as different access rights = different rows in the dbc.accessrights table. And you can't revoke something that is not there.

 

To provide access to all objects in a database except 'one' you can either:

- grant access at the table level for all relevant tables

or

- move the 'one' to another database.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Revoking access rights

You could create a role that includes specific access to specific tables and grant that role to the user.

Senior Apprentice

Re: Revoking access rights

You can use Roles (and generally you should) but you still have the same problem as originally asked about.

 

If a Role has a privilege at the database level then you cannot revoke that privilege at the table/object level.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Revoking access rights

True.  That's why I mentioned giving specific table rights to the role.

Senior Apprentice

Re: Revoking access rights

You did, that's true.

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com