January 10, 2003 at 3:36 am
I just received a request to assign column-level permissions to some of the tables in one of our databases. I have never done this before but I know how to assign it, I just want to ask a question about how it is going to work.
What levels of permissions override one another. We have a person who needs this column-level permission. She is a member of an existing role that already has certain permissions set at the object level. If I assign her permissions individually, will her rights come from her individual login or will they go to the highest level of security to which she is associated.
To continue the example, this user is a member of a role that has full permissions to add, edit, and delete data in a table. I then add her individually and give her individual login access to select only on a particular field. What kind of permissions does she have?
Or consider this example, this user has read-only rights to a table as a member of a role. I want to grant her rights to update one field in this table so I grant her this right to her individual login. What kind of permissions does she have?
Finally, does anyone know of any links where I can find more information about applying column-level permissions? I have done some searches but most of the information I'm finding is either for 6.5 or 7.0 (I'm on 2000) and even what I do find is not real helpful.
Thanks!
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
January 10, 2003 at 4:41 am
For securiy it is most restrictive of combined permissions take effect. So if you deny INSERT and allow UPDATES thru one role but have db_denywrite set shee will not be able to perform UPDATES.
However if you allow insert, updates or deletes you cannot limit the columns except thru 3 possible methods.
Method 1:
Create a view with the PK field(s) and the column she can update. (Note: She will be able to affect this value if she has update rights unless a special field such as IDENTITY based column.)
Grant UPDATE, INSERT, and/or DELETE plus SELECT to mary for the view. (Note: If you do not give her SELECT rights to the view she will not be able to perform any where condition on the view in and UPDATE or DELETE even if given access to table with SELECT, if you want to hid the table data then set SELECT here instead of table).
Method 2:
Grant SELECT rights to the table.
Use a Stored Procedure to alow her updates with a required set of variables for the identifying key that is used to be her where condition plus the column she is to affect. Great thing her is you give her EXECUTE rights and as long as the SP and the table have the same owner you won't haveto worry with permissions (should always set dbo as owner). (Note: This is the most recommended way to dea with these situations.)
Method 3:
Grant all right to the table.
Create a trigger that looks for her USER_NAME() and uses the if COLUMN_UPDATES() returns any other value than those columns you allow via BITWISE handling rollback changes and send user an error message.
(Note: this requires you never change the column positions and that you keep in mind if you change the table structure or the user access or needs you have to update the TRIGGER. Also, keep in mind transaction does occurr and is logged but rolledback if does not meet the requirements, this is better than and SP only when she must update several rows with different identifying values or alternate keys with the same data as opposed to the SP in which you have some limitations you have defined.)
Ex.
--My example table
create table marytest (
col1 int identity(1,1) not null primary key,
col2 char(3) not null,
col3 char(2) null
)
--Trigger
CREATE TRIGGER tr_mary ON dbo.marytest
FOR UPDATE
AS
/* COLUMNS_UPDATED() is based on binary position, col1 =1, col2 = 2, col3 = 4, col4 = 8 and so on. */
/* USER_NAME returns the login name of the executing user. */
/* Note: For multiple columns you have to OR values together like so and check for each possible remainder.
IF ( COLUMNS_UPDATED() ^ (2 | 4) IN (0,2,4)
So if the column is 2 or 3 or both then it is ok otherwise the value will not be in the IN statement.
*/
IF ( COLUMNS_UPDATED() ^ 2 ) != 0 AND USER_NAME() = 'mary'
BEGIN
ROLLBACK TRANSACTION /* Undo changes. */
RAISERROR ('You are only allowed to update col2.', 11, 1) /* Error to return to user. */
RETURN
END
--Will fail for mary
UPDATE marytest SET col2 = 'www', col3 = 'aa'
--WIll work for mary
UPDATE marytest SET col2 = 'www'
There are possible a few other round about ways but this is all I can think of.
January 10, 2003 at 8:52 am
I'd look hard for a way around this, I count this along with having different objects as featurs they could drop anytime. I typically use a view to restrict which cols they can see. Wont meet the needs everytime, but most often it does. Col level security..just say no!
Andy
January 10, 2003 at 9:14 am
I agree, I would like to avoid this BUT this database is for a third-party product that does not have the proper application security built into it and this request, while painful, is legitimate.
Because it's a third party system, I can't create a view to handle this.
Thanks anyway.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
January 18, 2006 at 2:34 pm
I want to do something similar. I have a test user that has read access to the database. I put deny permission on the credit card column, but I was still able to select all columns. Then I put deny select on the entire table, but can still select all columns.
What am I missing ?
January 18, 2006 at 2:52 pm
Is the user account you were testing with a member of the sysadmin fixed server role? If so, it maps in as dbo, the database owner, and bypasses all permission checks. BTW, if the user account owns the database, the same thing is true.
K. Brian Kelley
@kbriankelley
January 18, 2006 at 3:05 pm
The account I'm testing with WAS a sysadmin, but now it's just a regular user as far as I can see. However, I tried it with another test user that never had any rights, and it works ok, so maybe I missed something with the first user.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply