Difference between revisions of "User Management"

From dbawiki
Jump to: navigation, search
(Created page with "* Create User like...")
 
(→‎Unsupported way)
 
(11 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
* [https://oracle-base.com/articles/misc/os-authentication Create external user (using OPS$)]
 
* [[Create User like...]]
 
* [[Create User like...]]
 +
===Users created when installing Oracle 11.2.0.4 Standard Edition===
 +
<pre>
 +
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.
 +
</pre>
 +
 +
===Users created when installing Oracle 11.2.0.4 Enterprise Edition===
 +
<pre>
 +
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.
 +
</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>
 +
 +
===Create external user===
 +
====Check the authentification prefix====
 +
<pre>
 +
SQL> show parameter authent
 +
 +
NAME                                TYPE        VALUE
 +
------------------------------------ ----------- ------------------------------
 +
os_authent_prefix                    string      ops$
 +
SQL>
 +
</pre>
 +
====On Unix...====
 +
<pre>
 +
create user ops$oracle identified externally;
 +
grant create session to ops$oracle;
 +
</pre>
 +
====On Windows...====
 +
Watch out on Windows - the domain name needs to be included!
 +
<pre>
 +
create user "ops$domainname.com\oracle" identified externally;
 +
grant create session to "ops$domainname.com\oracle";
 +
</pre>
 +
Additionally, the following option must be set in "%ORACLE_HOME%\network\admin\sqlnet.ora"<br />
 +
If this is not present, you will not be able to connect as sysdba
 +
<pre>
 +
SQLNET.AUTHENTICATION_SERVICES=(NTS)
 +
</pre>

Latest revision as of 07:56, 26 September 2018

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)
Retrieved from "https://mailnest.com/dbawiki/index.php?title=User_Management&oldid=3298"