Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to operate sql in oracle. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

The difference between sys and system for oracle users:

The sys user is a super user with the highest privileges, sysdba role, and create database permissions.

System users are administrative operators with high privileges, have the role of sysoper, and do not have the authority of create database

Generally speaking, for database maintenance, you can log in using the system user.

Sqlplus:

Usage 2: sqlplus [[] [{logon | / nolog}] []]

Is: [- C] [- L] [- M "] [- R] [- S]

Is: {[/] [@] | /}

[AS {SYSDBA | SYSOPER | SYSASM}] [EDITION=value]

Oracle A database is a single instance (to create a database is to create a single instance, and there are several users by default)

Connection command

1. Conn, usage: conn username / password @ network service name [as sysdba/sysoper]

When connecting as a privileged user, you must bring as sysdba or as sysoper with you

Log in with an empty user:

[oracle@oracle11g ~] $sqlplus / nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 15 22:31:50 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL > show user

USER is ""

SQL >

Log in using the system user

[oracle@oracle11g ~] $sqlplus system/redhat

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 15 19:56:40 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL >

SQL > show user

USER is "SYSTEM"

SQL >

Switch users to scott

SQL > conn scott/redhat

ERROR:

ORA-28000: the account is locked indicates that the user has been locked

Use the system user to unlock

SQL > conn system/redhat

Connected.

SQL > alter user scott account unlock; this command to unlock

User altered.

Then log in again using the Scott user

SQL > conn scott/tiger

ERROR:

ORA-28001: the password has expired informs the user that the scott password expires

Changing password for scott

New password:

Retype new password:

Password changed

Connected.

SQL > show user

USER is "SCOTT"

File manipulation commands:

1. Run the sql script, start / root/a.sql

SQL > start / home/oracle/a.sql

2. Spool inputs the sqlplus screen into a file

SQL > spool / home/oracle/b.sql

SQL > select * from emp

SQL > spool off; saves the content queried by select * from emp to a file

&: can replace a variable, which requires user input when executed

SQL > select * from emp where job='&job'; will enter a value of job instead.

SQL > select * from emp where job='&job'

Enter value for job: MANAGER

Old 1: select * from emp where job='&job'

New 1: select * from emp where job='MANAGER'

EMPNO ENAME JOB MGR HIREDATE SAL COMM

DEPTNO

-

7566 JONES MANAGER 7839 02-APR-81 2975

twenty

7698 BLAKE MANAGER 7839 01-MAY-81 2850

thirty

7782 CLARK MANAGER 7839 09-JUN-81 2450

Display and set environment variables:

SQL > show linesize

Linesize 80

SQL > set linesize 50

Create a user

To create a new user in oracle to use the create user statement, it is generally necessary to have the permission of dba to use the

SQL > create user xiaoming identified by redhat

User created.

SQL > show user

USER is "SYSTEM"

To change the password for the user, dba permission or alter user system permission is required.

SQL > password xiaoming

Changing password for xiaoming

New password:

Retype new password:

Password changed

Or

Sql > alter user username identified by new password

SQL > alter user xiaoming identified by redhat

User altered.

Note: the newly created user cannot log in immediately, and the new user created does not have any permissions, so it cannot be logged in

SQL > conn xiaoming/huang

ERROR:

ORA-01045: user XIAOMING lacks CREATE SESSION privilege; logon deniedWarning: You are no longer connected to ORACLE.

SQL > show user

USER is ""

Delete a user:

Generally, delete a user as dba. If you use another user to delete a user, you need to have the permission of drop user.

For example: drop user user name [cascade]

When deleting a user, note that if the deleted user has already created a table, you need to add a parameter cascade when deleting.

This parameter means to delete the user and the table created by the user

User Management-Authorization

The new user created does not have any permissions and can not even log in. You need to establish the corresponding permissions for them, and use the grant command to give them permissions.

The command to reclaim permissions is revoke

Oracle already has the definition of permission in advance, and there are two kinds

1. System permissions: users' permissions related to database access (database building, table building, indexing, logging in database, etc.)

Create session (140system permissions)

2. Object permissions: users' rights to access data objects of other users

Data objects: tables, views, triggers, etc., created by each user (25)

Oracle role: in order to grant permissions easily, some roles are defined in advance and some permissions are given.

Select certain permissions from the system permissions to assign to the specified role

Connect is one of the roles (including 7 permissions)

Grant connect to xiaoming

There are also two kinds of roles:

1. Custom roles: define roles by yourself

2. Predefined roles: roles that have been established in the database (connect is a predefined role)

Examples of roles:

Connect

Dba: grant the dba role and you will have all the permissions of the dba system

Resource: allows a user to create a table in a tablespace

Example:

SQL > grant connect to xiaoming; Grant the connect role to xiaoming with some permissions

Grant succeeded.

Log in again using user xiaoming:

Once SQL > conn xiaoming/redhat; is authorized, the user xiaoming can log in.

Connected.

The newly created user does not have any tables. Can the newly created user create the table?

SQL > conn xiaoming/redhat

Connected.

SQL > show user

USER is "XIAOMING"

SQL > create table student (id number,name varchar2 (20))

Create table student (id number,name varchar2 (20))

*

ERROR at line 1:

ORA-01031: insufficient privileges

From the above error report, it is found that granting the connect role does not create the table, so what about adding the resource role?

Switch users and grant roles to xiaoming

SQL > show user

USER is "XIAOMING"

SQL > conn system/redhat

Connected.

SQL > grant resource to xiaoming

Grant succeeded.

Create the table again

SQL > show user

USER is "XIAOMING"

SQL > conn system/redhat

Connected.

SQL > grant resource to xiaoming

Grant succeeded.

SQL > conn xiaoming/redhat

Connected.

SQL > create table student (id number,name varchar2 (20))

Table created.

SQL > desc student

Name Null? Type

-

ID NUMBER

NAME VARCHAR2 (20)

You can see that the table was created successfully, which is what the resource role does.

Object permissions: users' permissions to access data objects of other users

1 、 select

2 、 insert

3 、 update

4 、 delete

5 、 all

6. Create index, etc.

Data objects: tables, views, triggers, etc., created by each user (25)

Can the user xiaoming query a table under the scott user?

SQL > select * from emp

Select * from emp

*

ERROR at line 1:

ORA-00942: table or view does not exist can clearly see that the table emp under scott cannot be accessed.

How do you do it?

Because the table emp is below the scott user, you need to use the scott user to authorize the xiaoming user

SQL > conn scott/redhat

Connected.

SQL > grant select on emp to xiaoming; uses the scott user for authorization

Grant succeeded.

SQL > conn xiaoming/redhat connects to xiaoming as a user

Connected.

SQL > select * from emp; queried table emp, but still failed because it is scoot table emp

Select * from emp

*

ERROR at line 1:

ORA-00942: table or view does not exist

So you need to query like this

The table below for SQL > select * from scott.emp; scott users

EMPNO ENAME JOB MGR HIREDATE SAL COMM

DEPTNO

-

7369 SMITH CLERK 7902 17-DEC-80 800

twenty

Revoke permission revoke

Scott withdraws the right of xiaoming to query.

Revoke select on emp from xiaoming

Revoke withdraws permissions. It must be taken back by the user to whom the original user gave the rights.

SQL > show user

USER is "XIAOMING"

SQL > conn scott/redhat

Connected.

SQL > revoke select on emp from xiaoming

Revoke succeeded.

Maintenance of permissions:

1. If it is an object permission

Hope that xiaoming users can query scott's emp table, and also hope that xiaoming can give this permission to another user?

Use scott users to authorize as follows

SQL > show user

USER is "SCOTT"

SQL > grant select on emp to xiaoming with grant option

Grant succeeded.

Then create a new user to grant permissions to the connect role

SQL > conn system/redhat

Connected.

SQL > create user xiaobai identified by redhat

User created.

SQL > grant connect to xiaobai

Grant succeeded.

Then use the xiaoming user to authorize the xiaobai user

SQL > conn xiaoming/redhat

Connected.

SQL > grant select on scott.emp to xiaobai

Grant succeeded.

Finally, xiaobai users are used to query the emp table.

SQL > conn xiaobai/redhat

Connected.

SQL > select * from scott.emp

EMPNO ENAME JOB MGR HIREDATE SAL COMM

DEPTNO

-

7369 SMITH CLERK 7902 17-DEC-80 800

twenty

2. If it is the system permission

System users authorize xiaoming and enable users to authorize permissions to other users

Grant connect to xiaoming with admin option; with admin option, it's just different.

Use profile to manage user passwords

Profile is the command set of password restriction and resource restriction. When supervising the database, oracle will set up automatically.

For a profile named default, when the established user does not specify the profile option, oracle will assign the default to the user

1. Account locking

Specify the maximum number of times the password can be entered when the account is logged in, or the time when the user is locked out. Generally, the command is executed under the identity of dba.

For example: specify that the user tea can only try to log in at most three times, and the lockout time is two days.

Create a profile file (rules)

SQL > create profile lock_account limit failed_login_attempts 3 password_lock_time 2;-"lock_account is the name of profile.

SQL > alter user tea profile lock_account; adds profile file constraints to user tea

2. Resolve locking:

SQL > conn system/redhat

Connected.

SQL > alter user scott account unlock

User altered.

SQL > conn scott/redhat

Connected

3. Termination password

In order for the user to change the password periodically, it can be done with the instruction of the termination password, which also requires the dba identity to operate.

Example: create a profile file for the user tea created earlier, requiring the user to change his login password every 10 days

The grace period is 2 days-> the grace period means that after 10, you are reminded that you still have two days to modify, which is equivalent to a total of 12 days.

SQL > create profile myprofile limit password_life_time 10 password_grace_time 2

SQL > alter user tea profile myprofile

4. Password history

If you want users not to use previously used passwords when changing passwords, password history stores the information of password changes in a data dictionary.

In this way, when the user changes the password, oracle will compare the new and old passwords, and when they find that the new and old passwords are the same, they will prompt the user to re-enter the password.

Example:

1. Establish the profile file

SQL > create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10

-> password_reuse_time: specify that passwords can be reused after 10 days (like old and new passwords, it will take 10 days to use them)

2. Assign it to a user-"tea"

SQL > alter user tea profile password_history

Delete profile

You can delete a profile file when it is not needed

SQL > drop profile password_history [cascade]

Users who have been constrained with this profile will be invalidated

Cascade: cascading relationship

This is the end of this article on "how to operate sql in oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.