Row Level Security 3 – In Pictures! November 22, 2012
Posted by mwidlake in database design, security.Tags: security, SQL, system development
3 comments
<..Part one intro and examples
<….Part two Permissions
I’ve noticed that there has not been a lot of traffic on this series on Row Level Security (data masking) so far – maybe due to how I am presenting the material? So here is a summary to date in picture/diagram format:
This is the flow of logic as to whether Row Level Security is applied to the SQL statement. Usually this is a query and usually it is on a table but the policy can be applied to other DML and also on views and synonyms:
These are the steps you need to add RLS to a table:
Below is a summary of how oracle fires and uses RLS functions and the predicates they provide:
An finally, a summary of what happens if an RLS function is invalid:
If you have not looked at the previous posts on this topic, more details are in there.
Enjoy!
—
Row Level Security Part 2 – permissions November 20, 2012
Posted by mwidlake in internals, security, SQL.Tags: security, SQL, system development
3 comments
<..Part 1, introduction..
..Part 3 summary in pictures..>
In this second post on the topic of “an introduction to Row Level Security” I want to cover a few things about what permissions you need to implement RLS and some of the consequences. In my introduction in part one I just said my main user has “DBA type Privileges”.
{NB This is all on Oracle V11.2 and I believe everything below is applicable to V10 as well. Also, I should point out that I am not an Oracle security expert – but despite repeatedly saying this, it seems like at least once a year I am asked to improve a system’s security on the grounds of “more than we have now is an improvement”}.
Firstly, you need the permission to create and drop RLS policies. This is controlled by having the EXECUTE privilege on SYS.DBMS_RLS. That is it. If you can execute this package you can create and drop RLS Policies, enable and disable them and also create, manage and use Policy Groups.
Obviously EXECUTE on SYS.DBMS_RLS is a privilege you need to restrict to only “empowered” accounts- but you also want to be very careful about your definition of empowered. If an account has this privilege, it can alter (disable!) any RLS policies that ANYONE created on the instance. You may need to think carefully about having several accounts that can alter all the RLS policies across all schemas in your instance.
You might want to create a single user that has the EXECUTE on SYS.DBMS_RLS and use that to implement and control RLS across the instance {and even have the account locked when it is not needed}. Of course, under normal Oracle anyone with SYS access can run the procedure – but you have SYS access under control already, don’t you?… :-).
By default, who has this privilege?
MDW> @obj_privs Enter value for obj_name: dbms_rls Enter value for obj_own: sys GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA -------------------- ------------ ---------------------- -------- ------------ --- EXECUTE_CATALOG_ROLE SYS DBMS_RLS SYS EXECUTE NO XDB SYS DBMS_RLS SYS EXECUTE NO WMSYS SYS DBMS_RLS SYS EXECUTE NO -- -- and who has that EXECUTE_CATALOG_ROLE? -- select * from dba_role_privs where granted_role='EXECUTE_CATALOG_ROLE' GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- DBA EXECUTE_CATALOG_ROLE YES YES IMP_FULL_DATABASE EXECUTE_CATALOG_ROLE NO YES EXP_FULL_DATABASE EXECUTE_CATALOG_ROLE NO YES SYS EXECUTE_CATALOG_ROLE YES YES
So, our old friend the DBA role has that privilege, via the EXECUTE_CATALOG_ROLE and IMP_/EXP_FULL_DATABASE.
Let’s go back to creating policies. I’m sticking to row-level RLS for now, not column-level. When you create a policy you basically are saying “when object X is accessed, run function Y and apply the resulting string to all access, as a predicate”
Lifted an example from the manual, but adding in a couple of bits the authors were too lazy to put in { 🙂 } :-
DBMS_RLS.ADD_POLICY ( object_schema => 'hr' ,object_name => 'employees' ,policy_name => 'emp_policy' ,function_schema => 'hr_rls' ,policy_function => 'emp_sec' ,statement_types => 'select');
The function HR_RLS.EMP_SEC returns a string P1
When someone issues a statement that access the HR.EMPLOYEES table the function HR_RLS.EMP_SEC is executed as part of the parse and, internally, the following view is created and placed into the orginal statement:
SELECT * FROM hr.employees WHERE P1;
As this takes place as part of the parse part of the statement processing, errors with the function are found before any effort to access the actual data occur. It is also why it is not simple to avoid RLS – it is done as part of the overall SQL processing carried out by Oracle.
If the function is invalid, generates an error or the supplied “WHERE predicate” P1 causes the final statement to be syntactically incorrect, you get an error.
At this point Oracle fails “secure” and simply won’t fire the SQL statement. Even if you own the tables and have full privilege access to them. I think it is worth a quick demonstration
I am logged on as the owner of the table and RLS function, MDW:-
MDW> select * from test1
ID CP_NAME OTHER_NAME MASK_FL
---------- ------------------------------ ------------------------------ ----------
1 ERIC ERIC THE RED 1
2 BROWN BOB THE BROWN 2
3 GREEN GARY THE GREEN 1
4 BLUE BILL THE BLUE 3
5 BLACK DAVE THE BLACK 4
6 PURPLE PETE THE PURPLE 5
7 RED ROGER THE RED 6
--
--All works OK
MDW> @rls_func_damage
--
CREATE OR REPLACE FUNCTION hide_rows (
v_schema IN VARCHAR2
,v_objname IN VARCHAR2)
RETURN VARCHAR2 AS
con VARCHAR2 (200);
BEGIN
-- The line below states loser not user
con := (loser='''||v_schema||''' or MASK_FL > 3)';
RETURN (con);
END hide_rows;
/
Function created.
--
-- Let us check the predicate coming from the function
--
select hide_rows(v_schema=>user,v_objname=>'TEST1') from dual;
HIDE_ROWS(V_SCHEMA=>USER,V_OBJNAME=>'TEST1')
---------------------------------------------------------------------------------------
(loser='MDW' or MASK_FL > 3)
-- "loser" is not a valid piece of syntax or a known variable. So...
--
select * from TEST1
*
ERROR at line 1:
ORA-28113: policy predicate has error
-- I now damage the statement creating the function so that it is syntactically incorrect
CREATE OR REPLACE FUNCTION hide_rows (
...
-- missing quote
con := (loser='''||v_schema||''' or MASK_FL > 3)';
...
Warning: Function created with compilation errors.
select * from TEST1
*
ERROR at line 1:
ORA-28110: policy function or package MDW.HIDE_ROWS has error
--re-create the function as I started with
Function created.
select * from TEST1
ID CP_NAME OTHER_NAME MASK_FL
---------- ------------------------------ ------------------------------ ----------
1 ERIC ERIC THE RED 1
2 BROWN BOB THE BROWN 2
...
7 RED ROGER THE RED 6
--
-- Back to a working system
As you can see, you get ORA-28110 if the function used by the RLS policy is damaged and ORA-28113 if the function returns a non-valid WHERE Predicate. In either case, the data in those tables is now inaccessible. That could be a problem…
It is of course very interesting that RLS is kind of “blind” to the functions it uses until it uses it. You can alter RLS activity if you can alter the functions.
Last post I mentioned that, as you RLS security is based on these functions, you should give thought to who can see them and change them. Hopefully the above highlights that. There is another access aspect to these functions that is key.
In the first example where I use DBMS_RLS.ADD_POLICY, I state function_schema – this is who owns the PL/SQL function. It defaults to the person who creates the Policy. The function is fired in the standard way with DEFINER RIGHTS – i.e. the rights of the function owner. {You could override this when creating the function such that it runs with executioner rights – but that strikes me as a potentially very confusing and a source of much pain and anguish}.
So the function can see what the owner can see, not the executioner. This has two key effects:
- You can restrict the access to any DB objects that the function requires to the owner of the function. The end user does not need to have access and it may be more secure if they do not.
- You have to grant access to objects directly to the function owner, via either object grants or system grants. Stored PL/SQL does not “see” roles if executed with definer rights.
So eg if your function is owned by MDW and references a master table called SEC_MASTER.MASKED_USERS then you need to grant select on SEC_MASTER.MASKED_USERS to MDW. No using roles.
{of course, calling a table MASKED_USERS might give anyone acquiring access to the DB a clue that it is important. I tend to call such “security important” tables things that are “obviously” boring, like ROTA_DATA.}
Finally, that WHERE Predicate P1 is in effect added to your SQL statement. It can be pretty much anything. It can reference other database objects. If it references other database objects THE EXECUTING USER MUST BE ABLE TO SEE THEM. Not the owner of the function; that function has been fired and the WHERE Predicate generated already. On actually running the SQL, the executing user must have access to all objects referenced, including those introduced by the P1 WHERE Predicate. Oh, and remember synonyms!
There is there a way to sidestep RLS and you sometimes need to.
If your logon has the EXEMPT ACCESS POLICY system privilege then RLS functions are utterly ignored by you. No check is made to see if policies apply to your SQL statements, let alone run them. You may need to use this privilege if you need to access functionality that RLS has issues with (see comments by Dom Brooks and Tony Sleight on my first post). You might also need it if you have errors with the policies but you need access to the data to keep the business moving. You probably need a locked-down user somewhere with the EXEMPT ACCESS POLICY privilege if you use RLS.
Of course, great care has to be taken in making use of this privilege as it side-steps all RLS_based security.
If you have master users that have full access to the data, this privilege also removes the overhead of the SQL engine identifying any policies, firing the function and including the extra predicates.
One very final point. I have found myself writing RLS functions that all check if the user was the owner of the table and, if so, allowing them to see all data. By granting EXEMPT ACCESS POLICY to that user I was able to do away with those checks and make the functions much simpler. In this case I did not so much think of EXEMPT ACCESS POLICY turning off security but the lack of it turning it on for restricted users.
That will do for part 2.
Row Level Security Part 1 November 15, 2012
Posted by mwidlake in database design, security.Tags: security, SQL, system development
9 comments
I’ve been working a little on Row Level Security (RLS) recently and wanted to mention a few things, so first some groundwork.
If you want to limit the rows certain users can see, you might think to use views or you might think to use RLS (part of VPD – Virtual Private Database). You can also (from V10 I think) limit which columns users can see. An example is probably the best way to show this. I’m doing this on Oracle 11.2.0.3.
I have two users, MDW and MDW_OFFSHORE. MDW has DBA-type privileges and MDW_OFFSHORE has connect, resource and one or two other simple privs. I will now demonstrate creating and populating a simple table under MDW, adding RLS to it and how it alters what MDW_OFFSHORE sees.
MDW> -- create a table and show row level and column level rls
MDW> --
MDW> drop table TEST1 purge;
MDW> --
MDW> create table test1
2 (id number not null
3 ,cp_name varchar2(30) not null
4 ,other_name varchar2(30) not null
5 ,MASK_FL number
6 )
7 /
MDW> insert into test1 VALUES (1,'ERIC','ERIC THE RED',1);
MDW> insert into test1 VALUES (2,'BROWN','BOB THE BROWN',2);
MDW> insert into test1 VALUES (3,'GREEN','GARY THE GREEN',1);
MDW> insert into test1 VALUES (4,'BLUE','BILL THE BLUE',3);
MDW> insert into test1 VALUES (5,'BLACK','DAVE THE BLACK',4);
MDW> insert into test1 VALUES (6,'PURPLE','PETE THE PURPLE',5);
MDW> insert into test1 VALUES (7,'RED','ROGER THE RED',6);
MDW> --
MDW> COMMIT;
MDW> SELECT * FROM TEST1;
ID CP_NAME OTHER_NAME MASK_FL
---------- ------------------------------ ------------------------------ ----------
1 ERIC ERIC THE RED 1
2 BROWN BOB THE BROWN 2
3 GREEN GARY THE GREEN 1
4 BLUE BILL THE BLUE 3
5 BLACK DAVE THE BLACK 4
6 PURPLE PETE THE PURPLE 5
7 RED ROGER THE RED 6
MDW> grant all on test1 to mdw_onshore;
MDW> grant select on test1 to mdw_offshore;
MDW> --
--
--This is where I create a function to generate the RLS rules
--
MDW> CREATE OR REPLACE FUNCTION hide_rows (
2 v_schema IN VARCHAR2
3 ,v_objname IN VARCHAR2)
4 RETURN VARCHAR2 AS
5 con VARCHAR2 (200);
6 BEGIN
7 -- note the use of backets to make it an atomic test
8 con := '(user='''||v_schema||''' or MASK_FL > 3)';
9 RETURN (con);
10 END hide_rows;
11 /
MDW> --
--
-- Now add the actual RLS policy to the table TEST1 using the function hide_rows
MDW> BEGIN
2 sys.DBMS_RLS.ADD_POLICY (
3 object_schema => 'MDW'
4 ,object_name => 'TEST1'
5 ,policy_name => 'hide_rows_policy'
6 ,policy_function => 'hide_rows'
7 ,function_schema => 'MDW'
8 );
9 END;
10 /
MDW> --
That’s it. In effect you create a function, in this hide_rows, that creates a WHERE predicate (more on that later) and associate it with the table by creating a policy hide_ rows_policy.
Now let’s see what impact it has:
-- as the main user:
--
MDW> select * from test1
2 /
ID CP_NAME OTHER_NAME MASK_FL
---------- ------------------------------ ------------------------------ ----------
1 ERIC ERIC THE RED 1
2 BROWN BOB THE BROWN 2
3 GREEN GARY THE GREEN 1
4 BLUE BILL THE BLUE 3
5 BLACK DAVE THE BLACK 4
6 PURPLE PETE THE PURPLE 5
7 RED ROGER THE RED 6
7 rows selected.
MDW>
-- now as the offshore user:
mdw_offshore> select * from mdw.test1
2 /
ID CP_NAME OTHER_NAME MASK_FL
---------- ------------------------------ ------------------------------ ----------
5 BLACK DAVE THE BLACK 4
6 PURPLE PETE THE PURPLE 5
7 RED ROGER THE RED 6
3 rows selected.
As you can see, mdw_offshore is seeing only a subset of rows in the table. This subset is determined by the function hide_rows. This function returns a WHERE PREDICATE that is added to any query run against the table. It is actually very simply to see what that predicate is, you just call the function passing in the table owner and name:
MDW> select hide_rows(v_schema => user,v_objname=>’TEST1′) from dual;
HIDE_ROWS(V_SCHEMA=>USER,V_OBJNAME=>’TEST1′)
——————————————————————————-
(user=’MDW’ or MASK_FL > 3)
Note that my refernce to v_owner in the function comes out as the text MDW – a way to ensure the predicate does not filter data looked at by the table owner. This implies of course that yes, you can stop the table owner from seeing data!
By default, this extra WHERE PREDICATE is applied to any SELECT, INSERT, UPDATE or DELETE statement run against this table. The function has to accept the v_schema and v_objname parameters and output a varchar2 string. At the time of being called as part of a RLS policy, oracle passes in the owner and name of the object being masked (which is usually a table and sometimes a view).
There are a few things I think it is important to keep in mind in respect of this extra Where Predicate:-
- You have very little control over what other where predicates will be applied to DML against this table, so keep it self-contained – which is why I bound it in brackets.
- It has to be syntactically correct but no check is made for this when you create the function – it is simply a string being outputted, as far as Oracle is concerned, at the time of creation.
- It can be any valid Where Predicate you like, so long as it does not reference other rows in the table with the policy is applied to
- This could be fired a lot. The performance impact could be considerable, which is why CONTEXTs are often used (as they are memory resident and can be configured by eg logon triggers) but I have skipped over that. Go look at the manuals.
I mentioned that the masking of data could be applied to just a column rather than whole rows. So let’s demonstrate that. I can do this by simply dropping and reapplying the same function in a different way:
MDW> BEGIN
-- first drop the policy, simply by stating the object owner, name and policy
2 sys.dbms_rls.drop_policy(
3 object_schema => 'MDW'
4 ,object_name => 'TEST1'
5 ,policy_name => 'hide_rows_policy'
6 );
-- and recreate - note the two new paramters
7 sys.DBMS_RLS.ADD_POLICY (
8 object_schema => 'MDW'
9 ,object_name => 'TEST1'
10 ,policy_name => 'hide_rows_policy'
11 ,policy_function => 'hide_rows'
12 ,function_schema => 'MDW'
13 ,sec_relevant_cols => 'OTHER_NAME,MASK_FL'
14 ,sec_relevant_cols_opt => sys.dbms_rls.ALL_ROWS
15 );
16 END;
17 /
PL/SQL procedure successfully completed.
--
-- as the main user
MDW> select * from test1
2 /
ID CP_NAME OTHER_NAME MASK_FL
---------- ------------------------------ ------------------------------ ----------
1 ERIC ERIC THE RED 1
2 BROWN BOB THE BROWN 2
3 GREEN GARY THE GREEN 1
4 BLUE BILL THE BLUE 3
5 BLACK DAVE THE BLACK 4
6 PURPLE PETE THE PURPLE 5
7 RED ROGER THE RED 6
7 rows selected.
--
-- now as the offshore user:
mdw_offshore> select * from mdw.test1
2 /
ID CP_NAME OTHER_NAME MASK_FL
---------- ------------------------------ ------------------------------ ----------
1 ERIC
2 BROWN
3 GREEN
4 BLUE
5 BLACK DAVE THE BLACK 4
6 PURPLE PETE THE PURPLE 5
7 RED ROGER THE RED 6
7 rows selected.
--
-- but what can look a little odd is that if mdw_offshore describes the table..
mdw_offshore> desc mdw.test1
Name Null? Type
----------------------------------------------------- -------- --------------
ID NOT NULL NUMBER
CP_NAME NOT NULL VARCHAR2(30)
OTHER_NAME NOT NULL VARCHAR2(30)
MASK_FL NUMBER
--
-- they see that OTHER_NAME is NOT NULL but...
mdw_offshore> select count(*) from mdw.test1 where other_name is null;
COUNT(*)
----------
4
That’s rather nice, I think. The same function can be used and rather than hide the rows it masks the stated columns. The sec_relevant_cols => ‘OTHER_NAME,MASK_FL’ parameter states the columns to apply the function on and the sec_relevant_cols_opt => sys.dbms_rls.ALL_ROWS parameter means “show a row even if a column or columns are hidden”. Without it, you only see rows if you do not reference the masked columns (even if indirectly via eg “select (*)”) – see this nice article by Tim Hall if you want an example of that.
With column masking the string from the function is not actually used as a Where Predicate but as a Boolean TRUE/FALSE test. If the test results in TRUE, you see the column(s), otherwise they are hidden.
As you can see from the example, this column-level RLS can result in some odd looking (though intentional) results. A DESC of the table shows the column OTHER_NAME is mandatory but records are found with it set to null. There can also be the situation where a user can insert a value for the masked column, but then cannot see it when they select the data back, though other users can see the populated column.
A limitation of column-level masking is that you can only mask columns with NULL and not, for example, a string of “MASKED”. Thus if a column you are masking is not manadatory, how would you distinguish between a true NULL and a masked NULL? {A nod of thanks goes to Pete Finnigan for confirming this “NULL only” limitation.}
One of the appeals of RLS over eg views is that it is hard to circumvent, as it is applied at the level of the SQL. Another is that the same function can be applied to many tables and might also be applied to both column and row masking, if designed well.
That will almost do for an introduction to the use of RLS to mask rows and columns. However I’ll finish with just a few important points that follow on from this being a security feature.
- I demonstrated that I could see what the function responsible for this security actually returns. If you do not protect unauthorised execution of that function, that could be a security hole.
- I masked the column that is used to control if the OTHER_NAME column is masked. Would you need to also do this?
- Data that is missing is data that could be of interest.
- Anyone who can alter that function can undo your security.
- Anyone who can view DBA_SOURCE (eg has SELECT ANY DICTIONARY) can view the function and see how it works.
- As you can appreciate from the above, apply Security securely is a right pain in the proverbial.
Relax with Good Security :-) April 14, 2010
Posted by mwidlake in Architecture.Tags: behaviour, Humour, security
1 comment so far
It’s nice to know that your computer system is very secure. It allows you to relax.
Is this because you feel confident that your data is protected and that your systems cannot be compromised by malicious actions?
Well maybe, but in my case, today (*), this is not why I can relax. Security is allowing me to relax as I’ve been locked out of my account at my client’s site and it is hard to tune SQL/review design documents if you can’t log on. It is a little bizarre that my account expired today as I know the date my account is set to self destruct – I filled in the relevant forms myself and simply had my boss sign them before I popped them over to the security team. Unfortunately, I can’t pop back over to them to jolly things along as I am now in a remote location (really, very, very remote!).
I just have to wait patiently for HR, Security and Management to complete the complex little Morris Dance they have to do to confirm I am a real person, need the access and have not recently been asked to leave {I hope I am not going to be asked to leave!}.
I’m not really complaining. Having worked for the NHS back in the late 80’s and early 90’s, I have shocking tales to tell, such as of taking home confidential patient clinical reports in order to play with “SQL*report writer” on my personal desktop, and similar stories about modern organisations in the private sector {well what do you know, person X owes HOW MUCH?}. So I am glad security is an order of magnitude better now. {No, honestly, it really is! No matter how bad you think things are where you are now, you should see the stuff I saw back then. These days, you at least have the ability to lock things down, if not the real-world reality.}
But, like all process-oriented things, it has to be designed to work well to be, well, effective. I got locked out of the same systems in a similar way just before Christmas. How many people are being paid a day’s wage to ring up the helpdesk and drink endless tea?
(*) I wrote this blog a while back. It might even have been before I had a blog. But a similar incident reminded me. Oh, and what was my reward for an enforced day being paid to drink tea and read a copy of “Oracle Scene”? Three days of working like a madman to catch up on that lost day. *sigh*. I did not get paid overtime.
Turning on SQL Audit February 2, 2010
Posted by mwidlake in development, performance, Testing.Tags: audit, performance, security, Testing
6 comments
I want to test out using the SQL AUDIT functionality.
The first thing you have to do (having read up on it a bit) is to enable it for the database. You do this by setting the initialization parameter AUDIT_TRAIL. On version 10.2 you have the options to write the audit entires to:
- The DB, by setting it to DB or DB_EXTENDED {not DB,EXTENDED as the manual says, that is the old format}. This puts the entries into the table SYS.AUD$
- The operating system, by setting it to OS, XML or XML_EXTENDED. If you set it to XML or XML_EXTENDED then the data is written out in XML format. You also optionally set AUDIT_FILE_DEST to where you want to data to be written to.
Writing the audit trail to the OS is potentially more secure, as you can stop those cunning and devious DBAs messing with the audit trail. {I’m not so sure that this really helps that much – if anyone knows of any DBAs caught out being naughty solely as a result of using the OS to store the SQL AUDIT records, it would be a fascinating comment}
I want to write to the DB as I want to be able to get at the audit data easily and I am not sure how I want to interrogate it. I’m faster with SQL than SED and AWK.
I also decided up front I wanted to use DB_EXTENDED so that the triggering SQL statement and all bind variables are caught, so I can see more about what it triggering the audit record. I am cautious of the impact of storing CLOBs though, which these two values are stored as. I’ve had performance issues moving lots of CLOBS around and I know from some old colleagues that Secure Files are a lot faster. If Secure Files are faster, that means CLOBs are slower :-). If the audit trail seems to add too much burden on my system, swapping back to just DB will be my first step.
Now for the bad news. You can’t just turn on AUDIT. That initialization parameter is not dynamic. You can’t even enable it for your session. It will need a restart of your database.
This tells me something. Oracle needs to do some setting up for SQL AUDIT when it starts the instance. Either start a new process, enable functionality in one of it’s regular processes or set up structures in memory to cope. Or a mixture thereof. I strongly suspect the need for memory structures {but this is only because, in reality, I have done some testing and I am writing this up afterwards}.
I should not really need to say this but DON’T go turning this on for a production system without extensive testing somewhere else first. There is not a lot “Out There” about the details of the performance impact of AUDIT but the general opinion is there is some; and that is reasonable given it is going to write database records for every action audited. Also, you have no idea yet of any knock-on effects. You know, things you did not expect that causes your database to lock or crash and you to get fired.
{Question, what happens if you alter the initialization file and restart only one node of a RAC database? I don’t know and so I should test that. My current test system is not RAC, but the final destination for this stuff is RAC}.
You probably also want to check that no one has gone and tried turning on SQL AUDIT on things already. You never know if someone else decided to have a play with this and issued a load of AUDIT statements only to find nothing happened – and left what they did in place “as nothing happened”. I already know of one example of this happening…
Here is a little script I knocked up to see what is currently set to be audited:
-- what_is_audited.sql -- Martin Widlake 11/01/10 -- simple listing of what auditing is currently set set pages 100 set pause on spool what_is_audited.lst select * from dba_priv_audit_opts order by user_name,privilege / select * from sys.dba_stmt_audit_opts order by user_name,audit_option / select * from DBA_OBJ_AUDIT_OPTS order by owner,object_name / spool off clear col -- -- EOF --
And some sample output. I’m not going to explain it in this post, but you can have a look though it.
DEV3> @what_is_audited USER_NAME PROXY_NAME ------------------------------ ------------------------------ PRIVILEGE SUCCESS FAILURE ---------------------------------------- ---------- ---------- MDW1 ALTER SYSTEM BY ACCESS BY ACCESS MDW1 AUDIT SYSTEM BY ACCESS BY ACCESS MDW1 CREATE SESSION BY ACCESS BY ACCESS ALTER SYSTEM BY ACCESS BY ACCESS AUDIT SYSTEM BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS 6 rows selected. USER_NAME PROXY_NAME ------------------------------ ------------------------------ AUDIT_OPTION SUCCESS FAILURE ---------------------------------------- ---------- ---------- MDW1 ALTER SYSTEM BY ACCESS BY ACCESS MDW1 CLUSTER BY ACCESS BY ACCESS MDW1 CONTEXT BY ACCESS BY ACCESS MDW1 CREATE SESSION BY ACCESS BY ACCESS MDW1 DATABASE LINK BY ACCESS BY ACCESS MDW1 DELETE TABLE BY ACCESS BY ACCESS MDW1 ... TYPE BY ACCESS BY ACCESS MDW1 UPDATE TABLE BY ACCESS BY ACCESS MDW1 USER BY ACCESS BY ACCESS MDW1 VIEW BY ACCESS BY ACCESS ALTER SYSTEM BY ACCESS BY ACCESS CLUSTER BY ACCESS BY ACCESS CONTEXT BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS DATABASE LINK BY ACCESS BY ACCESS DIMENSION BY ACCESS BY ACCESS DIRECTORY BY ACCESS BY ACCESS INDEX BY ACCESS BY ACCESS MATERIALIZED VIEW BY ACCESS BY ACCESS ... USER BY ACCESS BY ACCESS VIEW BY ACCESS BY ACCESS 56 rows selected. OWNER OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------------------ -------------- ALT AUD COM DEL GRA IND INS LOC REN SEL ------- ------- ------- ------- ------- ------- ------- ------- ------- ---- UPD REF EXE CRE REA WRI FBK ------- --- ------- ------- ------- ------- ------- MWPERF FORN_M_SEQ SEQUENCE -/- -/- -/- -/- -/- -/- -/- -/- -/- A/A -/- -/- -/- -/- -/- -/- -/- MWPERF PERSON TABLE A/A A/A A/A A/A A/A A/A A/A A/A A/A A/A A/A -/- -/- -/- -/- -/- A/A MWPERF ROAD_TYPE TABLE -/- -/- -/- A/A -/- -/- A/A -/- -/- A/A A/A -/- -/- -/- -/- -/- -/-
If you discover you have a lot of things set to be audited, ESPECIALLY if they are auditing select access, think about turning some or all of it off before you enable AUDITING by setting that initialization parameter.
Once you have turned on the feature, you can start testing it…
Martin Widlake's Yet Another Oracle Blog 