1 [root@zlm2 09:25:29 ~] 2 #mysql -uaaron8219 -pzlm -h192.168.1.101 3 mysql: [Warning] Using a password on the command line interface can be insecure. 4 Welcome to the MySQL monitor. Commands end with ; or g. 5 Your MySQL connection id is 4 6 Server version: 5.7.21-log MySQL Community Server (GPL) 7 8 Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 9 10 Oracle is a registered trademark of Oracle Corporation and/or its 11 affiliates. Other names may be trademarks of their respective 12 owners. 13 14 Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. 15 16 (firstname.lastname@example.org 3306)[(none)]>show databases; //The user "aaron8219" can see all the databases in the current MySQL instance. 17 +--------------------+ 18 | Database | 19 +--------------------+ 20 | information_schema | 21 | mysql | 22 | performance_schema | 23 | sys | 24 | sysbench | 25 | zlm | 26 +--------------------+ 27 6 rows in set (0.01 sec) 28 29 (email@example.com 3306)[(none)]>create database aaron8219; 30 Query OK, 1 row affected (0.00 sec) 31 32 (firstname.lastname@example.org 3306)[(none)]>use aaron8219; 33 Database changed 34 (email@example.com 3306)[aaron8219]>create table t1( 35 -> id int, 36 -> name char(10) 37 -> ) engine=innodb; 38 Query OK, 0 rows affected (0.02 sec)
All databases include the administrative accounts SYS, SYSTEM, SYSMAN, and DBSNMP
With access control enabled, ensure you have a user
admin database. This user can administrate user and roles such
as: create users, grant or revoke roles from users, and create or modify
You can create users either before or after enabling access control. If
you enable access control before creating any user, MongoDB provides
allows you to create a user administrator in the
admin database. Once
created, you must authenticate as the user administrator to create
additional users as needed.
1 SQL> alter system set os_authent_prefix=’’ scope=spfile; 2 SQL> create user user_name identified externally; 3 SQL> grant create session to user_name; 4 $ sqlplus / #when user_name logs in to the database server,this user can connect to SQL*Plus.
MySQL特权系统确保所有用户只能执行允许的操作。 作为用户，当您连接到MySQL服务器时，您的身份由您连接的主机和您指定的用户名决定。 在连接后发出请求时，系统会根据您的身份和您要执行的操作授予权限。
SYSDBA and SYSOPER are administrative privileges required to perform high-level administrative operations such as creating, starting up, shutting down, backing up, or recovering the database. The SYSDBA system privilege is for fully empowered database administrators and the SYSOPER system privilege allows a user to perform basic operational tasks, but without the ability to look at user data.
The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control of these privileges is therefore completely outside of the database itself. This control enables an administrator who is granted one of these privileges to connect to the database instance to start the database.
You can also think of the SYSDBA and SYSOPER privileges as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other way. For example, if you have the SYSDBA privilege, then you can connect to the database using AS SYSDBA.
The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as a SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. Oracle Enterprise Manager Database Control does not permit you to log in as user SYS without connecting as SYSDBA or SYSOPER.
When you connect with the SYSDBA or SYSOPER privilege, you connect with a default schema, not with the schema that is generally associated with your user name. For SYSDBA this schema is SYS; for SYSOPER the schema is PUBLIC.
When you connect as user
SYS, you have unlimited privileges on data dictionary tables. Be certain that you do not modify any data dictionary tables.
For example, connect
to the instance.
mongo --port 27017
Specify additional command line options as appropriate to connect
to your deployment, such as
Oracle strongly recommends that you set the OS_AUTHENT_PREFIX parameter to a null string
SHOW GRANTS FOR 'joe'@'office.example.com'; SHOW GRANTS FOR 'joe'@'home.example.com'; MySQL在识别您时会考虑您的主机名和用户名，因为没有理由假定给定的用户名属于所有主机上的同一个人。 例如，从office.example.com连接的用户joe不需要是与 从home.example.com连接的用户joe相同的人员。 MySQL通过使您能够区分恰好相同名称的不同主机上的用户来处理这个问题：您可以通过joe从office.example.com 授予一组连接权限，并通过joe从home获取一组不同的特权 .example.com。 要查看给定帐户具有的特权，请使用SHOW GRANTS语句。 例如：
You can alter your current user’s session to point at a different schema via ALTER SESSION statement
1 SQL> alter session set current_schema = hr;
Assiging Default Permanent and Temporary Tablespaces
1 SQL> alter user user_name default tablespace tb_name temporary tablespace temp_name;
1 SQL> alter user user_name identified by new_password;
SQL*PLUS password command
1 SQL> passw user_name 2 Changing password for user_name 3 New password:
1 SQL> alter user user_name account lock; 2 SQL> alter user user_name quota 500m on users;
Dropping Users. Before you drop a user,I recommend that you first lock the user.Locking the user prevents others from connecting to a locked database account.
1 SQL> alter user user_name account lock; 2 SQL> select username,lock_date from dba_users; 3 SQL> alter user user_name account unlock; 4 SQL> drop user user_name; 5 SQL> drop user user_name cascade; #the prior commend won’t work if the user owns any database objects.Use the CASCADE clause to remove a user and have its objects dropped.
Password Strength. You can enforce a minimum standard of password complexity by assigning a password verification function to a user’s profile. Oracle supplies a default password verification function that you create by running the following script as the SYS schema
1 SQL> @?/rdbms/admin/utlpwdmg 2 SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION ora12c_verify_function; 3 SQL> alter profile default limit PASSWORD_VERIFY_FUNCTION null; #disable the password function.
Limiting Database Resource Usage
1 SQL> alter system set resource_limit=true scope=both;
Assigning Database System Privileges
1 SQL> select destinct privilege from dba_sys_privs; 2 SQL> grant create session to user_name #minimally a user needs CREATE SESSION to be able to connect to the database. 3 SQL> revoke cteate table from user_name; #to take away privileges. 4 SQL> grant create table to user_name with admin option; #allows you to grant a system privilege to a user and also give that user the ability to administer a privilege.You can do this with the WITH ADMIN OPTION clause.
Assigning Database Object Privileges
1 SQL> grant insert,update,delete,select on object_owner to user_name; 2 SQL> grant insert(id,name,desc) on table_name to user_name #grants INSERT privileges to specific columns in the table. 3 SQL> grant insert on object_owner to user_name with grant option; #if you want a user that is being granted object privileges to be able to subsequently grant those same object privileges to other users,then use the WITH GRANT OPTION clause.
Grouping and Assigning Privileges
1 SQL> create role role_name; 2 SQL> grant select any table to role_name; 3 SQL> grant role_name to user_name;
1 (root@localhost mysql3306.sock)[(none)]>create user aaron8219@'192.168.1.101' identified by 'zlm'; 2 Query OK, 0 rows affected (0.00 sec) 3 4 (root@localhost mysql3306.sock)[(none)]>select user,host from mysql.user; 5 +---------------+---------------+ 6 | user | host | 7 +---------------+---------------+ 8 | rpl_mgr | % | 9 | aaron8219 | 192.168.1.% | 10 | repl | 192.168.1.% | 11 | replica | 192.168.1.% | 12 | zlm | 192.168.1.% | 13 | aaron8219 | 192.168.1.101 | 14 | mysql.session | localhost | 15 | mysql.sys | localhost | 16 | root | localhost | 17 +---------------+---------------+ 18 9 rows in set (0.00 sec) 19 20 (root@localhost mysql3306.sock)[(none)]>grant all privileges on aaron8219.* to aaron8219@'192.168.1.101'; //Grant the privileges only on "aaron8219" database. 21 Query OK, 0 rows affected (0.00 sec) 22 23 (root@localhost mysql3306.sock)[(none)]>show grants for aaron8219@'192.168.1.101'; 24 +----------------------------------------------------------------------+ 25 | Grants for firstname.lastname@example.org | 26 +----------------------------------------------------------------------+ 27 | GRANT USAGE ON *.* TO 'aaron8219'@'192.168.1.101' | 28 | GRANT ALL PRIVILEGES ON `aaron8219`.* TO 'aaron8219'@'192.168.1.101' | 29 +----------------------------------------------------------------------+ 30 2 rows in set (0.00 sec)
MySQL considers both your host name and user name in identifying you
because there is no reason to assume that a given user name belongs to
the same person on all hosts. For example, the user
joe who connects
office.example.com need not be the same person as the
joe who connects from
home.example.com. MySQL handles this by
enabling you to distinguish users on different hosts that happen to have
the same name: You can grant one set of privileges for connections
office.example.com, and a different set of privileges
for connections by
home.example.com. To see what privileges
a given account has, use
SHOW GRANTS statement.
-p <password>, and
--authenticationDatabase <database> command line options:
mongo --port 27017 -u "myUserAdmin" -p "abc123" --authenticationDatabase "admin"
view the default user account
1 SQL> select username from dba_users;
lock all users and set their password to expired
1 SQL> select ‘alter user ‘|| username || ‘ password expire account lock;’ from dba_users;
A locked user can only be accessed by altering the user to an unlocked state
1 SQL> alter user scott account unlock;
As a DBA, you can change the password for a user
1 SQL> alter user
Run this query to display users that have been created by another DBA versus those created by Oracle.For default users,there should be a record in the DEFAULT_PWD$ view.So,if a user doesn’t exist in DEFAULT_PWD$,then you can assume it’s not a default account.
1 SQL> select distinct u.username 2 ,case when d.user_name is null then ‘DBA created account’ 3 else ‘Oracle created account’ 4 from dba_users u 5 ,default_pwd$ d 6 where u.username=d.user_name(+);
You can check the DBA_USERS_WITH_DEFPWD view to see whether any Oracle-created user accounts are still to the default password
1 SQL> select * from dba_users_with_defpwd;
Creating a User with Database Authentication
1 SQL> create user user_name identified by password 2 default tablespace users 3 temporaty tablespace temp 4 quote unlimited on users; 5 SQL> grant create session to user_name; #to make the user useful 6 SQL> grant create table to user_name; #to be able to create tables. 7 SQL> grant create table,create session to user_name identified by password; #you can also use the GRANT . . . IDENTIFIED BY statement to create a user.
I supposed we are encountering a situation that there's an anonymous user has connected in our MySQL database with an account which has large privileges.The user is doing some query operations with bad performance.Which may subsequently lead to a high load of our database server.How to solve this issue efficiently and immediately?There's a little trick we can use below.
8.Creating a User with OS Authentication