Oracle creates users, roles, authorizations, build tables

The permission system of Oracle database is divided into System permission and object permission. System permissions (Database system privilege) allow the user to execute a specific set of commands. For example, the CREATE TABLE permission allows a user to create a table, and the grant any privilege permission allows the user to grant any system permissions. Object permissions (Database object privilege) enable users to perform certain operations on individual objects. For example, delete permission allows a user to delete rows of a table or view, and select permissions allow the user to query information from a table, view, sequence (sequences), or snapshot (snapshots) through select.

Each Oracle user has a name and password, and has some tables, views, and other resources created by it. An Oracle role is a set of permissions (privilege) (or the type of access each user needs depending on its state and criteria). The user can grant or assign the specified permissions to a role, and then assign the role to the appropriate user. A user can also authorize other users directly.

--Create tablespace (joffice)Createtablespace joffice Logging datafile'D:\oracle\data\joffice.dbf'size 50mautoextend on  Next10m maxsize 400mextent management local;--Create user and specify default tablespace (user name: joffice password: joffice)Create UserJoffice identified byJofficedefaulttablespace Joffice;--to create a user authorizationGrantDba toJoffice; --A table space was not specified when the user was created--alter user Joffice default Tablespace joffice

First, create a user

There are two built-in users within Oracle: System and sys. Users can log on directly to the system user to create additional users because the system has permissions to create other users. When you install Oracle, the user or system administrator can first create a user for themselves.

syntax [Create users]: Create user username identified by password [i.e. password];

Example: Create user test identified by test;

syntax [change users]: Alter user username identified by password [change password];

Example: Alter user test identified by 123456;

Second, delete the user

Syntax: Drop user username;

Example: Drop user test;

If the user owns the object, it cannot be deleted directly, otherwise an error value will be returned. Specifies the keyword cascade, which removes all objects from the user and then deletes the user.

Syntax: Drop user username cascade;

Example: Drop user test cascade;

Iii. Role of Authorization

Oracle is compatible with previous versions and provides three standard roles: Connect/resource and DBA.

(1) Explain three standard roles:

1 ". Connect role (Connect roles)

-Temporary users, especially those who do not need to build a table, usually give them connect role only.

--connect is using Oracle simple permissions, which only have access to other users ' tables, including select/insert/update and delete.

--Users with Connect role can also create a chain of tables, views, sequences (sequence), clusters (cluster), synonyms (synonym), session, and other data (link)

2 ". Resource role (Resource roles)

-more reliable and formal database users can grant resource role.

--resource provides users with additional permissions to create their own tables, sequences, procedures (procedure), triggers (trigger), indexes, and clusters (cluster).

3 ". DBA Role (Database administrator roles)

--DBA role has all of the system permissions

-Includes unlimited space limits and the ability to grant various permissions to other users. System is owned by DBA users

(2) Authorization order

Syntax: Grant connect, resource to user name;

Example: Grant connect, resource to test;

(3) Revoke permission

Syntax: Revoke connect, resource from user name;

In the following: Revoke connect, resource from test;

Iv. Creating/authorizing/deleting roles

In addition to the three system roles mentioned earlier----connect, resource, and DBA, users can also create their own role in Oracle. A user-created role can consist of a table or system permission or a combination of both. In order to create a role, the user must have the Create role system permission.

1 Create a role

Syntax: Create role name;

Example: Create role testrole;

2 "Authorized role

Syntax: Grant select on the class to role name;

In the following: Grant Select on class to TestRole;

Note: Now, all users with the TestRole role have select query permissions on the class table

3 Delete Roles

Syntax: drop role name;

Example: Drop role testrole;

Note: Permissions associated with the TestRole role are removed from the database

Https://www.cnblogs.com/roger112/p/7685307.html

Oracle creates users, roles, authorizations, build tables