matter:
=> user management(user, piviledges and roles)
=>profile
=> tablespace
CREATE USER
in order to manage users u must loggin as admini/ super user.
default admin accounts are : system, sys and scott
supe super: login as sysdba
login: sys as sysdba
password: just press enter
however u can logging as sysdba for user system ans sys.
sysdba can loggin incase of emmergencies and forgotten accounts.
SQL> sys as sysdba
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
==========================================================
connecting to adifferent user account on the active sql plus.
SQL> connect username/password.
SQL> select user from dual;
USER
-------------------------------
SYS
now switch to system user account.
SQL>connect system/mala1clara
SQL> select user from dual;
USER
-------------------------------------------
SYSTEM
Create a user.
to create new user, u must be a system administrator.
Syntax:
CREATE USER username
IDENTIFIED {BY password | EXTERNALLY | GLOBALLY AS 'external_name'}
options;
options:
DEFAULT TABLESPACE tablespace
TEMPORARY TABLESPACE tablespace
QUOTA int {K | M} ON tablespace
QUOTA UNLIMITED ON tablespace
PROFILE profile_name
PASSWORD EXPIRE
ACCOUNT {LOCK|UNLOCK}
EXAMPLES
-- Creating user without specifying options.
options will be decided by the compiler/engine
create user muna identified by lab1234;
however oracle 12c sha;ll accept c##muna
create user c##rose identified by lab1234;
User created.
==> creating user with options.
*******DROP USER MySchemaOwner CASCADE;******
CREATE USER c##fred IDENTIFIED BY lab1234
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;
User created.
creating user with specific qouta
CREATE USER c##muna IDENTIFIED BY lab1234
DEFAULT TABLESPACE users
QUOTA 20m ON users
PASSWORD EXPIRE;
==> account login.
loggin as muna.
SQL>connect c##muna/password
common user can not access account until he/she has been granted create seesion
priviledge
SQL>connect c##muna/lab1234;
*******
SQL> connect c##muna/lab1234;
ERROR:
ORA-01045: user C##MUNA lacks CREATE SESSION privilege; logon denied
*******
therefore muna must be given privilege.
first you must loggin as admin.
connect system/mala1clara
grant create session to c##muna;
Grant succeeded.
Now attempt login as muna.
connect c##muna/lab1234;
SQL> connect c##muna/lab1234;
Connected.
SQL> select user from dual;
USER
------------------------------
C##MUNA
==> grant to muna2.
SQL> grant create session to c##muna2;
Grant succeeded.
SQL> connect c##muna2/lab1234;
ERROR:
ORA-28001: the password has expired
Changing password for c##muna2
New password:
Retype new password:
Password changed
Connected.
SQL>
changing user password.
ALTER USER USERNAME IDENTIFIED BY NEWPASSOWRD;
EG.
Alter user c##muna identified by lab12345;
SQL> Alter user c##muna identified by lab12345;
User altered.
however if user himself/herself wish to change passowrd.
SQL>password
****
SQL> connect c##muna/lab12345
Connected.
SQL> password
Changing password for C##MUNA
Old password:
New password:
Retype new password:
Password changed
****
locking user account.
alter user c##muna
account lock;
SQL> alter user c##muna
2 account lock;
User altered.
SQL> connect c##muna/lab1234;
ERROR:
ORA-28000: the account is locked
===> create user wit administrative priviledges.
create user c##muna3 identified by lab1234
default tablespace users
quota unlimited on users
/
grant create session,dba to c##muna3;
Grant succeeded.
NOTE:
only administrators can create and manage other users.
supose we need to login as c##muna
connect c##muna/lab1234;
muna can not issue any command, before it is granted.
attemp. creating table.
SQL> create table emp(id number,ename varchar2(4));
create table emp(id number,ename varchar2(4))
*
ERROR at line 1:
ORA-01031: insufficient privileges
to solve this issue.
connect system/mala1clara;
grant create table to c##muna;
SQL> connect system/mala1clara;
Connected.
SQL>
SQL> grant create table to c##muna;
Grant succeeded.
==now login to muna and attempt creating table again.
SQL> connect c##muna/lab1234;
Connected.
SQL> create table emp(id number,ename varchar2(4));
Table created.
SQL> desc emp;
Name Null? Type
----------------------------------------- --------
ID NUMBER
ENAME VARCHAR2(4)
==Now attempt adding records.
insert into emp values(1,'H');
SQL> insert into emp values(1,'H');
insert into emp values(1,'H')
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
connect system/mala1clara;
SQL> connect system/mala1clara;
Connected.
SQL> grant insert on emp to c##muna;
Grant succeeded.
connect c##muna/lab1234;
SQL> connect system/mala1clara
Connected.
SQL> insert into c##muna3.emp values(1,'H');
SQL> select*from c##muna3.emp;
ID ENAM
---------- ----
1 H
1 H
==you can proved as many priviledges as u can depending on user level.
grant update on emp to c##muna3;
grant update,insert,delete on emp to c##muna3;
grant update,insert,delete on scott.emp to c##muna3;
grant all on emp to c##muna3;
grant all on system.emp to c##muna3;
grant alter on emp to c##nuna3;
grant alter,drop on emp to c##muna3;
=========================================================
==ROLE:
(set of priviledges created for user of same category)
due to complexity of avaibale priviledges.
instead of writing each privilege in a single command for number of common users.
you can creata a ROLE instead.
CREATE ROLE conn;
CREATE ROLE students;
CREATE ROLE admin;
CREATE ROLE managers;
you can GRANT
folowing
CREATE session, CREATE table, CREATE view,
CREATE procedure,CREATE synonym,
ALTER table dde, ALTER view, ALTER procedure,ALTER synonym,
DROP table, DROP view, DROP procedure,DROP synonym,
TO conn;
SQL> CREATE ROLE c##managers;
Role created.
SQL>
GRANT CREATE session, CREATE table, CREATE view,
CREATE procedure,CREATE synonym
TO c##managers;
SQL> GRANT CREATE session, CREATE table, CREATE view,
2 CREATE procedure,CREATE synonym
3 TO c##managers;
Grant succeeded.
now you can grant this role to user c##muna who is on manager categories.
grant rolename to username;
grant c##managers to c##muna;
SQL> grant c##managers to c##muna;
Grant succeeded.
==> t denie/reject privildges
revoke c##managers from c##muna;
========================************====================
GRANT conn TO MySchemaOwner;
You have to create a user first before you can GRANT permissions, roles or assign a
default ROLE.
SQL> create user u2 identified by u
2 default tablespace myusers
3 profile appl_profile
4 quota 20m on myusers;
grant create session,connect to u2
grant select,update,insert on emp to u2;
GRANT INSERT (acct_no) ON accounts TO scott;
select*from system.emp; ////here u2 is able to access the table belonging to
system user.
==> we can revoke / disallow priviledges.
using command revoke
eg.
SQL>revoke create table from c##muna;
since muna was given a role which contains CREATE TABLE PRIVILEDGE
We have to revoke permission from his role called c##managers as well.
revoke create table to role;
SQL>revoke create table to c##managers
1* revoke create table from c##managers
SQL> /
Revoke succeeded.
SQL> connect c##muna
Enter password:
Connected.
SQL> create table TTT(Tno number);
create table TTT(Tno number)
*
ERROR at line 1:
ORA-01031: insufficient privileges
you can perform revoke for other permisions too.
eg.
revoke insert on emp from c##muna;
revoke update,delete on emp from c##muna;
revoke rolename from c##muna;
==>consider role called c##managers
revoke c##managers from c##muna;
NOTE: c##manager is a role which was granted to c##muna.
supose we need to view object along with objects owner.
(we may have more than one users , each having number of stored objects or tables)
===>we can view from table dba_objects;
desc dba_objects;
SQL> desc dba_objects;
Name Null? Type
----------------------------------------- --------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(18)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
APPLICATION VARCHAR2(1)
DEFAULT_COLLATION VARCHAR2(100)
DUPLICATED VARCHAR2(1)
SHARDED VARCHAR2(1)
CREATED_APPID NUMBER
CREATED_VSNID NUMBER
MODIFIED_APPID NUMBER
MODIFIED_VSNID NUMBER
select OWNER,
OBJECT_TYPE,
OBJECT_NAME,
STATUS
from dba_objects; ///
select OWNER
from dba_objects
where STATUS = 'INVALID'; /// to view all users with regards to objects
where status is invalid.
select OWNER,OBJECT_NAME
from dba_objects
where STATUS = 'INVALID';
select OWNER,OBJECT_NAME
from dba_objects
where OBJECT_TYPE = 'TABLE';
select USER
from dba_objects; /// to view all users with regards to objects.
select * from all_users; // to view all users
SQL> desc all_users;
Name
Null? Type
----------------------------------------------------------------------
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
CREATED NOT NULL DATE
COMMON VARCHAR2(3)
ORACLE_MAINTAINED
VARCHAR2(1)
INHERITED
VARCHAR2(3)
DEFAULT_COLLATION
VARCHAR2(100)
IMPLICIT
VARCHAR2(3)
ALL_SHARD
VARCHAR2(3)
select * from dba_users; // users with their attributes
SQL> desc dba_users;
Name
Null? Type
-----------------------------------------------------------------------------------
------------------------------ --------
----------------------------------------------------------------------------
USERNAME
NOT NULL VARCHAR2(128)
USER_ID
NOT NULL NUMBER
PASSWORD
VARCHAR2(4000)
ACCOUNT_STATUS
NOT NULL VARCHAR2(32)
LOCK_DATE
DATE
EXPIRY_DATE
DATE
DEFAULT_TABLESPACE
NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE
NOT NULL VARCHAR2(30)
LOCAL_TEMP_TABLESPACE
VARCHAR2(30)
CREATED
NOT NULL DATE
PROFILE
NOT NULL VARCHAR2(128)
INITIAL_RSRC_CONSUMER_GROUP
VARCHAR2(128)
EXTERNAL_NAME
VARCHAR2(4000)
PASSWORD_VERSIONS
VARCHAR2(17)
EDITIONS_ENABLED
VARCHAR2(1)
AUTHENTICATION_TYPE
VARCHAR2(8)
PROXY_ONLY_CONNECT
VARCHAR2(1)
COMMON
VARCHAR2(3)
LAST_LOGIN
TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED
VARCHAR2(1)
INHERITED
VARCHAR2(3)
DEFAULT_COLLATION
VARCHAR2(100)
IMPLICIT
VARCHAR2(3)
ALL_SHARD
VARCHAR2(3)
SELECT USERNAME, VALUE || 'bytes' "Current UGA memory"
FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
WHERE sess.SID = stat.SID
AND stat.STATISTIC# = name.STATISTIC#
AND name.NAME = 'session uga memory';
SELECT GRANTEE, OWNER, GRANTOR, PRIVILEGE, GRANTABLE
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = 'EMP' and OWNER = 'SYSTEM';
---------------------------------
to see active user from DB.
select user from dual;
select user,uid from dual;
returns the User ID.
UID==user number and not the loging ID, it is fixed counted from the first user.
unless otherwise you drop and create the user after 6 months the userid will
change.
select OWNER,
OBJECT_TYPE,
OBJECT_NAME,
STATUS
from dba_objects
where STATUS = 'INVALID'
order by OWNER, OBJECT_TYPE, OBJECT_NAME;
OWNER OBJECT_TYPE OBJECT_NAME
------------------------------ ------------------- -----------------------------
PUBLIC SYNONYM DBA_HIST_ACTIVE_SESS_HISTORY
PUBLIC SYNONYM DBA_HIST_DB_CACHE_ADVICE
PUBLIC SYNONYM DBA_HIST_FILESTATXS
PUBLIC SYNONYM DBA_HIST_LATCH
create profile....
resource_parameters
SESSIONS_PER_USER
Specify the number of concurrent sessions to which you want to limit the user.
CPU_PER_SESSION
Specify the CPU time limit for a session, expressed in hundredth of seconds.
CPU_PER_CALL
Specify the CPU time limit for a call (a parse, execute, or fetch), expressed in
hundredths of seconds.
CONNECT_TIME
Specify the total elapsed time limit for a session, expressed in minutes.
IDLE_TIME
Specify the permitted periods of continuous inactive time during a session,
expressed in minutes. Long-running queries and other operations are not subject to
this limit.
LOGICAL_READS_PER_SESSION
Specify the permitted number of data blocks read in a session, including blocks
read from memory and disk.
LOGICAL_READS_PER_CALL
Specify the permitted number of data blocks read for a call to process a SQL
statement (a parse, execute, or fetch).
PRIVATE_SGA
Specify the amount of private space a session can allocate in the shared pool of
the system global area (SGA). Refer to size_clause for information on that clause.
CREATE PROFILE new_profile
LIMIT PASSWORD_REUSE_MAX 10
PASSWORD_REUSE_TIME 30;
CREATE PROFILE app_user
LIMIT
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL 3000
CONNECT_TIME 45
LOGICAL_READS_PER_SESSION DEFAULT
LOGICAL_READS_PER_CALL 1000
PRIVATE_SGA 15K
COMPOSITE_LIMIT 5000000;
create profile
c##appl_profilee
limit
sessions_per_user 2 --
cpu_per_session 10000 -- hunderth of seconds
cpu_per_call 1 -- hunderth of seconds
connect_time unlimited -- minutes
idle_time 30 -- minutes
logical_reads_per_session default -- db blocks
logical_reads_per_call default -- db blocks
-- composite_limit default --
private_sga 20M --
failed_login_attempts 3 --
password_life_time 30 -- days
password_reuse_time 12 --
password_reuse_max unlimited --
password_lock_time default -- days
password_grace_time 2 -- days
password_verify_function null;
SQL> alter user c##muna
2 profile c##appl_profile;
User altered.
SQL> connect c##muna/hdter
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect c##muna/lab12345
ERROR:
ORA-01017: invalid username/password; logon denied
SQL> connect c##muna/lab12345
ERROR:
ORA-28000: the account is locked
----------------------------------------------
ALTER PROFILE c##DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME UNLIMITED;
alter user c##usr
profile c##DEFAULT
/
make wrong logging attempts with =wrong password
connect c##usr/lab12
connect c##usr/lab12
connect c##usr/lab12
The fourth time must reject.
========================================================
--------------create table space
logical storage.
you reserve a particular amount of storage from the internal HDD for sdpecial
purpose.
you cann not phyisically control this reserved storage.
create tablespace
users
datafile
�/ora01/oracle/product/10.1.0/oradata/booktst_users_01.dbf�
size
50m;
create tablespace
procureofficer
datafile
'C:/app/clara/oradata/lab1.dbf'
size 50m;
Note that we used 50m to indicate that we wanted to create the tablespace
50 Megabytes in size. You can also use the K symbol for Kilobytes,
and the G symbol for Gigabytes. If you want to try to use the P symbol
for a 50 Petabyte tablespace,
////////////////----but you can only mention your path
create tablespace
myusers
datafile
'C:/oracle/product/10.1.0/oradata/booktst_users_01.dbf'
size 50m
--blocksize 3
--maxsize 100m
uniform size 10M
autoextend on
/
create tablespace
myusersss
datafile
'C:/oracle/product/10.1.0/oradata/admin/news.dbf'
size 50m
autoextend on
maxsize 100m
blocksize 32k
uniform size 10M
/
Autoextend on: Creating a tablespace with autoextend on alleviates the
potential
problem of a database outage if the tablespace reaches maximum extents.
You create a tablespace using autoextend with the autoextend on option.
Using "create tablespace . . . autoextend on" will push the point of failure to the
OS filesystem,
which is good because you never want you database to hang because a tablespace
cannot grow.
With autoextend on, you only need to monitor the free space in the filesystem
directory.
Maxsize: This is the maximum size that the datafile can grow.
We recommend creating a tablespace with maxsize unlimited to prevent a lock-up.
Size: This is the size of the underlying data file for the tablespace.
Blocksize: Specifying a larger blocksize will not result in a larger
tablespace.
See here on creating tablespaces with non-default blocksizes.
Uniform size: This specifies the size that the data file will grow when it
needs to extend.
viewing tablespace.
View Name Description
dba_tablespaces Describes each tablespace
dba_data_files Lists each datafile in the database.
dba_temp_files Describes each tempfile in the database
select * from v$tablespace;
select*from dba_tablespaces;
select*from dba_data_files;
select*from dba_temp_files;
====
CREATE TABLESPACE users1
DATAFILE 'C:/app/clara/oradata/batch4/TABLESPACES/ME.DBF' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 250M;
ALTER TABLESPACE users1
ADD DATAFILE 'C:/app/clara/oradata/batch4/TABLESPACES/ME1.DBF' SIZE 10M
AUTOEXTEND ON
NEXT 512K
MAXSIZE 550M;
ALTER DATABASE DATAFILE 'C:/app/clara/oradata/batch4/TABLESPACES/ME1.DBF'
AUTOEXTEND OFF;
ALTER DATABASE DATAFILE 'C:/app/clara/oradata/batch4/TABLESPACES/ME1.DBF'
RESIZE 300M;
