Ora_security-1 Creating and Managing database Users

One, user account attributes

1 , user name

by (Letters, numbers  $  _ ), the number of characters is less than - the next. Cannot be a reserved word. Letters are case insensitive. The user name is not allowed to be modified once it is created. If you must change it, you must first delete the account and then create one.

Note: The user name is specified in double quotes and does not follow the execution rules (except for length)

Sql>create user Peenboo identified Bypassword;

Sql>create user "peenboo#%" identified by Pa55w0rd;

2 , default table space, and quotas

2.1 Each user account has a default tablespace. Any schema objects that the user creates, such as tables or views, are saved in this table space. Unless you specify another tablespace when you create the object, it will be placed in the default table space.

You can set a default tablespace when you create a database, or you can make changes by using the following syntax:

Alterdatabase DEFAULT tablesapce tablespacename;

Note: When you create a database without specifying a default tablespace, the SYSTEM set as the default tablespace.

2.2 Quotas ( Quota ) is the amount of space in the tablespace that is allocated for the user's pattern object.

Sql>select Username,default_tablespace,temporary_tablespace from Dba_users

where username= ' Peenboo ';

Sql>alter user Peenboo quota 10m on users;

Sql>alter User Peenboo quota unlimited onexample;

Sql>select tablespace_name, Bytes, max_bytes from Dba_ts_quotas;

3 temporary table space ( Temporary tablespace )

The management of the temporal tablespace is fully automated. The objects in the temporary tablespace are not owned by the user, but belong to the sys user, andthesys user has unlimited quotas on all table spaces.

To change a user's temporary tablespace:

Alteruser username Temporary tablespace tablespace_name;

4 configuration file

A configuration file is an effective way to manage passwords and resources, but it only works for environments in which each application has its own database user account.

5 , account status

through dba_users of the Account_status you can see the status of each user account.

OPEN -- open (Available status)

LOCKED-- Lock ( DBA intentionally locked, no user can connect to the account)

EXPIRED-- Expired (indicates that the life cycle of the password has been terminated)

EXPIRED &locked-- not only is it locked, but its password has expired

EXPIRED (GRACE)--" normal time period effective " , passwords expire at the end of the lifecycle and can be configured with a normal time period where passwords can be modified.

LOCKED (TIMED)-- the account has been locked because the login attempt failed

expired&locked (TIMED)-- expired and time-out locked

EXPIRED (GRACE) &locked-- normal expiration and locking

EXPIRED (GRACE) &locked ( TIMED ) -- Normal expiration and timeout lockout

--The syntax for locking and unlocking is as follows:

Alteruser username Account LOCK;

Alteruser username Account UNLOCK;

--Force the user to change the password the next time they try to log on:

Alteruser username PASSWORD EXPIRE;

Second, the authentication method

1 , operating system, and password file authentication

to perform operating system and password file authentication for an account, the user must be authorized ( SYSDBA or Sysoper ) Permissions:

GRANT [SYSDBA | sysoper] to username;

Use Sqlplus when you log on, the syntax is concatenated as follows:

CONNECT Username/password [@db_alias] as [SYSDBA | sysoper]

Note: To determine who granted the SYSDBA and the Sysoper permissions, you can pass v$pwfile_users to query.

All user sessions must be authenticated. There is no "anonymous login" argument.

2 , password Authentication

sql> CONNECT Username/password [@db_alias]

Whether you change yourself or someone else's password, the syntax is:

ALTER USER usernameidentified by password;

3 , external authentication

in obtaining authentication, you can use external services server, radius Span style= "Font-family:simsun;" > server or windows Span style= "FONT-FAMILY:CALIBRI;" >windows local province authentication service.

if not enabled advancedsecurity In detail, the only external authentication that can be used is operating system authentication. This technique uses the same name as the operating system user account to create an ORACLE account, but preceded by the string specified by the Os_authent_prefix instance parameter. The string defaults to ops$. You can also query by the following ways:

Sql>select value from V$parameter where name = ' Os_authent_prefix ';

if the Linux or Unix is simpler, such as the user name Peenboo

Sql>create user Ops$peenboo identified by externally;

Sql>grant create session to Ops$peenboo;

Sql>sqlplus/

4 , Global identity authentication ( Global Authentication )

Use LDAP Server, global user ( Global User ) is in LDAP the user defined in the catalog.

There are two global identity authentication technologies:

Users can be defined in the directory or in the database.

Users can only be defined in the catalog.

Third, create an account

1 CREATE USER must contain two required parameters: User name and authentication method.

Sql>createuser Scott identified by Tiger

Default tablespace user Temporary Tablespace Temp

quota100m on users, quota unlimited on example

Profiledeveloper_profile

Password expire

Accountunlock;

2 In addition to the name, you can ALTER USER command to adjust each attribute on the account.

( 1 ) Change Password

Sql> alter user Scott identified by Lion;

(2) modifying the default tablespace and temporary table space

sql> ALTER user Scott default Tablespacetbs_peenboo temporary tablespace pbtemp;

(3) Modify Quotas

sql> ALTER user Scott Quato unlimited Ontbs_peenboo, Quato 0 on users;

(4) Modify the configuration file

Sql>alter user Scott profile Prod_profile;

(5) force change of password

SQL > ALTER user scoot password expire;

(6) lock user

sql> ALTER user Scott account lock;

(7) user has been created, need to delete rebuild

sql> drop user Scott;

(8) The user is a non-empty user, the following commands are required:

sql> drop user Scott Cascade;

Public user, is a conceptual user, if you want to grant permissions to each user, you can grant permissions to Public user.

This article is from the "struggle more than" blog, please be sure to keep this source http://peenboo.blog.51cto.com/2865551/1790443

Ora_security-1 Creating and Managing database Users