Database Reference
In-Depth Information
changed_by | frank
changed_at | 2012-01-25 15:44:43.314903
salary_op | UPDATE
emp_name | Bob
old_salary | 1100
new_salary | 1300
-[ RECORD 5 ]--------------------------
changed_by | frank
changed_at | 2012-01-25 15:44:43.314903
salary_op | UPDATE
emp_name | Mary
old_salary | 1000new_salary | 1200
On the other hand, you may not want anybody to have direct access to the salary
table, in which case you can perform the following:
REVOKE ALL ON salaries FROM PUBLIC;
Also, give users access to only two functions: the first is for any user looking at salar-
ies and the other is for changing salaries, which is available only to managers.
The functions themselves will have all the access to underlying tables because they
are declared as
SECURITY DEFINER
, which means they run with the privileges of
the user who created them.
The salary lookup function will look like the following:
CREATE OR REPLACE FUNCTION get_salary(text)
RETURNS integer
AS $$
-- if you look at other people's salaries,
it gets logged
INSERT INTO
salary_change_log(salary_op,emp_name,new_salary)
SELECT 'SELECT',emp_name,salary
FROM salaries
WHERE upper(emp_name) = upper($1)
