oracle user query permissions

Oracle database is the industry's leading relational database management system. It has the characteristics of security, stability, high reliability, and good scalability, and is widely used in enterprise data storage and management. In large database systems, managing users and user permissions is particularly important. This article will focus on the relevant knowledge of Oracle user query permissions in order to better understand and apply the database system.

1. The concept of users and permissions

A user refers to an account or session connected to the Oracle database. User accounts are usually authenticated by username and password, and are granted permissions in the database to perform a series of operations.

Permissions refer to the rights that a user account can perform specific operations or use specific objects in the database. In Oracle database, permissions are divided into two types: system permissions and object permissions.

System permissions refer to database system-level permissions, including CREATE TABLE, CREATE USER, etc. Typically only the database administrator has such permissions.

Object permissions refer to the user's permissions to operate database objects (such as tables, views, stored procedures, etc.), including SELECT, INSERT, UPDATE, DELETE, etc. Usually granted by the object's owner or administrator.

2. Query user permissions

Oracle provides multiple commands to view users and permissions, as shown below:

1. Query system permissions and object permissions

Use the following SQL statement to view the system permissions and object permissions of the current user:

SELECT * FROM USER_SYS_PRIVS;--查询系统级权限
SELECT * FROM USER_TAB_PRIVS;--查询表级权限
SELECT * FROM USER_COL_PRIVS;--查询列级权限

Among them, the USER_SYS_PRIVS table lists the system permissions owned by the user, and the USER_TAB_PRIVS table and USER_COL_PRIVS table list them respectively. The user's permissions on tables and columns have been removed.

2. Query the permissions of the specified user

For the permissions of other users, you can use the following SQL statement to query:

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='username';--查询系统级权限
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE='username';--查询表级权限
SELECT * FROM DBA_COL_PRIVS WHERE GRANTEE='username';--查询列级权限

Among them, DBA_SYS_PRIVS represents system-level permissions , DBA_TAB_PRIVS represents table-level permissions, and DBA_COL_PRIVS represents column-level permissions. The GRANTEE parameter specifies the user's name.

3. Granting and revoking user permissions

Granting and revoking user permissions is one of the core operations for managing database users. Oracle provides two commands, GRANT and REVOKE, to implement this function.

1. GRANT command

The GRANT command is used to grant user permissions. The following is the syntax of the GRANT command:

GRANT privilege [ON object] TO user;

Among them, privilege specifies the permission to be granted, and ON object specifies the object to be authorized (such as tables, views, etc.). If ON object is not specified, system-level permissions are granted.

The following are some authorization examples:

GRANT SELECT ON employees TO user1; --授予user1对employees表的SELECT权限
GRANT UPDATE(emp_name) ON employees TO user2; --授予user2对employees表的emp_name字段的UPDATE权限
GRANT CREATE SESSION TO user3; --授予user3创建会话的权限

2. REVOKE command

The REVOKE command is used to revoke user permissions. The following is the syntax of the REVOKE command:

REVOKE privilege [ON object] FROM user;

Among them, privilege specifies the permission to be revoked, and ON object specifies the object to be revoked (such as tables, views, etc.). If ON object is not specified, it means revoking system-level permissions.

The following are some examples of revoking permissions:

REVOKE SELECT ON employees FROM user1; --撤销user1对employees表的SELECT权限
REVOKE UPDATE(emp_name) ON employees FROM user2; --撤销user2对employees表的emp_name字段的UPDATE权限
REVOKE CREATE SESSION FROM user3; --撤销user3创建会话的权限

4. Summary

The management of Oracle users and permissions is a very important part of the database system. Authorization and granting permissions are one of the core operations for managing database users. This article mainly introduces common SQL commands for querying user permissions and related knowledge about authorization and revoking permissions. In actual applications, permissions need to be managed and assigned based on actual conditions to ensure data security and stability.

The above is the detailed content of oracle user query permissions. For more information, please follow other related articles on the PHP Chinese website!