How to revoke alter session privilege in Oracle

Question

I granted the CREATE SESSION privilege to a recently created database user, and I granted him the SELECT privilege on some objects for different database schemas.

I find an apps schema (SCHEMA#) in v$session that is different from the database USERNAME recently created, and I would like to understand the phenomenon.

I think that he executes alter session set current schema and I would like to know if is it possible to revoke alter session privilege in Oracle 11g.

Answer 1

The documentation for the alter session statement says:

To enable and disable the SQL trace facility, you must have ALTER SESSION system privilege.

To enable or disable resumable space allocation, you must have the RESUMABLE system privilege.

You do not need any privileges to perform the other operations of this statement unless otherwise indicated.

As you don't need any privileges to perform alter session set current_schema, there is nothing you can revoke to prevent that being done. If you had actually granted alter session - which you haven't, from what you said - then you could of course still revoke that, but it would make no difference to the ability to change the current schema.

But this isn't really a problem, and is mentioned in the security guide as a good thing:

For example, a given schema might own the schema objects for a specific application. If application users have the privileges to do so, then they can connect to the database using typical database user names and use the application and the corresponding objects. However, no user can connect to the database using the schema set up for the application. This configuration prevents access to the associated objects through the schema, and provides another layer of protection for schema objects. In this case, the application could issue an ALTER SESSION SET CURRENT_SCHEMA statement to connect the user to the correct application schema.

Your recently-created user does not have any additional privileges or abilities simply by changing their current schema. They have not 'become' that schema; they can still only do the things you specified by granting select privileges on objects. They can't see anything else, and can't do any more to the objects they can see. They haven't inherited any of the privileges that schema has - so they can't create or drop objects under that schema, for instance. (You would have to explicitly grant them additional any privileges, which presumably you have no intention of doing.)

What they can do is reference those objects without having to prefix them with the schema name, and without having to create synonyms. But they can still only select from them (if that is the only privilege you granted).