CREATE user ORA-01045: user lacks create session privilege; logo

Oracle CREATE user ORA-01045: user lacks create session privilege; logon denied .. conn internal/oraclegrant user aaaa identified by aaaa; conn aaaa/aaaa will report ERROR: SQL> conn aaaa/aaaa will report ERROR: ORA-01045: user aaaa lacks CREATE SESSION privilege; logon denied
Cause: the user must have at least the right to the session, otherwise the connection will fail. The user must have the right to perform other operations on the session. Solution: www.2cto.com 1 grant connect, resource to aaaa; 2 grant create session to aaaa; Oracle Security first: user Management ************************************** * ***** the user must have at least the session rights, otherwise, the connection will fail. The user should have the right to perform other operations on the session right. Oracle users and passwords are case-insensitive, which is really disappointing. 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 takes an optimistic attitude; oracle first assumes that users are insecure and adopt a pessimistic attitude. Users in Oracle are isolated from each other, which is called the user mode;
Built-in user: www.2cto.com sys, Network Administrator, highest permission; when you try to enter the User name: sys password: Universal, you find that the connection fails, and enter the User name: system password: common or user name: scott password: tiger
The system permission is not as high as the system permission. In fact, because of the high permissions of sys, you must 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; while dba_users can view the users managed by the dba; system, only databases on the local machine are managed. all_users can only view all users by dba. ****** create users: www.2cto.com create user username identified by password [externally] [default tablesapce tablespace name] [temporary tablespace name] [quota integer K | M | unlimited on tablespace name] // note: you must have the dba permission to create a user;
* ***** Query User: it is actually to query the table user_users/dba_users/all_users, but they are not called tables or data dictionaries here, and the data dictionary is maintained by the system; * ****** modify the user: alter user ..... // It is the same as the one created later. An Oracle instance is a database, which is equivalent to all the databases of SQL Server. An Oracle database is divided into many tablespaces, each tablespace is equivalent to a database in SQL Server. Example: create user student_useridentified by studentdefault tablespace userstemporary tablespace tempquota 5 m on usersquota 3 m on temp
/Users and temp are built-in tablespaces. student_user has 5 MB space in users and 3 MB space in temp. Newly created users do not have the right: SQL> conn student_user/student; ERROR: www.2cto.com ORA-01045: user STUDENT_USER lacks CREATE SESSION privilege; logon denied warning: you no longer connect to ORACLE. * *** Restrict user lock: alter user Username account lock; effect: SQL> alter user student_user account lock; user changed. SQL> conn student_user/student; ERROR: ORA-28000: the account is locked warning: you no longer connect to ORACLE. Unlock: alter user Username account unlock; password invalid: alter Username password expire; when a user creates many tables, if the user is deleted directly, all the tables below it will be deleted, so we can only invalidate the password;
// It can be seen that the table is owned by the user, and the user has no more tables. the space occupied by the tables created by the user is gone; // Therefore, we should not delete the user in general; * *** delete a user: www.2cto.com drop user Username [cascade] // cascade is forcibly 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] if it is a dcl statement, grant all on student to public: grant all permissions to student to all users. Data Dictionary: user_sys_privs select * from User_sys_privs permission revocation: www.2cto.com revoke permission list on table from user; SQL> revoke create table from iam; revocation successful.
Withdrawal only revokes the permission of this authorization. Therefore, if a user has a permission and you grant it the same permission, then when you revoke the permission, it originally had this right. Third: role management ************************************** * ** because a user may need a set of permissions, when we get a new user, we need to authorize each user the required permissions in turn. A role is a set of permissions, and users can be directly granted this role, users naturally have the rights of their roles;
Create 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: it indicates that the user granted this role needs to check when using the set role command; www.2cto.com revokes the permission: revoke permission from role name;
// The role is the set of permissions. instance: create role student_role/grant create table, create session, create view to student_role/grant student_role to scott/revoke create view from student_role/when a role's permissions are revoked, the permissions granted to the role are also revoked;