1. How to create / delete user in Sql / Oracle?
<sample user : teguh , password : triharto >
2. How can I set privilages while create a new user?
<access to/from SQL>
3. How can I view all forms / tables?
4. How can I list the users already got account in Sql?
5. Check Privilage user ?
6. delete/drop role ?
7. Which is the best web site to learn oracle (for beginners)?
You can download The Study Case & solution Lab Oracle & SQL Expert
Answer :
1. How to create / delete user in Sql / Oracle?<sample user : teguh , password : triharto >
*===============================================================
Welcome to Microsoft Telnet Server.
*===============================================================
C:\Documents and Settings\Administrator>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 25 10:50:16 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create user teguh identified by triharto;
User created.
SQL> grant connect to teguh;
Grant succeeded.
SQL> grant resource to teguh;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
*===============================================================
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
*===============================================================
C:\Documents and Settings\Administrator>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 25 10:53:03 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: teguh
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> connect sys/triharto as sysdba;
Connected.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\Documents and Settings\Administrator>
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 3 16:53:25 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password: *****
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> connect sys/triharto as sysdba;
Connected.
SQL>
or you can use SQL Oracle Tool
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 3 16:53:25 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password: *****
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create user teguh identified by triharto;
User created.
SQL> grant connect to teguh;
Grant succeeded.
SQL> grant resource to teguh;
Grant succeeded.
SQL> connect sys/triharto as sysdba;
Connected.
SQL>
2. How can I set privilages while create a new user?<access to/from SQL>
SQL> grant connect to teguh;
Grant succeeded.
SQL> grant resource to teguh;
Grant succeeded.
or you can using
SQL>grant previlags / roles to teguh;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\Documents and Settings\Administrator>
3. How can I view all forms / tables?
*===============================================================
Welcome to Microsoft Telnet Server.
*===============================================================
C:\Documents and Settings\Administrator>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 25 11:44:22 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SYSCATALOG SYNONYM
CATALOG SYNONYM
TAB SYNONYM
COL SYNONYM
TABQUOTAS SYNONYM
SYSFILES SYNONYM
PUBLICSYN SYNONYM
MVIEW$_ADV_WORKLOAD TABLE
MVIEW$_ADV_BASETABLE TABLE
MVIEW$_ADV_SQLDEPEND TABLE
MVIEW$_ADV_PRETTY TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MVIEW$_ADV_TEMP TABLE
MVIEW$_ADV_FILTER TABLE
MVIEW$_ADV_LOG TABLE
MVIEW$_ADV_FILTERINSTANCE TABLE
MVIEW$_ADV_LEVEL TABLE
MVIEW$_ADV_ROLLUP TABLE
MVIEW$_ADV_AJG TABLE
MVIEW$_ADV_FJG TABLE
MVIEW$_ADV_GC TABLE
MVIEW$_ADV_CLIQUE TABLE
MVIEW$_ADV_ELIGIBLE TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MVIEW$_ADV_OUTPUT TABLE
MVIEW$_ADV_EXCEPTIONS TABLE
MVIEW$_ADV_PARAMETERS TABLE
MVIEW$_ADV_INFO TABLE
MVIEW$_ADV_JOURNAL TABLE
MVIEW$_ADV_PLAN TABLE
MVIEW_WORKLOAD VIEW
MVIEW_FILTER VIEW
MVIEW_LOG VIEW
MVIEW_FILTERINSTANCE VIEW
MVIEW_RECOMMENDATIONS VIEW
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MVIEW_EVALUATIONS VIEW
MVIEW_EXCEPTIONS VIEW
AQ$_QUEUE_TABLES TABLE
AQ$_QUEUES TABLE
AQ$_SCHEDULES TABLE
AQ$_INTERNET_AGENTS TABLE
AQ$_INTERNET_AGENT_PRIVS TABLE
DEF$_AQCALL TABLE
DEF$_AQERROR TABLE
AQ$_DEF$_AQCALL_F VIEW
AQ$DEF$_AQCALL VIEW
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
AQ$_DEF$_AQERROR_F VIEW
AQ$DEF$_AQERROR VIEW
DEF$_ERROR TABLE
DEF$_DESTINATION TABLE
DEF$_CALLDEST TABLE
DEF$_DEFAULTDEST TABLE
DEF$_LOB TABLE
DEF$_TEMP$LOB TABLE
DEF$_PROPAGATOR TABLE
DEF$_ORIGIN TABLE
DEF$_PUSHED_TRANSACTIONS TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
OL$ TABLE
OL$HINTS TABLE
OL$NODES TABLE
LOGMNR_SESSION_EVOLVE$ TABLE
LOGMNR_HEADER1$ TABLE
LOGMNR_HEADER2$ TABLE
LOGMNR_UID$ TABLE
LOGMNRC_DBNAME_UID_MAP TABLE
LOGMNR_DICTSTATE$ TABLE
LOGMNR_DICTIONARY$ TABLE
LOGMNR_OBJ$ TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
LOGMNR_USER$ TABLE
LOGMNRC_GTLO TABLE
LOGMNRC_GTCS TABLE
LOGMNRC_GSII TABLE
LOGMNR_TAB$ TABLE
LOGMNR_COL$ TABLE
LOGMNR_ATTRCOL$ TABLE
LOGMNR_TS$ TABLE
LOGMNR_IND$ TABLE
LOGMNR_TABPART$ TABLE
LOGMNR_TABSUBPART$ TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
LOGMNR_TABCOMPART$ TABLE
LOGMNR_TYPE$ TABLE
LOGMNR_COLTYPE$ TABLE
LOGMNR_ATTRIBUTE$ TABLE
LOGMNR_LOB$ TABLE
LOGMNR_CDEF$ TABLE
LOGMNR_CCOL$ TABLE
LOGMNR_ICOL$ TABLE
LOGMNR_LOBFRAG$ TABLE
LOGMNR_INDPART$ TABLE
LOGMNR_INDSUBPART$ TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
LOGMNR_INDCOMPART$ TABLE
LOGMNRP_CTAS_PART_MAP TABLE
LOGMNRT_MDDL$ TABLE
LOGMNR_LOG$ TABLE
LOGMNR_PROCESSED_LOG$ TABLE
LOGMNR_SPILL$ TABLE
LOGMNR_AGE_SPILL$ TABLE
LOGMNR_RESTART_CKPT_TXINFO$ TABLE
LOGMNR_ERROR$ TABLE
LOGMNR_RESTART_CKPT$ TABLE
LOGMNR_FILTER$ TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
LOGMNR_PARAMETER$ TABLE
LOGMNR_SESSION$ TABLE
LOGSTDBY$PARAMETERS TABLE
LOGSTDBY$EVENTS TABLE
LOGSTDBY$APPLY_PROGRESS TABLE
LOGSTDBY$APPLY_MILESTONE TABLE
LOGSTDBY$SCN TABLE
LOGSTDBY$PLSQL TABLE
LOGSTDBY$SKIP_TRANSACTION TABLE
LOGSTDBY$SKIP TABLE
LOGSTDBY$SKIP_SUPPORT TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
LOGSTDBY$HISTORY TABLE
REPCAT$_REPCAT TABLE
REPCAT$_FLAVORS TABLE
REPCAT$_REPSCHEMA TABLE
REPCAT$_SNAPGROUP TABLE
REPCAT$_REPOBJECT TABLE
REPCAT$_REPCOLUMN TABLE
REPCAT$_KEY_COLUMNS TABLE
REPCAT$_GENERATED TABLE
REPCAT$_REPPROP TABLE
REPCAT$_REPCATLOG TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REPCAT$_DDL TABLE
REPCAT$_REPGROUP_PRIVS TABLE
REPCAT$_PRIORITY_GROUP TABLE
REPCAT$_PRIORITY TABLE
REPCAT$_COLUMN_GROUP TABLE
REPCAT$_GROUPED_COLUMN TABLE
REPCAT$_CONFLICT TABLE
REPCAT$_RESOLUTION_METHOD TABLE
REPCAT$_RESOLUTION TABLE
REPCAT$_RESOLUTION_STATISTICS TABLE
REPCAT$_RESOL_STATS_CONTROL TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REPCAT$_PARAMETER_COLUMN TABLE
REPCAT$_AUDIT_ATTRIBUTE TABLE
REPCAT$_AUDIT_COLUMN TABLE
REPCAT$_FLAVOR_OBJECTS TABLE
REPCAT$_TEMPLATE_STATUS TABLE
REPCAT$_TEMPLATE_TYPES TABLE
REPCAT$_REFRESH_TEMPLATES TABLE
REPCAT$_USER_AUTHORIZATIONS TABLE
REPCAT$_OBJECT_TYPES TABLE
REPCAT$_TEMPLATE_REFGROUPS TABLE
REPCAT$_TEMPLATE_OBJECTS TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REPCAT$_TEMPLATE_PARMS TABLE
REPCAT$_OBJECT_PARMS TABLE
REPCAT$_USER_PARM_VALUES TABLE
REPCAT$_TEMPLATE_SITES TABLE
REPCAT$_SITE_OBJECTS TABLE
REPCAT$_RUNTIME_PARMS TABLE
REPCAT$_TEMPLATE_TARGETS TABLE
REPCAT$_EXCEPTIONS TABLE
REPCAT$_INSTANTIATION_DDL TABLE
REPCAT$_EXTENSION TABLE
REPCAT$_SITES_NEW TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SQLPLUS_PRODUCT_PROFILE TABLE
PRODUCT_PRIVS VIEW
PRODUCT_USER_PROFILE SYNONYM
HELP TABLE
CUSTOMER TABLE
PASOK TABLE
PEMBELIAN TABLE
MVIEW$_ADV_INDEX TABLE
MVIEW$_ADV_PARTITION TABLE
MVIEW$_ADV_OWB TABLE
BARANG TABLE
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
SUPLIER TABLE
166 rows selected.
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\Documents and Settings\Administrator>
4. How can I list the users already got account in Sql?
*===============================================================
Welcome to Microsoft Telnet Server.
*===============================================================
C:\Documents and Settings\Administrator>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 25 11:35:38 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from all_users;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
FAKTA-DAN-UNIK.BLOGSPOT.COM 61 22-JUL-11
BI 60 22-JUL-11
PM 59 22-JUL-11
SH 58 22-JUL-11
IX 57 22-JUL-11
OE 56 22-JUL-11
HR 55 22-JUL-11
SCOTT 54 30-AUG-05
MGMT_VIEW 53 30-AUG-05
TEGUH 62 25-JUL-11
MDDATA 50 30-AUG-05
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYSMAN 51 30-AUG-05
MDSYS 46 30-AUG-05
SI_INFORMTN_SCHEMA 45 30-AUG-05
ORDPLUGINS 44 30-AUG-05
ORDSYS 43 30-AUG-05
OLAPSYS 47 30-AUG-05
ANONYMOUS 39 30-AUG-05
XDB 38 30-AUG-05
CTXSYS 36 30-AUG-05
EXFSYS 34 30-AUG-05
WMSYS 25 30-AUG-05
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
DBSNMP 24 30-AUG-05
TSMSYS 21 30-AUG-05
DMSYS 35 30-AUG-05
DIP 19 30-AUG-05
OUTLN 11 30-AUG-05
SYSTEM 5 30-AUG-05
SYS 0 30-AUG-05
29 rows selected.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\Documents and Settings\Administrator>
5. Check privilege user oracle
*===============================================================
C:\Users\Administrator>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 27 14:09:39 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: teguh
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc user_role_privs;
Name Null? Type
----------------------------------------- -------- ----------------------------
USERNAME VARCHAR2(30)
GRANTED_ROLE VARCHAR2(30)
ADMIN_OPTION VARCHAR2(3)
DEFAULT_ROLE VARCHAR2(3)
OS_GRANTED VARCHAR2(3)
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TEGUH CONNECT NO YES NO
TEGUH OEM_MONITOR NO YES NO
TEGUH RESOURCE NO YES NO
SQL>
6. delete/drop SQL> drop role oem_monitor;
C:\Users\Administrator>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 27 16:01:31 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: system
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop role oem_monitor;
Role dropped.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
C:\Users\Administrator>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 27 16:01:31 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: teguh
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from user_role_privs;
USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TEGUH CONNECT NO YES NO
TEGUH RESOURCE NO YES NO
SQL>
7. there are many , we can do it here at http://fakta-dan-unik.blogspot.com
No comments:
Post a Comment