[Oracle] User Creation and permission management, oracle permission management

[Oracle] User Creation and permission management, oracle permission management

# Creating a user requires the administrator privilege SQL> create user lzz identified by lzz123; // a common user is created, but not unlocked # change the password for the user # for yourself: SQL> password Change SCOTT's old password: *** new password: *** re-enter the new password: *** the password has been changed # For the user: SQL> alter user lzz identified by lzz; # delete a user (with DBA permissions) or drop user permissions, you cannot delete yourself. # If the user you want to delete has already created a table, you need to include the cascade SQL> drop user lzz parameter when deleting the table. # Users with permissions granted to other users include sys and system. There are many types of permissions. There are more than 100 roles. # roles are a set of permissions and assign roles to a user, this user has all permissions in this role. User-defined roles: users can define their own permissions to pre-Define roles for the role as needed: the system pre-defined (connec, dba, resource) statement is: grant connect to lzz; # data object: tables and views, as well as constraints, sequences, functions, stored procedures, and so on # Oracle permissions have two types of system permissions: Users have permissions on database-related objects: command: grant select on emp to lzz; # syntax for revoking permissions on Data Objects of other users: revoke select on emp from lzz; # transfer permissions for maintaining permissions: -- add with grant option if it is an object permission, for example: grant select on emp to lzz with grant option; -- if it is a system permission, join with admin op. Tion example: grant connect to xiaoming with admin option; # using profile to manage user passwords is a password restriction, and the resource restriction command set (1) account locking: specify the maximum number of times a user can enter a password during logon, or specify the lock time. Generally, the dba user executes this command. case: create profile file SQL> create profile aa limit 2 failed_login_attempts 3 password_lock_time 2; the configuration file has been created. SQL> alter user qinke profile aa; the user has changed. # Write the correct password after three errors SQL> conn qinke/qinke; ERROR: ORA-28000: the account is locked (2) unlock the account: SQL> alter user qinke account unlock; the user has changed. (3) Termination password: in order to allow the user to change the password regularly, the termination password can be used to complete the case: every 10 days, the extended limit is 2 days (measured in days) SQL> create profile aaa limit password_life_time 10 password_grace_time 2; the configuration file has created SQL> alter user qinke profile aaa; the user has changed. # Delete profile drop profile password_his [cascade];

Here are several small operations:

1. Create an object lzz and enable it to log on (system permission)

 

 

 

Add a predefined role to this user.

Successful.

2. You can create a table using lzz.

 

3. The lzz user can query scott's emp table (Object permission)

 

4. The scott user revokes the lzz permission to query scott's emp table.

 

 

5. lzz obtains the query permission of scott's emp table and passes it to qinke.

 

? What if scott revokes the query permission of the emp table?
The answer is that qinke cannot query the emp table information any more)

 

 

 

Create a user in oracle and assign the Administrator permission to the created user.

Create a user:
Create user Username identified by password default tablespace temporary tablespace;
Grant permissions:
Grant connect, resource, create any view, create any synonym, create database link to user name;

Permission issues for new oracle users

You have created a new user but have not authorized the user
SYS Input
Grant connect, resource to yh;
Grant yh connection and resource operation Permissions
Try logging in now. This time yh can be accessed as a normal user.