Oracle user, role, and permission management

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.