# 1 you have to connect sys as sysdba
# 2 when the DBA connect, it will be on root container db
show con_name
/*
CON_NAME
------------------------------
CDB$ROOT
*/
alter session set container=orclpdb
/*
Session altered.
*/
show con_name
/*
CON_NAME
------------------------------
ORCLPDB
*/
col username for a20
col account_status for a20
col common for a20
select username,account_status,common from dba_users;
/*
USERNAME ACCOUNT_STATUS COMMON
-------------------- -------------------- --------------------
SYS OPEN YES
SYSTEM OPEN YES
XS$NULL EXPIRED & LOCKED YES
LBACSYS LOCKED YES
OUTLN LOCKED YES
DBSNMP LOCKED YES
APPQOSSYS LOCKED YES
DBSFWUSER LOCKED YES
GGSYS LOCKED YES
ANONYMOUS EXPIRED & LOCKED YES
HR OPEN NO
...
*/
--to see the priv map
select * from SYSTEM_PRIVILEGE_MAP where name like '%SELECT%';
/*
create tables - to create ta bles in own schema.
create any tables - to create tables in any schema
*/
--now lets create new user called demo
--this will create user demo with password demo1234
create user demo identified by demo1234; -- he can not login yet
/*
User DEMO created.
-- but can not login
[oracle@test ~]$ sqlplus demo/demo1234@orclpdb
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 3 13:13:47 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR:
ORA-01045: user DEMO lacks CREATE SESSION privilege; logon denied
*/
--now these some sys priv
grant create session to demo;
/*
Grant succeeded.
[oracle@test ~]$ sqlplus demo/demo1234@orclpdb
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Dec 3 13:14:38 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
*/
grant create table to demo;
-- 2개의 권한을 한번에 줄수 있는 예시
-- you can do this also : grant create session,create table to demo
/*
Grant succeeded.
*/
GRANT UNLIMITED TABLESPACE TO DEMO;
--Now if you can see that I give him create table
--but in order the user to create a table he should have a space or size.
/*
Grant succeeded.
*/
grant create sequence to DEMO;
/*
Grant succeeded.
*/
grant create view to demo;
/*
Grant succeeded.
*/
grant create synonym to demo;
/*
Grant succeeded.
*/
grant select on hr.employees to demo;
-- 특정 테이블에 대한 조회 권한
/*
Grant succeeded.
*/
grant delete on hr.employees to demo;
/*
Grant succeeded.
*/
grant update (salary) on hr.employees to demo;
-- salary 필드에 대한 업데이트 권한 할당, 다른 필드에 대해서는 업데이트 불가능.
/*
Grant succeeded.
*/
grant all on hr.locations to demo;
--The demo when I give him all this, mean that the user demo can do everything in table locations.
--He can insert, he can update, he can delete, he can drop, he can alter the table.
/*
Grant succeeded.
*/
grant select, insert on hr.jobs to demo;
/*
Grant succeeded.
*/
grant select on hr.countries to public;
/*
This means that all the database users in the pluggable database
orclPDB can make select statement in table hr.countries.
Grant succeeded.
*/
-- HR 사용자의 모든 테이블을 읽을수 있는 권한을 SCOTT 사용자에게 부여.
-- 'select any table' 권한을 주는것보단 안전하다.
Select 'GRANT SELECT ON HR.'||Table_Name||' TO SCOTT;'
From All_Tables Where Owner='HR';
BEGIN
FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='HR') LOOP
EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to SCOTT';
END LOOP;
END;
/
