OracleDBA user management, oracledba user management

OracleDBA user management, oracledba user management

I would like to share with you the management of users in Oracle. The following are my Oracle learning notes stored on the database. I will share them with you today. For more information, see the source, the following Oracle version is 10 Gb. Some commands may be different from those of Oracle11g in the WinServer2003 operating system, but most of them are the same, next, we will continue to share the Oracle database management, table management, Oracle stored procedures, and PL/SQL programming. The Oracle management tools used are PL/SQL Developerl and SQL PLUS. You are welcome to criticize and correct them.

1. User Creation

The create user statement is used to create a database in an Oracle database. Generally, the database can be created only with dba permissions.
Demo:

SQL> create user UserName identified by UserPassWord; SQL> the user has been created. (When creating a user, the user password must start with a letter; otherwise, the user cannot be created)

 

2. Change the password
Change User Password:
You can use

1 SQL> password;

To change the password, you must have the database dba permission or the system alter user permission.

1 SQL> alter user Username identified by new password;

 

3. delete a user:
Database dba or drop user permissions are required.

1 demo: SQL> drop user Username [cascade] cascade deletes the table created by the user while deleting the user;

4. Grant Permissions

The created user does not have any permissions. You need to grant some database operation permissions to the user with the keyword grant, and revoke the user's permissions with the revoke

Grant the user the permission to connect to the database:
　

SQL> grant connect to user name;

 

System permission transfer settings:

SQL> grant connect to user name with admin option;

 

Permissions in the oracle database include system permissions and object permissions.
System permissions: User-related permissions on databases, such as creating databases, deleting databases, creating and deleting users;
Object permission: the user's permission to operate on its data objects, such as tables, views, functions, and processes:

5. Roles in oracle:
Role concept: a role is a combination of Multiple permissions, including pre-defined roles and custom roles, such as connect permissions, dba permissions, and resource permissions;
The connect permission is used to grant the user the permission to connect to the database. The dab permission is the highest permission for the database. The resource permission is used to grant the user the permission to use the database storage resources, that is, to create and operate tables;

Object permissions: select, insert, update, delete, all (including the above four), create index ...... Commonly used syntax is as follows:

1 grant permission on table name to user name; demo: grant select on emp to ludashi;

Grant the select object permission of the table under scott to li;

SQL> grant select on emp to li; SQL> conn li/li; SQL> select * from scott. emp; -- query the emp table under scott, which must be scott. emp can be queried successfully. This involves the concept of "solution.

If you are a dba user, grant the select object permission of the emp table under user scott to the li User:

SQL> grant select on scott. emp to li; (solution name. Table Name );

Set the user permission Portability:

SQL> grant select on emp to li with grant option; -- the li user can grant permissions granted to others;

 

6. Revoke User Permissions:
Revoke permission on table name from user name;
Scott withdraws the select permission assigned to the li User:

1 revoke select on emp from li;

 

The sys user withdraws scott and assigns the select permission of his emp table to li;

revoke select on scott.emp from li;

 

Desc command: desc table name; displays the table structure;

 

7. Transfer of Oracle permission revocation:
Recovery of system roles (such as connect and resources ):
Sys User: -- assign the database connection role to test1 and set it to pass-through;

sql>grant connect to test1 with admin option;

Test1 User: -- test1 pass the database connection permission to test2

sql>grant connect to test2;

 

Both test1 and test2 can connect to the database, that is, test1 passes the database connection permission to test2;

Sys User: -- sys user revokes the permission of test1 to connect to the database

sql>revoke connect from test1;

After revoking the permissions of the test1 user, test1 cannot connect to the database.

The role passed to test2 still exists while revoking the role test1 for the system role;

 

 

8. Permission revocation issues:
Sys User: -- sys user assigns the select permission of the emp table under scott to test1 and sets it to pass-through;

sql>grant select on scott.emp to test1 with grant option;

Test1 User: grant the select permission on the emp table to test2;

sql>grant select on scott.emp to test2;

Sys User: -- revoke the select permission of test1

　sql>revoke select on scott.emp from test1;

Revoke the select permission of test1 and revoke the permission of test2;

 

 

9. profile user permission management:
Oracle sets the number of user logins, and automatically locked the user after several logon failures;

Sys> User: create profile to create a configuration file. Set the configuration file named fileName after the password is locked for two days after three wrong password attempts. SQL> create profile fileName limit failed_login_attempts 3 password_lock_time 2; SQL> alter user userName profile fileName;

 

User unlock:

sql> alter user userName account unlock;

Oracle sets the password expiration time, that is, the password validity period;
Sys User: Create the configuration file fileName. Set the password expiration time to 10 days and the grace day to two days;

sql> create profile fileName limit password_life_time 10 password_grace_time 2;

What is the difference between oracle DBA and oracle?

What is the difference between oracle DBA and oracle?
-Oracle, equivalent to re-login. the user's home directory and PATH information will change.
Su oracle: After switching to oracle, the user's home directory and PATH are still the original user's home directory and PATH

Oracle question: what is the use of ora_dba in my "right-click a computer> Manage> local user and group> group?

This is a special ORACLE user and super administrator privilege. built as a DBA, it has the highest permission to manage databases;