Обсуждение: log select access
Hello everybody,
For PCI compliance I need to log user access to my PCI columns in a table and retain for 2 years.
I know I can grep the log, but with 1m log rows/day and infrequent PCI access, I’m thinking this isn’t the most efficient method.
I’ve been thinking about a SELECT rule, for the access views defined on the tables with PCI columns.
I reviewed the doc but belive I’m constrained
I would like to create a select rule that would log the statement in addition to executing the select.
“Presently, ON SELECT rules must be unconditional INSTEAD rules and must have actions that consist of a
single SELECT command. Thus, an ON SELECT rule effectively turns the table into a view, whose visible”
makes me think I can’t do this. Any advice how I might accomplish the goal.
format
CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
Create rule pci_select as on select to creditcard do
Instead (begin
Insert into pci_log( sql statement);
Select * from creditcard;
end)
Thanks
Doug Little
Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide
500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741
orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com
Вложения
On Thu, Jul 26, 2012 at 4:32 PM, Little, Douglas <DOUGLAS.LITTLE@orbitz.com> wrote: > > Hello everybody, > > > > For PCI compliance I need to log user access to my PCI columns in a table > and retain for 2 years. > > I know I can grep the log, but with 1m log rows/day and infrequent PCI > access, I’m thinking this isn’t the most efficient method. > > > > I’ve been thinking about a SELECT rule, for the access views defined on > the tables with PCI columns. > > I reviewed the doc but belive I’m constrained > > I would like to create a select rule that would log the statement in > addition to executing the select. > > > > “Presently, ON SELECT rules must be unconditional INSTEAD rules and must > have actions that consist of a > > single SELECT command. Thus, an ON SELECT rule effectively turns the table > into a view, whose visible” > > > > makes me think I can’t do this. Any advice how I might accomplish the > goal. > > > > format > > CREATE [ OR REPLACE ] RULE name AS ON event > > TO table [ WHERE condition ] > > DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) } > > > > Create rule pci_select as on select to creditcard do > > Instead (begin > > Insert into pci_log( sql statement); > > Select * from creditcard; > > end) Well for starters you don't have access to the sql statement: the only way to do that is through server side logging or capture inside the application. You can be tricky with views (which is a select rule) and try something like this: create table log(log text); create or replace function log() returns bool as $$ insert into log values (clock_timestamp() || ': ' || current_user); select false $$ language sql; create view log_foo as select * from foo union all select * from foo where (select log()); this crude way of making a 'statement level trigger' for a table. of course, this only logs the database role which isn't much help if you're using application managed roles -- but this is solvable as long as you can get it the logged in user somehow -- perhaps through a session table. merlin