ログインできないユーザ user10
ディクショナリ参照だけできるユーザ user20
データ参照だけできるユーザ user30
データ追加だけできるユーザ user40
テーブル作成だけできるユーザ user50
スーパーユーザ user60
(5.6)
mysql -u root -proot
drop user 'user10'@'%';
drop user 'user20'@'%';
drop user 'user30'@'%';
drop user 'user40'@'%';
drop user 'user50'@'%';
drop user 'user60'@'%';
create user 'user10'@'%' identified by 'user10';
create user 'user20'@'%' identified by 'user20';
grant process on *.* to 'user20'@'%';
create user 'user30'@'%' identified by 'user30';
grant select on test.* to 'user30'@'%';
create user 'user40'@'%' identified by 'user40';
grant insert on test.* to 'user40'@'%';
create user 'user50'@'%' identified by 'user50';
grant create on test.* to 'user50'@'%';
create user 'user60'@'%' identified by 'user60';
grant all on *.* to 'user60'@'%';
-- 動作確認
mysql -u user10 -puser10
mysql -u user20 -puser20
mysql -u user30 -puser30
mysql -u user40 -puser40
mysql -u user50 -puser50
mysql -u user60 -puser60
status;
use test;
select * from information_schema.processlist;
select * from test.tab1;
insert into test.tab1 values(1);
drop table test.tab2;
create table test.tab2(col1 int);
(12cR1)
conn / as sysdba
drop user user10 cascade;
drop user user20 cascade;
drop user user30 cascade;
drop user user40 cascade;
drop user user50 cascade;
drop user user60 cascade;
create user user10 identified by user10;
create user user20 identified by user20;
grant create session to user20;
grant select any dictionary to user20;
create user user30 identified by user30;
grant create session to user30;
grant select on test.tab1 to user30;
create user user40 identified by user40;
grant create session to user40;
grant insert on test.tab1 to user40;
create user user50 identified by user50;
grant create session to user50;
grant create any table to user50;
create user user60 identified by user60;
grant dba to user60;
-- 動作確認
conn user10/user10
conn user20/user20
conn user30/user30
conn user40/user40
conn user50/user50
conn user60/user60
sho user
select count(*) from v$session;
select * from test.tab1;
insert into test.tab1 values(1);
drop table test.tab2 purge;
create table test.tab2(col1 int);
(9.4)
PGPASSWORD=postgres psql -U postgres -h mmm050 -d test
drop owned by user10 cascade;
drop owned by user20 cascade;
drop owned by user30 cascade;
drop owned by user40 cascade;
drop owned by user50 cascade;
drop owned by user60 cascade;
drop user user10;
drop user user20;
drop user user30;
drop user user40;
drop user user50;
drop user user60;
create user user10 with nologin encrypted password 'user10';
create user user20 with login encrypted password 'user20';
create user user30 with login encrypted password 'user30';
grant connect on database test to user30;
grant usage on schema schema1 to user30;
grant select on schema1.tab1 to user30;
alter default privileges for user postgres in schema schema1 grant select on tables to user30;
※スキーマレベルの権限とオブジェクトレベルの権限の両方が必要
create user user40 with login encrypted password 'user40';
grant connect on database test to user40;
grant usage on schema schema1 to user40;
grant insert on schema1.tab1 to user40;
alter default privileges for user postgres in schema schema1 grant insert on tables to user40;
create user user50 with login encrypted password 'user50';
grant connect on database test to user50;
grant create on schema schema1 to user50;
create user user60 with superuser encrypted password 'user60';
-- 動作確認
PGPASSWORD=user10 psql -U user10 -h mmm050 -d postgres
PGPASSWORD=user20 psql -U user20 -h mmm050 -d postgres
PGPASSWORD=user30 psql -U user30 -h mmm050 -d postgres
PGPASSWORD=user40 psql -U user40 -h mmm050 -d postgres
PGPASSWORD=user50 psql -U user50 -h mmm050 -d postgres
PGPASSWORD=user60 psql -U user60 -h mmm050 -d postgres
\conninfo
\c test
set search_path=schema1;
show search_path;
select * from pg_stat_activity;
select * from schema1.tab1;
insert into schema1.tab1 values(1);
drop table schema1.tab2;
create table schema1.tab2(col1 int);
(2014)
use test
go
drop user user10;
drop user user20;
drop user user30;
drop user user40;
drop user user50;
drop user user60;
go
use master
go
drop login user10;
drop login user20;
drop login user30;
drop login user40;
drop login user50;
drop login user60;
go
create login user10 with password='user10', default_database=test, check_policy=off
create login user20 with password='user20', default_database=test, check_policy=off
create login user30 with password='user30', default_database=test, check_policy=off
create login user40 with password='user40', default_database=test, check_policy=off
create login user50 with password='user50', default_database=test, check_policy=off
create login user60 with password='user60', default_database=test, check_policy=off
go
use test
go
create user user10 for login user10 with default_schema=schema1;
deny connect to user10;
create user user20 for login user20 with default_schema=schema1;
use master
go
grant view server state to user20;
go
use test
go
grant view database state to user20;
create user user30 for login user30 with default_schema=schema1;
grant select on schema::schema1 to user30;
※スキーマレベルの権限があれば、オブジェクトレベルの権限は不要
create user user40 for login user40 with default_schema=schema1;
grant insert on schema::schema1 to user40;
create user user50 for login user50 with default_schema=schema1;
grant create table to user50;
grant alter on schema::schema1 to user50;
create user user60 for login user60 with default_schema=schema1;
use master
go
grant control server to user60;
go
use master
go
select * from sys.server_principals;
go
select * from sys.server_permissions;
go
use test
go
select * from sys.database_principals;
go
select * from sys.database_permissions;
go
-- 動作確認
select current_user;
select * from sys.dm_exec_sessions;
select * from schema1.tab1;
insert into schema1.tab1 values(1);
drop table schema1.tab2;
create table schema1.tab2(col1 int);
