Oracle creates user, role, authorization, and build table space

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.

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

Permissions:

Create session

CREATE table

Unlimited tablespace

Connect

Resource

Dba

Cases:

#sqlplus/nolog

Sql> Conn/as sysdba;

Sql>create user username identified by password

Sql> Grant DBA to username;

Sql> Conn Username/password

Sql> select * from User_sys_privs;

We will start with the creation of Oracle User Rights table, then explain the general actions such as landing, so that you have a deep understanding of the Oracle User Rights table.

First, create

sys;//system Administrator with the highest privileges

system;//Local Administrator, sub-high privilege

scott;//Normal user, password default is tiger, default unlocked

Second, landing

Sqlplus/as sysdba;//Login SYS account

Sqlplus Sys as sysdba;//ibid.

Sqlplus scott/tiger;//Landing Ordinary user Scott

Third, manage users

Create user zhangsan;//under the Administrator account, creating the users Zhangsan

Alert user Scott identified by tiger;//change password

Four, grant permissions

1, the default ordinary user Scott is not unlocked by default, cannot do that use, the new user does not have any permissions, must be granted permissions

　　

Grant Create session to zhangsan;//Grant Zhangsan user permission to create session, that is, login permission

Grant Unlimited tablespace to zhangsan;//grants Zhangsan users permission to use tablespaces

Grant CREATE table to zhangsan;//grants permissions for creating tables

Grante drop table to zhangsan;//grant permission to delete tables

Grant Insert table to zhangsan;//permissions for inserting tables

Grant Update table to zhangsan;//permissions to modify tables

Grant all to public;//this is more important, grant all permissions (all) for all users (public)

2, Oralce on the rights management more rigorous, ordinary users are also the default can not access each other, need to authorize each other

　　

Grant SELECT on TableName to zhangsan;//Grant Zhangsan user permission to view the specified table

Grant drop on TableName to zhangsan;//granting permission to delete table

Grant insert on TableName to zhangsan;//permission to be inserted

Grant update on TableName to zhangsan;//granting permission to modify tables

Grant Insert (ID) on tablename to Zhangsan;

Grant Update (ID) on TableName to zhangsan;//grants insert and Modify permissions to specific fields of the specified table, note that only the INSERT and update

Grant alert all table to zhangsan;//grants Zhangsan user alert permission to any table

V. Revocation of Rights

Basic syntax with GRANT, keyword revoke

Vi. Viewing permissions

SELECT * from user_sys_privs;//View all permissions for the current user

SELECT * from user_tab_privs;//View the user's permissions on the table

Vii. Table of users of the action table

　　

SELECT * FROM Zhangsan.tablename

Viii. Transfer of rights

That is, user a grants permission to B,b to grant the permission of the operation to C again, with the following command:

Grant alert table on TableName to Zhangsan with admin option;//keyword with admin option

Grant alert table on TableName to Zhangsan with Grant option;//keyword with GRANT option effect similar to admin

Nine, the role

A role is a collection of permissions that can grant a role to a user

Create role myrole;//creating roles

Grant create session to myrole;//grants permission to create session Myrole

Grant Myrole to zhangsan;//the role of Zhangsan user Myrole

Drop role myrole; remove roles

Oracle establishes tablespaces and users

[SQL]View PlainCopy

2. To establish the tablespace and the user's steps:
4. User
6. Create:Create user username identified by "password";
8. Authorization:Grant create session to user name;
10. Grant create table to user name;
12. Grant create tablespace to user name;
14. Grant Create view to user name;

[SQL]View PlainCopy

1. Table Space
2. Create a tablespace (typically a table space with n stored data and an index space):
3. Create tablespace table space name
4. DataFile ' Path (to build the path first) \***.dbf ' size *m
5. Tempfile ' path \***.dbf ' size *m
6. Autoextend on -automatic growth
7. --There are some commands to define the size, see the need
8. Default Storage (
9. Initial 100K,
10. Next 100k,
11. );

[SQL]View PlainCopy

1. Example: creating a table space
2. Create Tablespace Demospace
3. DataFile ' e:/oracle_tablespaces/demospace_tbspace.dbf '
4. Size 1500M
5. Autoextend on next 5M maxsize 3000M;
6. Delete Table space
7. Drop tablespace demospace including contents and datafiles

[SQL]View PlainCopy

1. User Rights
2. To grant a user permission to use the tablespace:
3. Alter user username quota Unlimited on table space;
4. or alter user username quota *m on table space;

Complete Example:

[SQL]View PlainCopy

2. --Table space
4. CREATE tablespace SDT
6. DataFile ' F:\tablespace\demo ' size 800M
8. EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
10. --Index Table space
12. CREATE tablespace Sdt_index
14. DataFile ' F:\tablespace\demo ' size 512M
16. EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
20. --2. Build Users
22. Create User demo identified by demo
24. Default Tablespace std;
28. --3. Empowering
30. Grant Connect,resource to demo;
32. Grant create any sequence to demo;
34. Grant create any table to demo;
36. Grant Delete any table to demo;
38. Grant insert any table to demo;
40. Grant select any table to demo;
42. Grant Unlimited tablespace to demo;
44. Grant execute any procedure to demo;
46. Grant update any table to demo;
48. Grant Create any view to demo;

[SQL]View PlainCopy

1. --Import and export commands
2. IP Export mode: Exp demo/[email protected]:1521/orcl file=f:/f.dmp full=y
3. Exp Demo/[email protected] file=f:/f.dmp full=y
4. Imp demo/[email protected] file=f:/f.dmp full=y ignore=y

Oracle creates user, role, authorization, and build table space