Hide a field in an SQL query

Question

Hide a field in an SQL query

Navigation

#1 by (2 votes)
#2 by (1 votes)

0

I have a SQL Server 2016 database and I need to hide the password field of a table, the idea is that with a SELECT can only be accessed by a specific user, I have no idea how to do it and I have only found with encryption using INSERT , but it's not what I need.

CREATE TABLE usuarios (
    nombre VARCHAR(50) NOT NULL,
    rut VARCHAR(15) NOT NULL,
    email VARCHAR(50) NOT NULL,
    pass VARCHAR(15)NOT NULL ,
    edad INT ,
    direccion VARCHAR(50) ,
    telefono VARCHAR(12),
    comuna VARCHAR(15),
    fecha_creacion DATETIME not null default getdate(),
    constraint pk_email primary key(email),
    constraint uq_rut unique(rut));

I hope you can guide me with documentation or code.

sql-server

asked by zhet 07.12.2016 в 16:25

source

2 answers

1

The approach you must take is different.

Security is not done in the table itself.

Security policies must be created at the user level. In this way you can give access to that table to the users you need.

Now, if you need other users to see data from that table but can not see the password column, you can 1) change the structure of the tables (put the data "visible to all" in one and the data " private "in another table available for only a few) or 2) create a view that queries that table to just the columns you need. So you give users permission only in sight, NOT the table.

For what you ask I recommend you investigate the following topics logins , users , roles at the database level . Closely related to these issues are the schemas and server-level roles

answered by 07.12.2016 в 16:50

C # is frozen and does not accept new changes How can I push a javascript fix of type {}?

score 2 · Accepted Answer

Yes you can, but it must be through the creation of a Rol

--Creación del usuario (en tu caso, es posible que esto ya exista, solo es un caso demostrativo)
CREATE USER SinPermisoContrasenaUser WITHOUT LOGIN;
GO 

--Creación del Rol
CREATE ROLE PermisosContrasenaRol;
GO 

--Asignación del usuario al Rol
EXEC sp_addrolemember @membername = 'SinPermisoContrasenaUser', @rolename = 'PermisosContrasenaRol';
GO 

--Quitar permisos de SELECT al Rol
DENY SELECT ON dbo.Users ([Password]) TO PermisosContrasenaRol;