HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Which database engines will allow me to GRANT/REVOKE on a specific column?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
columnenginesgrantrevokedatabaseallowwillspecificwhich

Problem

If I have a table with a single column of sensitive data, and I want to grant broad use of the table without exposing that one column, I know that I can create a VIEW that gives them access to all the non-sensitive columns. However, PostgreSQL allows you to grant column-level permissions in the form of

grant select (col1, ...coln) on table to role;


Are there other engines which provide this capability?

Solution

SQL Server 2000, 2005, 2008 has this capability

GRANT { ALL [ PRIVILEGES ] }
       | permission [ ( column [ ,...n ] ) ] [ ,...n ]
       [ ON [ class :: ] securable ] TO principal [ ,...n ] 
       [ WITH GRANT OPTION ] [ AS principal ]


ADDED

In SQL Server 2005, 2008 it is possible to encrypt a column of data by using symmetric encryption, see B.Symmetric encryption that includes an authenticator. This variant returns a NULL value, just as if the wrong key was used.

Code Snippets

GRANT { ALL [ PRIVILEGES ] }
       | permission [ ( column [ ,...n ] ) ] [ ,...n ]
       [ ON [ class :: ] securable ] TO principal [ ,...n ] 
       [ WITH GRANT OPTION ] [ AS principal ]

Context

StackExchange Database Administrators Q#394, answer score: 8

Revisions (0)

No revisions yet.