ユーザ作成

DB

create user user1 identified by "user1"
default tablespace users
temporary tablespace temp
quota unlimited on users;

create user test identified by test;


grant dba to user1;
grant dba to test;

select * from mysql.user;

create user 'user1'@'%' identified by 'user1';
create user 'user2'@'%' identified by 'user2';
create user 'user3'@'%' identified by 'user3';


show grants;
show grants for 'user1'@'%';
grant all on *.* to 'user1'@'%';

show privileges;

--パスワードなしで作成
create user 'jim'@'%';

--ユーザ名変更
rename user 'jim'@'%' to 'john'@'localhost';

--ホスト部をIPで指定
create user 'user12'@'192.168.137.0/255.255.255.0';

※ネットマスクは、8、16、24、または 32 ビットのアドレスを使用するよう
サーバーに指示するためにのみ使用できます。

 

※ロール作成参照

--ログイン作成(Windows認証)

create login [mmm060\winuser1]
from windows
with default_database = test
go

--ログイン作成(SQL Server認証)

USE [master]
GO
CREATE LOGIN [taro] WITH PASSWORD=N'Aaa!1234', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO

 

--ユーザ作成
use test
go
create user user1
for login [mmm060\winuser1]
go

USE [test]
GO
CREATE USER [taro] FOR LOGIN [taro]
GO


--既定のスキーマの変更
ALTER USER user1 WITH DEFAULT_SCHEMA = schema1;
go

 

--権限付与
use master
go
grant alter any database to public with grant option;
go

use test
go
grant select on object::dbo.tab1 to guest;
go


ALTER SERVER ROLE [sysadmin] ADD MEMBER [taro]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [TEST\ichiro]
GO
USE [test]
GO
ALTER ROLE [db_datareader] ADD MEMBER [ichiro]
GO