Oracle user permissions: 1. System permissions, which allow users to perform specific database actions, including creating tables, indexes, connection instances, etc.; 2. Object permissions, which allow users to operate specific objects. , including reading views, executing stored procedures, etc.
The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.
System permissions: Allow users to perform specific database actions, such as creating tables, creating indexes, connecting instances, etc. (for users)
Object permissions: Allow users to manipulate some specific objects, such as reading views, updating certain columns, executing stored procedures, etc. (for tables or views)
1. System permissions
More than one hundred effective permissions (SELECT * FROM SYSTEM_PRIVILEGE_MAP check)
The database administrator has advanced permissions to complete Administrative tasks such as:
– Create new user
– Delete user
– Delete Table
–Backup table
System permission classification:
DBA: Has all privileges, It has the highest authority in the system, and only the DBA can create the database structure.
RESOURCE: Users with Resource permissions can only create entities, not database structures.
CONNECT: Users with Connect permissions can only log in to Oracle and cannot create entities or database structures.
For ordinary users: grant connect, resource permissions.
For DBA management users: grant connect, resource, dba permissions.
a. Commonly used system permissions:
CREATE SESSION Create session
CREATE SEQUENCE Create Sequence
CREATE SYNONYM Create an object with the same name
CREATE TABLE Create a table in user mode
CREATE ANY TABLE Create a table in any schema
DROP TABLE Drop a table in user mode
DROP ANY TABLE Drop a table in any schema
CREATE PROCEDURE Create a stored procedure
EXECUTE ANY PROCEDURE Execute a stored procedure in any mode
CREATE USER Create user
DROP USER Delete user
CREATE VIEW Create view
2. Object permissions
Different objects have different object permissions
The owner of the object has all permissions
The owner of the object can assign permissions to others
ORACLE has a total of object permissions
Object permission table view sequence process
Modify (alter)
delete
execute
index
Insert
References
Select
Update( update)
In addition, the permissions of connect and resource are as follows
CONNECT role: only has the permission to create SESSION
RESOURCE role: only has Permission to create CLUSTER, INDEXTYPE, OPERATOR, PROCEDEURE, SEQUENCE, TABLE, TRIGGER, TYPE.
At the same time, when the ORACLE resource role is granted to a user, not only the permissions of the ORACLE resource role itself will be granted, but also the unlimited tablespace permissions. However, when the resource is granted to a role, it will not be granted. Unlimited tablespace permissions.
There is indeed no permission to create a view. It seems that if you need permission to create a view, you can only authorize it separately:
GRANT CREATE VIEW TO USER;
Recommended tutorial: " Oracle Video Tutorial》
The above is the detailed content of What are the user rights in oracle?. For more information, please follow other related articles on the PHP Chinese website!