This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Hello guys, Since last two months I have published about the SQL Server Interview Questions and Answers.
During this time, I got lots of messages and requests about the PostgreSQL.
In this August – 2016, I am going to post about only PostgreSQL and will try to complete all pending requests of readers.
In this post, I am going to publish about The Row Level Security which is now a common feature of all new version of RDBMSs like SQL Server 2016, PostgreSQL 9.5.
With the concept of Row Level Security, we can restrict the user for a particular set of records.
Previously, we are doing this kind of restriction using by creating filtered VIEWS and assign related VIEWS to related Users.
PostgreSQL 9.5 has this Row Level Security feature which extends the overall security policy of the PostgreSQL.
Let me demonstrate this.
Execute below all steps using Postgres Super user:
First create two sample users:
|
1 2 |
CREATE USER abc LOGIN PASSWORD '123'; CREATE USER xyz LOGIN PASSWORD '321'; |
|
1 2 3 4 5 6 7 8 |
CREATE TABLE tbl_Employees ( EmpID INT ,EmpName CHARACTER VARYING ); INSERT INTO tbl_Employees VALUES (1,'Anvesh'),(2,'Martin'),(3,'Roy') ,(4,'Jeeny'),(5,'Kavita'),(6,'Neevan'); |
|
1 |
ALTER TABLE tbl_Employees ADD pgUser CHARACTER VARYING; |
|
1 2 |
UPDATE tbl_Employees SET pgUser = 'abc' WHERE EmpID IN (1,2,3); UPDATE tbl_Employees SET pgUser = 'xyz' WHERE EmpID IN (4,5,6); |
|
1 2 |
GRANT SELECT,UPDATE,INSERT,DELETE ON tbl_Employees TO abc; GRANT SELECT,UPDATE,INSERT,DELETE ON tbl_Employees TO xyz; |
|
1 2 |
CREATE POLICY policy_employee_user ON tbl_Employees FOR ALL TO PUBLIC USING (pgUser = current_user); |
|
1 |
ALTER TABLE tbl_Employees ENABLE ROW LEVEL SECURITY; |
|
1 |
SELECT *FROM tbl_Employees; |
|
1 2 3 4 5 |
empid empname pguser -------------------------- 1 Anvesh abc 2 Martin abc 3 Roy abc |
|
1 2 3 4 5 |
empid empname pguser -------------------------- 4 Jeeny xyz 5 Kavita xyz 6 Neevan xyz |
