Two types of Oracle user permissions and their usage

Permission is the user's power to execute a function. In Oracle, permissions are divided into system permissions and entity permissions based on different system management methods. System permission refers to whether authorized users can connect to the database and perform system operations in the database. The entity permission refers to the user's permissions on the specific schema object. This may be a bit fuzzy. For example, the select any table is a system permission, which indicates that any table can be viewed. Select on table1 is the object permission, indicating the query permission for table 1. The following are Oracle user permissions.

1. What is a user ):

A user is a name defined in the database that can connect to and access objects.
Users connect to the database and Access database objects.

Ii. What is schema ):

A schema is a collection of database objects (used by a user .).
Schema objects are the logical structures that directly refer to the database's data.
A schema is a collection of database objects, and a schema object is the logical structure of database data.

Iii. Differences between user and schema:

Schemas and users help database administrators manage database security.
A user is used to connect to a database object, while a mode is used to create a management object. The mode is one-to-one with the user in oracle.

From the definition, we can see that schema is a set of database objects. To distinguish each set, we need to name this set, these names are the nodes similar to user names that we see in the Enterprise Manager Solution. These nodes similar to user names are actually a schema, which contains various objects such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links.

An Oracle user generally corresponds to a schema. The schema name of the user is the same as the user name and serves as the default schema of the user. This is why the schema names are database usernames in the Enterprise Manager Solution. You cannot create a new schema in an Oracle database. To create a schema, you can only create one user, but it is not used to create a schema). When creating a user, create a schem with the same name as the user name and use it as the default shcema for the user. That is to say, the number of schemas is the same as the number of users, and the schema names are one-to-one and the same as the names of users. All aliases that can be called schema as users are inaccurate, but it is easier to understand.

An Oracle user has a default schema, and Its schema name is the same as the user name. Of course, an Oracle user can also use other schemas. If we access a table without specifying which schema the table belongs to, the system will automatically add the default sheman name to the table. For example, when we access the database, we can access the emp table under the scott user through select * from emp; in fact, the complete Syntax of this SQL statement is select * from scott. emp. The full name of an object in the database is schema. object, not user. object.

Similarly, if we do not specify the schema of the object when creating the object, the schema of the object is the default schema of the user. This is like an Oracle user having a default tablespace, but this user can also use other tablespaces. If we do not specify a tablespace when creating an object, the object is stored in the default tablespace, to store objects in other tablespaces, We need to specify the tablespace of the object when creating the object.

Summary: For oracle, the user is the mode. The mode is the user.

Iv. Oracle user permissions

An Oracle user permission consists of system permissions and user object permissions.

1. Oracle user permission system ):
System permissions include: SQL> select distinct PRIVILEGE from dba_sys_privs order by PRIVILEGE;

View related to system permissions: dba_sys_privs (all system permissions); user_sys_privs system permissions ).

Oracle user permission object ):
An object created in one mode, or an object granted to another mode and granted to the adm management. For example:
Grant select, insert, update, delete on table_name to schema_name. This grants a user object permission.

Views related to Oracle user permissions: user_tab_privs; for example:
SQL> select * from USER_TAB_PRIVS where GRANTOR = 'testdb' AND PRIVILEGE = 'execute ';

Oracle user permission system) is different from the user object permission: The system permission is generalized. Is relative to the entire object. The user object permission is only for a single object or some objects.

Example of system permission: grant create table to user.

Example of user object permission: grant select on table_name to schema_name;

Role: a role is a set of permissions.
To facilitate management, we organize some common permissions into a set, assign roles, and then assign roles to users to improve management efficiency. For example, creating a database in a certain mode, read-only users under a certain number of users, readable users and users that can be inserted, and so on, is of great significance in actual production.

1. create a role without specifying the password: create role testrole;
2. create a role and specify the password: create role testrole identified by tanfufa;
3. Modify the role: alter role testrole identified by luqiaoling;

1. Seven methods for viewing Oracle user permissions
2. Explain how to use the Oracle user permission View
3. Introduction to Oracle user permissions and role management
4. Create and assign Oracle user permissions
5. An analysis of the management method of the Oracle user permission table