7

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?

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
TML
  • 1,374
  • 13
  • 21

6 Answers6

8

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.

garik
  • 6,782
  • 10
  • 44
  • 56
5

This is an SQL standard feature. "Most" SQL implementations support it, including PostgreSQL, MySQL, Oracle, DB2, SQL Server.

marc_s
  • 9,052
  • 6
  • 46
  • 52
Peter Eisentraut
  • 10,723
  • 1
  • 35
  • 35
3

In Oracle this is called Column Masking.

Gaius
  • 11,238
  • 3
  • 32
  • 64
1

MySQL can also perform GRANTs and REVOKEs on column privileges.

In fact, the mysql.columns_priv and information_schema.COLUMN_PRIVILEGES contain those privileges at that level.

mysql> show create table mysql.columns_priv\G
************* 1. row *************
Table: columns_priv
Create Table: CREATE TABLE columns_priv (
Host char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
Db char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
User char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
Table_name char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
Column_name char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
Column_priv set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '',
PRIMARY KEY (Host,Db,User,Table_name,Column_name)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges'

mysql> show create table information_schema.column_privileges\G
************* 1. row *************
Table: COLUMN_PRIVILEGES
Create Table: CREATE TEMPORARY TABLE COLUMN_PRIVILEGES (
GRANTEE varchar(81) NOT NULL DEFAULT '',
TABLE_CATALOG varchar(512) NOT NULL DEFAULT '',
TABLE_SCHEMA varchar(64) NOT NULL DEFAULT '',
TABLE_NAME varchar(64) NOT NULL DEFAULT '',
COLUMN_NAME varchar(64) NOT NULL DEFAULT '',
PRIVILEGE_TYPE varchar(64) NOT NULL DEFAULT '',
IS_GRANTABLE varchar(3) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8

RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536
1

As @igor said SQL Server has that capability. I think back to Sybase times i.e. version 4.2. I'm sure later (perhaps SQL-Server 7.0) I heard recommendation not to use that feature, but to use views or stored procedures instead,

bernd_k
  • 12,369
  • 24
  • 79
  • 111
0

I thought this was usually done by creating a view and granting select on the view.

As I recall the harder question relates whether one can grant insert and/or update on the view in a meaningful way. (Obviously the view must have all columns which would be required to maintain referential integrity constraints on the underlying table's schema).

However this is all academic for me. I'd love to read some details from people who used views to manage insert/update access to a subset of columns using a view ... or how various vendor implement these columnar access grants.

Jim Dennis
  • 775
  • 1
  • 7
  • 12