Oracle : check for read/write privileges, automated testing

Question

I have a question about Oracle and checking privileges.

Some background Info

I wrote some php scripts that will 'test' various things in our environment. One of those tasks is checking that a user has execute privileges on a procedure and that the procedure is valid/compiled.

Here is the query

select ao.object_name, utp.grantee, ao.status, utp.privilege
from all_objects ao, user_tab_privs utp 
where utp.owner = ao.owner and
ao.object_name = utp.table_name and
upper( ao.object_name ) = :object_name and 
ao.object_type = 'PACKAGE' and
utp.privilege = 'EXECUTE' and
ao.status = 'VALID'

This has worked well and has saved us time on procedure privileges; I do realize now that I can also double check the all_tab_privs to check execute access as well.

The problem

Now my question is, how do I do something similar with tables? We ran into an issue where a certain user had SELECT privs on a table but not UPDATE/INSERT privs. How can I check for each of these privileges individually. I've looked into all_tab_privs but haven't found it shows me what I want. It has procedures I can execute but when I check to see if a known table is there it isn't. For example, I'll run the following

select * from all_tab_privs 
where table_name = 'KNOWN_TABLE' and 
grantee = 'CURRENT_USER'
and privilege in ( 'SELECT', 'UPDATE', 'INSERT' );

but instead of getting back 3 rows for a table I know 100% that I can already select/insert/update it returns nothing.

Any ideas? Thank you.

Disclaimer

I am aware that I could just try inserting/updating data and then deleting it but I'd rather not do this. I'd rather not leave any trace since these scripts will run periodically, should be repeatable, and shouldn't alter the state of any piece of data, even if it's just a sequence on the table.

Also, if you could provide a 'list' of possible queries that I can use to determine privileges that would be fine. For example, to determine if i have select access run query 1, 2 and 3. If either returns data then you have select privs and so on for insert/update.

Solution 1

I ended up solving this problem using a multi-step approach based around different queries and the results they brought back. I'm executing all the queries with some PHP code so it wasn't 100% necessary that I only have big query to solve it all.

Additionally, our databases are split up physically and they are linked together through database links so I had to do some additional work to make sure that this privilege checking worked across database links.

Currently I'm only checking for SELECT, DELETE, UPDATE, and INSERT privileges; that is all I need really for now.

The procedure

Here is the general procedure in a nut-list.

1. Get a list of all the database links available to the user.

2. For the following steps, start with the current database we are logged into and then check each database link retrieved from step 1.

   2a. Check to see if the table is visible using a database query.

   2b. If the table is visible, check to see if any of the permission queries return that this user has access to the table.

The queries

Here are the queries for each of the steps above.

1 Database links

select db_link from all_db_links

2a Table Visibility

select * from all_tables%DB_LINK% where table_name = :table_name and owner = :owner

The %DB_LINK% above is replaced by @db_link where applicable. If we are checking the current connection then I remove it entirely. Remember, the queries are being executed by a PHP script so I can do some string manipulation on the string to either remove the %DB_LINK% for the current database or replace it with one of the database links we got from step 1.

2b. Users, Roles, Tables

There are 4 queries all together here.

/*****/
/* 1 */
/*****/
select * 
from user_tab_privs%DB_LINK%
where 
owner = :owner
and
table_name = :table_name
and
privilege = :privilege

/*****/
/* 2 */
/*****/

select * from user_sys_privs%DB_LINK% where privilege = :privilege

/*****/
/* 3 */
/*****/

select * from
      (
        select distinct granted_role from 
        (
          select null linker, granted_role 
          from user_role_privs%DB_LINK%
          union all
          select role linker, granted_role 
          from role_role_privs%DB_LINK%
        )
        start with linker is null
        connect by prior granted_role = linker 
      ) user_roles join role_tab_privs%DB_LINK% rtab on user_roles.granted_role = rtab.role
      where 
      owner = :owner
      and
      table_name = :table_name
      and
      rtab.privilege = :privilege

/*****/
/* 4 */
/*****/

select * from
  (
    select distinct granted_role from 
    (
      select null linker, granted_role 
      from user_role_privs%DB_LINK%
      union all
      select role linker, granted_role 
      from role_role_privs%DB_LINK%
    )
    start with linker is null
    connect by prior granted_role = linker 
  ) user_roles join role_sys_privs%DB_LINK% rtab on user_roles.granted_role = rtab.role
  where rtab.privilege = :privilege

Explanations

Database links

In the phpunit tests I pass in two things : table name and schema name ( owner ). However, because of the database links we have to explicitly check the other databases by using the @db_link in the queries. Otherwise I might report a table as being inaccessible when in reality it is accessible via the database link.

Table visibility

If the user can't see the table then there is no point in checking for privileges. Checking for privileges also prevents the case where a user has been given 'SELECT ANY TABLE' privileges but the table itself doesn't actually exist from causing an unwanted failure.

