The USER pseudo-column is quite useful when you are using remote data access methods. For example, you may not want all remote users to see all rows in a table. To solve this problem, you must think of remote users as special users within your database. To enforce the data restriction, you need to create a view that the remote accounts will access. But what can you use in the WHERE clause to properly restrict the records? The USER pseudo-column, combined with properly selected usernames, allows you to enforce this restriction.
Queries used to define views may also reference pseudo-columns. A pseudo-column is a “column” that returns a value when it is selected, but it is not an actual column in a table. The USER pseudo-column, when selected, always returns the Oracle username that executed the query. So, if a column in the table contains usernames, those values can be compared against the USER pseudo-column to restrict its records, as shown in the following example. In this example, the NAME table is queried. If the value of the first part of the Name column is the same as the name of the user entering the query, the records are returned.
NOTE
We need to shift our point of view for this discussion. Because the discussion concerns operations on the database that owns the table being queried, we’ll refer to that database as the “local” database and the users from other databases as “remote” users.
When restricting remote access to your table rows, you should first consider which columns would be the best used for the restriction. The data within a table has logical divisions, such as department or region. For each distinct division, create a separate user account in your local database. For this example, let’s add a REGION column to the BOOKSHELF table. You should now be able to record the list of books from multiple distributed locations in a single table:
Suppose you have four major regions represented in your BOOKSHELF table, and you have created an Oracle account for each region. You could then set up each remote user’s database link to use his or her specific user account in your local database. For this example, assume the regions are called NORTH, EAST, SOUTH, and WEST. For each of the regions, a specific database link is created. For example, the members of the SOUTH department would use the database link shown in the following listing:
The database link shown in this example is a private database link with an explicit login to the SOUTH account in the remote database.
When remote users query via their database links (such as SOUTH_LINK from the previous example), they will be logged into the HQ database, with their department name (such as SOUTH) as their username. Therefore, the value of the USER column for any table that the user queries will be SOUTH.
Now create a view of your base table, comparing the USER pseudo-column to the value of the DEPARTMENT column in the view’s WHERE clause:
A user who connects via the SOUTH_LINK database link—and thus is logged in as the SOUTH user—would only be able to see the BOOKSHELF records that have a REGION value equal to ‘SOUTH’. If users are accessing your table from a remote database, their logins are occurring via database links—and you know the local accounts they are using because you set them up.
This type of restriction can also be performed in the remote database rather than in the database where the table resides. Users in the remote database may create views within their databases of the following form:
In this case, the REGION restriction is still in force, but it is administered locally, and the REGION restriction is coded into the view’s query. Choosing between the two restriction options (local or remote) is based on the number of accounts required for the desired restriction to be enforced.
To secure your production database, you should limit the privileges granted to the accounts used by database links. Grant those privileges via roles, and use views (with the WITH READ ONLY or WITH CHECK OPTION clause) to further limit the ability of those accounts to make unauthorized changes to the data.
As these examples show, the Branch host has few hardware requirements. All it has to support is the front-end tool and Oracle Net Services. A client machine, such as the Branch host, is used primarily for presentation of the data via the database access tools. The server side, such as the Headquarters host, is used to maintain the data and process the data access requests from users.
Regardless of the configuration you use and the configuration tools available, you need to tell Oracle Net Services how to find the remote database. Work with your DBA to make sure the remote server is properly configured to listen for new connection requests and to make sure the client machines are properly configured to issue those requests.
Leave a Reply