SQL> DESC DBA_OBJECTS
Name Null? Type
----------------------------------------------------- -------- ------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> COLUMN OBJECT_NAME FORMAT A11
SQL> SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER = 'SCOTT';
OWNER OBJECT_NAME
------------------------------ -----------
SCOTT PK_DEPT
SCOTT DEPT
SCOTT EMP
SCOTT PK_EMP
SCOTT BONUS
SCOTT SALGRADE
6 rows selected.
SQL> CREATE USER NEWUSER IDENTIFIED BY NEWUSER;
User created.
SQL> ALTER USER NEWUSER DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
SQL> GRANT CREATE SESSION TO NEWUSER;
Grant succeeded.
SQL> SELECT 'GRANT INSERT, UPDATE, DELETE ON '||OWNER||'.'||OBJECT_NAME||' TO NEWUSER;' FROM DBA_OBJECTS
2 WHERE OWNER= 'SCOTT'
3 AND (OBJECT_TYPE ='TABLE' OR OBJECT_TYPE='VIEW');
'GRANTINSERT,UPDATE,DELETEON'||OWNER||'.'||OBJECT_NAME||'TONEWUSER;'
--------------------------------------------------------------------------------
GRANT INSERT, UPDATE, DELETE ON SCOTT.DEPT TO NEWUSER;
GRANT INSERT, UPDATE, DELETE ON SCOTT.EMP TO NEWUSER;
GRANT INSERT, UPDATE, DELETE ON SCOTT.BONUS TO NEWUSER;
GRANT INSERT, UPDATE, DELETE ON SCOTT.SALGRADE TO NEWUSER;
From here you can copy paste the query to .sql file and run it from there or you can spool the file, edit
the spooled file and then run it as follows. We are naming our file as newuser.sql, LOCATED IN C:\HARRY
FOLDER.
SQL> @c:\harry\newuser.sql
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
=======================================
NOW,
SQL> DESC DBA_TAB_PRIVS
Name Null? Type
----------------------------------------------------- -------- -------------
GRANTEE NOT NULL VARCHAR2(30)
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
GRANTOR NOT NULL VARCHAR2(30)
PRIVILEGE NOT NULL VARCHAR2(40)
GRANTABLE VARCHAR2(3)
HIERARCHY VARCHAR2(3)
SQL> COLUMN OWNER FORMAT A10
SQL> COLUMN TABLE_NAME FORMAT A10
SQL> COLUMN PRIVILEGE FORMAT A10
SQL> COLUMN GRANTEE FORMAT A10
SQL> SELECT ROWNUM, GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE FROM DBA_TAB_PRIVS WHERE OWNER = 'SCOTT';
ROWNUM GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
---------- ---------- ---------- ---------- ---------- ----------
1 HELLO SCOTT DEPT SCOTT DELETE
2 HELLO SCOTT DEPT SCOTT INSERT
3 HELLO SCOTT DEPT SCOTT SELECT
4 HELLO SCOTT DEPT SCOTT UPDATE
5 HELLO SCOTT EMP SCOTT DELETE
6 HELLO SCOTT EMP SCOTT INSERT
7 HELLO SCOTT EMP SCOTT SELECT
8 HELLO SCOTT EMP SCOTT UPDATE
9 HELLO SCOTT BONUS SCOTT DELETE
10 HELLO SCOTT BONUS SCOTT INSERT
11 HELLO SCOTT BONUS SCOTT SELECT
12 HELLO SCOTT BONUS SCOTT UPDATE
13 HELLO SCOTT SALGRADE SCOTT DELETE
14 HELLO SCOTT SALGRADE SCOTT INSERT
15 HELLO SCOTT SALGRADE SCOTT SELECT
16 HELLO SCOTT SALGRADE SCOTT UPDATE
17 NEWUSER SCOTT DEPT SCOTT DELETE
18 NEWUSER SCOTT DEPT SCOTT INSERT
19 NEWUSER SCOTT DEPT SCOTT UPDATE
20 NEWUSER SCOTT EMP SCOTT DELETE
21 NEWUSER SCOTT EMP SCOTT INSERT
22 NEWUSER SCOTT EMP SCOTT UPDATE
23 NEWUSER SCOTT BONUS SCOTT DELETE
24 NEWUSER SCOTT BONUS SCOTT INSERT
25 NEWUSER SCOTT BONUS SCOTT UPDATE
26 NEWUSER SCOTT SALGRADE SCOTT DELETE
27 NEWUSER SCOTT SALGRADE SCOTT INSERT
28 NEWUSER SCOTT SALGRADE SCOTT UPDATE
28 rows selected.
SQL> SELECT ROWNUM, GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE,
ROW_NUMBER() OVER(PARTITION BY GRANTEE,TABLE_NAME ORDER BY PRIVILEGE)RNK_PART
FROM DBA_TAB_PRIVS WHERE OWNER = 'SCOTT'
ROWNUM GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE RNK_PART
---------- ---------- ---------- ---------- ---------- ---------- ----------
9 HELLO SCOTT BONUS SCOTT DELETE 1
10 HELLO SCOTT BONUS SCOTT INSERT 2
11 HELLO SCOTT BONUS SCOTT SELECT 3
12 HELLO SCOTT BONUS SCOTT UPDATE 4
1 HELLO SCOTT DEPT SCOTT DELETE 1
2 HELLO SCOTT DEPT SCOTT INSERT 2
3 HELLO SCOTT DEPT SCOTT SELECT 3
4 HELLO SCOTT DEPT SCOTT UPDATE 4
5 HELLO SCOTT EMP SCOTT DELETE 1
6 HELLO SCOTT EMP SCOTT INSERT 2
7 HELLO SCOTT EMP SCOTT SELECT 3
8 HELLO SCOTT EMP SCOTT UPDATE 4
13 HELLO SCOTT SALGRADE SCOTT DELETE 1
14 HELLO SCOTT SALGRADE SCOTT INSERT 2
15 HELLO SCOTT SALGRADE SCOTT SELECT 3
16 HELLO SCOTT SALGRADE SCOTT UPDATE 4
23 NEWUSER SCOTT BONUS SCOTT DELETE 1
24 NEWUSER SCOTT BONUS SCOTT INSERT 2
25 NEWUSER SCOTT BONUS SCOTT UPDATE 3
17 NEWUSER SCOTT DEPT SCOTT DELETE 1
18 NEWUSER SCOTT DEPT SCOTT INSERT 2
19 NEWUSER SCOTT DEPT SCOTT UPDATE 3
20 NEWUSER SCOTT EMP SCOTT DELETE 1
21 NEWUSER SCOTT EMP SCOTT INSERT 2
22 NEWUSER SCOTT EMP SCOTT UPDATE 3
26 NEWUSER SCOTT SALGRADE SCOTT DELETE 1
27 NEWUSER SCOTT SALGRADE SCOTT INSERT 2
28 NEWUSER SCOTT SALGRADE SCOTT UPDATE 3
28 rows selected.
=======================================================
Now say you have user that is locked OR NEEDS TO BE LOCKED, then use use
SQL> ALTER USER USERNAME ACCOUNT UNLOCK/LOCK;
To create a user,
SQL> CREATE USER USERNAMME IDENTIFIED BY PASSWORD;
SQL> GRANT CREATE SESSION, RESOURCE TO USERNAME;
SQL> ALTER USER USERNAME
DEFAULT TABLESPACE USERS;
SQL> ALTER USER USERNAME
DEFAULT TEMPORARY TABLESPACE TEMP;
SQL> ALTER USER USERNAME
QUOTA UNLIMITED ON USERS;
SQL> ALTER USER USERNAME
QUOTA 50M ON USERS;
SQL> SELECT ROWNUM, ROLE FROM DBA_ROLES;
ROWNUM ROLE
---------- ------------------------------
1 CONNECT 2 RESOURCE 3 DBA
4 SELECT_CATALOG_ROLE
5 EXECUTE_CATALOG_ROLE
6 DELETE_CATALOG_ROLE
7 EXP_FULL_DATABASE
8 IMP_FULL_DATABASE
9 RECOVERY_CATALOG_OWNER
10 GATHER_SYSTEM_STATISTICS
11 LOGSTDBY_ADMINISTRATOR
12 AQ_ADMINISTRATOR_ROLE
13 AQ_USER_ROLE
14 GLOBAL_AQ_USER_ROLE
15 SCHEDULER_ADMIN
16 HS_ADMIN_ROLE
17 AUTHENTICATEDUSER
18 OEM_ADVISOR
19 OEM_MONITOR
20 WM_ADMIN_ROLE
21 JAVAUSERPRIV
22 JAVAIDPRIV
23 JAVASYSPRIV
24 JAVADEBUGPRIV
25 EJBCLIENT
26 JAVA_ADMIN
27 JAVA_DEPLOY
28 CTXAPP
29 XDBADMIN
30 XDBWEBSERVICES
31 OLAP_DBA
32 OLAP_USER
33 MGMT_USER
33 rows selected.
Comments
Post a Comment