in which database software / language can we have a database accessible by multiple users except its two columns to be accessed only by admin user

Question

in which database software / language is it possible to create a database accessible by multiple users except its two columns to be accessed only by admin user. please give details how this database can be created.

Answer 1

Here is an example (from Oracle) of using a view to control access to shield access to some columns in a database. Note that we can further restrict which columns can be updated.

SQL> conn apc/apc
Connected.
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                     NOT NULL VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> create or replace view v_emp as
  2  select empno, ename, job, mgr, hiredate, deptno from emp
  3  /

View created.

SQL> grant select, insert, update (job, mgr, deptno) on v_emp to a
  2  /

Grant succeeded.

SQL> conn a/a
Connected.
SQL> create synonym emp for apc.v_emp
  2  /

Synonym created.

SQL> select * from emp where deptno = 10
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE      DEPTNO
---------- ---------- --------- ---------- --------- ----------
      7782 BOEHMER    MANAGER         7839 09-JUN-81         10
      7839 SCHNEIDER  PRESIDENT            17-NOV-81         10
      7934 KISHORE    CLERK           7782 23-JAN-82         10

SQL> update emp set deptno = 40 where empno = 7934
  2  /

1 row updated.

SQL> insert into emp values (8000, 'APC', 'DOGSBODY', 7934, sysdate, 40)
  2  /

1 row created.

SQL> update emp set hiredate = sysdate-720 where empno = 7934
  2  /
update emp set hiredate = sysdate-720 where empno = 7934
       *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> delete from emp where empno = 7934
  2  /
delete from emp where empno = 7934
            *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>

This is a relatively straightforward example because the view is one-to-one with the table and the shielded columns are optional.; If the shielded columns had been defined as NOT NULL then I would need an INSTEAD OF trigger to default or derive values on INSERT (or I would have to withold the INSERT privilege).

Answer 2

In most DBMS like (Oracle, Mysql, SQL server...) you can grant users access to any column you want or revoke any permission.

Answer 3

Oracle can do this using views, but the more "proper" way would be to use Column-Level Virtual Private Database with column-masking behavior, in which sensitive columns appear as null to non-privileged users.