12c版本之前被赋予了select权限的数据库用户,无法收回lock权限和for update的权限,如下:
How To Revoke Lock Privilege From A User That Has Select Any Table Privilege (文档 ID 1357623.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.7.0 to 11.2.0.2 [Release 8.1.7 to 11.2]
Information in this document applies to any platform.
GOAL
An user who is granted the select privilege on a table can lock that table by running a "select for update" statement. Is it possible to stop the user from locking the table?
SOLUTION
This is not possible now. Currently, SELECT FOR UPDATE requires only SELECT privilege on the table. Enhancement request Bug 6823286 was raised for this issue.
Oracle 12c添加read和read any tables权限,防止仅有select权限的用户执行锁表操作(如lock table或select...for update)。
在12c以前的版本中,对用户test赋予create session和select on userA.table1 to test后,test用户可以执行lock table userA.table1 IN EXCLUSIVE MODE;和select...for update 对表userA.table1加锁。
12c中的read权限不提供lock table和select for update权限,对用户赋予read权限后,该用户只能使用select查询,不可执行其他操作,不会加锁。
具体参考如下:
https://docs.oracle.com/database/121/DBSEG/release_changes.htm#GUID-00745268-6964-4EE7-92FE-6B4B72931043
New READ Object Privilege and READ ANY TABLE System Privilege for SELECT Operations
You now can use the READ
object privilege to enable users query database tables, views, materialized views, and synonyms. You still can use the SELECT
object privilege, but remember that in addition to enabling users to query objects, the SELECT
object privilege allows users to perform the following operations, which enables them to lock the rows of a table:
-
LOCK TABLE
table_name
IN EXCLUSIVE MODE;
-
SELECT ... FROM
table_name
FOR UPDATE;
The READ
object privilege does not provide these additional privileges. For better security, grant users the READ
object privilege if you want to restrict them to performing queries only.
In addition to the READ
object privilege, you can grant users the READ ANY TABLE
privilege to enable them to query any table in the database.
When a user who as been granted the READ
object privilege wants to perform a query, the user still must use the SELECT
statement. There is no accompanying READ
SQL statement for the READ
object privilege.
The GRANT ALL PRIVILEGES TO
user
SQL statement includes the READ ANY TABLE
system privilege. The GRANT ALL PRIVILEGES ON
object
TO
user
statement includes the READ
object privilege.