User Privilege

User Privilege

SELECT USERNAME, ACCOUNT_STATUS,CREATED,profile FROM DBA_USERS WHERE USERNAME ='&A';

SELECT USERNAME, ACCOUNT_STATUS,CREATED FROM DBA_USERS WHERE USERNAME LIKE '%&A%';

SELECT GRANTEE, GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE='&A';
select owner,table_name, privilege from dba_tab_privs where grantee='&a';

select * from dba_sys_privs where grantee='&A';

SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='&A';


SELECT DISTINCT GRANTEE,OWNER,TABLE_NAME, PRIVILEGE FROM DBA_TAB_PRIVS
WHERE OWNER='NORSNAPADM' AND GRANTEE LIKE 'SELECT_%_%'
AND TABLE_NAME IN ('DEALER_PROFILE');

------------------------------------------------------
select * from dba_role_privs
where granted_role in ('ADMIN_ROLE','CONNECT','RESOURCE') and
grantee not in ('SYS','SYSTEM');
Log on  in trigger

CREATE OR REPLACE TRIGGER sysadm_logon_trg_jay
after logon on Raj.schema
BEGIN
  EXECUTE IMMEDIATE
          'declare begin ' ||
          'dbms_application_info.set_client_info ( 101 ); end;';
  EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =Scott';
end;
/


select * from DBA_SYS_PRIVS -- users

select * from ROLE_SYS_PRIVS -- roles

select * from DBA_TAB_PRIVS -- obj

System Privileges:
select * from DBA_SYS_PRIVS where GRANTEE='USERNAME';
Object Privileges:
select * from DBA_TAB_PRIVS where GRANTEE='USERNAME'

select

     lpad(' ', 2*level) || granted_role "User, his roles and privileges"

    from

     (

     /* THE USERS */

       select

        null     grantee,

        username granted_role

        from

         dba_users

      where

         username like upper('%&enter_username%')

   /* 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

select grantee, granted_role

from dba_role_privs drp

where drp.grantee in (select username from dba_users)

order by granted_role

Script: To clone or Duplicate Oracle User

select username from dba_users order by username
/
undefine user
accept userid prompt 'Enter user to clone: '
accept newuser prompt 'Enter new username: '
accept passwd prompt 'Enter new password: '
select username, created from dba_users
where  lower(username) = lower('&newuser')
/
accept poo prompt 'Continue? (ctrl-c to exit)'
spool D:\TEST\clone.sql
select 'create user ' || '&newuser' || ' identified by ' || '&passwd' ||
       ' default tablespace ' || default_tablespace ||
       ' temporary tablespace ' || temporary_tablespace || ';' "user"
from   dba_users
where  username = '&userid'
/
select 'alter user &newuser quota '||
       decode(max_bytes, -1, 'unlimited', ceil(max_bytes / 1024 / 1024) || 'M') ||
       ' on ' || tablespace_name || ';'
from   dba_ts_quotas where username = '&&userid'
/
select 'grant ' ||granted_role || ' to &newuser' ||
       decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "ROLE"
from   dba_role_privs where  grantee = '&&userid'
/
select 'grant ' || privilege || ' to &newuser' ||
       decode(admin_option, 'NO', ';', 'YES', ' with admin option;') "PRIV"
from   dba_sys_privs where  grantee = '&&userid'
/
spool off
undefine user
set verify on
set feedback on
set heading on
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL>@D:\TEST\clone.sql