| Flashback Data Archive - Problem [message #520484] |
Tue, 23 August 2011 01:56  |
 |
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
|
Senior Member |
|
|
Hello,
I need store history for two tables in my system. I thought that Flashback Data Archive will be the best option. There is also another ways to do this but don't focus on this. I need to to this by FDA (Flashback Data Archive);
So my prerequisition was to create tablespace and flascback archive, and alter table to be archived.
create tablespace audit_archive datafile 'd:\oradata\teta\audit_archive.ora' size 100M;
create flashback archive audit_flash_archive
tablespace audit_archive quota 10G retention 10 year;
alter table teta_admin.t_prac flashback archive audit_flash_archive;
and everything works fine but on sys user.
i can query this table using "as of timestamp" clause
select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-23 08:20:00','yyyy-mm-dd hh24:mi:ss')
but final construction of idea was to create additional user (interface), grant select on teta_admin.t_prac object and query archive data from interface user.
and this is point of my failure. this don't work on new user.
interface user have such sys privs:
SQL> SELECT * FROM dba_sys_privs
2 WHERE grantee = 'INTERFACE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
INTERFACE CREATE SESSION NO
and table privs:
SQL> SELECT * FROM dba_tab_privs
2 WHERE grantee = 'INTERFACE';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN INSERT
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN DELETE
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN ALTER
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN FLASHBACK
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN SELECT
what i need to do in order to query this flashback table from interface user.
when i try to do this from this user oracle says ORA-00942.
thanks in advance.
|
|
|
|
|
|
|
|
|
|
| Re: Flashback Data Archive - Problem [message #520504 is a reply to message #520488] |
Tue, 23 August 2011 03:15  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You should check out the privileges FLASHBACK ARCHIVE ADMINISTER and FLASHBACK ARCHIVE.
But are you sure you want to use this facility? It costs US$5800.00 per CPU, on top of your Enterprise Edition licences. I see it as being intended for enabling compliance with legal requirements (usually for financial regulations) more than something you would actually use on a regular basis.
|
|
|
|
| Re: Flashback Data Archive - Problem [message #520506 is a reply to message #520498] |
Tue, 23 August 2011 03:36 |
|
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 23 August 2011 09:591/
4/ SHOW US! SHOW US! SHOW US! Do NOT explain, SHOW US! How can you expect we tell you what you did wrong if you don't SHOW US what you did?
Regards
Michel
I thought that my description was enough to understand my issue - sorry.
this is my log: (permissions for INTERFACE user are like previous)
SQL> show user
USER is "SYS"
SQL> select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-22 11:32:33','yyyy-mm-dd hh24:mi:ss') where rownum <=0;
no rows selected
SQL> show user
USER is "SYS"
SQL> select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-22 11:32:33','yyyy-mm-dd hh24:mi:ss') where rownum <=1;
PRAC_ID IMIE IMIE_2 NAZWISKO NR_EW
---------- --------------- --------------- ---------------------------------------- --------------------
0001 XXX YYY 000
SQL> connect interface
Enter password:
Po│╣czono.
SQL> show user;
u┐ytkownik to "INTERFACE"
SQL> select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-22 11:32:33','yyyy-mm-dd hh24:mi:ss') where rownum <=1;
select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-22 11:32:33','yyyy-mm-dd hh24:mi:ss') where rownum <=1
*
BúąD w linii 1:
ORA-01031: niewystarczaj╣ce uprawnienia
this is issue. from SYS i can get historical data but from inteface i can't.
after GRANT ANY TABLE to interface user I can get this historical table but it can't be done this way. interface user should have rights only for teta_admin.t_prac table - nothing more.
SQL> connect / as sysdba
Po│╣czono.
SQL> grant select any table to interface;
Pomyťlnie przyznano uprawnienia.
SQL> connect interface
Proszŕ podaŠ has│o:
Po│╣czono.
SQL> select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-22 11:32:33','yyyy-mm-dd hh24:mi:ss') where rownum <=1;
PRAC_ID IMIE IMIE_2 NAZWISKO NR_EW
---------- --------------- --------------- ---------------------------------------- --------------------
0001 XXX YYY 000
[Updated on: Tue, 23 August 2011 03:44] Report message to a moderator |
|
|
|
| Re: Flashback Data Archive - Problem [message #520510 is a reply to message #520504] |
Tue, 23 August 2011 03:47 |
|
piotrtal
Messages: 168
Registered: June 2011
Location: Poland/Czestochowa
|
Senior Member |
|
|
John Watson wrote on Tue, 23 August 2011 10:15You should check out the privileges FLASHBACK ARCHIVE ADMINISTER and FLASHBACK ARCHIVE.
But are you sure you want to use this facility? It costs US$5800.00 per CPU, on top of your Enterprise Edition licences. I see it as being intended for enabling compliance with legal requirements (usually for financial regulations) more than something you would actually use on a regular basis.
John, the same situation:
SQL> grant FLASHBACK ARCHIVE ADMINISTER to interface;
Pomyťlnie przyznano uprawnienia.
SQL> grant FLASHBACK ARCHIVE to interface;
grant FLASHBACK ARCHIVE to interface
*
BúąD w linii 1:
ORA-00990: brakuj╣ce lub niepoprawne uprawnienie
SQL> grant FLASHBACK ANY TABLE to interface;
Pomyťlnie przyznano uprawnienia.
SQL> SELECT * FROM dba_sys_privs
2 WHERE grantee = 'INTERFACE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
INTERFACE FLASHBACK ARCHIVE ADMINISTER NO
INTERFACE CREATE SESSION NO
INTERFACE GRANT ANY OBJECT PRIVILEGE NO
INTERFACE GRANT ANY ROLE NO
INTERFACE FLASHBACK ANY TABLE NO
INTERFACE GRANT ANY PRIVILEGE NO
6 wierszy zosta│o wybranych.
SQL> connect interface
Proszŕ podaŠ has│o:
Po│╣czono.
SQL> select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-22 11:32:33','yyyy-mm-dd hh24:mi:ss') where rownum <=1;
select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-22 11:32:33','yyyy-mm-dd hh24:mi:ss') where rownum <=1
*
BúąD w linii 1:
ORA-01031: niewystarczaj╣ce uprawnienia
we have Enterprise Edition. does this functionality (i mean: FLASHBACK DATA ARCHIVE) should be purchased for this edition?
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
