user_management
Differences
This shows you the differences between two versions of the page.
| Both sides previous revisionPrevious revision | |||
| user_management [2018/12/08 12:49] – created 0.0.0.0 | user_management [2021/09/17 14:49] (current) – external edit 127.0.0.1 | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ====== User_Management ====== | + | * [[https:// |
| + | * [[Create User like...]] | ||
| + | ==== Users created when installing Oracle 11.2.0.4 Standard Edition | ||
| + | < | ||
| + | select username from dba_users order by 1; | ||
| - | * [[https:// | + | USERNAME |
| - | * [[Create User like...]] | + | ------------------------------ |
| - | =====Users created when installing Oracle 11.2.0.4 Standard Edition===== | + | ANONYMOUS |
| - | < | + | APEX_030200 |
| + | APEX_PUBLIC_USER | ||
| + | APPQOSSYS | ||
| + | CTXSYS | ||
| + | DBSNMP | ||
| + | DIP | ||
| + | EXFSYS | ||
| + | FLOWS_FILES | ||
| + | MDSYS | ||
| + | MGMT_VIEW | ||
| + | ORACLE_OCM | ||
| + | ORDDATA | ||
| + | ORDPLUGINS | ||
| + | ORDSYS | ||
| + | OUTLN | ||
| + | OWBSYS | ||
| + | OWBSYS_AUDIT | ||
| + | SI_INFORMTN_SCHEMA | ||
| + | SYS | ||
| + | SYSMAN | ||
| + | SYSTEM | ||
| + | WMSYS | ||
| + | XDB | ||
| + | XS$NULL | ||
| - | =====Users created when installing Oracle 11.2.0.4 Enterprise Edition===== | + | 25 rows selected. |
| - | < | + | </ |
| - | =====Show list of database users and their roles / privileges===== | + | ==== Users created when installing Oracle 11.2.0.4 Enterprise Edition ==== |
| - | < | + | < |
| - | =====Rename an Oracle schema without exporting and importing all schema data===== | + | select username from dba_users order by 1; |
| - | If the user is very large, it can take a very long time to use the standard way expdp / impdp.<br /> | + | |
| + | USERNAME | ||
| + | ------------------------------ | ||
| + | ANONYMOUS | ||
| + | APEX_030200 | ||
| + | APEX_PUBLIC_USER | ||
| + | APPQOSSYS | ||
| + | CTXSYS | ||
| + | DBSNMP | ||
| + | DIP | ||
| + | EXFSYS | ||
| + | FLOWS_FILES | ||
| + | MDDATA | ||
| + | MDSYS | ||
| + | MGMT_VIEW | ||
| + | OLAPSYS | ||
| + | ORACLE_OCM | ||
| + | ORDDATA | ||
| + | ORDPLUGINS | ||
| + | ORDSYS | ||
| + | OUTLN | ||
| + | OWBSYS | ||
| + | OWBSYS_AUDIT | ||
| + | SI_INFORMTN_SCHEMA | ||
| + | SPATIAL_CSW_ADMIN_USR | ||
| + | SPATIAL_WFS_ADMIN_USR | ||
| + | SYS | ||
| + | SYSMAN | ||
| + | SYSTEM | ||
| + | WMSYS | ||
| + | XDB | ||
| + | XS$NULL | ||
| + | |||
| + | 29 rows selected. | ||
| + | </ | ||
| + | |||
| + | ==== Show list of database users and their roles / privileges ==== | ||
| + | < | ||
| + | select | ||
| + | lpad(' ', | ||
| + | from | ||
| + | ( | ||
| + | /* THE USERS */ | ||
| + | select | ||
| + | null | ||
| + | username granted_role | ||
| + | from | ||
| + | dba_users | ||
| + | /* THE ROLES TO ROLES RELATIONS */ | ||
| + | union | ||
| + | select | ||
| + | grantee, | ||
| + | granted_role | ||
| + | from | ||
| + | dba_role_privs | ||
| + | /* THE ROLES TO PRIVILEGE RELATIONS */ | ||
| + | union | ||
| + | select | ||
| + | grantee, | ||
| + | privilege | ||
| + | from | ||
| + | dba_sys_privs | ||
| + | ) | ||
| + | start with grantee is null | ||
| + | connect by grantee = prior granted_role; | ||
| + | </ | ||
| + | ==== Rename an Oracle schema without exporting and importing all schema data ==== | ||
| + | If the user is very large, it can take a very long time to use the standard way expdp / impdp.\\ | ||
| It can be done more efficiently using transportable tablespaces technique to remap the schema. | It can be done more efficiently using transportable tablespaces technique to remap the schema. | ||
| - | * [[https:// | + | * [[https:// |
| Basic steps | Basic steps | ||
| - | * check with DBMS_TTS.TRANSPORT_SET_CHECK that tablespace can be transported | + | * check with DBMS_TTS.TRANSPORT_SET_CHECK that tablespace can be transported |
| - | * Make the tablespace read only | + | * Make the tablespace read only |
| - | * Export tablespace metadata | + | * Export tablespace metadata |
| - | * Drop the tablespace but keep its datafiles | + | * Drop the tablespace but keep its datafiles |
| - | * Create the new user account representing the renamed schema and grant needed privileges | + | * Create the new user account representing the renamed schema and grant needed privileges |
| - | * Import tablespace metadata and rename schema objects owner at the same time | + | * Import tablespace metadata and rename schema objects owner at the same time |
| - | * Make tablespce read write | + | * Make tablespce read write |
| - | * Set password for new user account and set default tablespace for this new account | + | * Set password for new user account and set default tablespace for this new account |
| - | * Drop old user account representing the schema before renaming | + | * Drop old user account representing the schema before renaming |
| - | ====Unsupported way==== | + | ==== Rename an Oracle schema using Rodrigo Jorge' |
| + | Essentially performs a network datapump from PL/SQL using a database link back to the same database so no intermediate dump file is necessary. | ||
| + | * https:// | ||
| + | |||
| + | === Unsupported way (Oracle have closed this loophole as of 19.9!) | ||
| An unsupported workaround to rename a schema is to simply update the username in sys.user$ and restart the database. | An unsupported workaround to rename a schema is to simply update the username in sys.user$ and restart the database. | ||
| - | < | + | < |
| - | < | + | select user# from sys.user$ where name = ' |
| - | < | + | </ |
| - | < | + | < |
| + | | ||
| + | ---------- | ||
| + | 172 | ||
| + | </ | ||
| + | < | ||
| + | update sys.user$ set name = ' | ||
| + | </ | ||
| + | < | ||
| + | 1 row updated. | ||
| + | </ | ||
| - | =====Create external user===== | + | ==== Create external user ==== |
| - | ====Check the authentification prefix==== | + | === Check the authentification prefix === |
| - | < | + | < |
| - | ====On Unix...==== | + | SQL> show parameter authent |
| - | < | + | |
| - | ====On Windows...==== | + | NAME |
| + | ------------------------------------ ----------- ------------------------------ | ||
| + | os_authent_prefix | ||
| + | SQL> | ||
| + | </ | ||
| + | === On Unix... === | ||
| + | < | ||
| + | create user ops$oracle identified externally; | ||
| + | grant create session to ops$oracle; | ||
| + | </ | ||
| + | === On Windows... === | ||
| Watch out on Windows - the domain name needs to be included! | Watch out on Windows - the domain name needs to be included! | ||
| - | < | + | < |
| - | Additionally, | + | create user " |
| + | grant create session to " | ||
| + | </ | ||
| + | Additionally, | ||
| + | etwork\\admin\\sqlnet.ora" | ||
| If this is not present, you will not be able to connect as sysdba | If this is not present, you will not be able to connect as sysdba | ||
| - | < | + | < |
| + | SQLNET.AUTHENTICATION_SERVICES=(NTS) | ||
| + | </ | ||
user_management.1544273361.txt.gz · Last modified: 2018/12/08 12:49 by 0.0.0.0