Difference between revisions of "User Management"
From dbawiki
(→On Windows...) |
(→Unsupported way) |
||
| (4 intermediate revisions by the same user not shown) | |||
| Line 73: | Line 73: | ||
29 rows selected. | 29 rows selected. | ||
| + | </pre> | ||
| + | |||
| + | ===Show list of database users and their roles / privileges=== | ||
| + | <pre> | ||
| + | select | ||
| + | lpad(' ', 2*level) || granted_role "User, his roles and privileges" | ||
| + | from | ||
| + | ( | ||
| + | /* THE USERS */ | ||
| + | select | ||
| + | null grantee, | ||
| + | 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; | ||
| + | </pre> | ||
| + | ===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.<br /> | ||
| + | It can be done more efficiently using transportable tablespaces technique to remap the schema. | ||
| + | * [https://pierreforstmanndotcom.wordpress.com/2015/02/27/how-to-rename-an-oracle-schema-without-exporting-and-importing-all-schema-data/ Rename an Oracle Schema using Transportable Tablespace] | ||
| + | Basic steps | ||
| + | * check with DBMS_TTS.TRANSPORT_SET_CHECK that tablespace can be transported | ||
| + | * Make the tablespace read only | ||
| + | * Export tablespace metadata | ||
| + | * Drop the tablespace but keep its datafiles | ||
| + | * 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 | ||
| + | * Make tablespce read write | ||
| + | * Set password for new user account and set default tablespace for this new account | ||
| + | * Drop old user account representing the schema before renaming | ||
| + | |||
| + | ====Unsupported way==== | ||
| + | An unsupported workaround to rename a schema is to simply update the username in sys.user$ and restart the database. | ||
| + | <pre> | ||
| + | select user# from sys.user$ where name = 'EPK'; | ||
| + | </pre> | ||
| + | <pre> | ||
| + | USER_ID | ||
| + | ---------- | ||
| + | 172 | ||
| + | </pre> | ||
| + | <pre> | ||
| + | update sys.user$ set name = 'KPK' where user# = 172; | ||
| + | </pre> | ||
| + | <pre> | ||
| + | 1 row updated. | ||
</pre> | </pre> | ||
Latest revision as of 07:56, 26 September 2018
Contents
Users created when installing Oracle 11.2.0.4 Standard Edition[edit]
select username from dba_users order by 1; USERNAME ------------------------------ 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 25 rows selected.
Users created when installing Oracle 11.2.0.4 Enterprise Edition[edit]
select username from dba_users order by 1; 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[edit]
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
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[edit]
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.
Basic steps
- check with DBMS_TTS.TRANSPORT_SET_CHECK that tablespace can be transported
- Make the tablespace read only
- Export tablespace metadata
- Drop the tablespace but keep its datafiles
- 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
- Make tablespce read write
- Set password for new user account and set default tablespace for this new account
- Drop old user account representing the schema before renaming
Unsupported way[edit]
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 = 'EPK';
USER_ID
----------
172
update sys.user$ set name = 'KPK' where user# = 172;
1 row updated.
Create external user[edit]
Check the authentification prefix[edit]
SQL> show parameter authent NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ os_authent_prefix string ops$ SQL>
On Unix...[edit]
create user ops$oracle identified externally; grant create session to ops$oracle;
On Windows...[edit]
Watch out on Windows - the domain name needs to be included!
create user "ops$domainname.com\oracle" identified externally; grant create session to "ops$domainname.com\oracle";
Additionally, the following option must be set in "%ORACLE_HOME%\network\admin\sqlnet.ora"
If this is not present, you will not be able to connect as sysdba
SQLNET.AUTHENTICATION_SERVICES=(NTS)
