Shulou(Shulou.com)06/01 Report--

The user (user) is a name defined in the database. When accessing the database, provide the appropriate user name and password.

A Schema is a collection of objects owned by the user. There is an one-to-one relationship between the user and the schema, and the two names are the same.

Creating a user in the Oracle database is done by the dba user using the command create user; if you want to create a user as another user, you must have create user system privileges.

First, create a user syntax definition:

CREATE USER test IDENTIFIED BY test

DERAULT TABKESPACE users_ts

TEMPORARY TABLESPACE temp_ts

QUOTA 3m ON users_ts

As an example above:

Set up a database user

Account: test

Password: test

Default tablespace used: users_ts

Temporary tablespace used: temp_ts

Quota in tablespace users_ts: 3m

Link to the database

Authorization must be given to link to the database (create session)

1, example: Grant session permissions to the initial user

Connect system/manager

Grant create session to test

Connect test/test

You can connect to the Oracle database by using a test account

2 example: grant create table permission to the database account to perform preliminary operations on the database

Connect system/manager

Grant creat table to test

Connect test/test

Create table....

You can connect to the Oracle database by using a test account, and then create a database table.

3 example: modify user password

Alter user test (user name) identified by test (user password)

4 example: unlock user

Alter user test (user name to be unlocked) account unlock

5 example: modify user space quota

Alter user test QUOTA 10m on users_ts

6 example: syntax for deleting a database user

DROP USER username (user name)

Note: if the schema contains database objects, you must have CASCADE, or an error message will be displayed

Drop user user name cascade

Grant system permissions

7 example: grant system permissions syntax

Grant system permissions list (separated by "," among multiple system permissions) to users

Grant create session,create table to test

That is, permissions to create sessions and create tables are granted to test users.

Reclaim system permissions

8 example: reclaim system permissions syntax

Revoke system permissions list (separated by "," among multiple system permissions) from users

Revoke create session,create table from test

That is, the permissions to create sessions and tables are withdrawn from the test account.

9 displays the system permissions that the current user has

Select * from user_sys_privs where grantee='test'

10 display the permissions of the current role

Select * from session_privs

Grant object permissions

1 example:

Grant permission list (separated by commas) on object to account

Connect BDB Link B account

Grant select, insert on tx to A

Give the query and added permissions of B.TX to A

2 example: basic syntax for reclaiming object permissions

Connect BDB Link B account

Revoke select, insert on TX from A

3 example: displays the object permissions that the current user has

Select * from user_tab_privs

Through USER_TAB_PRIVS, you can view the object permissions that the current user has.

Create a role

Example: roles created

CREATE ROLE test_role

Empower a role

Example: authorize a role

Grant create session, create table to test_role

Use roles to authorize users

Example: use roles to authorize users

GRANT test_role (role name) TO user_name (user name)

Example: delete a role

DROP ROLE test_role

Use user_role_privs to display the roles of the current user

Example:

SELECT USERNAME, GRANTED_ROLE FROM USER_ROLE_PRIVS

Three SQL statements

SQL statements are case-insensitive and can be uppercase, lowercase, or mixed

DEPT table: company department table, field (deptno,dname,loc)

EMP table: company employee table, fields (empno, ename, job, mgr, hiredate, sal, comm, deptno)

DEPT is the master table and EMP is the slave table

1 example: show table structure

Desc emp

2 example: query all columns

Selcet * from dept

3 example: query specific columns

Select deptno,dname from dept

Use arithmetic expressions

Example: query annual salary

Select sal*12 from emp

Cancel duplicate lines and use the keyword distinct

Example:

Select distinct deptno, job from emp

Where conditional sentence

Select ename, hiredate from emp where to_char (hiredate,'yyyymmdd') 100 and sal > 1000

ORDER BY sentence

ASC is used for ascending sort (default), DESC is used for descending order, and ORDER BY sentence must be placed last when there are multiple words.

Example:

Select ename, sal from emp order by sal desc

Insert add data

Insert into emp

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.