Oracle user, role, and permission management

Create or replace type address as object (province varchar

(20), city varchar (20 ));

Alter trigger sys. JIS $ ROLE_TRIGGER $ compile;

Create table test (id number primary key, name char (50 ));
Tablespace authorization
Alter user quota unlimited on tablespace;
Alter user test quota unlimited on test;

Create user
Create user Username identified by password;
Create user icwallet identified by icwallet;

Delete a user
Drop user cascade

Change Password
Alter user Username identified by password;
Alter user icwallet identified by icwallet;

Lock:
Alter user Username account lock;

Authorization
Revoke
Grant create session to the_username;
Grant system_privilege to username [with admin option];

Create a database role
Create role role_name;
Create role role_name identified by role_password

First: user Management ************************************** *****
The user must have at least the session rights; otherwise, the connection fails;
The user shall have the right to perform other operations on the session;

Oracle users and passwords are case-insensitive;

In Oralce, all users must be explicitly authorized to perform operations;
In SQL Server, the created user automatically has some permissions;

Oracle does not rely on the operating system; SQL Server depends on Windows;

MicroSoft assumes that most users are legal users and is optimistic;
Oracle assumes that all users are insecure and adopts a pessimistic attitude;

Users in Oracle are isolated from each other and are called the user mode;

Built-in users:
Sys, Network Administrator, highest permission;
When you try to enter the User name: sys
Password: General
Then, you find that the connection fails;
And enter the User name: system
Password: General
Or User name: scott
Password: tiger
Can be connected, so you think
Sys does not have high system permissions;
In fact, because sys has high permissions, you must
To log on as a dba: sys/General as sysdba
// You can use the data dictionary user_users to view the users managed by the current user:
Select * from user_users;
Dba_users can view users managed by DBAs;

System: only the databases on the local machine are managed;
All_users can only view all users by dba;

* ***** Create a user:
Create user Username identified by password [externally]
[Default tablesapce tablespace name]
[Temporary tablespace name]
[Quota integer K | M | unlimited on tablespace name]
// Note: the user must have the dba permission;
* ***** Query a user:
In fact, it is to query the table user_users/dba_users/all_users,
But they are not called tables or data dictionaries here, and data dictionaries are systems.
Maintained;
* ****** Modify the user:
Alter user... // is created later;

An instance in Oracle is a database, which is equivalent to the full database of SQL Server.

Database;
A database in Oracle is divided into many tablespaces, each of which is equivalent to SQL

A database in Server;

Example:

Create user student_user
Identified by student
Default tablespace users
Temporary tablespace temp
Quota 5 m on users
Quota 3 m on temp

/Users and temp are built-in tablespaces, and student_user has 5 MB of space in users.

In temp, there are 3 m;
New users do not have the right:
SQL> conn student_user/student;
ERROR:
ORA-01045: user STUDENT_USER lacks create session privilege;

Logon denied

Warning: you are no longer connected to ORACLE.

* ***** Restrict users
Lock: alter user Username account lock;
Effect:
SQL> alter user student_user account lock;
The user has changed.
SQL> conn student_user/student;
ERROR:
ORA-28000: the account is locked
Warning: you are no longer connected to ORACLE.
Unlock: alter user Username account unlock;
Invalid password: alter Username password expire; when a user creates many tables,
If you delete a user directly, all the tables under it will be deleted, so we can
Only make the password invalid;
// It can be seen that the table is owned by the user. If the user is absent, the table created by the user occupies all the space.

No;
// Therefore, we do not delete users in general;

* *** Delete a user:
Drop user Username [cascade]
// Cascade is force-deleted, even if someone else is using this user's table;
// The database management statement automatically ends the transaction and has no chance of rollback;

Second: permission management ************************************** *********

Authorization: grant [system privilege name] [role] [on authorized operation table name] to [User Name

LIST]
[Public] [with admin option]
For dcl statements, you do not need to authorize the operation table name on.

Grant all on student to public: grant all permissions on student to all

User;

Data Dictionary: user_sys_privs
Select * from user_sys_privs
Revoke permissions:
Revoke permission list on table from user;
SQL> revoke create table from iam;
Withdrawing successful.
Revoking only revokes the permission of this authorization. Therefore, if a user has a certain right,
And you have granted it the same right, and when you revoke this permission, it turns out
This right still exists;

Third: role management ************************************** ***
Because a user may need a set of permissions, when we get a new user

, We need
Each role is a set of permissions. You can

Directly assigned this
A role, the user naturally has the rights of its role;
Create a role:
Create role name [no identified | identified by password

| Externally]
Not identified: indicates that the user authorized to this role does not need to be checked during use;
Identified by: indicates that the user granted this role needs to check when using the set role command

Verification;

Revoke permission: revoke permission from role name;

// A role is a set of permissions;

Instance:
Create role student_role
/
Grant create table, create session, create view to student_role