Seven differences between MySQL and Oracle: User Permissions: mysqloracle

Seven differences between MySQL and Oracle: User Permissions: mysqloracle

User Permissions

No.	Category	ORACLE	MYSQL	Note
1	Create user	Create user user_name identified by user_password Default tablespace starSpace temporary tablespace temp;	Create user user_name identified by user_password;	1. Create a user in oracle Oracle has three default users: sys/system/scott. here, sys and system are system users and have dba permissions. scott is a demo account of Oracle Database. They are created during database installation and do not have dba permissions. Create USER command: Create user user_name identified by user_password [Default tablespace tableSpace] [Temporary tablespace tableSpace }; Note: Each user has a default tablespace and a temporary tablespace. If not specified, oracle sets system as the default tablespace and temp as the temporary tablespace. 2. Create a user in mysql Create USER command: Mysql> create user yy identified by '20140901 '; Yy indicates the username you want to create, and 123 indicates the password. The user created above can log in anywhere. If you want to restrict Logon at a fixed address, such as localhost Logon: Mysql> create user yy @ localhost identified by '20140901 ';
2	Delete a user	Drop user user_name cascade;	Drop user user_name;	1. Oracle SQL> drop user Username; // The user has not created any entity SQL> drop user Username CASCADE; // delete all users and their created entities Note: The user currently being connected cannot be deleted. 2. Mysql After 4.1.1, you can use the drop user statement to delete a MYSQL account. However, in versions earlier than 5.0.2, the drop user statement can only delete users without any permissions. In Versions later than 5.0.2, the drop user statement can delete any user. (Of course, You cannot delete yourself ). Example: drop user "garfield" @ "localhost ". Don't forget to add the following @. Otherwise, an error will be reported. To delete a MYSQL account in versions 4.1.1 and 5.0.2, perform the following operations. 1) Use the show grants statement to check the permissions of the MYSQL account to be deleted, for example, show grants for "garfield" @ "localhost ". 2) use the revoke statement to revoke the permissions of the user in show grants. Executing this statement will delete related records from all other permission tables except the user table, and revoke the global permissions of the user in the user table. 3) use the drop user statement to delete a user from the user table.
3	Change Password	Alter user user_name identified by new_password	Mysqladmin-u root-p 123456 password "your password ";	1. Change mysql password Method 1: 1) the root account has no password before the change. C: \ mysql \ bin> mysqladmin-u root password "your password" 2) If the root user has a password before the change, assume that the password is 123456. C: \ mysql \ bin> mysqladmin-u root-p123456 password "your password" Note: The changed password cannot be enclosed in single quotes. It can be enclosed in double quotes or no quotation marks. Method 2: 1) c: \ mysql \ bin> mysql-uroot-p password to log on as root 2) mysql> use mysql select database 3) mysql> update user set password = password ('your password') where User = 'root '; 4) mysqlflush privileges; reload the permission table
4	Set User Permissions	Grant connect to star -- the star role allows users to connect to the database and create database objects. Grant resource to star -- the star role allows users to use the bucket in the database. Grant dba to star -- DBA permission	Grant all on picture. * TO test identified by "test ";	1. For more information, see <oracle vs mysql user privilege. Doc> 2.1 Oracle permission settings 2. For more information, see <oracle vs mysql user privilege limit .doc> 1.4 user permission settings
5	Revoke permissions	Revoke select, update on product from user02;	REVOKE privileges (columns) ON what FROM user	1. Oracle The basic format of the Revoke statement is as follows: REVOKE permission type [(Field List)] [, permission type [(Field List)]…] ON {database name. Table name} FROM username @ domain name or IP address For example, the Administrator revokes the permission of admin @ localhost to create and create databases and tables for the database xsxk, and revokes the permission that the user can grant to other users, run the following command. Mysql> revoke create, drop on xsxk. * from admin @ localhost; Mysql> revoke grant option on xsxk. * from admin @ localhost; The "User Name @ domain name or IP address" section in the revoke statement must match the "user name @ domain name or IP address" section in the original grant statement, and the "permission type" section can be an authorized part of the permission. In addition, revoke can only revoke permissions and cannot delete user accounts. The user's records are retained in the authorization table. Users can still connect to the database server. To completely delete a user, use the delete statement mentioned earlier to delete the user record from the user table. 2. Mysql To cancel a user's permissions, use the REVOKE statement. The syntax of REVOKE is very similar TO the GRANT statement, except that it is replaced by from without the indetifed by and with grant option clauses: REVOKE privileges (columns) ON what FROM user The user part must match the user part of the user you want to revoke permission from the original GRANT statement. Privileges does not need to be matched. You can use the GRANT statement to GRANT permissions, and then use the REVOKE statement to REVOKE only some permissions. The REVOKE statement only deletes permissions, but does not delete users. Even if you revoke all permissions, the user records in the user table are retained, which means that the user can still connect to the server. To completely Delete a user, you must use a Delete statement to explicitly Delete user records from the user table.