Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
I just realized this should be a "community wiki" question, but I don't have enough rep to make it so... –  TML Jan 7 '11 at 18:42
    
sql server 2005, 2008... encrypting a column of data –  garik Jan 7 '11 at 18:43
    
not many things are CW anymore... –  xenoterracide Jan 9 '11 at 15:55

6 Answers 6

up vote 5 down vote accepted

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

share|improve this answer
    
Thanks, Peter - I don't have a copy of the specification, so I wasn't sure if it was covered there. I do happen to know that Firebird nee Interbase does NOT support this, and last time I tried it in MySQL, it didn't work (admittedly, that was a while ago in MySQL terms). –  TML Jan 11 '11 at 0:59

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.

share|improve this answer

In Oracle this is called Column Masking.

share|improve this answer
2  
That's not the same thing. –  Peter Eisentraut Jan 7 '11 at 20:04

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,

share|improve this answer
    
sql2k, 2k5 new future: encrypting a column of data, it allows to return the same number of columns and NULL values in columns if you have not valid key. –  garik Jan 7 '11 at 18:55

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

share|improve this answer

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.

share|improve this answer
    
Granting update and insert is very meaningful on a view. You can use triggers on views to make inserts and updates actually do something. I'm not necessarily saying that is advisable, but quite possible. –  Justin Dearing Jun 13 '11 at 16:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.