Relationship between Oracle user access permissions and PUBLIC roles

A user is created in the database, which is used by a third-party system. The customer stressed that this user can only access limited tables within the scope specified by us.

Therefore, I created a user, such as user_third, who only gave the connect role limited authorization using grant select on table.

For this reason, I wrote an article on how to implement that user B in Oracle can only access user A's view ()

After the user's access password is handed over to the customer, a new problem is found, which allows the user to access tables of other users.

I checked and found that it could indeed access tables of other users. For example, tables under TT users in the data library.

What's going on?

You must have guessed it from the titles I listed. This must be related to the public role.

When the Oracle TimeTen is created, the table it creates automatically grants its query permission to the public role.

By default, any user in the database has a public role.

Therefore, the problem lies in the permission of the public role. We only need to query which permissions are granted by the public role and revoke them.

By executing the following SQL statements, we can determine which permissions are granted to the public role.

Select grantor, OWNER, TABLE_NAME, privilege from DBA_TAB_PRIVS where grantee = 'public ';

Some results are as follows:

Grantor owner TABLE_NAME PRIVILEGE

----------------------------------------------------------------------

×× GTJA TT_03_24577_L UPDATE

GTJA TT_03_24577_L SELECT

GTJA TT_03_24577_L INSERT

GTJA TT_03_24577_L DELETE

The query, update, deletion, and insertion permissions of the TT table are granted to the public role. This is terrible, and it damages the security of the database system.

Based on this, we can determine that any database user can perform query, update, delete, and insert operations on these tables.

It is very easy to solve this permission problem. However, we must ensure that the normal use of TT is not affected. This is a trade-off between availability and security. If TT is abnormal, You need to migrate it to another database. It is best to use an independent database only for TT.

The solution is as follows:

1. Solve the problem from Oracle TimeTen. When creating a cachegroup, remove the option that grants public permissions. Of course, the premise is to ensure that TT can be used normally.

2. revoke these permissions from the database from the public role. The premise is that TT can work normally after these permissions are revoked.

An example of the revocation method is as follows:

Revoke select on TT_03_24577_L from public;

To sum up, this example shows that the permissions of Oracle are complex enough. You must pay attention to the permissions of the public role during daily management.

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12