Tag Info

Hot answers tagged

14

Easier to use the --single-transaction switch: mysqldump --single-transaction -u username -p db > db.sql


14

List all users who have been assigned a particular role -- Change 'DBA' to the required role select * from dba_role_privs where granted_role = 'DBA' List all roles given to a user -- Change 'PHIL@ to the required user select * from dba_role_privs where grantee = 'PHIL'; List all privileges given to a user select lpad(' ', 2*level) || granted_role ...


13

What I think is that the database you are trying to dump contains procedures/methods that were defined by a user while logged in as root@'foobar'. Now the solution is that you have to replace the definer's for that procedures/methods then you can generate the dump without the error. you can do this like .. UPDATE `mysql`.`proc` p SET definer = ...


13

postgres=> \l Liste der Datenbanken Name | Eigentümer | Kodierung | Sortierfolge | Zeichentyp | Zugriffsprivilegien ----------------+------------+-----------+--------------+------------+----------------------- postgres | postgres | UTF8 | de_AT.utf8 | de_AT.utf8 | template0 | ...


10

The quickest solution would just be to re-create the definer so it does exist, as long as it doesn't create any conflicts with existing users. CREATE USER 'root'@'foobar';


7

Yes, a role is a collection of system and/or object privileges. It simplifies privilege management by allowing you to manage bundles of privileges.


7

Perhaps you are looking for SYSTEM_PRIVILEGE_MAP?


7

This error says that the user doesn't have quota on tablespace SYSTEM which is set as the default persistent tablespace. You can assign a user the quota like this: sql> alter user scott quota 50m on system; Here, 50m means that the user quota on the SYSTEM tablespace is 50 mebibytes. You can also set the quota to unlimited. However it is a bad ...


7

You've granted CREATE, CONNECT, and TEMPORARY privileges on the database to myuser but you haven't granted SELECT and INSERT table privileges yet. You'll need something like: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser; In addition you need privileges on sequences if you have any serial columns or other column defaults drawing from ...


7

Create stored procedures that do these tasks for them - these can be set to execute as owner and the user account can just be granted execute rights on the stored procedures. It means they'll have to learn the interface to your stored procedures (so document them well or give them view definition rights to them also).


6

perhaps you mean listing users and their privileges for a database - I can't quite tell from the question: postgres=> \du List of roles Role name | Attributes | Member of -----------------+--------------+------------------------------------------------ dba | Create role | ...


6

You don't need to grant SYSDBA privileges, and shouldn't unless really necessary. You should follow the principle of least privilege. From Oracle's security guidelines: Do not provide database users or roles more privileges than are necessary. (If possible, grant privileges to roles, not users.) In other words, the principle of least privilege is ...


6

For this you need to grant select permission to user (MySQL User) on that particular column of table. GRANT SELECT (name) ON MyDb.User TO 'MySQLUser'@'MySQLHost'; For explanation have a look at MySQL Documentation on column Privileges


6

At this point, there's no right to grant, it's hardcoded to superuser. That's been discussed on the mailing list lately, and may change in 9.5 if someone finds the time to work on it. As a workaround, you can create a SECURITY DEFINER function that is owned by the superuser, and runs the query you want. This will allow non-superusers to see the contents of ...


5

While there is no privilege you can revoke, and I'd wonder why you can't just create another user, the answer to your question is.... Yes If you have the Enterprise Edition of Oracle you can use the Virtual Private Database feature to prevent tables from being selected. Here is an overview from the Oracle Database Security Guide 11g Release 2: Oracle ...


5

Because of deferred segment creation. In Oracle 11.2, when you create a table with no data, you no longer allocate any space in the tablespace. Oracle doesn't actually create the segment until you try to insert data into the table. This is a difference from earlier versions in which the segment was created when the table was created rather than when data ...


5

ALL_TAB_PRIVS is a superset of ALL_TAB_PRIVS_RECD. You probably want ALL_TAB_PRIVS as it includes tables that the current user owns.


