RE: Privileges for other's table
zhu chao, Thanks for checking this out. I tried it again this morning and it worked like your example. At first I thought there might be a difference when creating a view on all rows (*) versus creating the view on a subset (x, y, z) but under both conditions a view was created successfully. Of course, attempting to grant select on the view failed with a ORA-1720. May be this is an hidden feature of 8.1.7.3 on Tru64. Unfortunately, I still have the screen dumps so I can't tell my boss that I'm going crazy and need a extended vacation ;) Mike -Original Message- Hand, Michael T, hi, what you tested is not the normal behavior of oracle, neither the document said like that nor in my database behavior like that.Maybe something is wrong with your database:) Look: SQL conn internal Connected. SQL create user t identified by t; User created. SQL grant connect to t; Grant succeeded. SQL conn t/t Connected. SQL select count(*) from sys.obj$; select count(*) from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL conn internal Connected. SQL grant select on obj$ to t; Grant succeeded. SQL conn t/t Connected. SQL select count(*) from sys.obj$; COUNT(*) -- 26011 SQL create or replace view sysobj as select * from sys.obj$; View created. SQL select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production CORE8.1.7.0.0 Production TNS for Linux: Version 8.1.7.0.0 - Development NLSRTL Version 3.4.1.0.0 - Production Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net === 2002-09-09 16:23:00 ,you wrote£º=== Ok, I haven't had to deal with privileges much lately but this one had be stumped for a while, V8.1.7 Tru64. This is from an account with minimal privileges: SQL select count(*) from sys.obj$; select count(*) from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL connect sys Enter password: Connected. SQL grant select on obj$ to utility; Grant succeeded. SQL connect utility/ Connected. SQL select count(*) from sys.obj$; COUNT(*) -- 37742 SQL create view o_by_vlo as select name, obj# from sys.obj$; create view o_by_vlo as select name, obj# from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL connect sys Enter password: Connected. SQL grant select on obj$ to utility with grant option; Grant succeeded. SQL connect utility/ Connected. SQL create view o_by_vlo as select name, obj# from sys.obj$; View created. . . . So it would seem that with grant option is needed to create a view on another schema's tables. It took a little thinking on my part to realize that this makes sense as the view owner would be able to grant privileges on the underlying tables. Incidentally, I also found out that you can't use the grant option within roles. I wonder why? Michael Hand Polaroid Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
Privileges for other's table
Ok, I haven't had to deal with privileges much lately but this one had be stumped for a while, V8.1.7 Tru64. This is from an account with minimal privileges: SQL select count(*) from sys.obj$; select count(*) from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL connect sys Enter password: Connected. SQL grant select on obj$ to utility; Grant succeeded. SQL connect utility/ Connected. SQL select count(*) from sys.obj$; COUNT(*) -- 37742 SQL create view o_by_vlo as select name, obj# from sys.obj$; create view o_by_vlo as select name, obj# from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL connect sys Enter password: Connected. SQL grant select on obj$ to utility with grant option; Grant succeeded. SQL connect utility/ Connected. SQL create view o_by_vlo as select name, obj# from sys.obj$; View created. . . . So it would seem that with grant option is needed to create a view on another schema's tables. It took a little thinking on my part to realize that this makes sense as the view owner would be able to grant privileges on the underlying tables. Incidentally, I also found out that you can't use the grant option within roles. I wonder why? Michael Hand Polaroid Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Privileges for other's table
Hand, Michael T, hi, what you tested is not the normal behavior of oracle, neither the document said like that nor in my database behavior like that.Maybe something is wrong with your database:) Look: SQL conn internal Connected. SQL create user t identified by t; User created. SQL grant connect to t; Grant succeeded. SQL conn t/t Connected. SQL select count(*) from sys.obj$; select count(*) from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL conn internal Connected. SQL grant select on obj$ to t; Grant succeeded. SQL conn t/t Connected. SQL select count(*) from sys.obj$; COUNT(*) -- 26011 SQL create or replace view sysobj as select * from sys.obj$; View created. SQL select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production PL/SQL Release 8.1.7.0.0 - Production CORE8.1.7.0.0 Production TNS for Linux: Version 8.1.7.0.0 - Development NLSRTL Version 3.4.1.0.0 - Production Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] www.happyit.net === 2002-09-09 16:23:00 ,you wrote£º=== Ok, I haven't had to deal with privileges much lately but this one had be stumped for a while, V8.1.7 Tru64. This is from an account with minimal privileges: SQL select count(*) from sys.obj$; select count(*) from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL connect sys Enter password: Connected. SQL grant select on obj$ to utility; Grant succeeded. SQL connect utility/ Connected. SQL select count(*) from sys.obj$; COUNT(*) -- 37742 SQL create view o_by_vlo as select name, obj# from sys.obj$; create view o_by_vlo as select name, obj# from sys.obj$ * ERROR at line 1: ORA-00942: table or view does not exist SQL connect sys Enter password: Connected. SQL grant select on obj$ to utility with grant option; Grant succeeded. SQL connect utility/ Connected. SQL create view o_by_vlo as select name, obj# from sys.obj$; View created. . . . So it would seem that with grant option is needed to create a view on another schema's tables. It took a little thinking on my part to realize that this makes sense as the view owner would be able to grant privileges on the underlying tables. Incidentally, I also found out that you can't use the grant option within roles. I wonder why? Michael Hand Polaroid Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hand, Michael T INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = = = = = = = = = = = = = = = = = = = = -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