The band of 4 queries

As shown by other posters, a user can be given access to a table in many ways. Specifically, they can be given roles, and those roles can be given roles, and then those roles can be assigned access. Or the user can be given explicit access or generic access through system privileges.

The first of the four queries checks to see if the user has been given explicit SELECT, DELETE, etc, privileges on the table. It's easy to understand and would ideally be all that's necessary

The second checks to see if the user has been granted any system privileges like DELETE ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, etc. These are not granted explicitly on a table but the user can perform any of the referenced actions on any table they have visibility.

The third query will see if any of the roles that user has, either directly or indirectly, has been given explicit SELECT, DELETE, etc, privileges on the table. This is similar to query 1 except it's based on the roles given to the user, not the user.

The fourth checks to see if any of the roles that user has, either directly or indirectly, has been given any system privileges like DELETE ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, etc. This one is similar to query 2.

That's it! I chain these together and use the results returned from each to determine whether a user has the desired privileges or not.

Details worth mentioning

If a user has any privileges across db_link_1 it does NOT mean that they have the same privileges on tables they access across db_link_2. Most should know this but I wanted to make sure I stated it explicitly. For example, select privs on table 1 across db_link_1 does not imply select privs on table 2 across db_link_2.

I check each db_link one at a time. So first I start of with the database I'm connected to directly, no database link required for this. Then, if I can't find the table or don't have the privs on the table I move on to the next database link.

In queries 2 and 4, I use 'SELECT ANY TABLE', 'DELETE ANY TABLE', etc in place of the :privilege variable.

In queries 1 and 3, I use 'SELECT', 'DELETE', 'UPDATE', 'INSERT' in place of the :privilege variable.

Solution 2

This looks rather optimistic to me, as the role issue could get really complex, particularly if roles ever get password protected, and I'd never really trust the method 100% without actually trying the DML.

It might be more simple to try queries such as:

select count(*)
from   schema_name.table_name
where  1=0;

insert into schema_name.table_name
select *
from   schema_name.table_name
where  1=0;

delete from schema_name.table_name
where  1=0;

update schema_name.table_name
set    column_name = column_name
where  1=0;

I believe that such queries would fail if no privileges were granted (no database handy to check it), and they would never modify any data.

By the way, the ANY privileges are generally regarded as a security problem, so you might like to fail the system if any user is granted them.

Solution 3

If you have privileges granted via roles, you need a more complicated check. The link in comments gives some queries to use to look at the wider picture, but if you want to check what the current user can see - as one of your queries suggests - then you can query the session_roles view to see what object privileges are currently available to your session via roles, in addition to directly-granted object privileges:

select atp.table_schema, atp.table_name, atp.privilege, atp.grantee,
    'Direct' as grant_type
from all_tab_privs atp
where atp.grantee = user
union all
select atp.table_schema, atp.table_name, atp.privilege, atp.grantee,
    'Via role' as grant_type
from session_roles sr
join all_tab_privs atp on atp.grantee = sr.role;

You can obviously add filters if you want to look at a specific object or privilege, and the grant_type pseudo-column is just for info - not really that useful since you can compare grantee with user to get the same info I suppose.

You might want to look at session_privs as well, to check your user has any system privileges you expect.

But if you want a single query to check the privileges for another user or several users at once, you'll need something more like the linked queries, and the privileges necessary to run them.

Comments

[Jerry Saravia]

Hom much more complex can the roles issue get? The role checking is being done for a web application so we never use role passwords for any roles assigned to the web application.

[Jerry Saravia]

Your approach is very valid I think. My queries are already recursively checking for roles as well. The reason I don't throw an error on the 'ANY TABLE' privs is because in our dev environment we use these types of privs for fast development and setup sometimes.

[David Aldridge]

For specific applications I agree that there will probably be a limit to the complexity, but that also means that there is a "hidden constraint" on the system in that a more complex role-based structure cannot be adopted without modifying (or breaking) this privilege testing method. I really think that there's only one way to be sure. Consider as well whether there are column-based privileges to be considered - docs.oracle.com/cd/B19306_01/network.102/b14266/… - because that's another layer for all of us to consider.

[Jerry Saravia]

Thanks @David Aldridge You're right about the more detailed checking of privileges and that these considerations are more for the general purpose rather than my specific use case. For my use case, I'm only doing automated testing on the level of the table. In my code I call the code as follows $this->verifySelectPrivsOnTable( $schemaName, $tableName ) but I can definitely see it being changed to >verifySelectPrivsOnTable( $schemaName, $tableName, $columnName )

[Jerry Saravia]

accepted my 'solution' since this is what i am using right now. however, it was based on hints given by other users and info i got from oracle documentation.

[lucasvc]

Duck test :)