5

Check for "CONNECT" rights SELECT SUSER_NAME(grantee_principal_id), * FROM sys.server_permissions WHERE type = 'COSQ' GO USE MYDB GO SELECT USER_NAME(grantee_principal_id), * FROM sys.database_permissions WHERE type = 'CO' To fix as needed USE master GO GRANT CONNECT SQL TO myLogin GO USE MYDB GO GRANT CONNECT TO MyUser GO


5

You need to grant the REFERENCES privilege on the reference table to user2. (See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.) Here's a demo: SQL> create user user1 identified by user1; User created. SQL> grant create session, create table, unlimited tablespace to user1; Grant ...


5

From here If you have the PROCESS privilege, you can see all threads. If you have the SUPER privilege, you can kill all threads and statements. Otherwise, you can see and kill only your own threads and statements. You can also use the mysqladmin processlist and mysqladmin kill commands to examine and kill threads. See here for privilege ...


4

I just realized -- so long as you don't mind locking out the user while you log in -- back up the mysql.user table (well, the user's hashed password, at the very least) set their password to something you know : UPDATE mysql.user SET password=PASSWORD('new password') WHERE user='username' AND host='hostname'; log in as them set their password back to what ...


4

Under the hood, when you see a user with USAGE only, that the user is written in the mysql.user table with all global privileges turned off. You originally stated the the user had this: grant usage on statistics.* to cptnotsoawesome@localhost identified by 'password'; You should see a row in mysql.user with the MD5 password and all globals privs set to ...


4

USAGE means that user doesn't have any privileges. You have to use 'DROP USER'. drop user cptnotsoawesome@localhost; You can't actually revoke USAGE, without dropping the user.USAGE is a global level privilege. have a look at This Link.


4

The comment by gbn brought me to the solution: No idea how it came, but root@localhost was lacking some privileges. So first obtain them all via UPDATE mysql.user SET XXX_priv = 'Y' WHERE user = 'root' AND host = 'localhost' There are quite a few columns, so using something like SELECT GROUP_CONCAT(column_name) FROM information_schema.columns WHERE ...


4

Oh my goodness, I think the problem stems from mixing the mysql schema of different mysql versions. First of all, run this query: desc mysql.user; For MySQL 5.6, you get 43 columns mysql> desc mysql.user; +------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type ...


4

Use the command supplied by @Gord for already existing objects. You will probably want to grant DEFAULT PRIVILEGES, too. So your administrator can access future objects automatically, too. Can be done per schema: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO administrator; If you omit the schema, it applies to the whole database: ...


4

The CONNECT privilege for PUBLIC looks somewhat special: \l template1 | postgres | UTF8 | Hungarian, Hungary | Hungarian, Hungary | postgres=CTc/postgres That is, user test (which is the member of PUBLIC only) cannot connect to template1: postgres=# \c template1 test Password for user test: FATAL: permission denied for database "template1" DETAIL: ...


4

It would typically be better to use a view to hide columns as needed and then GRANT permissions on the view. This decouples the table structure and permssions. If you change the table or recreate it, permissions will be lost. Or add a new column that now need separate permissions. a.k.a add some views to simplify the permission set hierarchy.


4

create table usr_t_user_reg (username varchar2(30), db_instance varchar2(30), primary key(username,db_instance) ) / CREATE OR REPLACE VIEW usr_v_user_not_reg AS SELECT username "User", db_instance "Instance", ( CASE WHEN username IN ( SELECT username FROM sys.dba_users MINUS SELECT ...


4

No, the wildcard for table names can only be *, and does not permit other characters or patterns. You could generate the 18 GRANT statements you need: SELECT CONCAT('GRANT SELECT,INSERT,UPDATE,DELETE ON prod.`', TABLE_NAME, '` TO ...;') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'prod' AND TABLE_NAME LIKE 'vs\_%'; Capture the output of that ...



Only top voted, non community-wiki answers of a minimum length are eligible