Oracle creates user Ora-01045:user lacks create SESSION Privilege;logon denied. The problem

Oracle creates user Ora-01045:user lacks create SESSION Privilege;logon denied. The problem

Conn Internal/oracle

Grant user aaaa identified by AAAA;

Conn AAAA/AAAA will error:

Sql>conn AAAA/AAAA will error:

ERROR:

Ora-01045:user AAAA lacks CREATE SESSION privilege; Logon denied

Reason:

The user needs at least the right to session, or the connection is unsuccessful;

The user shall have the right to have other actions on the right of the session;

Workaround: www.2cto.com

1

Grant Connect, resource toaaaa;

2

Grant create session to AAAA;

Oracle's security

First: User Management *******************************************

The user needs at least the right to session, or the connection is unsuccessful;

The user shall have the right to have other actions on the right of the session;

Oracle's user and password is not case-sensitive, it is really surprising;

Oralce, all users must be explicitly authorized in order to operate;

In SQL Server, the user created automatically has some permissions;

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

Microsoft assumes that most users are legitimate users and take an optimistic attitude;

Oracle first assumes that the user is insecure and takes a pessimistic attitude;

Users in Oracle are isolated from each other, known as user mode;

Built-in User: www.2cto.com

SYS, network administrator, highest authority;

When you try to enter the user name: sys

Password: Universal

After that, you find that the connection is not up;

and enter the user name: System

Password: Universal

or user name: Scott

Password: Tiger

Can be connected, so you think

SYS has no system-high permissions;

In fact, because SYS has a high authority, you have to

DBA identity to login: sys/General as Sysdba

You can use the data dictionary user_users to view the users managed by the current user:

SELECT * Fromuser_users;

Dba_users can view DBA-managed users;

System, manage only the database on this computer;

And All_users View all users, only the DBA to view;

Create User: Www.2cto.com

Create user username identified by password [externally]

[Default TABLESAPCE tablespace name]

[Temporary tablespace temp table space name]

[Quota integer k| m|unlimited on table space name]

Note: The creation user must have DBA authority;

Query users:

is actually to query the table user_users/dba_users/all_users,

Just don't call them a table, a data dictionary, and a data dictionary is a system

maintenance of;

To modify a user:

Alter user.....//back and create the same;

An instance of Oracle is a database that is equivalent to all SQL Server databases;

A database of Oracle is divided into a number of table spaces, each of which corresponds to a database in SQL Server;

Example Demo:

Create User Student_user

Identified by student

Default Tablespace users

Temporary Tablespace Temp

Quota 5m on users

Quota 3m on Temp

/Among them, users,temp are built-in table space, Student_user has 5m space in the users, 3m in temp;

The newly created user is not entitled to:

Sql> connstudent_user/student;

Error:www.2cto.com

Ora-01045:user student_userlacks CREATE SESSION privilege; Logon denied

Warning: You are no longer connected to ORACLE.

Restrict users

Lock: Alter user username account lock;

Effect:

Sql> alter Userstudent_user account lock;

The user has changed.

Sql> connstudent_user/student;

ERROR:

Ora-28000:the Accountis Locked

Warning: You are no longer connected to ORACLE.

Unlock: Alter user username account unlock;

Invalid password: Alter user name password expire; When a user creates a number of tables,

If you delete the user directly, the table below will be deleted, so we can

Only makes its password invalid;

Visible, the table is the user's hand, the user has not, the user created the table, occupy the space is gone;

Therefore, in general, we do not delete users;

Delete User: Www.2cto.com

Drop user username [cascade]

Cascade is forced to delete even if someone else is using this user's table;

The database management statement is the automatic end transaction, does not have the rollback opportunity;

Second: Rights Management ***********************************************

Authorization: Grant [System privilege name] [role] [on authorized action table name] to [list of user names]

[Public] [With Adminoption]

If it is a DCL statement, you do not have to be authorized to manipulate the table name

Grant all on student Topublic: grants all users access to student;

Data dictionary: User_sys_privs

SELECT * Fromuser_sys_privs

Permission retraction: www.2cto.com

Revoke permissions list on table from user;

sql> revoke createtable from IAM;

Undo success.

Undo just revokes permission from this authorization, so if a user has a certain right,

And you give it the same right, and then you take back this privilege when it turns out

The right to have is still there;

Third: Role Management *****************************************

Because a user may need a collection of certain permissions, when we get a new user, we need to give a

The user authorizes each of the permissions it requires, and the role is a collection of permissions that the user can directly grant to this

Role, the user naturally has the right to its role;

To create a role:

Create role name [noidentified|identified by password |externally]

Not identified: Indicates that the user granted the role does not need to be tested while in use;

Identified by: Indicates that the user granted the role needs to be tested when using the set role command;

Www.2cto.com

Revoke permissions: Revoke permissions from role name;

A role is a collection of permissions;

Instance:

Create role Student_role

/

Grant Create table,createsession,create view to Student_role

/

Grant Student_role to Scott

/

Revoke CREATE VIEW Fromstudent_role

/

When a permission for a role is retracted, the permission of the user to whom the role is granted is also recovered accordingly;

Oracle creates user Ora-01045:user lacks create SESSION Privilege;logon denied. The problem